Querying the database
So, we've established a connection to the MySQL server and selected a database to query. Next in line is executing the actual queries that will mine and manipulate the database tables. Let's assume that we have a table within the database
favorite_cars. The table was created as follows:
mysql>create table favorite_cars model char(15), horsepower INT, color char(6) );
This leaves us with three columns of information to manipulate.
All queries are handled via PHP's
mysql_query(query [, link_identifier]);
query parameter specifies the actual query that you would like to perform on the MySQL table. As was the case with previous commands, the
link_identifier is optional, used when there are multiple server connections open.
Let's demonstrate the
mysql_query() function by inserting some data into the
Example 6a Insertion of data:
<? // . . . establish MySQL server connection // . . . select database $table = "favorite_cars"; $query = "insert into $table values ("Ferrari", "155", "red")"; $result = mysql_query($query); if (! $result) : print "Could not insert data!"; endif; ?>
Whether or not you specify the query statement within a separate variable as I have done is simply a matter of style preference. You could just have easily done the following:
Example 6b Alternative usage of
<? . . . $result = mysql_query("insert into $table values ("Ferrari", "155", "red")"); . . . ?>
Just as we have used
mysql_query() to insert data, the same function would be used to mine data from the table.
Example 7 Mining data from a MySQL table:
<? . . . $query = "select * from $table where model = "Ferrari""; $result = mysql_query($query); . . . ?>
Many of you may be wondering where the data went when we queried the table in the above example. For the sake of simplicity, it is safe to assume that any results are sitting within the variable
$result. However, in order to display the data to the browser screen, we need to employ another MySQL function entitled
mysql_fetch_array(), in addition to making use of some basic PHP programming concepts.
mysql_fetch_array(result [, result_type]);
The result parameter is just the result returned by the
Example 8 Displaying data to the browser:
<? // . . . establish MySQL server connection // . . . select database $query = "select * from $table where model = "Ferrari""; $result = mysql_query($query); while ($row = mysql_fetch_array($result)) : print $row["model"]; print "<BR>"; print $row["horsepower"]; print "<BR>"; print $row["color"]; print "<BR>"; endwhile; ?>
First inserting data into the 'favorite_cars' table as was done in example 6, and then executing example 8, the following would be displayed to the browser:
Ferrari 155 red
Although there are other functions used to facilitate the display of data returned from a query, such as
mysql_result(), I personally find
mysql_fetch_array to be the easiest to use. In terms of performance, both functions are equally fast. Again, it's more a matter of style than anything else.
A heavenly match?
Well, I'm not sure that I'd go that far, but you can see the advantages of using PHP and MySQL together. They're each powerful in their own right, they're compatible together, and they're open source. They might not be a match made in heaven, but I definitely think we're looking at a long-term relationship here.
W.J. Gilmore has been developing PHP applications since 1997, and is frequently published on the subject within some of the Web's most popular development sites. He is the author of 'A Programmer's Introduction to PHP 4.0' (January 2001, Apress), and is the Assistant Editorial Director of Web and Open Source Technologies at Apress.
Discuss this article in the O'Reilly Network Forum.
Return to the O'Reilly Network Hub.