Here’s a quick tutorial for implementing a random quote on your site using php/ mysql. It’s not the most imaginitive script ever, but it’s a sound bit of learning for php noobs, and does have practical applications. It can be used for testimonials, or anything at all (ok, that’s a bit of an excessive claim, but it’s a bit useful). The techniques covered will definitely come in useful at some point in your programming career.
It may seem counter intuitive to use a database to randomise a few quotations, but I’m going to assume scalability is the issue at hand here, and that you’re going to end up with hundreds of quotes. Whatever justifies it will do! Besides, I’ll show you a quick php only script at the end for those who think a database is too much trouble.
First we’ll need a database called quote. Then you will need to set it up to take the entries like this:
<?php
//connect to mysql
mysql_connect(“localhost”,”yourusername”,”yourpassword”)or die(mysql_error());
//connect to database
mysql_select_db(“quote”)or die(mysql_error());
mysql_query(“CREATE TABLE quotes(
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
quotation TEXT,
author VARCHAR(165))”)
or die(mysql_error());
echo”table created!”;
?>
Easy! Feel free to mess with the paramaters there – some of you may not want to allow for massive quotations, so you may decide to use VARCHAR for quotation too. All depends on the verbosity of your sources!
So your database is set up, and your positively bursting with literary intent? Grand! Next part is to create a form to input the quotations, here it is, and it’s (almost) good old HTML
We’ll call it add_quote.php
<form action=”<?php echo $_SERVER['PHP_SELF']; ?>” method=”post”>
Quote<input type=”text” name=”quote”>
Author<input type=”text” name=”author”>
<input type=”submit” value=”add new quote”>
</form>
You’ve probably seen <?php echo $_SERVER['PHP_SELF']; ?> before? If not, it is just a way of telling the form that it should be processed by the document it lives in – by it’s SELF in other words. Ok, so we have a field to input the quote, and author – now we need to capture the data and squirt it into the database. Don’t forget to connect again, too:
//connect to mysql
mysql_connect(“localhost”,”yourusername”,”yourpassword”)or die(mysql_error());
//connect to database
mysql_select_db(“quote”)or die(mysql_error());
if ($_POST) {
$quote=$_POST['quote'];
$author=$_POST['author'];
//create new db entry
mysql_query(“INSERT INTO quotes(quotation,author) VALUES(‘$quote’,'$author’)”)or die(mysql_error());
}
Notice the if statement there? It prevents the form from capturing any data unless anything has been POSTed using the submit button. Next we assign the data to variables imaginitively called $quote and $author. The next bit I hope is self explanatory.
Finally on this page, we should show which quotes we have added already – so that we don’t accidentally duplicate any.
//get quotes from database
$query=”SELECT * FROM quotes”;
$result=mysql_query($query)or die(mysql_error());
//list quotes
while($row=mysql_fetch_array($result))
{
echo”<div><p>”.$row['quotation'].” ~ “.$row['author'].”</p></div>”;
}
Again, self explanatory stuff so simple even an army of monkeys could reproduce it! We SELECT everything (* means everything in SQL) FROM our table (quotes) and assign the output to a variable: $result. Then for each row ($row) in the results (mysql_fetch_array($result)) we output the quote and author ($row['quotation'] & $row['author']) and some appropriate HTML.
Job done mate – and for convenience I would put a link to your random quote page under your form to check it works:
<a href=”random_quote.php”>See a random quote!</a>
You’re nearly done – the next page random_quote.php uses some code we’ve already written but with important differences, so I’ll hold your hand till we reach the other side. Step one connect to the database:
<?php
//connect to mysql
mysql_connect(“localhost”,”yourusername”,”yourpassword”)or die(mysql_error());
//connect to database
mysql_select_db(“quote”)or die(mysql_error());
You’ll be a dab hand at connecting to mysql by now! The next part of the process is to choose a random quote, but in order to do so, we need to know how many quotes are avaiable. Here’s how we count them:
//count number of quotes in database
$num_quotes=”SELECT COUNT(*) FROM quotes”;
$count=mysql_query($num_quotes)or die(mysql_error());
This should be pretty simple to understand – the query asks simply to COUNT every enrty FROM the table quotes, then assign the result to $count. The weird this is, that $count doesn’t come back as a number on its own – if you were to echo it now, you’d get something like resource id=3. Weird, huh? So this next bit of code strips away the unnecessary and just leaves the number of quotes on its own.
//format the count
$count = floatval($count);
Now we have the actual number of quotes assigned to our var, we can choose one at random – a number between 1 and the total number of quotes.
//choose a random quote
$num = Rand (1,$count);
Now pull that quote out of the database on its own. Each time a quote is added, it is given an id number which mysql automatically assigns (check back to AUTO_INCREMENT when we created the table). So by finding the id field with the corresponding random number we have located our random quote!
//get chosen quote from database
$query=”SELECT * FROM quotes WHERE id=$num”;
$result=mysql_query($query)or die(mysql_error());
Now, print it on the screen already!
//display quote
while($row=mysql_fetch_array($result))
{
echo”<div><p>”.$row['quotation'].” ~ “.$row['author'].”</p></div>”;
}
?>
And you’re done! Easy, wasn’t it? Here’s the two files in full for all you copy and pasters out there.
add_quote.php
<form action=”<?php echo $_SERVER['PHP_SELF']; ?>” method=”post”>
Quote<input type=”text” name=”quote”>
Author<input type=”text” name=”author”>
<input type=”submit” value=”add new quote”>
</form>
<a href=”random_quote.php”>See a random quote!</a>
<?php
//connect to mysql
mysql_connect(“localhost”,”root”,”harlequin”)or die(mysql_error());
//connect to database
mysql_select_db(“quote”)or die(mysql_error());
if ($_POST) {
$quote=$_POST['quote'];
$author=$_POST['author'];
//create new db entry
mysql_query(“INSERT INTO quotes(quotation,author) VALUES(‘$quote’,'$author’)”)or die(mysql_error());
}
//get quotes from database
$query=”SELECT * FROM quotes”;
$result=mysql_query($query)or die(mysql_error());
//list quotes
while($row=mysql_fetch_array($result))
{
echo”<div><p>”.$row['quotation'].” ~ “.$row['author'].”</p></div>”;
}
?>
random_quote.php
<?php
//connect to mysql
mysql_connect(“localhost”,”root”,”harlequin”)or die(mysql_error());
//connect to database
mysql_select_db(“quote”)or die(mysql_error());
//count number of quotes in database
$num_quotes=”SELECT COUNT(*) FROM quotes”;
$count=mysql_query($num_quotes)or die(mysql_error());
//choose a random quote
$count = floatval($count);
$num = Rand (1,$count);
//get chosen quote from database
$query=”SELECT * FROM quotes WHERE id=$num”;
$result=mysql_query($query)or die(mysql_error());
//display quote
while($row=mysql_fetch_array($result))
{
echo”<div><p>”.$row['quotation'].” ~ “.$row['author'].”</p></div>”;
}
?>Here’s a quick tutorial for implementing a random quote on your site using php/ mysql. It’s not the most imaginitive script ever, but it’s a sound bit of learning for php noobs, and does have practical applications. It can be used for testimonials, or anything at all (ok, that’s a bit of an excessive claim, but it’s a bit useful). The techniques covered will definitely come in useful at some point in your programming career.
This post has flown! Read it here http://www.webhelpforum.co.uk/featured-tutorials/php-mysql-random-quotes
Filed under: php
