mysql_fetch_array return only one row

Question

Ok, I have the following code:

$array = mysql_query("SELECT artist FROM directory WHERE artist LIKE 'a%' 
        OR artist LIKE 'b%' 
        OR artist LIKE 'c%'");
    $array_result= mysql_fetch_array($array);

Then, when I try to echo the contents, I can only echo $array_result[0];, which outputs the first item, but if I try to echo $array_result[1]; I get an undefined offset.

Yet if I run the above query through PHPMyAdmin it returns a list of 10 items. Why is this not recognized as an array of 10 items, allowing me to echo 0-9?

Thanks for the help.

I know that % is the usual wildcard, but have you tried using * instead? — JMichelB
– JMichelB, Commented Aug 9, 2011 at 18:52

Toby Allen · Accepted Answer · 2014-03-08 21:32:28Z

20

That's because the array represents a single row in the returned result set. You need to execute the mysql_fetch_array() function again to get the next record. Example:

while($data = mysql_fetch_array($array)) {
  //will output all data on each loop.
  var_dump($data);
}

edited Mar 8, 2014 at 21:32

Toby Allen

11.3k12 gold badges80 silver badges132 bronze badges

answered Aug 9, 2011 at 18:51

Dor

7,5225 gold badges36 silver badges47 bronze badges

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

5 Comments

Ando Over a year ago

Thanks a bunch. Oddly enough, when I use this, it doesn't get the value for the data in first row. I.e. Say first artist is Apple, the second is Banana, the third is Cantaloupe. When I var dump, it just does Banana -> Cantaloupe -> etc, no apple.

Dor Over a year ago

@Ando: Does it get 'apple' when you use PMA (PhpMyAdmin)? Could be: (1) Your php-generated SQL query doesn't filter the records appropriately. (2) You don't ORDER BY, but you LIMIT, thus maybe the record with 'apple' is left behind while filtering.

Ando Over a year ago

My SQL query does bring back Apple, and I do ORDER BY artist ASC. What is peculiar is if I ORDER BY desc, then it shows Apple at the bottom, but then it is missing the item that should be first...very odd

Ando Over a year ago

Also, if I use while(list($artist) = mysql_fetch_array($result)) { echo $artist; } the list shows only items 1-9 instead of 0-9.

Dor Over a year ago

Try posting the exact code, table description (SHOW TABLE STATUS LIKE "tbl_name"; DESCRIBE tbl_name;) and output, we'll try and see where's the problem.

veidelis · Accepted Answer · 2011-08-09 18:58:44Z

5

You should be using while to get all data.

$array_result = array();
while ($row = mysql_fetch_array($array, MYSQL_NUM)) {
    $array_result[] = $row;
}
echo $array_result[4];

answered Aug 9, 2011 at 18:58

veidelis

1493 silver badges18 bronze badges

3 Comments

Dor Over a year ago

The data is stored in PHP's memory space anyway. Copying that data is usually useless.

Dave Chen Over a year ago

@Dor I fail to see how moving the data into an array is useless. How else are you going to access the data? Suppose you want the two rows, you're not going to be able to get both of them in the same iteration of the while loop, so it's going to be a good idea to first copy the data into a usable array.

Anthony Over a year ago

@DaveChen i think you're right. this feels like a shortcoming of php.

Pedro Lobito · Accepted Answer · 2011-08-09 19:03:25Z

1

I prefer to use this code instead:

$query  = "SELECT artist FROM directory WHERE artist LIKE 'a%' 
        OR artist LIKE 'b%' 
        OR artist LIKE 'c%'";           
$result = mysql_query($query) or die(mysql_error());


while(list($artist) = mysql_fetch_array($result))
{
echo $artist;
}

If you add more fields to your query just add more variables to list($artist,$field1,$field2, etc...)
I hope it helps :)

answered Aug 9, 2011 at 19:03

Pedro Lobito

99.8k36 gold badges274 silver badges278 bronze badges

1 Comment

Dor Over a year ago

Or: while($data = mysql_fetch_assoc($result)) { extract($data); /*...*/ }. See: php.net/manual/en/function.extract.php

Collectives™ on Stack Overflow

mysql_fetch_array return only one row

3 Answers 3

5 Comments

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related