mysql_fetch_array does not retrieve all rows

Question

$query = "SELECT * FROM table";
$result = mysql_query($query, $db);
$all = mysql_fetch_assoc($result);
echo mysql_num_rows($result) . ":" . count($all);

This returns

2063:7

I have not used count before, so I'm not 100% sure it's not counting the table columns. It's late and I might be going nuts.

Here's another example of what's happening:

$result = mysql_query($query, $db);
echo "Rows: " . mysql_num_rows($result) . " <BR />";

$player_array = mysql_fetch_assoc($result);
echo "<pre>";
print_r($player_array);
echo "</pre>";

Which outputs:

Rows: 9 
Array
(
    [playerID] => 10000030
)

TL;DR: I submit queries which return multiple rows, but fetch_array only gives me a small portion of those rows in the resulting array.

Shakti Singh · Accepted Answer · 2011-03-07 09:58:10Z

8

mysql_fetch_assoc returns only one row in once you have to use loop to retrieve all rows

while($row = mysql_fetch_assoc($result))
{
   print_r($row);
}

edited Mar 7, 2011 at 9:58

answered Mar 7, 2011 at 9:47

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

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

Comments

Felix Kling · Accepted Answer · 2011-03-07 09:53:09Z

7

Every call to mysql_fetch_assoc($result); gives you one row of the result set:

(from the documentation)

mysql_fetch_assoc — Fetch a result row as an associative array

Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead. mysql_fetch_assoc() is equivalent to calling mysql_fetch_array() with MYSQL_ASSOC for the optional second parameter. It only returns an associative array.

You have to use the function in a loop:

$all = array();

while(($row = mysql_fetch_assoc($result))) {
    $all[] = $row;
}

The example in the document shows how it is done.

edited Mar 7, 2011 at 9:53

answered Mar 7, 2011 at 9:48

Felix Kling

820k181 gold badges1.1k silver badges1.2k bronze badges

2 Comments

sejje Over a year ago

My apologies for being a dunce. This is on the tail end of a huge debug and I haven't used PHP much in years. That's my story!

Felix Kling Over a year ago

@sejje: No need to apologise :)

hcb · Accepted Answer · 2011-03-07 09:49:18Z

2

mysql_fetch_assoc doesn't work that way, you need to call it multiple times to get all rows. Like this:

while ($row = mysql_fetch_assoc($db_result))
{
  print_r($row);
}

answered Mar 7, 2011 at 9:49

hcb

8,3971 gold badge20 silver badges17 bronze badges

Collectives™ on Stack Overflow

mysql_fetch_array does not retrieve all rows

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related