This post is the third in a continuing series of DIY tutorials, which aims to make things as simple as possible.
The [Easy as Pie] series focuses on tasks that most people consider too challenging and presents it in a way that is easy (as pie!) to understand.
Introduction
In this post, you are going to have a fantastic time learning:
- How to create a MySQL Database
- How to create a table in a MySQL Database
- How to add data to a MySQL Database
- How to get data from a MySQL Database
- How to create a random quote generator
The good stuff
If you're like me, you learn really well from example. For this reason, I include the download for the random quote generator right here. Enjoy!
Download the Random Quote Generator source code
Let's get started!
Previous articles in this series
Creating a MySQL Database
I am going to use cPanel for this example, as it is what my host uses.
Step 1: Go into cPanel
Step 2: Click on 'MySQL® Databases'
Step 3: Find the input box where it says 'New Database:'
Step 4: Insert your database name, and click 'Create Database'
Step 5: Find 'Current Users:' and input a new username and password into the fields, then click create user
Step 6: Find 'Add Users To Your Databases:' Select your new database and username from the dropdowns, then hit 'Add User to Database'
Now you have a database set up!
From here, you can open PHPMyAdmin from the link at the bottom of the page, but I prefer using SQLyog Enterprise (free version available)
If you are going to be using SQLyog, you may have to add your IP into the 'Access Hosts:' section. You can get your IP from cmyip.com.
PLEASE BE AWARE OF THE SECURITY RISKS INVOLVED WITH ALLOWING REMOTE ACCESS TO YOUR DATABASE(S)
Creating a Table in a MySQL Database
Now we have transitioned into SQLyog, if you want to use PHPMyAdmin, you can go into the 'Query Window' that PHPMyAdmin provides, which is very similar to SQLyog's query input area, albeit much less verbose.
First, we have to decide the columns we are going to need. Because we are going to be making a random quote generator, we're going to need the following columns:
- id - this will automatically increment as entries are added
- quote - this will contain the quote
- author - this will contain the person who said the quote
The syntax for creating our table is:
CREATE TABLE `quotes` (
`id` int(11) NOT NULL auto_increment,
`quote` varchar(255),
`author` varchar(40),
PRIMARY KEY (`id`)
)
This will create a table named 'quotes' with our 3 columns.
id is set to NOT NULL as it will always have a value, and it will automatically increment (so long as we don't define a value)
quote is set to VARCHAR and is limited to 255 characters
author is also set to VARCHAR, but is limited to only 40 characters
Our primary key is set to id - this means that you cannot have two of the same id
To execute this in SQLyog, go to 'Edit' => 'Execute Query' => 'Execute Current Query' ('F5' or 'F9' depending on your version) after putting the query in the query area at the top.
While we are here, we can add a few entries to our newly-created table. We could do this with the WYSIWYG interface, but that's no fun, so here's how you add values using a query:
INSERT INTO `quotes` (`quote`,`author`) values ('A rose by any other name would smell as sweet','William Shakespeare')
By not defining the id column, it auto-fills with the value of 1.
Feel free to add a few more, though it's not necessary as we will be building a script to add them right about... now.
Adding data to a MySQL Database
We just learned the query to add data, now we just need to create an interface for it. I'm going to be using PHP for the backend code.
Before we begin, create a new page, and call it quotemanager.php
The first step will be creating the HTML form.
<form action="" method="post"> <fieldset> <legend>Add a Quote</legend> <label for="quote">Quote:</label> <input type="text" name="quote" id="quote" maxlength="255" /> <label for="author">Author:</label> <input type="text" name="author" id="author" maxlength="40" /> <input type="submit" value="Add Quote" /> </fieldset> </form>
Here's how that will look:
Then we have to do the PHP stuff.
Here's the code used to connect to a MySQL database:
$user="username";
$password="password";
$database="database";
$connection = mysql_connect('localhost',$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
Most of this should be pretty self-explanatory. The first 3 lines are simply declaring variables, then you run the mysql_connect() function which connects to the server using your credentials, then the next line is selecting the appropriate database, and if it is unable to do so, it spits out an error.
You may need to change 'localhost' to your domain if you are using an external database.
Now, we are going to create the PHP code that will grab the form values on submit and add it to the database.
<?php
if ($_REQUEST['quote'] != "") {
if($_REQUEST['author'] != "") {
$author = $_REQUEST['author'];
} else {
$author = "Anonymous";
}
$quote = $_REQUEST['quote'];
$query="INSERT INTO `quotes` (`quote`,`author`) values ('" . mysql_real_escape_string($quote) . "','" . mysql_real_escape_string($author) . "')";
$result = mysql_query($query) or die(mysql_error());
echo("inserted quote: " . htmlentities($quote) . " by " . htmlentities($author) . " into database");
} else {
echo("<p>Please enter a quote and author</p>");
}
?>
Here's what this script is doing:
if ($_REQUEST['quote'] != "") {
This means "if there is a quote specified (quote does not equal nothing) then continue on."
if($_REQUEST['author'] != "") {
$author = $_REQUEST['author'];
} else {
$author = "Anonymous";
}
This means "if there is an author specified, use that value, otherwise (else) define author as 'Anonymous'."
$query="INSERT INTO `quotes` (`quote`,`author`) values ('" . mysql_real_escape_string($quote) . "','" . mysql_real_escape_string($author) . "')";
This defines the query we learned earlier
The mysql_real_escape_string() escapes the string so you aren't opened up to SQL Injection vulnerabilities.
Don't forget to escape the user input!
$result = mysql_query($query) or die(mysql_error());
This actually runs the query, and spits out an error if it fails
echo("inserted quote: " . htmlentities($quote) . " by " . htmlentities($author) . " into database");
This outputs what we put into the database
The htmlentities() converts things like < to <, so you don't run into XSS vulnerabilities.
} else {
echo("<p>Please enter a quote and author</p>");
}
This means that there was no quote specified, so it asks for input from the person viewing the page.
So that's it, now we can add all the data we want into our quotes table. But really, that's not all that helpful, we need to have a way to output it!
Retrieving data from the Database
We are now going to list all quotes in the database along with the author.
<?php
$query="SELECT `quote`, `author` FROM `quotes`";
$result=mysql_query($query) or die(mysql_error());
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {
$quote = htmlentities(mysql_result($result,$i,"quote"));
$author = htmlentities(mysql_result($result,$i,"author"));
echo("<blockquote>" . $quote . " ~ <cite>" . $author . "</cite></blockquote>");
$i++;
}
mysql_close($connection);
?>
This is what is going on:
$query="SELECT `quote`, `author` FROM `quotes`"; $result=mysql_query($query) or die(mysql_error());
Look familiar? This is the select query which selects the quote and author columns out of the quotes table, or more accurately, selects the values therein.
$num=mysql_numrows($result);
This sets $num to the number of rows that are returned by the select query. So for instance, if you have 9 quotes in the table, the number will be... you guessed it -- 9!
$i=0;
Just setting the $i variable to 0, as we are about to loop through the results, and need a counter
while ($i < $num) {
This is the beginning of a while loop. Each iteration, it checks for a condition (in this case, it checks that $i is less than the number of results (which is stored in $num))
$quote = htmlentities(mysql_result($result,$i,"quote")); $author = htmlentities(mysql_result($result,$i,"author"));
In order to use the values in the loop, we have to define them in this way. The syntax being used is:
- mysql_result() - function
- $result - Which resultset to use
- $i - Which row you want to pull (as this increments, it will pull each row)
- "quote"/"author" - Specified which field/column to pull from
$i++;
This means "Increment $i by 1 (The same could be written $i = $i + 1; or $i+=1;)
mysql_close($connection);
As we are no longer using the MySQL Connection, we can close it here.
That's it for getting data out of a database - pretty straightforward! Now on to the fun stuff!
Making a random quote generator
Alright, if you have read this far and retained around 15% of it, you are scientifically proven to be a genius. Great job!
We're going to create a random quote generator, using the scripts we have already made
The first thing we need to do is create a new page, where the quote will show up. We'll call this page quote.php, because I'm feeling adventurous.
Here's the code we're going to put on this page:
<h1>Quote of the moment</h1>
<?php
$user="username";
$password="password";
$database="database";
$connection=mysql_connect('localhost',$user,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT `quote`, `author` FROM `quotes` ORDER by rand() LIMIT 1";
$result=mysql_query($query) or die(mysql_error());
$quote = htmlentities(mysql_result($result,$i,"quote"));
$author = htmlentities(mysql_result($result,$i,"author"));
echo("<blockquote>" . $quote . " ~ <cite>" . $author . "</cite></blockquote>");
mysql_close($connection);
?>
Alright, so the only new thing is:
$query="SELECT `quote`, `author` FROM `quotes` ORDER by rand() LIMIT 1";
Here is our query for randomly selecting one of the quotes and authors out of the database. The rand() means random, and will generate a random column to pull from each time it is run
The LIMIT 1 means just that, limit the query to one result.
That's all you need for your random quote generator! By only slightly modifying this code, you can create a plethora of mini-applications, such as a random image generator, random trivia, etc. The possibilities, as they say, are endless.
Download the Random Quote Generator source code.
That's all, folks
And so concludes the article. If you enjoyed it, please leave a comment. Don't forget to subscribe to the RSS feed for future articles.
