« Text Wrap | Main | Random Entries Using PHP »

Querying a MySQL database with PHP - Random Quotes

If you are using a MySQL database and your blog is PHP enabled (see Converting to PHP), you can use PHP scripts to pull blog data from your database to add customizations to your blog. For example, say you would like to have on your sidebar a quote, randomly pulled from a selection of quotes, with a new quote displayed every time you refresh the page. You can accomplish this by creating a separate weblog for quotes, then using a simple PHP script to pull the data from your MySQL database, and using a PHP include to put the script results into your main weblog page.

PHP scripts are a level of difficulty up from Movable Type, and require careful study to understand. I recommend a book by Larry Ullman called PHP and MySQL for Dynamic Web Sites. Somehow, without reading this book, I managed to get a PHP script to work that I found somewhere on the MT forums. However, I had no idea how it worked. So after reading the book on a long airplane flight and comparing what I learned to the script I had, I created a new, simplified script to generate random quotations. There are several other, probably easier, methods to generate random quotations. The script detailed here is an example of how a PHP script that queries a MySQL database functions and should be regarded in that context.

Here is an example of the script in action. Refresh the browser to see a new quote.


Anatomy of a Simple PHP Script

Here is a PHP script that I use to generate the random quotations shown above. I will be referring to this script throughout the tutorial.

<?php

define ('HOSTNAME', 'localhost_or_hostname');
define ('USERNAME', 'username');
define ('PASSWORD', 'database_password');
define ('DATABASE_NAME', 'database_name');

$db = mysql_connect(HOSTNAME, USERNAME, PASSWORD) or die ('I cannot connect to MySQL.');

mysql_select_db(DATABASE_NAME);

$query = "SELECT entry_title,entry_text FROM mt_entry WHERE (entry_blog_id=1) and (entry_status=2) ORDER by rand() LIMIT 1";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
echo "<p>" , ($row['entry_title']) , "</p> \n <p>" , nl2br($row['entry_text']) , "</p>";
}

mysql_free_result($result);
mysql_close();
?>

1. A few general notes

PHP scripts begin with <? or <?php. They end with ?>. By the way, if you are doing dynamic publishing with MT, you must begin your PHP script with <?php.

A semi-colon indicates the end of a statement. You can use regular line breaks to format your script so that it is easier to understand; PHP will ignore those line breaks. PHP will not ignore a semi-colon however, and will interpret it as the end of statement.

In a PHP script, variables begin with a $.

You can use Movable Type to create a new Index template that holds your PHP script. In your weblog template menu, choose create new index template. Put the script code in the template body. Name the template and create the name of the output file. I call this script "random-quote.php". Unselect the rebuild automatically option.

2. Connecting to the database

The first lines of the script connect the script to your MySQL database:

define ('HOSTNAME', 'localhost_or_hostname');
define ('USERNAME', 'username');
define ('PASSWORD', 'database_password');
define ('DATABASE_NAME', 'database_name');

$db = mysql_connect(HOSTNAME, USERNAME, PASSWORD) or die ('I cannot connect to the database.');

mysql_select_db(DATABASE_NAME);

HOSTNAME, USERNAME, PASSWORD, and DATABASE_NAME are all constants that you must define at the beginning of a PHP script that connects to your database. Your Movable Type configuration script - mt.cfg - will have the database name (Database) and the user name (DBUser). The Movable Type database password file - mt-db-pass.cgi - will contain your MySQL database password. If your database resides on the same server as the PHP script you intend to run (and in most cases it does), you should be able to simply use the word "localhost". If not, you can specify the name of the server on which the database resides, e.g. tomato.he.net.

So, if my database name were mtdatabase, my username elise, my password 1234567abc, and the database were running on the same server as the script, I would write this part of the PHP script to look like:

define ('HOSTNAME', 'localhost');
define ('USERNAME', 'elise');
define ('PASSWORD', '1234567abc');
define ('DATABASE_NAME', 'mtdatabase');

$db = mysql_connect(HOSTNAME, USERNAME, PASSWORD) or die ('I cannot connect to MySQL.');

mysql_select_db(DATABASE_NAME);

The next variable defined, $db, will invoke the mysql_connect command to connect to the MySQL database. If the hostname, username, or password were entered incorrectly, the script will "die" and you will get the error message, "I cannot connect to MySQL".

The last line in this section uses the mysqlselectdb command to select the specific database to which you want to connect.

To see if you have created this section of your script properly, end the script here, with a closing ?> tag. Run the script by pointing to the file from your browser. If it running properly, you should see a blank page. If not, you should get an error message.

3. Creating a MySQL Query Statement

MySQL responds to a set of specific commands that you can use to "query", or ask a question, of the database. (MySQL manual)

$query = "SELECT entry_title,entry_text FROM mt_entry WHERE (entry_blog_id=1) and (entry_status=2) ORDER by rand() LIMIT 1";

In this line we define the variable $query as the specific query we want to run on our database. In this case the query is pretty self explanatory. SELECT selects what fields we want, in our case the entry title and the entry text. FROM determines the table in the database we want, in this case the mt_entry table. WHERE is a conditional which narrows the choices. I want the entries from blog number 1 (or some other blog ID, if you have more than one blogs running on the same MT installation), and I want the entry status to be "2", which in Movable Type means "published". I would like the entries and titles returned to me in a random order, so I use the command rand() with the query command ORDER. I only want to pull up one record at a time, so my LIMIT is 1.

4. Querying the Database and Displaying the Results

mysql_query is the PHP function that initiates a query from a MySQL database. (PHP manual) The script line,

$result = mysql_query($query);

will define the variable $result as the initiation of the query that uses the parameters defined previously in the variable $query.

