- Good Things Come in Free Packages
- New Functions
- Installing MySQL
- SQL Queries
- Setting up a Simple Database
- Basic SQL Queries
- Putting Content into Your Database with PHP
- Getting Content out of Your Database with PHP
This chapter is from the book
This chapter is from the book
This chapter is from the book
Getting Content out of Your Database with PHP
OK, so you've got your PHP scripts set up to put as much data as you want into the database. Now what? Well, you could always go back to that not-so-user-friendly command-line interface that MySQL offers you and do select statements all day and night to see what's in there, or you could do it the easy way with PHP.
The basic PHP/MySQL logic is still the same to get data out of the database. You connect to the database server and login; choose the database to use; send SQL queries to the server to add, delete, and modify data—voila.
This script also introduces you to the while statement. You'll use the while statement because it's not always definite how much data your SQL queries will return. You may get one row of data from a query, or you might get fifty. The while statement lets you go through all the data returned from your query, then stops when the data ends. Examples of this are shown in Figures 7–3, 7–4, and 7–5.
){kind=link}
){kind=link}
){kind=link}
FIGURE 7–3 Initial load of Script 7-2
FIGURE 7–4 Script 7-2 after clicking on the Order by Author link
FIGURE 7–5 Script 7-2 displaying articles by author
Since the basic concepts are the same, we'll spice up Script 7-2, with a few extra features to enhance the user's viewing experience. We'll give the user the choice of how he or she wants the information ordered, and we'll also give the user the choice of which author's articles he or she wants to view.
Script 7-2 data_out.php3
1. <html>
2. <head>
3. <title>Getting Data out of the Database</title>
4. </head>
5. <body bgcolor="#FFFFFF">
6. <h1>The Daily News</h1>
7. Order news by
8. <a href="data_out.php3?orderby=date">Date</a>,
9. <a href="data_out.php3?orderby=heading">Heading</a> or by
10. <a href="data_out.php3?orderby=author">Author</a>.
11. <p>
12. <form action="data_out.php3" method="POST">
13. Or only see articles written by (<i>enter author name</i>):
14. <input type="text" name="author">
15. <input type="submit" name="submit" value="Submit!">
16. </form>
17. <table border="1" cellpadding="3">
18. <?php
19. /* This program gets news items from the database */
20. $db = mysql_connect("localhost", "root");
21. mysql_select_db("php3", $db);
22. if ($orderby == 'date'):
23. $sql = "select * from news order by 'date'";
24. elseif ($orderby == 'author'):
25. $sql = "select * from news order by 'author_name'";
26. elseif ($orderby == 'heading'):
27. $sql = "select * from news order by 'heading'";
28. elseif (isset($submit)):
29. $sql = "select * from news where author_name = '$author'";
30. else:
31. $sql = "select * from news";
32. endif;
33. $result = mysql_query($sql);
34. while ($row = mysql_fetch_array($result)) {
35. print("<tr><td bgcolor=\"#003399\"><b>");
36. printf("<font color=\"white\">%s</font></b></td></tr>\n",
37. $row["heading"]);
38. printf("<td>By: <a href=\"mailto:%s\">%s</a>\n",
39. $row["author_email"], $row["author_name"]);
40. printf("<br>Posted: %s<hr>\n",
41. $row["date"]);
42. printf("%s</td></tr>\n",
43. $row["body"]);
44. }
45. ?>
46. </table>
47. </body>
48. </html>
HOW THE SCRIPT WORKS
As you remember from previous chapters in the book, these URLs send variables to PHP. The variables in lines 8 through 10 are used by the PHP script to determine which SQL statements it will send to the MySQL server.
The form in lines 12 through 16 is used to send an optional query to show only those news items written by a single author. No values are returned if the user enters an author name that is not in the database.
Lines 20 and 21 establish the connection to the MySQL server and select the database that is used for the queries.
Lines 22 through 32 give the users some power over what kind of information they can get out of the database and how this information is displayed. The links in lines 8 through 10 and the form in lines 12 through 16 determine which of the if statements are used. The orderby variable drives how the data is displayed. If the orderby variable has no value and the Submit button was not pressed, then line 31 is used as the SQL query.
At line 33, the query must be done and the data is stored in $result.
At line 34, the mysql_fetch_array function grabs a row of all the data returned from the query. The while statement increments the row after each loop. The loop continues until all the rows returned from the query are processed by the mysql_fetch_array function.
With lines 35 through 43, each of the rows returned by the mysql_fetch_array function are printed out using the printf function. The columns of each row are referenced by their column name.
At line 44, the while loop ends after all the rows returned from the SQL query are processed.
Recap
The database lets you modularize your data into convenient chunks of information that are easy to manage, and it makes it easy for the users to get at the information they want.
Database integration with your Web site gives you a lot of flexibility on how you can display information. We could have done the same thing by writing the output of the data_in script to a text file. But if you did that, you couldn't selectively display only certain authors, nor could you change the order of the display. Now imagine that there were 1,000 news items to sort through. One large text file containing all that information would make it difficult for users to get at the information that they want.
But the best part is, database integration using PHP is quick and easy. As you saw in Script 7-1, it takes only a few lines of basic code to use a database with PHP.