5 useful PHP functions for MySQL data fetching
1. Function good_query. This function is analogous to mysql_query. It executes query and return result resource — the one, that could be used with standard functions mysql_fetch_row, mysql_fetch_assoc, mysql_num_rows and others, TRUE (for successful UPDATE, DELETE, DROP, etc SQL statements) or FALSE (for failed queries).
Any of good_query… functions has two arguments:
- $sql - SQL query string
- $debug - use debug logging (see ‘Debug logging’ below)
Advantages:
- good_query() has built-in error checking: for failed queries it prints error string and original SQL query text both to standard output and PHP(web-server) error log
- Debug logging: second argument of good_query() is $debug flag. When $debug == 1, good_query prints(to standard output) every executed query. When $debug == 2, good_query saves every executed query to error log.
- ‘good_query’ is one letter shorter than ‘mysql_query’ and easier to type
source code: php
<?php
function good_query($string, $debug=0)
{
if ($debug == 1)
print $string;
if ($debug == 2)
error_log($string);
$result = mysql_query($string);
if ($result == false)
{
error_log("SQL error: ".mysql_error()."\n\nOriginal query: $string\n");
// Remove following line from production servers
die("SQL error: ".mysql_error()."\b<br>\n<br>Original query: $string \n<br>\n<br>");
}
return $result;
}
?>
2. Function good_query_list. This function executes (with error checking) query, retrieves first row and returns it as an array.
Advantages:
- Call only one good_query_list function instead of mysql_query, mysql_fetch_row, mysql_free_result
- Debug logging: see good_query function
Note: You should probably add ‘LIMIT 1′ to all queries passed to good_query_list (since this function returns only first row)
source code: php
<?php
function good_query_list($sql, $debug=0)
{
// this function require presence of good_query() function
$result = good_query($sql, $debug);
if($lst = mysql_fetch_row($result))
{
mysql_free_result($result);
return $lst;
}
mysql_free_result($result);
return false;
}
?>
Usage example:
source code: php
<?php
// Imagine, you want to fetch specific user information from users table
// You need information only for one user
@list($login, $email, $settings) = good_query_list("SELECT login, email, settings
FROM users
WHERE id={$user_id}");
// we have added @ -- to avoid assignment warning when good_query_list
// did not found any rows and returned false
if ($login == false)
die("no user {$user_id} found");
// A lot simplier than mysql_query, mysql_fetch_row, mysql_free_result functions
// and if statements
?>
3. Function good_query_assoc. Executes (with error checking) query, retrieves first row and returns it as a hash(associative) array. Very similar to good_query_list().
source code: php
<?php
function good_query_assoc($sql, $debug=0)
{
// this function require presence of good_query() function
$result = good_query($sql, $debug);
if($lst = mysql_fetch_assoc($result))
{
mysql_free_result($result);
return $lst;
}
mysql_free_result($result);
return false;
}
?>
Usage example:
source code: php
<?php
// Imagine, you want to fetch specific user information from users table
// You need information only for one user
$user = good_query_assoc("SELECT login, email, settings FROM users WHERE id={$user_id}");
if ($user == false)
die("no user {$user_id} found");
echo "Login: ".$user['login'];
echo "\nEmail: ".$user['email'];
echo "\nSettings: ".$user['settings'];
?>
4. Function good_query_value. Executes (with error checking) query, retrieves first cell (first row and first column) and returns it.
- Simpler than list($variable) = good_query_list(…) function.
Note: You should probably select only one field and add ‘LIMIT 1′ to all queries passed to good_query_value (since this function returns only first cell)
source code: php
<?php
function good_query_value($sql, $debug=0)
{
// this function require presence of good_query_list() function
$lst = good_query_list($sql, $debug);
return is_array($lst)?$lst[0]:false;
}
?>
Usage example:
source code: php
<?php
// good_query_value is useful for count()-queries
$count = good_query_value("SELECT count(*) FROM table");
echo "$count row(s) in a table";
?>
5. And, finally, good_query_table. This function executes (with error checking) SQL query and retrieves entire result table as two-dimensional array. First dimension is rows(0..row count), second dimension is columns (associative hash).
- Extremely useful for loading data to array-based templates
Note: Since all table is loaded into memory, you should use this function for small tables or add ‘LIMIT …’.
source code: php
<?php
function good_query_table($sql, $debug=0)
{
// this function require presence of good_query() function
$result = good_query($sql, $debug);
$table = array();
if (mysql_num_rows($result) > 0)
{
$i = 0;
while($table[$i] = mysql_fetch_assoc($result))
$i++;
unset($table[$i]);
}
mysql_free_result($result);
return $table;
}
?>
Usage example (draw table as HTML):
source code: php
<?php
$table = good_query_table("SELECT * FROM table");
echo "<table border='1'>";
foreach($table as $row)
{
echo "<tr>";
foreach($row as $column=>$cell)
{
echo "<td>".$cell."</td>";
}
echo "</tr>"
}
echo "</table>";
?>
Another example (rendering blog data as HTML):
source code: php/html
<?php
// fetching first 20 records from simple blog table
// id - comments - text - posted_at
$blog = good_query_table("SELECT id, comments, text, posted_at FROM blog LIMIT 0, 20");
// output template:
foreach($blog as $post)
{
?>
<div class="post">
<p><?=$post['text']?></p>
<div class="info">
posted at <?=$post['posted_at']?>,
<a href="./comment-url?id=<?=$post['id']?>"><?=$post['comments']?> comment(s)</a>
</div>
</div>
<?
}
?>
Some usage tips:
- You may add time measure to good_query function, to find out how long you SQL queries are executed
- Create trivial functions good_row, good_assoc, good_num, good_affected which will simple call correspondingly mysql_fetch_row, mysql_fetch_assoc, mysql_num_rows, mysql_affected_rows. They are much easier to type.
- Since architecture of some other PHP database extensions (like pgsql for PostgreSQL) is very close to mysql, you could easily switch you application to use another DB server by rewriting good_… functions.
6 comments April 17, 2007
