PHP mySQL - Can you return an associated array with a number index?

Question

Hi!

I have this method in my db class

public function query($queryString)
    {

     if (!$this->_connected) $this->_connectToDb(); //connect to database

     $results = mysql_query($queryString, $this->_dbLink) or trigger_error(mysql_error());


     return mysql_num_rows($results) > 0 ? mysql_fetch_assoc($results) : false;    




    }

This works great for queries that return 1 row, but how can I get an array returned something like this?

$array[0]['name'] = 'jim'
$array[0]['id'] =  120
$array[1]['name'] = 'judith' 
$array[1]['ID'] = 121

Now I know I could use a while loop to insert this data into the array like so, but I was wondering if PHP could do this with an internal function? I havn't been able to find on the docs what I'm after.

The reason I don't want to run the while within the method is because I am going to reiterate back over the array when it's returned, and I'd rather not run through the results twice (for performance reasons).

Is there a way to do this? Do I have a problem with my general query method design?

Thank you muchly!

Answer 1

public function query($queryString)
    {

        if (!$this->_connected) $this->_connectToDb(); //connect to database

        $results = mysql_query($queryString, $this->_dbLink) or trigger_error(mysql_error());

        $data = array();
        while($row = mysql_fetch_assoc($results))
         {
            $data[] = $row;
         }
        return $data;

    }

this will always return an array.

EDIT: I didn't read the question well. If you realy don't want to use the loop then I would do this:

public function query($queryString)
    {

        if (!$this->_connected) $this->_connectToDb(); //connect to database

        return mysql_query($queryString, $this->_dbLink) or trigger_error(mysql_error());

    }

then loop over it, however I would just use the loop.

Answer 2

Most people use a while() loop in the query to do exactly what you want and then loop over the array to process it.

However, you're right: it wastes memory, which could be a problem with a large dataset. An alternative is for your query method to return the resultset resource. Then your while loop can use that to fetch each row as it requires it.

To abstract that away, I would suggest another class to do that for you. Then your query call would return a new instance of that class which has the MySQL resultset resource as an instance variable and packages up the mysql_fetch_assoc() call.

Answer 3

Look at PEAR::MDB2 (Quickstart Cheatsheet). It provides lots of different functions for doing something like this. It also does not tie you down into using MySQL specific functions because it is a database abstraction layer.

$result = $db->queryRow($query, MDB2_FETCHMODE_ASSOC);

There are other abstraction layers such as ADO as well.

Answer 4

You might also want to look at the PDO extension. You can load the entire result set into an array or you can loop using foreach.

<?php
$db = new PDO($connection_string, $username, $password);
$result = $db->query($queryString);
foreach($result as $row) {
    // do something
}

// or

$result = $db->query($queryString);
$result_array = $result->fetchAll(PDO::FETCH_ASSOC);
?>

Answer 5

thanks for the ideas. I have a function that returns an associative array from the sql (used in Moodle).

$results = get_records_sql($sql);

//to create a numerically indexed array:
$data = array();
foreach ($results as $row)
     {
           $data[] = $row;
     }
return $data;

}