Intro to Databases - Sending a Query to the Database and Getting the Results
(Page 6 of 8 )
Now is where all the earlier chapters come into play - sending a query to the database. See there was a method to my madness. It is actually ridiculously easy - the real power comes from the SQL. Remember any valid SQL statement can be sent as long as the user has the permissions to do it. So you can create tables, Insert, Update, Delete, more as long as you can write the SQL for it. Amazing!
As for retreiving the the information returned from the database - there are a couple of flavors for it. We will discuss getting the results as an associative array (keys of the array are the database column names), an ordered array (keys of the array are numerical), or an object. So what is the difference? Basically - just the way you access them (not the data itself).
A quick note again on security - Databases usually hold all the data that a person of evil intent wants ( personal information for identity theft, or credit card information, passwords, and more) so they are usually what a hacker wants to get at. So if the SQL uses user input in it - escape the data at the very least - Please! For more information - again please read Chris Shiflett's Security Workbook.
Natively
Time to send a query to the MySQL database. To do this we will be using the function mysql_query(). Now the thing to remember is that the mysql_query() returns a link to the results - not the results themselves. (FYI - Most database systems have a native function called *_query() but some may require you to prepare and execute a query instead. So if your system doesn't have the *_query function - look for *_prepare() and *_execute().)
To actually get the results from the query we need to use another function depending on the flavor we want to have for the results. To have the results returned as an associative array - we use the function mysql_fetch_assoc(). Results returned as an ordered array - use the function mysql_fetch_row(). And the results returned as an object- use the function mysql_fetch_object(). And that function will only return 1 row of the results - not all of them - so a loop will be needed to cycle through all the rows returned by the query.
<?php /* connect to the DB */ /* select the database */ /* write the SQL */ $sql = "SELECT foo, bar FROM table WHERE id = '$id' "; /* send the query */ $result = mysql_query($sql); /* check for an error with the query */ if (!$result) { /* if there is a problem with the query - tell me and display any error messages given then stop the program gracefully */ echo 'Error with the query. Message returned: ' . mysql_error(); die; }
/* here is where the different flavors are shown. In reality only one method would be shown/used. I will place all the various methods in the same situation (loop) and show how to access the results returned. You can choose which method works best for you. */ /* associative array */ /* get the results */ while ($row = mysql_fetch_assoc($result)) { /* access the results */ echo 'The value of foo is ' . $row['foo']; echo 'The value of bar is ' . $row['bar']; } /* ordered array */ /* get the results */ while ($row = mysql_fetch_row($result)) { /* access the results */ echo 'The value of foo is ' . $row[0]; echo 'The value of bar is ' . $row[1]; } /* object */ /* get the results */ while ($row = mysql_fetch_object($result)) { /* access the results */ echo 'The value of foo is ' . $row->foo; echo 'The value of bar is ' . $row->bar; } ?>
PEAR DB
Of course PEAR DB can do all these things too. Otherwise I wouldn't have chosen to show them to you. How you ask... well this will get a little technical so hold on... By using the method query from the DB object returned by the connect call earlier. (Again - the database system may require the query to run through the prepare and execute rather then the query method.) This will return a new DB_result object and with that you can call the fetchRow method to get the actual data. And how do you choose between getting an associative array, an ordered array, and an object? Good question. Answer - with the DB object method setFetchMode.
Have I scared you yet? Don't worry - it sounds harder then it actually is. So check out the code below.
<?php /* create the connection to the database */ /* write the SQL */ $sql = "SELECT foo, bar FROM table WHERE id = '$id' ";
/* here is where the different flavors are shown. In reality only one method would be shown/used. I will place all the various methods in the same situation (loop) and show how to access the results returned. You can choose which method works best for you. */
/* associative array */ $db->setFetchMode(DB_FETCHMODE_ASSOC); /* send the query */ $result =& $db->query($sql); /* get the results */ while ($result->fetchInto($row)) { echo 'The value of foo is ' . $row['foo']; echo 'The value of bar is ' . $row['bar']; }
/* ordered array */ $db->setFetchMode(DB_FETCHMODE_ORDERED); /* send the query */ $result =& $db->query($sql); /* get the resultdatabase with each, hows */ while ($result->fetchInto($row)) { /* access the results */ echo 'The value of foo is ' . $row[0]; echo 'The value of bar is ' . $row[1]; }
/* object */ $db->setFetchMode(DB_FETCHMODE_OBJECT); /* send the query */ $result =& $db->query($sql); /* get the results */ while ($result->fetchInto($row)) { /* access the results */ echo 'The value of foo is ' . $row->foo; echo 'The value of bar is ' . $row->bar; } ?>
/ 3 