Accessing a mySQL result array with PHP

mySQL, foreach, while loops and print_r not giving the same result...

[mipapage]

As the subject says, I'm querying a database, and getting different results with mySQL, foreach, while loops and print_r

Only the mySQL command line is giving me the result that I am after - I've checked my 'I'm a PHP rookie' manuals, and no luck. Any help?

$result = mysql_query('SELECT field FROM database'); 
$row = mysql_fetch_array($result); 
print_r ($row); 
print "\n<ul>\n"; 
while ( $row = mysql_fetch_array($result) ) { 
print "<li>" . $row["field"] . "</li>\n";} 
print "</ul>\n";

Problems:

1. The print_r gives me the [0] and the first $row[field] result (both results the same value):

   Array ( [0] => result [Field] => result )

   
   
2. The While loop gives me the rest of the results that should be in the array, minus the first two that appear in the print_r above!
   

I've tried using a foreach, but it gives me the same result as the print_r ($row); above. I've tried both methods with and without the print_r, which I was using for debugging when I saw the results were weird.

Querying directly in mySQL gives me the whole result set.

Any ideas greatly appreciated!

[ukgimp]

[webmasterworld.com...]

might help.

[mipapage]

ukgimp,

Thanks for the reply!

Okay, funny thing is that I've read that thread before!
Not so funny is that it gives me the same result as #2 above: it's cutting off the first value.
Print_r ($row) gives me #1 above.

Here's what I wrote, based on coopsters advice from that thread (changes in bold):

$result = mysql_query('SELECT field FROM database'); 
$row = mysql_fetch_array($result); 
print_r ($row);  
print "\n<ul>\n"; 
while ( $row = mysql_fetch_array($result) ){ 
$page = $row[Field]; 
print "<li>" . $page . "</li>\n";} 
print "</ul>\n"; 

I see what coopster's saying, but from what I've read putting it into another variable isn't necessary, I think, 'cause in my case as as it loops through it writes the code, no?

<that sliding sound is my small handle on PHP getting away from me...>

[gethan]

Try these;

1 ===
$result = mysql_query("your query");
while ($row = mysql_fetch_array($result)) {
print_r($row);
}
2 ===
$result = mysql_query("your query");
while ($row = mysql_fetch_array($result)) {
print "<li>{$row[field]}</li>\n";
}
3 ===
$result = mysql_query("your query");
while ($row = mysql_fetch_array($result)) {
foreach ($row as $key => $val) {
print "$key = $val\n";
}

Each of those tests will give you the same results. The reason your test gives you different results is that:

$result is a set of all the matching rows returned by the query. A pointer initially is set to the first row, mysql_fetch_array puts that row into an array and moves the pointer on. mysql_fetch_array returns false when the pointer is pointing at nothing and stops the while loop.

I hope this helps :)

[Birdman]

Here is the format I use and it works well for me:

$query = "SELECT field FROM table";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result))
{
$page = $row['field'];
}

[brotherhood of LAN]

>first $row[field] result (both results the same value):

You can prevent that by using either/or of these

$result = mysql_query($query,MYSQL_ASSOC);
$result = mysql_query($query,MYSQL_NUM);

The first option will return the database rows with the column names in your database as the array keys. the second choice uses integers in the keys.

[mipapage]

Okay, here goes an answer to you all!
Thanks a lot to each of you for the help.

---

gethan
I tried all 3, and all 3 give the same results, and they give the results I was expecting!

$result is a set of all the matching rows returned by the query. A pointer initially is set to the first row, mysql_fetch_array puts that row into an array and moves the pointer on. mysql_fetch_array returns false when the pointer is pointing at nothing and stops the while loop.

Okay, I understand this, and even thought that my problem may be 'pointer' related, but have failed to grasp the signifigance of how this applies to my situation; can you elaborate?

---

Birdman
Yours works too, dammit!

For whatever reason, using $query and $result the way you have built it gets all of the results, while the way that I did it in msg#3 hacks off the first result.

I built a page with gethan's ideas and then added yours underneath his, after having tried his - the result when I added your was that the result for his last method returned "0 = Value Field = Value" instead of all the values as it had done previously. I'm sure this makes sense, but right now, I don't get it!

---

brotherhood of LAN
I haven't quite finished trying yours out, I am so excited that this is actually working. I imagine that it is going to teach me something significant when I try it though :-}

[ergophobe]

Okay, I understand this, and even thought that my problem may be 'pointer' related, but have failed to grasp the signifigance of how this applies to my situation; can you elaborate?

Excuse me if I misunderstand your question and this is overly simplistic, but I think it's based on a very simple misunderstanding.

mysql_fetch_array returns an array that is equivalent to one row

Let's say your table looks like this

index ¦ data
00001 ¦ Tom
00002 ¦ mipa
00003 ¦ birdman

your query is SELECT * FROM table.
mysql_fetch_array returns a one-dimensional array with two elements, index=00001 and data='Tom'. There is nothing else there. When you use print_r it prints out

array (0=>1, 1=>'Tom').

Now, when you go through the while loop, every time you call mysql_fetch_array, the pointer advances one row. So the next time through, mysql_fetch_array again returns a one-dimensional array, but this time with index=00002 and data='mipa'.

If you do print_r, your output is

array (0=>2, 1=>'mipa').

The while loop goes until mysql_fetch_array returns false, in other wrds the pointer has advanced beyond the scope of the data set. This stops the loop.

In other words

using a while/mysql_fetch_array combo effectively loops through the entire data set.

using foreach and print_r effectively "loop" (used loosely of course for print_r) through the single-row array that is returned by mysql_fetch_array.

Does that help make it clear?

Tom

[mipapage]

Does that help make it clear?

Ahhhh! YESSSS!

Excuse me if I misunderstand your question and this is overly simplistic, but I think it's based on a very simple misunderstanding.

You are correct! I knew there was something key and basic that I wasn't getting here - Thank you for showing me the way!

[ergophobe]

Wow, font size 5 - I'm flattered (and glad to be of assistance).

I thought everyone might be missing your question. I've been showing my wife how to do some PHP/MySQL programming and she had the *exact* same problem. It's helped me see some of the more basic problems that folks have, that many of us have forgotten.

I'm keeping track of them for an eventual "Orientation to PHP" post.

Cheers,

Tom

[gethan]

> can you elaborate?

I think ergophobe covered it nicely :)

Try print_r on the $result as well... probably not what you would expect.

I use a little function like this for debugging,

function debug($var) {
print "<pre>";
print_r($var);
print "</pre>";
}
$myvar = "Some random variable";
debug($myvar);

[mipapage]

Thanks gethan,

I'm just playing around now, got the 'problem script' working nicely, thanks to you all.

Much appreciated!