5 useful PHP functions for MySQL data fetching

April 17, 2007 at 8:47 am 14 comments

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.

Entry filed under: mysql. Tags: .

PHP: Export Database Schema as XML AJAX Technologies

14 Comments Add your own

  • 1. assurlefe  |  July 20, 2007 at 7:38 am

    Communications
    In a rapidly moving news environment, FDF operates a 24/7 press office and provides communications support to members FDF is the voice of the UK food and drink manufacturing industry. We work hard to promote the interests of the UK’s most important manufacturing sector.

    Reply
  • 2. invis3579  |  August 3, 2007 at 5:39 am

    Wow , I am using wordpress.com too , could you show me how to quote PHP code like that , please !
    in colors & scroll bars

    Reply
  • 3. Best Home Improvement Info  |  August 17, 2007 at 6:13 am

    This is very nice and informative post. I have bookmarked your site in order to find out your post in the future.

    Reply
  • 4. Idetrorce  |  December 15, 2007 at 2:22 pm

    very interesting, but I don’t agree with you
    Idetrorce

    Reply
  • 5. simple way of life  |  January 15, 2008 at 8:25 am

    Thanks for that great piece of php code. I have been searching for this code and found it on your blog. Hope this code will make my work little bit easier.

    regards
    Once upon a lifetime in kerala

    Reply
  • 6. Siber  |  August 1, 2008 at 4:45 am

    Interesting web page is, i\’ll see you later one more time

    Reply
  • 7. Alexwebmaster  |  March 3, 2009 at 10:44 am

    Hello webmaster
    I would like to share with you a link to your site
    write me here preonrelt@mail.ru

    Reply
  • 8. Zoened  |  April 9, 2010 at 2:39 am

    this tutorial is good, Can i would to copy this file??? Thanks

    Reply
  • 9. prescription glasses  |  January 22, 2013 at 3:48 pm

    Hi there! I could have sworn I’ve been to this site before but after browsing through some of the post I realized it’s new to me.
    Anyhow, I’m definitely delighted I found it and I’ll be book-marking and checking back often!

    Reply
  • 10. Rosalie  |  May 6, 2013 at 2:22 am

    A Blogger template that would work great for a parenting blog, children’s book blog, or teacher’s blog.
    However, Free Premium are the best choice to opt for any sort of blog
    because they are extremely industrious and graceful themes.

    * If you have more than one blog you can access them all through the one blogger
    dashboard.

    Reply
  • 11. aranzacjemodne.pinger.pl  |  May 22, 2013 at 10:51 pm

    Thanks for finally writing about >5 useful PHP functions
    for MySQL data fetching | Worldtechies <Loved it!

    Reply
  • 12. www.insertosatirico.com  |  May 23, 2013 at 11:38 pm

    Hi there, just wanted to tell you, I liked this blog post.
    It was funny. Keep on posting!

    Reply
  • 13. Candy Crush  |  July 26, 2013 at 5:27 am

    Here’s how to make an ice cream pie, including directions for a cookie crust (with variations), plus ideas for fillings and toppings. Starting in level 51 you will face chocolate squares. Should you get the top ranking on Google, Yahoo and MSN for your keywords, nobody can knock you off unless they do a better optimization job than you do.

    Reply
  • 14. Best Acne Treatment  |  June 13, 2014 at 11:57 am

    I found your blog on search engines and bookmark it currently.
    keep up the nice work.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Flickr Photos

The firsts lights

Lago di Braies

Survivor Mode

More Photos

Blog Stats

  • 705,780 hits

%d bloggers like this: