simple db query function

Question

I want to be able to call a query function and based on the results they are put into an object that I can call outside the function. This is what I have:

<?php

function query($sql) {
global $r;

$database = "theatre";
$db_conn = pg_connect ("host=localhost port=5432 
dbname=$database user=postgres");
if (!$db_conn): ?>
<h1>Failed connecting to postgres database <?php echo $database;?></h1> 
<?php
exit;
endif;

$ep = pg_query ($db_conn, $sql);

while ($r = pg_fetch_object ($ep)) {

}

pg_free_result($ep);
pg_close($db_conn);

}

query('select * from test');
echo $r->fname;

I don't want to hardcode any column names into the function. So fname is an example but it will be dynamic based on the query. I have a while loop but not sure what to put in it.

Answer 1

Value returned by function pg_fetch_object is already an object, so no need to loop through results. Just return it:

return pg_fetch_object ($ep);

Then just use the returned results without referring to this awkward global variable $r.

$r = query('select * from test');
echo $r->fname;

Also, no need to free the resource with pg_free_result. It gets freed automatically at end of scope it exists within (that is current function in this case). Calling pg_close may also slow down your script, as the connection will have to be recreated anew next time you call the function. I'd suggest storing connection in local static variable and not closing it at all - it will be closed automatically.

function query($sql) {
    $database = "theatre";
    static $db_conn;
    if (!$db_conn) {
        $db_conn = pg_connect ("host=localhost port=5432 dbname=$database user=postgres");
    }
    ...

As a long term solution I'd recommend researching subject of Object-Relational Mapping and learning one of available ORM-ish tools (i.e. Zend Db Table, Doctrine, Propel, or one of existing implementations of ActiveRecord pattern).