April 05, 2005
Querying a MySQL database with PHP - Random Quotes
Updated. Originally posted April 3, 2005.
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.
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:
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:
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 mysql_select_db 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)
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,
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:(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)
($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.
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.)
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:
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:
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:
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:
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.
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.
PHP and MySQL for Dynamic Web Sites - very useful book for learning about PHP and MySQL
LMT tutorial Converting to PHP
Using PHP and MT Includes - LMT tutorial
An Improvement to the rand() function - code snippet from ScriptyGoddess
Random Entries Using PHP - LMT tutorial
MT-SQL plugin - Brad Choate's MySQL plugin that gives you a tag that you can use to use to query your MySQL database directly with SELECT statements.
The DB and Dynamic Publishing - Arvind shows how to use EZSQL to more easily access the database and perform commands.
Has this tutorial been helpful? Please consider linking to Learning Movable Type at http://learningmovabletype.com/ . Thanks!
If you would like to send a trackback
please use the following URL: http://learningmovabletype.com/cgi-bin/mt32/mt-tb.cgi/419
For your further reading enjoyment, I have enhanced the layout of Carpe Bonum once again. Most noticeably, you are now looking at a three column layout. I wanted to show you more stuff near the top of the page than......[read more]
Tracked: April 15, 2005 08:59 AM