MySQL Data Retrieval (Advanced)

[ober]

NOTE: This information is now obsolete, as the mysql_ functions are deprecated and have been removed from PHP as of php5.5.

 

Use the mysqli or PDO_Mysql extensions instead.  

 

 

 

MySQL Data Retrieval

A very common problem I see on these forums is that some people are not checking for errors after an SQL query. You need to check for a good return (no errors) before performing any subsequent SQL related commands (such as a fetch or number of rows). Also, you must check if you got data back after attempting to retrieve the data; do not just start using the row data blindly. Good coding practice never assumes there will be data returned. Example:

// Connect to MySQL server first – You can use variables instead of these literals
$db = mysql_connect('localhost', 'username', 'password');
if (!$db) {
   echo 'Could not connect to MySQL server. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
   exit;
}

// Select the database you want to use – You can use a variable here too instead
if (!mysql_select_db('DBname', $db)) {   // Did selection fail?
   // Handle error
   echo 'DB Selection failed. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
   exit;
}

// An example for retrieving zero or more rows
$sql = "SELECT name, email FROM people_table";
$result = mysql_query($sql, $db);
if (!$result) {
   // Handle error
   echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
   exit;
}
// The while loop stops when there's no data left; it might not even go in loop
// and echo anything when there's no data returned on the first call to
// mysql_fetch_assoc()
while($row = mysql_fetch_assoc($result)) {  // Retrieve data until no more
   echo $row['name'], '<br />';
   echo $row['email'], '<br />';
}

// Optional – Free up memory. Done automatically when script ends
if (!mysql_free_result($result)) {  
   // Handle error or ignore it
   echo 'Freeing results failed. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
   exit;
}

// Once the results table is freed, don't attempt to use the $result variable anymore

//****************************************************************

// For retrieving zero or one row (the first row with that name)
$name = mysql_escape_string('John Doe');  // or use addslashes()
$sql = "SELECT email FROM people_table WHERE name = '$name'";
$result = mysql_query($sql, $db);
if (!$result) {
   // Handle error
   echo 'Query failed. SQL: ', $sql, '<br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
   exit;
}

$row = mysql_fetch_assoc($result);    // Try to retrieve the data
if (!$row)           // No data returned?
   // Not necessarily an error – Just no data matched search criteria
   echo 'No data found <br />';
else      // There is valid data returned
   echo "The email for $name is ", $row['email'], '<br />';

// Optional – Close MySQL connection. Will be closed at end of script anyway
// Do NOT put a mysql_close() after a query and before fetching data!
if (!mysql_close($db)) {  
   echo "Couldn't close database <br />";  // or ignore error
   exit;
}

Note that some people use die(), which is simply an alias of exit().

I've also seen this type of poor coding:

$nbr = mysql_num_rows(mysql_query("SELECT name, email FROM people_table"));

// or
$row = mysql_fetch_assoc(mysql_query("SELECT name, email FROM people_table WHERE id = '$id'"));

Never enclose one MySQL function inside another like that. As already explained, a query could return an error and a valid results resource won't exist (or be returned). This would then cause the outer function to fail with a "not a valid MySQL result resource" message (see the Annoying Errors section below).

TIP: You can make the formatting of SQL statements a little bit easier by using arrays and the sprintf() function. One array could hold all the column names you have in a table. Like this:

$columns = array('catg_id', 'catg_description');

You can even find out what the column names are dynamically and place the column names in an array producing the same result as above. In the following example, the first '%s' in sprintf() will be replaced with '*' and the second '%s' will get replaced with 'categories'. Here's the example:

// An example to automatically get column names
$sql = sprintf('SELECT %s FROM %s LIMIT 1',
              '*',
              'categories'
             );
echo 'COLUMN NAME SELECT SQL: ', $sql, '<br>';

$result = mysql_query($sql);
if (!$result) {
   echo 'Query failed. SQL: ', $sql, '<br>Error: ', mysql_error(), '<br>';
   exit;
}

$row = mysql_fetch_assoc($result);  // Retrieve the one row

if (!$row)  // No data found?

   // Set columns manually or display error (and exit)
   $columns = array('catg_id', 'catg_description');
else
   // Convert associate keys into values
   $columns = array_keys($row);

echo '<pre>', print_r($columns, TRUE), '</pre>';

The above code would display something like this:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]COLUMN NAME SELECT SQL: SELECT * FROM categories LIMIT 1

Array
(
    [0] => catg_id
    [1] => catg_description
)
[/quote]
Inserting data into a table can be easier with having a columns array and a values array. Then using implode() to handle the formatting of commas and generating the SQL. This is especially handy when you have lots of column names in your table. Example:

// Escape characters when necessary and surround value with quotes
function quote($_value = '', $_check_gpc = TRUE) {

   if (($_check_gpc) &&
       (get_magic_quotes_gpc()))
       return ("'" . $_value . "'");
   else
       return ("'" . mysql_escape_string($_value) . "'");

}

/*
* You can create arrays to hold table and column names
*/
$tables = array('categories');   // Doesn't have to be an array
$columns = array('catg_id', 'catg_description');

/*
* Build an array of values to place in table
* The order of values needs to correspond to column(s) order given
*/
$values = array();            // Initialize
$values[] = quote('');        // catg_id blank - auto increment
$values[] = quote($_POST['catg']); // New category – Contains: Cameras

/*
* Use sprintf() to nicely take care of building the SQL statement
*/
$sql = sprintf('INSERT INTO %s (%s) VALUES (%s)',
              implode (', ', $tables),
              implode (', ', $columns),
              implode (', ', $values)
             );

