PHP SQL query results

Question

Alright, I'm pretty confident I did this only a few days ago, although I may be going crazy. I am attempting to loop through an SQL result array for example..

$query = mysql_query("SELECT * FROM `my_table`");
$result = mysql_fetch_assoc($query);

Now $result should return multiple rows.. and it does if I loop through it using a while loop. Unfortunately, Im trying to access this data with a foreach loop, and for some reason it will not work. Its only giving me the first row and print_r($result) only gives me the first row as well.

foreach($result as $name => $value)
    echo "$name = $value\n";

Any suggestions would be appreciated!

** EDIT:

I love all of the smart answers.. I know the website for the php manual and I know what mysql_fetch_assoc() returns. Here is my solution:

function returnSQLArray() { 
    $returnArray = array(); 
    $row = 0; 
    $query = mysql_query("some sql"); 
    while($result = mysql_fetch_assoc($query)) { 
        $returnArray[$row] = $result; 
        $row++; 
    } 
    return $returnArray; 
}

Mark Baker · Accepted Answer · 2010-11-18 22:56:38Z

4

$result = mysql_fetch_assoc($query); returns a single row... you need to loop fetching each row. You're looping through that one row to extract each column.

answered Nov 18, 2010 at 22:56

Mark Baker

213k34 gold badges354 silver badges390 bronze badges

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

2 Comments

Dalton Conley Over a year ago

Yes, I know this. Is there a way I can increment the row pointer to get to the next row?

Mark Baker Over a year ago

It's automatically incremented each time you fetch a row using mysql_fetch_xxx. You need to loop the fetch - while ($result = mysql_fetch_assoc($query) - which will set $result to each row in turn until there are no rows left and it returns a false to exit the loop

zanlok · Accepted Answer · 2010-11-18 23:47:20Z

1

What Vladson is sarcastically pointing out is nonetheless very true. My forays into PHP programming (many years' worth) have been ever-sprinkled with a great many readups on the php.net site. I'd call it the best online programming documentation in existence, far beating any other language I've used in 20 years.. mostly because of the amazing calibre of the community contributions.

Also, I'd highly recommend abstracting what you're talking about into a db helper class. Reference perhaps the PHPBB code for an example. PHPBB code may be less OO than is ideal, but it's still a good reference point for architecture. And, don't just do this because you may switch out your data layer or change the version, but because it makes it trivial to introduce common error reporting, query logging, data caching, and many other such useful features. This also makes it easier to juggle more than one connection.

Example might be so that you can expose an interface more like: (excuse the very ADODB nature here, but it's still a nice way to think of MySQL, too)

include "db.inc.php";    
$SQL = "SELECT * FROM user WHERE id=123";
$oDB = new Database("localhost", "database", "user", "password");
$oRS = $oDB->NewRecordSet($SQL);
while( $data = $oRS->Read() ) {
    // do stuff
}

In this manner, the pages have to worry less about the tedium of accessing the data, and can just think more about how to filter the data and what to do with it.

edited Nov 18, 2010 at 23:47

answered Nov 18, 2010 at 23:41

zanlok

1,6301 gold badge16 silver badges29 bronze badges

1 Comment

zanlok Over a year ago

merely using mysqli doesn't give you any of the advantages I was trying to point out when creating your own abstracted data layer class(es), though

Lance Kidwell · Accepted Answer · 2010-11-18 23:04:34Z

0

while ($result = mysql_fetch_assoc($query))
{    
    // do stuff
}

answered Nov 18, 2010 at 23:04

Lance Kidwell

9051 gold badge15 silver badges18 bronze badges

Comments

Vladson · Accepted Answer · 2010-11-18 23:07:22Z

-1

There is a thing called Manual http://www.php.net/manual/en/function.mysql-fetch-assoc.php examples are also there (a lot of them)

answered Nov 18, 2010 at 23:07

Vladson

193 bronze badges

Collectives™ on Stack Overflow

PHP SQL query results

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related