mysql_fetch_array and while loop in php [closed]

Question

I'm trying to get data from DB and print it on the page. I use next select:

$tableIndex=mysql_query('SELECT table_index FROM table_names');

When I use a while loop to print, it's ok. Code:

while($row = mysql_fetch_array($tableIndex) ){
        echo $row[0].'<br>';}

Result:

1st

2nd

3rd

But without while loop, it gives me just first element of array.

Code:

$row = mysql_fetch_array($tableIndex);
    echo $row[0].'<br>';
    echo $row[1].'<br>';
    echo $row[2].'<br>';

Result:

1st

_

_

(where "_" is blank space)

Can someone expalin why it works so strangely?

Answer 1

You're fundamentally misunderstanding how these things work.

The "array" in mysql_fetch_array is not an array of all the records, but an array of the data within the current record.

In your case, you're only fetching a single field, so the array will only contain a single element (ie $row[0]), but the principle is the same -- it's an array of the single record that you have just read.

Only the data for the current record is in the array at any one time. That's what the while loop is for; it goes back any loads each record one after the other.

If you want to create an array that contains all the data, you need to do it like this:

$fullData = array()
while($row = mysql_fetch_array($tableIndex) ){
    $fullData[] = $row[0];
}

That will put all the data that you're reading into a single big array, which is what you're expecting. Now you can do what you wanted to do in the question:

echo $fullData[0].'<br>';
echo $fullData[1].'<br>';
echo $fullData[2].'<br>';

Hope that helps.

It's worth pointing out here that the mysql_xxx() family of functions are deprected and considered obsolete. If you're just learning PHP (which would seem to be the case), I strongly recommend that you stop learning these functions, and learn the PDO library instead. It is more modern and has a lot of features that the mysql functions can't provide. In addition, future versions of PHP will remove the mysql functions entirely, so you'll have to switch at some point -- it may as well be now, while you're still learning.

Also (to keep things relevant to the question), the PDO library has a feature that does in fact do what you're looking for in a single function: PDO::fetchAll(). Using this method means that you can fetch all the data into a single big array in one line with no need to do a while loop. The code would look a bit like this:

$sth = $dbh->prepare("SELECT name, colour FROM fruit");
$sth->execute();
$result = $sth->fetchAll();

(example taken from the PHP manual for PDO::fetchAll)

Answer 2

Every time your loop loops around, this code is executed again:

$row = mysql_fetch_array($tableIndex);

So every time $row gets the new value. The equivalent without a loop would be:

$row = mysql_fetch_array($tableIndex);
echo $row[0].'<br>';
$row = mysql_fetch_array($tableIndex);
echo $row[0].'<br>';
$row = mysql_fetch_array($tableIndex);
echo $row[0].'<br>';