mySQL: Can you spot my mistake

[Flash Gordon]

Can you spot the mistake in my query?
$results = mysql_query("SELECT username, password, confirmation_day FROM " . $database_table . "WHERE status='active'");
while($row = mysql_fetch_array($results)) {
$contents[] = $row;
$contents_username[] = $row['username'];
$contents_password[] = $row['password'];
$contents_confirmation_day = $row['confirmation_day'];
}
print_r($contents); If I take out "WHERE" i get stuff printed to screen. With it there, I get nothing. :(

Perhaps it is getting too late to be doing mySQL right now.
FG

[Cota]

I'm not sure about this, but if its like MS SQL, then "active" is not the same as "Active"....could it be a case sensitivity issue?

[CyanBlue]

I don't... But then I suck at SQL commands...

What I normally do is to type in the SQL command in the phpMyAdmin page to see if I get the results or not and then move that to PHP script... What do you get when you type it in phpMyAdmin page???

Also try 'LIKE' instead of '=' in the WHERE clause to see if that maeks any difference...

[Flash Gordon]

I'm not sure about this, but if its like MS SQL, then "active" is not the same as "Active"....could it be a case sensitivity issue?Nah, that's not it. It is definately a lowever case "active".

myPHPAdmin produces this: attached.

As you can see it is basically about the same coding.

EDIT: perhaps I need this to add "status" to my selection. SELECT username, password, confirmation_day, status ...................hmmmmm.
EDIT2: Still no love adding status to my selection.

I have been try to use = more so than like because I remember Xeef raise a security issue when i was using that before. He also said that = was faster than LIKE

[CyanBlue]

The only reason why I suggest 'LIKE' was to see if you had 'active'/'Active'... That's all... Other than that, you've got to use '=' rather than 'LIKE'... Don't have any clue on that PHP script though... Hm...

[Flash Gordon]

*pulling hair out*
This is the output of this code:
Array ( [0] => Array ( [0] => active [status] => active [1] => ** [username] => ** [2] => ** [password] => ** ) ) $results = mysql_query("SELECT status, username, password FROM " . $database_table); Now when I simply add "=" OR "LIKE" I get nothing.

Go figure......

EDIT: Notice I didn't have a space after $database_table. Should be " Where...." However, that still didn't fix it.

[Flash Gordon]

Okay here is what happened:

My original probelm was the lack of space after $database_table. In the chaos of looking around for the error and trying every combination, and extra ); got thrown in there, producing errors and killing my code.

It appears to work now. :)

[CyanBlue]

Huh??? I don't understand that 'space' bit... Can you show me the actual line that works???

[Flash Gordon]

WRONG:$results = mysql_query("SELECT username, password, confirmation_day FROM " . $database_table . "WHERE status='active'");
CORRECT:$results = mysql_query("SELECT username, password, confirmation_day FROM " . $database_table . " WHERE status='active'");
"space" before " WHERE".
---------------^

[Cota]

Ah, it was seeing the value of database_table and "WHERE" as one word....yeah, spacing kills..

[CyanBlue]

Doh!!! :D

[Flash Gordon]

I really got to spot doing mySQL at night.....
$insert = "UPDATE $database_table SET 'status' = 'active' WHERE 'id' = '3'";
$ok = mysql_query($insert) or die("Query failed : " . mysql_error());
Output:Query failed : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''status' = 'active' WHERE 'id' = 3' at line 1
myPHPadmin: UPDATE `users` SET `status` = 'active' WHERE `id` = '3' LIMIT 1 ;

Is this another DUH!!! ?

[Cota]

whats with the extra tics, ` for?

[Flash Gordon]

Thoses are "Magic Quotes" (or back tics) i belive. They do special stuff, but there aren't needed in mysql_queries, at least not that i think.

hmmm......

Oh, and here is the kicker, I get no syntax error when i run the script locally....

[Flash Gordon]

I need to go flush my head......
Thanks Cota..fixed it..but I have no idea WHY thoses are need. I have never used them before.....

I suppose they `` designate the "field names" and quotes are the variables....but still never needed it before.

[Cota]

Why cant everyone use good 'ole SQL, with no magic quotes...

[Flash Gordon]

Magic Quotes: http://www.webmasterstop.com/63.html

althought I'm not so sure it is right......

EDIT: Yeah, I don't think that article is completely correct. Notice Post #9 has ' in it and it WORKED! According to the article it shouldn't have.

[Cota]

Thats one area where MS SQL is better....good ole SQL, not magic quotes..

[Flash Gordon]

Yeah, it would be better except for the MS part :p

[Flash Gordon]

$insert = "UPDATE table SET status = 'active', confirmation_day = '" . date('l') . "' WHERE username = '" . $payer_email . "'";
$ok = mysql_query($insert) or die("Query failed : " . mysql_error());

There is the correct syntax without the magic quotes.

I was "" the feild names. => NO NO

DOH!

[ovydiu]

Ah, it was seeing the value of database_table and "WHERE" as one word....yeah, spacing kills..
that's just what i was about to say.. ;)