|
|
 |
|
MySQL & PHP Manual - Build your own database driven web site!
|
Finally, a practical, down-to-earth guide to learning PHP & MySQL!
We hope you enjoy this sample chapter from "Build Your Own Database Driven Website Using PHP & MySQL" This book is only available from the SitePoint Website for US$34.95. It also comes with electronic access to all PHP and MySQL code samples used throughout the book.
Page - 1 - 2 - 3 - 4 - 5 - 6
You are on Page 3
Click here for previous page....
Handling SELECT Result Sets
For most SQL queries, the mysql_query function returns either true (success) or false (failure). For SELECT queries this just isn't enough. You'll recall that SELECT queries are used to view stored data in the database. In addition to indicating whether the query succeeded or failed, PHP must also receive the results of the query. As a result, when it processes a SELECT query, mysql_query returns a number that identifies a "result set", which contains a list of all the rows (entries) returned from the query. False is still returned if the query fails for any reason.
$result = @mysql_query("SELECT JokeText FROM Jokes");
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
Provided no error was encountered in processing the query, the above code will place a result set that contains the text of all the jokes stored in the Jokes table into the variable $result. As there's no practical limit on the number of jokes in the database, that result set can be pretty big.
We mentioned before that the while loop is a useful control structure for dealing with large amounts of data. Here's an outline of the code to process the rows in a result set one at a time:
while ( $row = mysql_fetch_array($result) ) {
// process the row...
}
The condition for the while loop probably doesn't much resemble the conditions you're used to, so let me explain how it works. Consider the condition as a statement all by itself:
$row = mysql_fetch_array($result);
The mysql_fetch_array function accepts a result set as a parameter (stored in the $result variable in this case), and returns the next row in the result set as an array. If you're not familiar with the concept of arrays, don't worry: we'll discuss it in a moment. When there are no more rows in the result set, mysql_fetch_array instead returns false.
Now, the above statement assigns a value to the $row variable, but at the same time the whole statement itself takes on that same value. This is what lets us use the statement as a condition in our while loop. Since while loops keep looping until their condition evaluates to false, the loop will occur as many times as there are rows in the result set, with $row taking on the value of the next row each time the loop executes. All that's left is to figure out how to get the values out of the $row variable each time the loop runs.
Rows of a result set are represented as arrays. An array is a special kind of variable that contains multiple values. If you think of a variable as a box that contains a value, then an array can be thought of as a box with compartments, where each compartment is able to store an individual value. In the case of our database row, the compartments are named after the table columns in our result set. If $row is a row in our result set, then $row["JokeText"] is the value in the JokeText column of that row. So here's what our while loop should look like if we want to print the text of all the jokes in our database:
while ( $row = mysql_fetch_array($result) ) {
echo("<p>" . $row["JokeText"] . "</p>");
}
To summarize, here's the complete code of a PHP Web page that will connect to our database, fetch the text of all the jokes in the database, and display them in HTML paragraphs. The code of this example is available as jokelist.php in the http://sitepoint.com/books/?bookid=MoreBooks code archive.
<html>
<head>
<title> Our List of Jokes </title>
<head>
<body>
<?php
// Connect to the database server
$dbcnx = @mysql_connect("localhost", "root", "mypasswd");
if (!$dbcnx) {
echo( "<p>Unable to connect to the " .
"database server at this time.</p>" );
exit();
}
// Select the jokes database
if (! @mysql_select_db("jokes") ) {
echo( "<p>Unable to locate the joke " .
"database at this time.</p>" );
exit();
}
?>
<p> Here are all the jokes in our database: </p>
<blockquote>
<?php
// Request the text of all the jokes
$result = @mysql_query("SELECT JokeText FROM Jokes");
if (!$result) {
echo("<p>Error performing query: " . mysql_error() . "</p>");
exit();
}
// Display the text of each joke in a paragraph
while ( $row = mysql_fetch_array($result) ) {
echo("<p>" . $row["JokeText"] . "</p>");
}
?>
</blockquote>
</body>
</html>
Inserting Data into the Database
In this section, we'll see how we can use all the tools at our disposal to allow visitors to our site to add their own jokes to the database. If you enjoy a challenge, you might want to try to figure this out on your own before you read any further. There is precious little new material in this section. It's mostly just a sample application of everything we've learned so far.
If you want to let visitors to your site type in new jokes, you'll obviously need a form. Here's the code for a form that will fit the bill:
<form action="<?=$PHP_SELF?>" method="post">
<p>Type your joke here:<br />
<textarea name="joketext" rows="10" cols="40" wrap></textarea><br />
<input type="submit" name="submitjoke" value="SUBMIT" /></p>
</form>
As we've seen before, this form, when submitted, will load the very same page (due to the use of the $PHP_SELF variable for the form's action attribute), but with two variables attached to the request. The first, $joketext, will contain the text of the joke as typed into the text area. The second, $submitjoke, will always contain the value "SUBMIT", which can be used as a sign that a joke has been submitted.
To insert the submitted joke into the database, we just use mysql_query to run an INSERT query, using the $joketext variable for the value to be submitted:
if ($submitjoke == "SUBMIT") {
$sql = "INSERT INTO Jokes SET
JokeText='$joketext',
JokeDate=CURDATE()";
if (@mysql_query($sql)) {
echo("<p>Your joke has been added.</p>");
} else {
echo("<p>Error adding submitted joke: " .
mysql_error() . "</p>");
}
}
The one new trick in this whole example appears in the SQL code here. Note the use of the MySQL function CURDATE() to assign the current date as the value of the JokeDate column to be inserted into the database. MySQL actually has dozens of these functions, but we'll only introduce them as required. For a complete function reference, refer to the MySQL Reference Manual.
Read on....
Page - 1 - 2 - 3 - 4 - 5 - 6
|
|
|
