Trouble with SELECT using multiple variables. (SOLVED)

[Usarjjaco]

Hey Guys;plain and simple; trying to get results to display based on comparing two variables.. but keep getting a "not valid sql resource" error. I think it may have something to do with the fact I query the 2 variables but I don't pull them... I am trying to find out how to pull them to the script without showing them in the front end... here is what I have.

$lati=mysql_query("SELECT lat FROM survivors WHERE username =$'uname'");$longi=mysql_query("SELECT long FROM survivors WHERE username=$'uname'");$query =mysql_query("SELECT * FROM buildings WHERE lat=$'lati' AND long=$'longi'");echo "<table frame ='hsides' table border='2' table bgcolor='burlywood'><tr><th align='center'><font color='white'>Choices</font></th><th align='center'><font color='white'>Stories</font></th><th align='center'><font color='white'>Windows</font></th><th align='center'><font color='white'>Doors</font></th><th align='center'><font color='white'>Barricaded?</font></th></tr>";while($row = mysql_fetch_array($query))

Of course below the while statement is where I have the results table where the data should post; and the connection data above the code; if you guys need to see it let me know. I'm guessing there is a simple answer to this. I'm just trying to get the database to show the user which buildings are located at their current coordinates.P.S. $'uname' is a session variable that is the user's indentifier.Thanks!JJ

[jeffman]

Errm, typo?$'uname'

[Usarjjaco]

Errm, typo?$'uname'

Yes, Sorry; I have it right; even with the correct ' ' it is still giving me the not a valid resource error for the fetch line. Any ideas?

[justsomeguy]

That just means the query failed, you can check for an error from MySQL:$lati=mysql_query("...") or trigger_error(mysql_error(), E_USER_ERROR);or:$lati=mysql_query("...") or exit(mysql_error());

[Usarjjaco]

That just means the query failed, you can check for an error from MySQL:$lati=mysql_query("...") or trigger_error(mysql_error(), E_USER_ERROR);or:$lati=mysql_query("...") or exit(mysql_error());

Fatal error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'long FROM survivors WHERE username=$'uname'' at line 1 in /home5/theripti/public_html/zombies/investigatebuilding.php on line 79That's the error I get; but I've used other lines using the same $'uname' session variable and they work fine... Any ideas?

[justsomeguy]

First, "$'uname'" is not valid syntax to refer to a variable, if you're trying to put a variable value in a string do it like this:"SELECT lat FROM survivors WHERE username ='{$uname}'"But that's not the error the error message is talking about, you're using "long" for a column name and that's a reserved word in SQL, it's a numeric data type. You need to surround that in backquotes, e.g.:SELECT `long` FROM ...

[Usarjjaco]

First, "$'uname'" is not valid syntax to refer to a variable, if you're trying to put a variable value in a string do it like this:"SELECT lat FROM survivors WHERE username ='{$uname}'"But that's not the error the error message is talking about, you're using "long" for a column name and that's a reserved word in SQL, it's a numeric data type. You need to surround that in backquotes, e.g.:SELECT `long` FROM ...

Now using this code:

$longi=mysql_query("SELECT 'long' FROM survivors WHERE username=$'{uname}'") or trigger_error(mysql_error(), E_USER_ERROR);$lati=mysql_query("SELECT lat FROM survivors WHERE username=$'{uname}'") or trigger_error(mysql_error(), E_USER_ERROR);

And getting this error:Fatal error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''{uname}'' at line 1 in /home5/theripti/public_html/zombies/investigatebuilding.php on line 79Thanks for all your help btw.

[justsomeguy]

This:username=$'{uname}'is not the same as this:username ='{$uname}'

[Usarjjaco]

This:username=$'{uname}'is not the same as this:username ='{$uname}'

you sir are a godsend . .. However the problem I'm having now is: Not getting any error; but it's not returning a result.My users position is lat 4255 and long -8225 There should be one building at this location based on the data; but it is not showing up... ?Here is the code:

<?php$longi=mysql_query("SELECT 'long' FROM survivors WHERE username='{$uname}'") or trigger_error(mysql_error(), E_USER_ERROR);$lati=mysql_query("SELECT lat FROM survivors WHERE username='{$uname}'") or trigger_error(mysql_error(), E_USER_ERROR);$query =mysql_query("SELECT * FROM buildings WHERE lat='{$lati}' AND 'long'='{$longi}'");echo "<table frame ='hsides' table border='2' table bgcolor='burlywood'><tr><th align='center'><font color='white'>Choices</font></th><th align='center'><font color='white'>Stories</font></th><th align='center'><font color='white'>Windows</font></th><th align='center'><font color='white'>Doors</font></th><th align='center'><font color='white'>Barricaded?</font></th></tr>";while($row = mysql_fetch_array($query)){echo "<tr>";echo "<td align='center' font color='white'><a href='http://$row[link]'>$row[linkname]</a></td>";echo "<td align='center'>$row[stories]</td>";echo "<td align='center'>$row[windows]</td>";echo "<td align='center'>$row[doors]</td>";echo "<td align='center'>$row[barricaded]</font></td>";echo "</tr>";}echo "</table>"?>

