[SOLVED] Question about Filtering Queries

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:

[SOLVED] Question about Filtering Queries

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

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

THanks but...

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

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

hmmm.

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

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

Data

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

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

YAY!!!

Post 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!
Post Reply