Page 1 of 1

Odd behavior for tricky select sql query

Posted: Thu Feb 24, 2005 9:47 pm
by gjb79
:?:
I have a script that demands a bit of filtering from an sql database.

Code: Select all

$GBookID = $HTTP_GET_VARSї'GBookID']; 
$GChapterID = $HTTP_GET_VARSї'GChapterID']; 
$LetterID = $HTTP_GET_VARSї'LetterID']; 


mysql_select_db($database_lexicon, $lexicon);
$query_Characters = "SELECT * FROM `character` WHERE CNameL like '$LetterID%' AND (BookID < '$GBookID') OR (BookID = '$GBookID' AND ChapterID <= '$GChapterID') ORDER BY `CNameL` ASC";
$Characters = mysql_query($query_Characters, $lexicon) or die(mysql_error());
$row_Characters = mysql_fetch_assoc($Characters);
$totalRows_Characters = mysql_num_rows($Characters);
As you can see, it should first limit the results to the first letter of CNameL being equal to LetterID, Then it filters it by GBookID and GChapterID. When I seperate this script so it searches and filters by just the LetterID part everything works well. Also when I filter by GBookID and GChapterID, Everything works perfectly.

The problem occurs when I combine them. For some reason when GBookID = 3, everything works perfectly with the LetterID and GBookID and GChapterID. But when GBookID = anything else, The results that appear are incorrect. Random entries, starting with any letter not just the one specified in LetterID, appear.

I hope I am making since. Does anyone have an Idea as to what is causing this? or what I could do to fix it?

Posted: Thu Feb 24, 2005 9:50 pm
by feyd
you have a precedence problem... you need to add some parens.

Posted: Thu Feb 24, 2005 9:52 pm
by timvw
You are mistaken in your operator precedence...
select conda AND ( ( condb1 and condb2 ) or ( condc1 and condc2 ) )


But $HTTP_*_VARS is so oldstyle. Today we use $_GET and $_POST.
And you might want to use mysql_escape_string and other validation to make sure $_GET['LetterID'] is a character..


[edit] beaten by the fingerquick master[/edit]

Well....

Posted: Thu Feb 24, 2005 9:59 pm
by gjb79
Ahh Ofcourse!!! Thank you both so very much!

I wonder why, after spending hours looking through code for mistakes, it always ends up being something simple.

:lol:

Solution

Code: Select all

mysql_select_db($database_lexicon, $lexicon);
$query_Characters = "SELECT * FROM `character` WHERE (CNameL like '$LetterID%') AND ((BookID < '$GBookID') OR (BookID = '$GBookID' AND ChapterID <= '$GChapterID')) ORDER BY `CNameL` ASC";
$Characters = mysql_query($query_Characters, $lexicon) or die(mysql_error());
$row_Characters = mysql_fetch_assoc($Characters);
$totalRows_Characters = mysql_num_rows($Characters);