Query double posting results?

What is wrong with my DB?

[freshrod]

I thought I had some problem with my PHP code at first, but then I took my code down to the bare essentials and I'm still getting this weird result.

When I run this simple query it prints out each field from the table twice and then moves on to the next field.
(i.e. userId, userId, userName, userName, userInfo, userInfo, etc, etc...)

Here's the code:

<?php

if (!($connection = mysql_connect("localhost", "root", "may1968")))
die ("Cannot Connect to DB!");

if (!(mysql_select_db("mhs", $connection)))
showerror();

function showerror() {
die ("Error " . mysql_errno() . " : " . mysql_error());

$result = mysql_query ("SELECT * FROM users WHERE userId = 1");

while ($row = mysql_fetch_array ($result))
{
foreach ($row as $stuff)
print "{$stuff} ";
print "\n";
}

?>

I've checked and double checked the DB with the MySQL Command Line Client and the table looks fine to me... no doubles there. Any ideas?

[siMKin]

this cannot possibly be the code you've done your own test with. There is at least one } missing.
Also why do you do

print "{$stuff} ";

and not just print $stuff or echo $stuff
?

As for your problem. Try this to further debug it:
<?php
echo "The amount of rows returned by the DB is : ".mysql_num_rows($result)."\n";
echo "<pre>\n";
while ($row = mysql_fetch_assoc($result))
{
print_r($row);
}
echo "</pre>\n";
?>

[dreamcatcher]

Hi freshrod,

I have to ask. Why do you have a foreach loop in your while loop? Also, try enclosing your query in quotes:

$result = mysql_query ("SELECT * FROM users WHERE userId = '1'");
while ($row = mysql_fetch_array ($result))
{
echo $row['userName']; //etc etc
}

dc

[freshrod]

Thanks for helping. I'll try and answer the questions you have.

I was missing a } in the function showerror() code, but only here. It was in the original, I just didn't get it copied.

I tried your debugging suggestion siMKin, and it printed:
The amount of rows returned by the DB is : 1
which is right, but not anything else from the print_r($row);

dreamcatcher: I guess what I was trying to do with the foreach in the while loop was not have to write an echo $row['userName']; etc, etc... for each field. I've seen it done that way in some examples I have in books and it always worked fine, but perhaps I'm using it wrong here. It is returning all the fields in the specified row, but printing each twice for some odd reason.

[siMKin]

Also, try enclosing your query in quotes:

$result = mysql_query ("SELECT * FROM users WHERE userId = '1'");

The only quotes i see added are those around '1' - but you should actually not quote that. It's an integer.

I tried your debugging suggestion siMKin, and it printed:
The amount of rows returned by the DB is : 1
which is right, but not anything else from the print_r($row);

Could you give the full/complete code of what you did?

about the missing }, i guess you haven't simply copy-pasted your whole script here then, but only bits. The problem with that is, that you might also have left out the bit that's causing the problem. So, please copy and paste the whole code here.

[freshrod]

siMKin: Thanks for the help. It actually dawned on me what I was doing wrong while I was taking a shower...lol. (has that happened to anyone else?)

It was double printing because it was returning one set for the while loop and one for the foreach. Since I was only querying one row... I was basically telling it to print twice. Duh! I had used similar code before, but it was when populating a list of several rows, not just one.

I'm still new to PHP (obviously) and I'll probably have more questions as I continue to tidy this project up... But I am learning! Thanks for all the help. You guys are awesome!

[dreamcatcher]

The only quotes i see added are those around '1' - but you should actually not quote that. It's an integer.

I don`t think it really makes any difference.

[siMKin]

well, i never comment on it if people quote integers like that, cause in this case it doesn't matter for the result. It is not correct however, because you're comparing two different types with eachother (an integer and a string).
That in some cases it could yield unexpected results becomes clear when, for example, you define an ENUM field with the values '0',1','2'. These are strings. When you do the opposite of what you suggested:

SELECT * FROM table WHERE enumfield=1
(whereas you should write SELECT * FROM table WHERE enumfield='1')

it will go wrong. MySQL will select all the records where the enumfield equals '0', that one being the 1st value in the list. Similarly 'WHERE enumfield=2' will select all the records where the enumfields equals '1'.

So better make it a habit that you use integers as integers and strings as string. That way you know you'll never go wrong.

[dreamcatcher]

MySQL will select all the records where the enumfield equals '0'

Actually thats not true. I quote an integer and everytime it returns the correct result set from an ENUM field.

dc

[siMKin]

That's my point exactly.
If you quote it, it will return the right result, because the values in an ENUM field are strings. And everything quoted is considered a string too.

BUT, if you don't quote it (so, it's used an integer), you'll get the wrong results.
And this is a good example of a situation in which comparing an integer with a string goes wrong

[coopster]

Although MySQL will do type conversion on-the-fly [dev.mysql.com] it will become confusing if you try to do so with an ENUM column type. This is probably best demonstrated with an example so you can see what siMKin is emphasizing.

CREATE TABLE enumTest (enumCol ENUM('3','2','1')); 
INSERT INTO enumTest VALUES ('2'), (1); 
SELECT * FROM enumTest; 
+---------+ 
¦ enumCol ¦ 
+---------+ 
¦ 2       ¦ 
¦ 3       ¦ 
+---------+

The first insertion was a string value, so ENUM finds that value and uses it to store the request. The second VALUE inserted was a numeric value and ENUM will use the associated index to store the request, in this case the index at position number one in the ENUM set happens to be the value '3'.