MySQL Query Enum

Question

I have been working on this for a while now, I know it's simpler than what I am making it, but I just can't get it. I have some code where I am trying to query an enum either 1 or 0 from my table so this is exactly what I have to do this.

$username = 'test'
$passResult = mysql_query("SELECT usrSetPass FROM members WHERE usr='.$username.'");

Now I have all the connection stuff down I think, I get no errors there, but when I print this thing out in my echo I get this,

Heres my echo:

echo 'Hello, '.$username.', you Result is: '.$passResult.'!';

What I want to get is:

Hello, test, your Result is: 1

or 

Hello, test, your Result is: 0

Now what I get is:

Hello, test, your Result is: Resource id #6

Now no matter what I do I get the same thing, I have no idea what I'm doing wrong here guys if someone could point this out that would be awesome. What this enum is being use essentially for a boolean just to see if the user has personally set a password not the computer generated version.

Unrelated comment: Please use [prepared statements](http://stackoverflow.com/questions/1290975/how-to-create-a-secure-mysql-prepared-statement-in-php) instead of just concatenating `$username` into the query string. It's much safer in general, and leads to less security breaches through SQL injection. — Sebastian Paaske Tørholm, Jan 24 '11 at 09:01

score 2 · Accepted Answer · answered Jan 24 '11 at 09:02

2

mysql_query returns a result resource, essentially a pointer to the memory where the results are buffered. That result set can contain many rows, as you can select many rows, so you need to fetch the row(s) you want then the column(s) you want from those rows.

/* execute the query and get a result resource back */
$passResult = mysql_query("SELECT usrSetPass FROM members WHERE usr='" . mysql_real_escape_string($username) . "'");

/* retrieve the first row from $passResult */
$row = mysql_fetch_assoc($passResult); 

/* assign the usrSetPass column's value from that row to $passed */
$passed = $row['usrSetPass'];

Also, your query is wrong. You enclosed it in double quotes, so you're not actually breaking out of the string and concatenating $username when you use the single quotes and dots inside. I've corrected it above.

answered Jan 24 '11 at 09:02

Dan Grossman

51,866
10
112
101

Okay so I understand what you are doing here, but now I'm getting this Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in C:\wamp\www\Login\Test.php on line 16 Thanks for your help – awmayhall Jan 24 '11 at 09:17
`mysql_query` will return false if the query failed to run. That's what happened, so you can't get a row out of a nonexistant result set. `echo mysql_error($passResult)` to see the error message MySQL sent. – Dan Grossman Jan 24 '11 at 09:21
If my response answered your question please click the checkmark to mark it as so here :) – Dan Grossman Jan 24 '11 at 09:26

Bjoern · Answer 2 · 2011-01-24T09:15:43.807

mysql_query doesn't return a value, it returns a resource (see here in the manual).

The returned result resource should be passed to another function for dealing with result tables (like mysql_fetch_array() or mysql_fetch_assoc()), to access the returned data.

Example based on your initial code:

$username = 'test';
$passResult = mysql_query("SELECT usrSetPass FROM members WHERE usr='".$username."'");
while ($row = mysql_fetch_assoc($passResult)) {
    echo $row['usrSetPass'];
}

MySQL Query Enum

2 Answers2