Store
Community Documentation

v3 Knowledgebase

Using the Database query() method

Phpfox provides an array of tools to use to extract information for many different database systems. Generally, if you follow the coding standards of the Phpfox database interface, you can get data from any supported system.

There may be times when a data request is too complex to easily use the normal database commands to get your data. One of the methods that you might use is the ->query(). This will let you process any code that you can build into a properly formatted string.

If you use this method to return a dataset, you will have to process the result to extract the data in a useful form. This is not too difficult but it can be tricky and the code used may not work on another system with a different database system.

I thought that there would surely be something within the phpfox database library that would process the data and provide us with a nice array. It turns out that there is code to do this and is included in the database drivers under the methods _getRows or getRow.

Alas, it is a private function and we cannot get to it. It is locked away in the nether regions of the driver, forever out of our reach. Truly we are experiencing the curse of Tantalus.

What we need is some code that would allow us to extract the data regardless of the database system that is being used. I found that this could be done with a little code that I borrow from the existing Phpfox database coding.

In the file include/library/phpfox/database/database.class.php in the construct area is a switch that we can use as a template to build some code to identify the database system produce some strings that will help us process our dataset for each system.

The code that I came up with for this purpose is:
PHP:
switch(Phpfox::getParam(array('db''driver')))
{
    case 
'mysqli':
        
$sFetchArray 'mysqli_fetch_array';
        
$sAssoc MYSQL_ASSOC;
        break;
    case 
'postgres':
        
$sFetchArray 'pg_fetch_array';
        
$sAssoc PGSQL_ASSOC;
        break;    
    case 
'mssql':
        
$sFetchArray 'mssql_fetch_array';
        
$sAssoc MSSQL_ASSOC;
        break;
    case 
'oracle':
        
$sFetchArray 'oci_fetch_array';
        
$sAssoc OCI_ASSOC OCI_RETURN_NULLS;
        break;        
    case 
'sqlite':
        
$sFetchArray 'sqlite_fetch_array';
        
$sAssoc SQLITE_ASSOC;
        break;
    default:
        
$sFetchArray 'mysql_fetch_array';
        
$sAssoc MYSQL_ASSOC;
        break;

With this code in place, if we can use the query method to produce a recordset:
PHP:
$sQuery 'SELECT c1.user_id, c1.time_stamp, c1.weight, cg.starting_weight 
FROM phpfox_checkin as c1, (
  SELECT user_id, max(time_stamp) time_stamp 
  FROM phpfox_checkin
  GROUP BY user_id
  ) c2
JOIN phpfox_checkin_goal as cg on c2.user_id = cg.user_id 
WHERE c1.time_stamp = c2.time_stamp
AND c1.user_id = c2.user_id;'
;

$result Phpfox::getLib('database')->query($sQuery); 

Then we can process the $result with some code that should be useable regardless of which database system is used.
PHP:
while($row $sFetchArray($result$sAssoc))
{
    
$iLost += $row['starting_weight'] - $row['weight'];

The first row above will translate into code that is appropriate for each different system. For example in mysqli, the code would look like:
PHP:
while($row mysqli_fetch_array($resultMYSQL_ASSOC)) 

If the system were postgres, it would look like:
PHP:
while($row pg_fetch_array($resultPGSQL_ASSOC)) 

I should point out that I have only tested this on my system which uses mysqli.

I hope this information will be helpful for those interested in using this method. If I have missed something in the database object that would result in an easier way to do this, let me know and I will include that information in this article.