[justsomeguy]

$query =mysql_query("SELECT * FROM buildings WHERE lat='{$lati}' AND 'long'='{$longi}'");$lati and $longi are not values, they are MySQL result resources. You need to get the row and value from each one if you want to use it in another query.

[Usarjjaco]

Ok;What would be the quickest way to do that? I know this is totally a hassle for you but I'm learning from scratch so your help is appreciated. How would I get the values for them to user in my final query without the previous query being visible to the front-end user? ... I only want the front - end user to see the end query result.

[justsomeguy]

You can just use mysql_fetch_assoc to get the row from the result, and the value will be in that row. The result would probably only have 1 row in it, assuming there's only one entry for each user. If a user has more than one entry and you want to return everything associated with it, that's an entirely different query altogether.

[Usarjjaco]

So are you saying add in: $result2=mysql_fetch_assoc($longi) $result3=mysql_fetch_assoc ($lati) and then change the $query =mysql_query("SELECT * FROM buildings WHERE lat='$lati' AND 'long'='$longi'");TO$query =mysql_query("SELECT * FROM buildings WHERE lat='$result2' AND 'long'='$result3'");and then use the samewhile($row = mysql_fetch_assoc($query))Does that look right?

[justsomeguy]

mysql_fetch_assoc returns an array, so you would need to access the specific field in the array. e.g.:$row = mysql_fetch_assoc($lati);$lat = $row['lat'];

[Usarjjaco]

OkWell I input that information; page works without error but it's still not showing any results; Here's the code:

<?php$longi=mysql_query("SELECT 'long' FROM survivors WHERE username='{$uname}'") or trigger_error(mysql_error(), E_USER_ERROR);$lati=mysql_query("SELECT lat FROM survivors WHERE username='{$uname}'") or trigger_error(mysql_error(), E_USER_ERROR);$result2=mysql_fetch_assoc($lati);$lat=$row['lat'];$result3=mysql_fetch_assoc($longi);$long=$row['long'];$query =mysql_query("SELECT * FROM buildings WHERE lat='{$lat}' AND 'long'='{$long}'");echo "<table frame ='hsides' table border='2' table bgcolor='gray'><tr><th align='center'><font color='white'>Choices</font></th><th align='center'><font color='white'>Stories</font></th><th align='center'><font color='white'>Windows</font></th><th align='center'><font color='white'>Doors</font></th><th align='center'><font color='white'>Barricaded?</font></th></tr>";while($row = mysql_fetch_assoc($query)){echo "<tr>";echo "<td align='center' font color='white'><a href='http://$row[link]'>$row[linkname]</a></td>";echo "<td align='center'>$row[stories]</td>";echo "<td align='center'>$row[windows]</td>";echo "<td align='center'>$row[doors]</td>";echo "<td align='center'>$row[barricaded]</font></td>";echo "</tr>";}echo "</table>"?>

[justsomeguy]

$result2=mysql_fetch_assoc($lati);$lat=$row['lat'];You're mixing variable names, you're getting the array into $result2 but then trying to get a value from $row.

[Usarjjaco]

$result2=mysql_fetch_assoc($lati);$lat=$row['lat'];You're mixing variable names, you're getting the array into $result2 but then trying to get a value from $row.

Ha! Like a charm! .. Thanks a bunch; seems to be working good now. Thanks for all your help

[Usarjjaco]

Ok Now There's still one problem. After a few tests I've realized that it's actually only taking the first out of the two variables... so it's only showing the buildings based on latitude and not longitude... Any ideas?

[justsomeguy]

I would need to see the code, but the way you get the longitude from the result would be the same as that for the latitude.

[Usarjjaco]

I would need to see the code, but the way you get the longitude from the result would be the same as that for the latitude.

Yeah, I ended up figuring it out; .. don't know if it's the "right" way but it works. It seemed to not be pulling both variables because I was pulling two variables from the same table but using to query's to do it. I dropped it down to one query and bam, worked like a charm.