query problem

[Stooney]

I have a query that should have 10 results.  I make the query, then count the results with mysql_num_rows().  It says 10, that's good.  Then as soon as I do mysql_fetch_array, then count that, it's only 2.  Makes no sense, here's there relevant code:

 

$table_items=mysql_query("SELECT itemnum FROM stauctioneer_tableitems WHERE `table`='$tid' ORDER BY slot ASC");
echo mysql_num_rows($table_items);  //outputs 10

$items=mysql_fetch_array($table_items);
echo count($items);  //outputs 2

I'm stumped on this one.  I've echoed the exact query and ran it through phpmyadmin, it works fine.  I've tried different values for $tid, same results.  Any help is appreciated.

[Aureole]

I just did a little test on my site.

 

<?php

$table_items = mysql_query( "SELECT `mem_id` FROM `members`" );

echo mysql_num_rows( $table_items ); // Outputs 9

 

$items = mysql_fetch_array($table_items);

var_dump( $items ); // Outputs array(2) { [0]=> string(1) "1" ["mem_id"]=> string(1) "1" }

 

echo count( $items ); // Outputs 2

 

$i = 1;

while( $test = mysql_fetch_array( $table_items ) )

{

$i++;

}

echo $i; // Outputs 9

?>

 

I think it's because you're counting the array $items... which has two parts within... for me anyway.

[Stooney]

Hmm.  I just tried something else, makes even less sense now

 

<?php
$table_items=mysql_query("SELECT itemnum FROM stauctioneer_tableitems WHERE `table`='$tid' ORDER BY slot ASC");
echo mysql_num_rows($table_items);     //outputs 10
$items=mysql_fetch_array($table_items);
echo count($items);                            //outputs 2
print_r($items);                                  //outputs Array ( [0] => 77 [itemnum] => 77 ) 
?>

 

p.s. I need to sleep, I'll check replies in the morning.

 

 

[pdkv2]

mysql_fetch_array() will return the array containing the fields of only one row out of fetched rows.

 

where as you should use mysql_fetch_array() ten times to fetch all the ten rows.

 

Cheers !

[Aureole]

That's why when I did the thing where I looped (with while()) and incremented $i each time, it was 9 (for me) like it should have been.

[pdkv2]

yes exactly !

[gizmola]

pdkv2 has the answer - your row has 2 columns because mysql_fetch_array creates both a numeric array of the columns of the row and an associative array.  Since your query returns 1 column, when you try and do a count() on it, you get the answer of 2.

 

I personally use mysql_fetch_assoc()  which is a wrapper around mysql_fetch_array() that only returns the associative array version of the row.  Really if you look at the php.net manual page for either function it has an example of how you should loop through the result set fetching rows until the set is empty, as this is fairly typical code.

 

After your first fetch, you'd be able to do this:

 

echo "Item num: $items['itemnum']";