put query results into an object

Question

I am using php and postgresql.

I need a function that:

connects to a db if not already connected run the query safely put the results into an object

For example: I do a query that is 'select * from test'. I get back 2 rows...There are three columns in 'test' (id,fname,lname).

I have an object named $ep. I want to be able to put the column name results into the object and also put the values into the object. So I could do a var_dump of $ep->fname and it would show me an array with two results.

I don't want to hardcode the column names into my function. I want it to be dynamic. So regardless of what table or columns I request it will put everything into the object for me.

Answer 1

An Object Relational Mapper (ORM) can give you a lot of the functionality you described (and more). Take a look at:

• Doctrine: http://www.doctrine-project.org/
• Propel: http://www.propelorm.org/

Here's what Propel code looks like:

$book = BookQuery::create()->findPK(123); // retrieve a record from a database
$book->setName('Don\'t be Hax0red!'); // modify. Don't worry about escaping
$book->save(); // persist the modification to the database

$books = BookQuery::create()  // retrieve all books...
  ->filterByPublishYear(2009) // ... published in 2009
  ->orderByTitle()            // ... ordered by title
  ->joinWith('Book.Author')   // ... with their author
  ->find();

foreach($books as $book) {
  echo  $book->getAuthor()->getFullName();
}

Answer 2

Try

pg_fetch_object

read the documentation

• http://php.net/manual/en/function.pg-fetch-object.php

example

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

$ep = pg_query ($db_conn, "SELECT * FROM verlag ORDER BY autor");
$row = 0; // postgres needs a row counter other dbs might not 

while ($data = pg_fetch_object ($ep, $row)) {
    echo $data->fname;
    $row++;
}

?>

Answer 3

You would need to construct your own array or object, and then loop through the results, adding each of them to the array/object as you went.

There are some packages which include this functionality, like ezSQL.