- Putting Content into Your Database with PHP
- Getting Content Out of Your Database with PHP
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Getting Content Out of Your Database with PHP
Okay, 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.
data_out.php
The basic PHP/MySQL logic is still the same to get data out of the database. You connect to the database server and log in, choose the database to use, and send SQL queries to the server to add, delete, and modify data—voila[as].
The script in Listing 2 also introduces you to the while statement. You'll use the while statement because it's not always definite about how much data your SQL queries will return. You may get 1 row of data from or query, or you might get 50. The while statement lets you go through all the data returned from your query and then stop when the data ends.
Because the basic concepts are the same, we'll spice up this next script with a few extra features to enhance the user's viewing experience. We'll give the user the choice of how he wants the information ordered, and we'll also give him the choice of which author's articles he wants to view.
Listing 2: data_out.php
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.php?orderby=date">Date</a>,
9. <a href="data_out.php?orderby=heading">Heading</a> or by
10. <a href="data_out.php?orderby=author">Author</a>.
11. <p>
12. <form action="data_out.php" 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
Here's a description of the lines in Listing 2:
|
8–10 |
These URLs send variables to PHP. These variables are used by the PHP script to determine which SQL statements it will send to the MySQL server. |
|
12–16 |
This form 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. |
|
20–21 |
These lines establish the connection to the MySQL server and select the database that is used for the queries. |
|
22–32 |
These lines 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–10 and the form in lines 12–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. |
|
33 |
The query must be done and the data is stored in $result. |
|
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 of returned from the query are "processed" by the mysql_fetch_array function. |
|
35–43 |
Each of the rows returned by the mysql_fetch_array function is printed out using the printf function. The columns of each row are referenced by their column name. |
|
44 |
The while loop ends after all the rows returned from the SQL query are processed. |
There's a lot more to PHP and MySQL interaction, but you've already gotten over the hardest part of learning how they work together once you've got these two scripts working.
More information on PHP's MySQL functions can be found at http://www.php.net/manual/en/ref.mysql.php.