mysql_fetch_array() takes the query result variable ($result) and returns one row of data at a time, in an array format. (PHP manual) We will want to use a loop so the function will continue to access every returned row as long as there are more rows to be read. To do this we use a while construct, like so:

while ($row = mysql_fetch_array($result)) {
// Do something with the variable $row
}

(PHP manual)

What we will do with the $row variable is to use the echo function to display html of the results that can be interpreted by a browser. (PHP manual)

echo "<p>" , ($row['entry_title']) , "</p> \n <p>" , nl2br($row['entry_text']) , "</p>";

($row['entry_title']) returns the entry title and ($row['entry_text']) the entry text. The "<p>" and "</p>" tags place the title and entry text into paragraph tags. \n is a PHP function that generates a new line in the HTML source, making the source easier to read. It doesn't affect the final presentation of the web page. With the echo function, you can use commas to string together multiple chunks of data to be displayed in the web browser.

If you have Text Formatting set to the default position of converting line breaks in your blog Edit Entry, the MySQL database won't include the line breaks tags in the edit entry. To display the line breaks, use the PHP function nl2br before ($row['entry_text']). (Note that the "l" in nl2br is a lower case "L".) (PHP manual)

5. Finishing Up the Script

mysql_free_result($result); and mysql_close(); are optional, but they are considered good programming form. mysql_free_result($result); frees up querying resources once you are done with mysql_fetch_array(). mysql_close(); closes the connection to the database.

End the script with a ?> on the last line.

Review your code. Remember that every line of code must end with a semicolon.

6. Security

You can run the script as written. If the script file name has a .php extension, then everything between the <?php and the ?> will be hidden from the source. However, if you inadvertently save the file as a .inc file, everything in the script can be visible, including your MySQL access information.

It is considered more secure to remove the database access information all together and put it into a separate script that will reside outside of your public html directory. Create a new script called mysql_connect.php. (Populate the constants with your connection information as previously described.)

<?php

define ('HOSTNAME', 'localhost_or_hostname');
define ('USERNAME', 'username');
define ('PASSWORD', 'database_password');
define ('DATABASE_NAME', 'database_name');

$db = mysql_connect(HOSTNAME, USERNAME, PASSWORD) or die ('I cannot connect to MySQL.');

mysql_select_db(DATABASE_NAME);

?>

Using FTP, put this file in your root directory, outside of the public html directory.

Replace this section on your random-quote.php script with the following:

require_once ('../mysql_connect.php');

You may need to adjust the reference to the connect script depending on where you have placed it on your server. I had to use ('../../mysql_connect.php').

Now your random-quote.php script should look like this:

<?php

require_once ('../mysql_connect.php');

$query = "SELECT entry_title,entry_text FROM mt_entry WHERE (entry_blog_id=1) and (entry_status=2) ORDER by rand() LIMIT 1";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {

echo "<p>" , ($row['entry_title']) , "</p> \n <p>" , nl2br($row['entry_text']) , "</p>";
}

mysql_free_result($result);
mysql_close();
?>

7. Using a PHP Include to include the script on to a web page.

The last step is to include the output of the PHP script on to the web page where you want the results of the script to appear. (Note that PHP includes only work on web pages that have a php extension. See Converting to PHP.)

In the example shown at the beginning of this tutorial, the code that is placed in the blog entry, to pull in the random quotes, takes this format:

<?php include("/path/to/your/random-quote-script.php"); ?>

or

<?php include("http://www.yourdomain.com/path-blog/random-quote-script.php"); ?>

If I had created a new index template in the LMT blog and used that template for the random quote PHP script, the path might look something like this:

<?php include("/home/elise/public_html/mt/random-quote-script.php"); ?>

or

<?php include("http://www.elise.com/mt/random-quote-script.php"); ?>

Place the PHP include code on the template or in the blog entry where you would like the script results to appear. Rebuild the template or blog entry.

Final Notes

This tutorial is meant to be a simplified introduction to understanding PHP scripts. I am not an expert in PHP. Because my brain doesn't hold on to details that well - in one ear and out the other - I write down notes like these to remind myself the next time I want to tackle a PHP script. I welcome comments from those of you who really do understand PHP and MySQL who have a remark that would be beneficial to the understanding of those of us who are beginners. To those of you with specific questions about your script implementation, unfortunately, I can't help you. I have found the MT Support Forums to be helpful regarding PHP scripts and their interaction with Movable Type, so I refer you to the forums.

Links:

Comments (6)

Danny:

This tutorial is excellent, and easy to understand.

The only problem I am having is that it only selects the first record in my database, not a random row?

Has anyone else had this problem?

Thanks

Hi Eric, I don't know of any.

Hi Danny, that's odd. You might look at Scripty Goddess' tip regarding the random function: http://www.scriptygoddess.com/archives/2004/06/02/random-with-mysql/. I haven't had to try this as the rand() function seems to work fine for me.

Hey,

Just wanna thanks for all the tutorials you've written in the past. I read a lot of them during my journey to learn movable type.

I was just in the process of trying to use this plugin when i noticed something bizarre. I want to call some Customfields data from the database. But I can't find it anywhere? Do you have any ideas on this? I'm using customfields 1.21 and mt 3.31.

Hi Dennis, CustomFields data is a bit harder to find and extract. They are stored in the mt_plugindata table and to access that data in PHP, I suggest you read this tutorial for more information.

Rich:

EXCELLENT TUTORIAL! I was able to follow and understand this so easily It worked from the very first. Love to see more of these as I'm a bit slower on the uptake these days.

vijith:

how to define two database at atime
define ('DATABASE_NAME', 'database_name');
here in this DATABASE_NAME contain only one database how to define two database here to the "DATABASE_NAME"


Post a comment

(If you haven't left a comment here before, your comment may need to be approved before will appear on the entry. Thanks for waiting.)