SQL Inner join strage problem

[mhulbrock]

I have done inner joins many times, for the life of me, i cannot figure out why this statement is not working. This is designed to filter by state from a GET

$state = 'NJ';
echo($state);
$dealersState = mquery("SELECT cscart_users.user_id, cscart_users.company,cscart_users.url,cscart_user_profiles.b_state
FROM cscart_users
INNER JOIN cscart_user_profiles
ON cscart_users.user_id=cscart_user_profiles.user_id
WHERE cscart_users.user_id = $userID AND cscart_user_profiles.b_state=$state
ORDER BY cscart_users.company");
 while ( $row = mysql_fetch_array($dealersState) ) {
 company($row['user_id'],$row['url'],$row['company']);
}

Error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/spatu0/public_html/dealers.php on line 108


BUT THIS ONE WORKS

$state = 'NJ';
echo($state);
$dealersState = mquery("SELECT cscart_users.user_id, cscart_users.company,cscart_users.url,cscart_user_profiles.b_state
FROM cscart_users
INNER JOIN cscart_user_profiles
ON cscart_users.user_id=cscart_user_profiles.user_id
WHERE cscart_users.user_id = $userID AND cscart_user_profiles.b_state='NJ'
ORDER BY cscart_users.company");
 while ( $row = mysql_fetch_array($dealersState) ) {
 company($row['user_id'],$row['url'],$row['company']);
}

All i changed was hard coding in the state variable for testing purposes

[mhulbrock]

Here is my solution

$dealersState = mquery("SELECT `cscart_users`.`user_id`, `cscart_users`.`company`,`cscart_users`.`url`,`cscart_user_profiles`.`b_state`
FROM `cscart_users`
INNER JOIN `cscart_user_profiles`
ON `cscart_users`.`user_id`=`cscart_user_profiles`.`user_id`
WHERE (`cscart_users`.`user_id` = $userID) AND (`cscart_user_profiles`.`b_state` = '$state')
ORDER BY `cscart_users`.`company`");

[rocknbil]

Right. Here is your problem.

...AND cscart_user_profiles.b_state=$state
...AND cscart_user_profiles.b_state='NJ'

Textual fields must be quoted.

Additionally this

Error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/spatu0/public_html/dealers.php on line 108

Says you're (probably) not doing this. :-)

$result = mysql_query($query) or die("cannot execute query: " . mysql_error());

Which would have told you "You have an error in your MYSQL syntax . . . "