Page 1 of 2
Database Query Problem with Sorting.
Posted: Wed Jan 19, 2005 12:48 pm
by gjb79
The below code works almost perfectly. It is designed to filter the results of the table character by the tables BookID and ChapterID where the results are less than or equal to variables of simular names.
My problem is thus:
The chapters are based on the books. so if somebody read up to book 3 and up to chapter 15, the code should display all chapters of books 1 and 2 and only chapters 1-15 of book 3. Right now it will only display chapters 1-15 of book 1, 1-15 of book 2, and 1-15 of book 3.
How can I fix this?
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: Wed Jan 19, 2005 1:13 pm
by feyd
maybe...
Code: Select all
SELECT * FROM `character` WHERE (BookID < '$GBookID')
UNION
SELECT * FROM `character` WHERE (BookID = '$GBookID') AND (ChapterID <= '$GChapterID')
Posted: Wed Jan 19, 2005 1:31 pm
by JAM
...or perhaps even...
Code: Select all
SELECT * FROM `character` WHERE (BookID < '$GBookID') or (BookID = '$GBookID' AND ChapterID <= '$GChapterID')
hmm
Posted: Wed Jan 19, 2005 1:33 pm
by gjb79
Hey thats a great idea, but I can't seem to get around this error:
You have an error in your SQL syntax near 'UNION SELECT * FROM `character` WHERE (BookID = '5') AND (ChapterID <= '11')' at line 1
I dont see the syntax error, do you?
Code: Select all
<?php
$GBookID = $HTTP_GET_VARSї'GBookID'];
$GChapterID = $HTTP_GET_VARSї'GChapterID'];
mysql_select_db($database_lexicon, $lexicon);
$query_Characters = "SELECT * FROM `character` WHERE (BookID <= '$GBookID') UNION 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: Wed Jan 19, 2005 1:35 pm
by feyd
try JAM's.. it should work better.
Posted: Wed Jan 19, 2005 1:36 pm
by JAM
UNION came with MySQL version 4 (I think). I'm not surprised anymore that people still run older versions, so please verify that you indeed are.
Posted: Wed Jan 19, 2005 1:46 pm
by gjb79
JAM
Thanks for the advice, I tested that out and it seems to be working. The characters that dissapeared when they should have been there no longer dissapears!
Thanks a lot!
Thanks
SOLVED
Posted: Wed Jan 19, 2005 1:56 pm
by gjb79
Yeah I have PHP 4 installed.
Thanks again!
I have two other areas I need to apply this bit of code to to make sure that the correct information from those tables is filtered correctly.
When I try I get a big slowdown from the server and the page is showing up oddly.
Such as the page is displaying but the table the text appears within has altered. or images that should appear if certian conditions are met in the php appear when they shouldn't. Infact the condition for the image to appear hasn't even been placed in the database yet.
Somethings funky going on. I'll write in a minute with more details once I do a little detective work.
Thanks
Posted: Wed Jan 19, 2005 1:59 pm
by JAM
gjb79 wrote:The characters that dissapeared when they should have been there no longer dissapears!
Forgive me, but I'm not sure I follow. :lol:
I however take it from the rest of the post that it worked, and if so, I'm glad.
Posted: Wed Jan 19, 2005 1:59 pm
by feyd
*cough* MySQL 4, not PHP 4
--feyd v2
Posted: Wed Jan 19, 2005 2:12 pm
by gjb79
Jam - I am creating a read along guide to a series of books. The characters in the books are loaded into a database along with the book number and chapter number they appear in.
This way when a reader visits the page they input which book they're on and which chapter they've read up to. The webpage is supposed to pull only the characters that are less than or equal to the book number they are on and the chapters from the previous books and up to the chapter they are on.
I have it looped so that details are displayed based on the characters that are loaded and the books that they appear in. for example
Suzie appears in book 2 chapter 4
detail 1 of suzie happens in book 2 chapter 4
detail 2 of suzie happens in book 3 chapter 2
So when someone puts in they've read up to book 3 chapter 1 it shows suzie and only detail 1. when they read another chapter it will then show detail 2.
I was having the same troubles with filtering the details as with filtering the characters as I listed above. I tried to fix that problem and now the page is behaving oddly. heres the script for the details after I applied the above fix.
Code: Select all
mysql_select_db($database_lexicon, $lexicon);
$query_Descriptions = "SELECT * FROM description WHERE (CharID = '$GCharID') AND (BookID < '$GBookID') or (BookID = '$GBookID' AND ChapterID <= '$GChapterID')";
$Descriptions = mysql_query($query_Descriptions, $lexicon) or die(mysql_error());
$row_Descriptions = mysql_fetch_assoc($Descriptions);
$totalRows_Descriptions = mysql_num_rows($Descriptions);
Posted: Wed Jan 19, 2005 2:28 pm
by gjb79
Almost forgot.
The weirdness:
With details, it seems to work alright for the first person, displaying the details according to the book/chapter variables.
The next character down however doesn't have any of his/her details displayed, insted it displays the last detail of the first character.
Every character after that displays the last detail listed in the first character rather than the details linked to the new character.
Posted: Wed Jan 19, 2005 2:35 pm
by feyd
forgot to use a loop?
Posted: Wed Jan 19, 2005 2:42 pm
by gjb79
Heres the entire Loop. Ignore the If Statements, Needed to convert the Book Numbers to Abreviations.
Code: Select all
<?php
$GCharID = $row_Charactersї'ID'];
mysql_select_db($database_lexicon, $lexicon);
$query_Descriptions = "SELECT * FROM description WHERE (CharID = '$GCharID') AND (BookID < '$GBookID') or (BookID = '$GBookID' AND ChapterID <= '$GChapterID')";
$Descriptions = mysql_query($query_Descriptions, $lexicon) or die(mysql_error());
$row_Descriptions = mysql_fetch_assoc($Descriptions);
$totalRows_Descriptions = mysql_num_rows($Descriptions);
do
{
if ($row_Descriptionsї'BookID'] == 1) { $BookID = "SS";
} elseif ($row_Descriptionsї'BookID'] == 2) { $BookID = "CS";
} elseif ($row_Descriptionsї'BookID'] == 3) { $BookID = "PA";
} elseif ($row_Descriptionsї'BookID'] == 4) { $BookID = "GF";
} elseif ($row_Descriptionsї'BookID'] == 5) { $BookID = "OP";
} elseif ($row_Descriptionsї'BookID'] == 6) { $BookID = "HBP";
} else { $BookID = "??"; }
echo $row_Descriptionsї'description'];
if ($row_Descriptionsї'description'] == "") { echo ""; } else { echo " (" . $BookID . " - " . $row_Descriptionsї'ChapterID'] . ") <BR>"; }
} while($row_Descriptions = mysql_fetch_assoc($Descriptions));
?>
Posted: Wed Jan 19, 2005 3:05 pm
by JAM
Note your where clause...
Code: Select all
WHERE (CharID = '$GCharID') AND (BookID < '$GBookID') or (BookID = '$GBookID' AND ChapterID <= '$GChapterID')
...is different from...
Code: Select all
WHERE CharID = '$GCharID' AND (BookID < '$GBookID' or (BookID = '$GBookID' AND ChapterID <= '$GChapterID'))
Might be that you need to move around the ()'s to suit you better....
(I reserve myself for errors above. Do not copy n' paste without actual reading...)