« The $r / $_REQUEST Shortcut | Main | Authentication (Login / Logout) & Sessions »

March 30, 2009

The just_sql() Function & The $reply Hash

The SQL facility for querying the database and getting results back has been abstracted into one function. This presumes that your application has its tables in a single database. If this is not the case you can use the do_sql($db, $sql, $debug) function to specify which db credentials .ini file to use for the query where it is different from the value set in $db_name.

The main function for running a query is just_sql(sprintf("select * from foo where bar = %d", $bar_id)). I tend to use this function in conjunction for sprintf() for extra security.

The $reply Hash

The following code:

$reply = just_sql("select * from equipment where expired_date is null order by update_date desc limit 3");

$rows = $reply['rows'];

header('Content-type: text/plain');

print_r($reply);

produces the following reply hash:

Array
(
    [num_of_rows] => 3
    [rows] => Array
        (
            [0] => Array
                (
                    [equipment_id] => 31
                    [name] => DVD Player
                    [description] => 
                    [insert_by] => 1
                    [insert_date] => 1228746274
                    [update_by] => 1
                    [update_date] => 1228746274
                    [expired_date] => 
                )

            [1] => Array
                (
                    [equipment_id] => 30
                    [name] => Portable Video Conferencing Equipment
                    [description] => 
                    [insert_by] => 1
                    [insert_date] => 1226590805
                    [update_by] => 1
                    [update_date] => 1226590805
                    [expired_date] => 
                )

            [2] => Array
                (
                    [equipment_id] => 29
                    [name] => 7 X PCs
                    [description] => 
                    [insert_by] => 1
                    [insert_date] => 1226327521
                    [update_by] => 1
                    [update_date] => 1226327521
                    [expired_date] => 
                )

        )

    [first_row] => Array
        (
            [equipment_id] => 31
            [name] => DVD Player
            [description] => 
            [insert_by] => 1
            [insert_date] => 1228746274
            [update_by] => 1
            [update_date] => 1228746274
            [expired_date] => 
        )

    [status] => 1
    [message] => OK
    [sql] => select * from equipment where expired_date is null order by update_date desc limit 3
)

N.B. The where clause of the query includes expired_date is null. Every table should have a number of common columns, including an expired_date. This is set with a unix_timestamp() when the row is to be deleted from the DB. In other words, no rows are ever truly deleted, merely expired. You need this test in your where clauses to exclude "deleted" rows.

The result rows are found in $reply['rows'] as a list of hashes. The first row is found in $reply['first_row'] too in case that's all you need. $reply['num_of_rows'] tells you how many rows were returned and the original query SQL is included in $reply['sql'] for easier debugging of dynamic queries.

Tags: Lightweight PHP Web Application Framework

Posted by pj at March 30, 2009 01:52 PM

Comments