homepage Welcome to WebmasterWorld Guest from 54.242.18.190
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved