Page 1 of 1

[SOLVED] Question about Filtering Queries

Posted: Tue Jan 18, 2005 3:13 pm
by gjb79
Is this possible? The tables in the SQL Database are set as tiny integers. The variable that is being passed in both GBookID and GChapterID is a number.

I an trying to retrieve certain rows from the database table character based on the two variables being equal or less than the numbers within the BookID and CharacterID columns in the table.

Make since?

Code: Select all

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


mysql_select_db($database_lexicon, $lexicon);
$query_Characters = "SELECT * FROM `character` WHERE BookID <= $GBookID AND ChapterID <= $GChapterID";
$Characters = mysql_query($query_Characters, $lexicon) or die(mysql_error());
$row_Characters = mysql_fetch_assoc($Characters);
$totalRows_Characters = mysql_num_rows($Characters);

Posted: Tue Jan 18, 2005 3:27 pm
by feyd
your order of ops is turned around :) at least it is for var less than or equal to column

Code: Select all

$GBookID = (isset($HTTP_GET_VARS&#1111;'GBookID']) ? intval($HTTP_GET_VARS&#1111;'GBookID']) : 0);
$GChapterID = (isset($HTTP_GET_VARS&#1111;'GChapterID']) ? intval($HTTP_GET_VARS&#1111;'GChapterID']) : 0);


mysql_select_db($database_lexicon, $lexicon);
$query_Characters = "SELECT * FROM `character` WHERE $GBookID <= BookID AND $GChapterID <= ChapterID";
$Characters = mysql_query($query_Characters, $lexicon) or die(mysql_error());
$row_Characters = mysql_fetch_assoc($Characters);
$totalRows_Characters = mysql_num_rows($Characters);

THanks but...

Posted: Tue Jan 18, 2005 3:42 pm
by gjb79
Hey thanks for that. but I'm still getting an error.

Maybe it would help if I put the error up here.
You have an error in your SQL syntax near '<= BookID AND 34 <= ChapterID' at line 1
Any Ideas?

Posted: Tue Jan 18, 2005 3:49 pm
by feyd
quotes? i.e.

Code: Select all

"SELECT ... '$GBookID'
there's also a potential for the variables to print nothing if zero is being passed or used.

hmmm.

Posted: Tue Jan 18, 2005 4:01 pm
by gjb79
Well Zero cannot be passed or used as the initial variables are setup using a dropdown form box that I placed the variables into Zero is no option. Which helps in this trouble shooting.

I put the quotes in and no longer have the syntax error, however It isn't working. Elements are still being brought into the query that should have been filtered out by the <= sign.
8O

Posted: Tue Jan 18, 2005 4:04 pm
by feyd
can you post examples of input values, the table structure, a sample of the data stored, what data you are expecting, and what data you are getting back?

Data

Posted: Tue Jan 18, 2005 4:16 pm
by gjb79
Sure!
The table structure has the following columns
ID, CNameF, CNameL, Birth, Death, BookID, ChapterID

Sample, following the above structure
1, Hannah, Abbott, 1980, Present, 1, 7
2, Euan, Abercrombie, 1984, Present, 5, 11
3, Stuart, Ackerley, 1983, Present, 4, 12

Lets say the input value is BookID = 4, ChapterID = 11
In this situation, Hannah would appear as she is in book 1, Euan would not appear as he is in book 5, Stuart would pass being in book 4, however he would not show up as he is in chapter 12 and not 11.

I am creating a Read-along guide to a series of books to help people keep track of characters. I do not want them to see characters or information about those characters, until they have read x amount of books and are up to x chapter in the latest book they are reading.

So if the reader read one more chapter (and now is on chapter 12) then visited this page, he/she would then see Stuart show up.

Currently all the data is showing up. Nothing is being filtered out.

So I need all the queries to list data that is less than or equal to the value of the variable.

Posted: Tue Jan 18, 2005 4:36 pm
by feyd
out of curiosity, what data types are the columns?

here's a modified version of your query, logic is updated to match your newest post.. just in case it's screwy for some reason...

Code: Select all

SELECT * FROM `character` WHERE (BookID <= '$GBookID') AND (ChapterID <= '$GChapterID')
Also, did you use my $GBookID and $GChapterID setting code?

YAY!!!

Posted: Tue Jan 18, 2005 4:58 pm
by gjb79
:D :D :D
It worked!

That alternate selection code you gave me worked perfectly!

That and I discovered a slight glitch in passing the variables from the form, the GBookID always showed up as a value of 1. I'd still have known if the script worked because the chapter variable was correct and it would have filtered some names.

Thank you sooo much!