echo 'INSERT SQL: ', $sql, '<br>';

$result = mysql_query($sql);
if (!$result) {
   echo 'Insert failed. SQL: ', $sql, '<br>Error: ', mysql_error(), '<br>';
   exit;
}

$last_id = mysql_insert_id(); // Get last auto increment id used
echo 'Last insert id: ', $last_id, '<br>';

Which displays something like this:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]INSERT SQL: INSERT INTO categories (catg_id, catg_description) VALUES ('', 'Cameras')
Last insert id: 1
[/quote]
The update SQL is a little trickier because we have two separate arrays that hold the column name and values. You could have one array where the keys of the array are the names of the columns and the values would of course be what you want to update or modify. This sample code uses two separate arrays for column and values. When you're table contains an auto increment column, then you probably don't want to change it's value. So, this code removes it so it won't get updated. See example code:

// Returns column_name = 'value' for update SQL
function kv_pair($_key = array(), $_value = array()) {

   $_result = '';
   $_key_cnt = count($_key);
   $_value_cnt = count($_value);

   if (($_key_cnt > 0)   &&
       ($_key_cnt == $_value_cnt)) {

       $_result = array();
       for ($i = 0; $i < $_key_cnt; $i++)
           $_result[] = $_key[$i] . ' = ' . quote($_value[$i]);

       $_result = implode(', ', $_result);
   }

   return $_result;
}

$without_id = $columns;       // Copy original columns array
$id = array_shift($without_id);  // Remove first catg_id entry

$values = array();            // Initialize
$values[] = 'Books';          // Change category description to Books

$where = array();             // Initialize
$where[] = $columns[0] . ' = ' . quote($last_id); // catg_id = 'last number'
$operator = '';               // Just one condition; no need for operator

$sql = sprintf('UPDATE %s SET %s WHERE %s',
              implode (', ', $tables),
              kv_pair($without_id, $values),  // returns a string
              implode (" $operator ", $where)
             );

echo 'UPDATE SQL: ', $sql, '<br>';

$result = mysql_query($sql);
if (!$result) {
   echo 'Update failed. SQL: ', $sql, '<br>Error: ', mysql_error(), '<br>';
   exit;
}

The update code displays something like:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]UPDATE SQL: UPDATE categories SET catg_description = 'Books' WHERE catg_id = '1'
[/quote]
Generating the select SQL is pretty simple using this method. See example code:

$where = array();
$where[] = $columns[0] . ' = ' . quote($last_id); // Need ID or description
$where[] = $columns[1] . ' = ' . quote('Books');  // but using both to show AND
$operator = 'AND';

$sql = sprintf('SELECT %s FROM %s WHERE %s',
              implode (', ', $columns),
              implode (', ', $tables),
              implode (" $operator ", $where)
             );

echo 'SELECT SQL: ', $sql, '<br>';

$result = mysql_query($sql);
if (!$result) {
   echo 'Query failed. SQL: ', $sql, '<br>Error: ', mysql_error(), '<br>';
   exit;
}

while ($row = mysql_fetch_assoc($result))

   echo '<pre>', print_r($row, TRUE), '</pre>';

The select code above would display something like this:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]SELECT SQL: SELECT catg_id, catg_description FROM categories WHERE catg_id = '1' AND catg_description = 'Books'

Array
(
    [catg_id] => 1
    [catg_description] => Books
)
[/quote]
Here's an example of a select SQL using two tables:

$tables = array('categories', 'inventory');
$columns = array('catg_id', 'catg_description',
                'inv_in_stock', 'inv_description', 'inv_price');

$where = array();
$where[] = $tables[0] . '.' . $columns[0] . ' = ' .
          $tables[1] . '.' . $columns[0];
$where[] = $columns[1] . ' = ' . quote('Books');
$operator = 'AND';

$columns[0] = $tables[0] . '.' . $columns[0]; // make into categories.catg_id
$sql = sprintf('SELECT %s FROM %s WHERE %s',
              implode (', ', $columns),
              implode (', ', $tables),
              implode (" $operator ", $where)
             );

echo 'SELECT SQL: ', $sql, '<br>';

$result = mysql_query($sql);
if (!$result) {
   echo 'Query failed. SQL: ', $sql, '<br>Error: ', mysql_error(), '<br>';
   exit;
}

while ($row = mysql_fetch_assoc($result))

   echo '<pre>', print_r($row, TRUE), '</pre>';

The select code above would display something like this:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]SELECT SQL: SELECT categories.catg_id, catg_description, inv_in_stock, inv_description, inv_price FROM categories, inventory WHERE categories.catg_id = inventory.catg_id AND catg_description = 'Books'

Array
(
    [catg_id] => 1
    [catg_description] => Books
    [inv_in_stock] => 10
    [inv_description] => Setting Up LAMP: Getting Linux, Apache, MySQL,and PHP Working Together
    [inv_price] => 34.99
)
[/quote]



Acknowledgement: toplay.

[Barand]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]$row = mysql_fetch_assoc(mysql_query("SELECT name, email FROM people_table WHERE id = '$id'"));

Never enclose one MySQL function inside another like that. As already explained, a query could return an error and a valid results resource won't exist (or be returned). This would then cause the outer function to fail with a "not a valid MySQL result resource" message (see the Annoying Errors section below).[/quote]

It's worse than that - even if the query has no errors you repeat the query execution every time you want to fetch a row.