Welcome to WebmasterWorld Guest from 54.166.152.121

Forum Moderators: open

Message Too Old, No Replies

SQL Inner join strage problem

     
6:16 pm on Feb 10, 2011 (gmt 0)

5+ Year Member



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
7:36 pm on Feb 10, 2011 (gmt 0)

5+ Year Member



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`");
6:02 pm on Feb 11, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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 . . . "
 

Featured Threads

Hot Threads This Week

Hot Threads This Month