Odd behavior for tricky select sql query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Odd behavior for tricky select sql query

Post by gjb79 »

:?:
I have a script that demands a bit of filtering from an sql database.

Code: Select all

$GBookID = $HTTP_GET_VARS&#1111;'GBookID']; 
$GChapterID = $HTTP_GET_VARS&#1111;'GChapterID']; 
$LetterID = $HTTP_GET_VARS&#1111;'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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you have a precedence problem... you need to add some parens.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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]
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Well....

Post 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);
Post Reply