| SQL Inner join strage problem
|
mhulbrock
msg:4265265 | 6:16 pm on Feb 10, 2011 (gmt 0) |
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
msg:4265328 | 7:36 pm on Feb 10, 2011 (gmt 0) |
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
msg:4265757 | 6:02 pm on Feb 11, 2011 (gmt 0) |
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 . . . "
|
|
|
