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

Question

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!

FWIW, this is kind of tangential but did you know that if you can call mysql_num_rows() then you already have the full result set occupying memory in your PHP process? There's no way to know the number of rows until the app has fetched them all. — Bill Karwin
– Bill Karwin, Commented Dec 4, 2008 at 1:54

UnkwnTech · Accepted Answer · 2008-12-04 01:55:59Z

4

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.

edited Dec 4, 2008 at 1:55

answered Dec 4, 2008 at 1:48

UnkwnTech

91.4k68 gold badges192 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Justin Yost Over a year ago

I'll second the suggestion to stick with the first code, it's cleaner and easier to work with.

alex Over a year ago

Looks like I'll have to do it this way. I'm always looking for ways to refactor my code to make it as inexpensive as possible. Thank you for your answer.

mcrumley · Accepted Answer · 2008-12-14 06:03:22Z

2

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);
?>

edited Dec 14, 2008 at 6:03

answered Dec 10, 2008 at 9:17

mcrumley

5,7103 gold badges28 silver badges33 bronze badges

Comments

staticsan · Accepted Answer · 2008-12-04 01:50:22Z

1

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.

answered Dec 4, 2008 at 1:50

staticsan

30.6k5 gold badges64 silver badges72 bronze badges

Comments

grepsedawk · Accepted Answer · 2008-12-04 02:25:47Z

0

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.

answered Dec 4, 2008 at 2:25

grepsedawk

6,0615 gold badges27 silver badges22 bronze badges

3 Comments

alex Over a year ago

Isn't the SQL language different for different databases?

grepsedawk Over a year ago

SQL is a "standard". However most of the databases implement only certain portions of the standard (SQL95 for example). In general most of the basic query syntax will work reliably across multiple databases.

alex Over a year ago

Well with my method, abstraction should be able to be done. Thanks for the info.

Tina · Accepted Answer · 2009-02-19 05:54:35Z

0

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;

}

answered Feb 19, 2009 at 5:54

Tina

1 Comment

alex Over a year ago

I'm using PDO now and it has this function built in... simply call the method fetchAll(MYSQL_ASSOC) or similiar. Check the docs.

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related