Page 1 of 1

Query problem

Posted: Fri May 26, 2006 12:07 pm
by _ThndR
Hey all,

I've got this little problem with a query... I just don't see what's wrong with the syntax.

This is the code:

Code: Select all

<?
$sessid = session_id();
if(!($result = @mysql_query("SELECT * FROM user_active WHERE sessid=\"".$sessid."\" AND userid=".$_SESSION['userid']." AND ip=\"".$_SERVER['REMOTE_ADDR']."\"", $connection)))
	  ShowError();
?>
The query gives me this error:
Error 1064 : 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 '' at line 1

I guess I'm just missing something.

Any help would be greatly appreciated!
Thanks in advance.

Posted: Fri May 26, 2006 12:17 pm
by Flamie
ok what are the types of your fields and whats $connection?

Posted: Fri May 26, 2006 12:20 pm
by _ThndR
Thanks for the fast reply!

$connection is just a mysql connection.
sessid is a varchar(100), ip is varchar(20), userid is int(5). Inserting values into this table works fine.

Posted: Fri May 26, 2006 12:22 pm
by PrObLeM
Flamie wrote:ok what are the types of your fields and whats $connection?
$connection is the resource link_identifier ( http://www.php.net/mysql_query )

but try the query without the slash double quotes ( " ") like so:

Code: Select all

if(!($result = @mysql_query("SELECT * FROM user_active WHERE sessid='".$sessid."' AND userid=".$_SESSION['userid']." AND ip='".$_SERVER['REMOTE_ADDR']."'", $connection)))

Posted: Fri May 26, 2006 12:25 pm
by PrObLeM
oh and you don't need a varchar(100) for the session id:

From the manual: ( http://www.php.net/session_id )
Length of PHPSESSID appears to be 32 characters by default.

Posted: Fri May 26, 2006 12:27 pm
by _ThndR
PrObLeM, thanks for the replie and the tip!

I tried the quote thing you suggested, but still no luck though :)

Posted: Fri May 26, 2006 12:45 pm
by KalvinB

Code: Select all

<? 
$sessid = session_id(); 
if(!($result = @mysql_query("
SELECT 
* 
FROM 
user_active 
WHERE 
sessid=\"".$sessid."\" 
AND userid=".$_SESSION['userid']." 
AND ip=\"".$_SERVER['REMOTE_ADDR']."\"
"
, $connection))) 
     ShowError(); 
?>
The only field that isn't quoted and therefore the cause of your problem is userid

Your query equates to

SELECT * FROM user_active WHERE sessid="$sessid" AND userid= AND ip="$_SERVER['REMOTE_ADDR']"

Your session is most likely not set to a value

You can either fix that or put quotes around the value to allow for empty userids.

To make debugging oh so much easier in the future:

Code: Select all

<? 
$sessid = session_id(); 

$query ="
SELECT 
* 
FROM 
user_active 
WHERE 
sessid=\"".$sessid."\" 
AND userid=".$_SESSION['userid']." 
AND ip=\"".$_SERVER['REMOTE_ADDR']."\"
";

$result = mysql_query($query, $connection) or exit(my_sql_error() . "<br>" . $query);

?>
This way you can see what query is actually being processed.

Posted: Fri May 26, 2006 1:22 pm
by _ThndR
Thanks for the reply!

The $_SESSION['userid'] is set, 10 in this case. And, when I just echo out the $query, everything seems to be fine:
SELECT * FROM user_active WHERE sessid="68bf87dc2e45282b98fdcae0e5cd39xx" AND userid=10 AND ip="xx.xxx.xx.xxx"

Putting quotes around the userid doesn't seem to make a difference...

Strange eh? ;)

Re: Query problem

Posted: Fri May 26, 2006 1:45 pm
by RobertGonzalez

Code: Select all

<?php
$sessid = session_id();
$sql = "SELECT * 
        FROM user_active 
        WHERE sessid = '$sessid' 
        AND user_id = " . $_SESSION['userid'] . " 
        AND ip = '" . $_SERVER['REMOTE_ADDR'] . "'";

if(!($result = @mysql_query($sql, $connection)))
	  ShowError();
?>

Posted: Sat May 27, 2006 2:45 am
by _ThndR
Hmm still no luck either... The SQL syntax looks fine to me, though. Strange

Posted: Sat May 27, 2006 3:07 am
by RobertGonzalez

Code: Select all

<?php
$sessid = session_id();
$sql = "SELECT *
        FROM user_active
        WHERE sessid = '$sessid'
        AND user_id = " . $_SESSION['userid'] . "
        AND ip = '" . $_SERVER['REMOTE_ADDR'] . "'";

if ( !$result = mysql_query($sql) )
{
    die("Could not get the selected data: " . mysql_error());
}
?>
Run this the way it is and report back the error that is thrown.

Posted: Sat May 27, 2006 3:52 am
by _ThndR
I ran it like you said; only this is the output:

Error 1064 : 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 '' at line 1

Strange thing is that the "Could not get the selected data: " part isn't displayed... Though I'm very sure this query produces the error!

Posted: Sat May 27, 2006 4:06 am
by _ThndR
Well... I fixed my problem :P

[Shame Mode]
The error was produced by a function that is called right after that query I posted here... I gave it $_SESSION['userId'] as parameter, but it had to be $_SESSION['userid']... That produced the error.
[/Shame Mode]

Omg I'm really taking down the status I didn't have.
Sorry to waste your time with my looking-not-carefully-enough ;)

Thanks for your time, and I'll eventually learn from these stupid mistakes :P

Posted: Sat May 27, 2006 9:56 am
by RobertGonzalez
No prob. I'm sure most of us have done something along those lines. One this I would suggest is to set up error checking on all your db interaction, if for nothing else, then for telling you where and why things get porked. Just a suggestion, and good luck with the rest of your script.