First of all, we are going to set up the database table. We only need one, here it is.
- Code: Select all
CREATE TABLE quotes (
id INT(11) AUTO_INCREMENT NOT NULL,
quote TEXT NOT NULL,
author VARCHAR(50) NOT NULL,
DATE DATETIME NOT NULL,
PRIMARY KEY (id)
);
This will create a table in our database called quotes with the columns quote, author and a date added. Also, it has an id which is an auto_increment (it will increse automatically with each database insert). The PRIMARY KEY for our ID row is to guarantee that the number entered in the ID row is always unique, since all of our quotes are unique.
We also need a config file which will connect php to the database.
config.php
- Code: Select all
<?php
DEFINE ('DB_USER', 'username'); //This is your database username
DEFINE ('DB_PASSWORD', 'password'); //This is your database password
DEFINE ('DB_HOST', 'localhost'); //Databse host, this is nearly always localhost
DEFINE ('DB_NAME', 'db_name'); //Your database name
$db = @mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not connect to MySQL: ' . mysql_error());
@mysql_select_db (DB_NAME) OR die('Could not select the database: ' . mysql_error() );
?>
This connects to your database, please replace the values username, password and db_name with their proper values (and localhost if that is not your database host).
Ok, now we need a page to insert new quotes into the database. We'll call this page add.php. Firstly, we'll just add the basic html for the page.
add.php
- Code: Select all
<html>
<head>
<title>Add a new quote</title>
</head>
<body>
<form action="process.php" method="post">
Author:
<input type="text" name="author" size="30" />
Your Quote: <textarea name="quote"></textarea>
<input type="submit" name="submit" value="Submit" />
</form>
</body>
</html>
This creates a form with two fields, author and quote. When the form has been submitted, it will send all the data to process.php so we now need to create that page.
In this file, we will parse all the data and insert it into the database
process.php
- Code: Select all
<?php
//include the config file
include("config.php");
if(isset($_POST['submit'])){
$problem = FALSE;
if(empty($_POST['author'])){
$problem = TRUE;
echo "You did not enter a name for the author, please go back and add one.\n";
}
if(empty($_POST['quote'])){
$problem = TRUE;
echo "You did not enter a quote, please go back and add one.\n";
}
if(!$problem){
$quote = $_POST['quote'];
$author = $_POST['author'];
$query = @mysql_query("INSERT INTO quotes (quote, author, date) VALUES ('$quote', '$author', NOW())");
if($query){
echo "Congratulations, your quote has been added to the database. <a href=\"index.php\">Go back home?</a>.";
}
else
{
echo "There was an error with the query, please go back and try again.\n";
}
}
}
else
{
echo "You have not submitted the form. Please go back and fill it out.";
}
?>
Ok, so now I'll tell you what this does. First of all, it checks if the user has pressed the submit button and submitted the form. Next it creates a boolean variable called $problem. This is used in the error checking, if it equals TRUE then there is an error otherwise there isn't one. The next two if statements are checks to see if each field in the form is empty.
Then it checks if there are no errors (by checking if $problem is equal to FALSE). If there are none, it then goes on to assign the two form elements to their own variables and it also creates a variable which is a MySQL query which inserts the quote, author and date into the database.
Then it checks if the mysql_query has run. If it has, it displays a message telling the user that it has. The else statement is just displaying a message to the user if the mysql query has not run. Then the next few lines are closing all the ifs. The last else statement is just displaying a message to the user if there has been no form submitted.
Now we need a page to display all the quotes. We'll call this index.php (the default home page of a directory).
index.php
- Code: Select all
<?php
//include the config file
include("config.php");
$q = @mysql_query("SELECT quote, author, id, DATE_FORMAT(date, '%M %d, %Y') as qdate FROM quotes ORDER BY id DESC");
if(mysql_num_rows($q)){
while($f = mysql_fetch_array($q, MYSQL_ASSOC)){
$quote = nl2br($f['quote']);
echo "
<div style=\"border:1px #EAEAEA solid; padding:5px; margin-bottom:15px;\">\n";
echo "Submitted by " . $f['author'] . " on " . $f['qdate'] . ".\n";
echo $quote;
echo "</div>
\n";
}
}
else
{
echo "Could not retrieve data.\n";
}
echo "<a href=\"add.php\">Add your own quote?</a>\n";
?>
Ok, firstly, we include the connection to the database (config.php). Then we create a MySQL query to select the quote, author, date and id from the database. We order the by the id descending which means that the most recent will be first.
Then we check if the query has returned any data (mysql_num_rows) and if it has, we will move on the the next bit. Otherwise it will display a message to the user saying that no data could be retrieved.
Next, we loop through all the results that have been returned and create a box for them containing the author name, quote and date submitted. The variable $quote is set to create every new line in the variable $f['quote'] to a breaking space ( ).
That concludes the tutorial. In the next part, we will add some admin functions (delete and edit quotes).
Thankyou for reading.
