Database Query Problem with Sorting.

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

Moderator: General Moderators

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

Database Query Problem with Sorting.

Post 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&#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 »

maybe...

Code: Select all

SELECT * FROM `character` WHERE (BookID < '$GBookID')
UNION
SELECT * FROM `character` WHERE (BookID = '$GBookID') AND (ChapterID <= '$GChapterID')
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

...or perhaps even...

Code: Select all

SELECT * FROM `character` WHERE (BookID < '$GBookID') or (BookID = '$GBookID' AND ChapterID <= '$GChapterID')
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

hmm

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

Post by feyd »

try JAM's.. it should work better.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

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

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

SOLVED

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

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

Post by feyd »

*cough* MySQL 4, not PHP 4

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

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

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

Post by feyd »

forgot to use a loop?
User avatar
gjb79
Forum Commoner
Posts: 96
Joined: Fri Jul 18, 2003 6:35 am
Location: x <-- (DC)
Contact:

Post 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&#1111;'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
	  &#123;	
	  if ($row_Descriptions&#1111;'BookID'] == 1) &#123; $BookID = "SS";
	   &#125; elseif ($row_Descriptions&#1111;'BookID'] == 2) &#123; $BookID = "CS";
	    &#125; elseif ($row_Descriptions&#1111;'BookID'] == 3) &#123; $BookID = "PA";
		 &#125; elseif ($row_Descriptions&#1111;'BookID'] == 4) &#123; $BookID = "GF";
		  &#125; elseif ($row_Descriptions&#1111;'BookID'] == 5) &#123; $BookID = "OP";
		   &#125; elseif ($row_Descriptions&#1111;'BookID'] == 6) &#123; $BookID = "HBP";
		    &#125; else &#123; $BookID = "??"; &#125;
	  echo $row_Descriptions&#1111;'description'];
	  if ($row_Descriptions&#1111;'description'] == "") &#123; echo ""; &#125; else &#123; echo " (" . $BookID . " - " . $row_Descriptions&#1111;'ChapterID'] . ") <BR>"; &#125;
	  &#125; while($row_Descriptions = mysql_fetch_assoc($Descriptions));
?>
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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...)
Last edited by JAM on Wed Jan 19, 2005 3:18 pm, edited 3 times in total.
Post Reply