Jump to content

Trouble with SELECT using multiple variables. (SOLVED)


Usarjjaco
 Share

Recommended Posts

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

Edited by usarjjaco
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Edited by usarjjaco
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...