Topic awaiting preservation: mysql query'n

Perhaps I'm attempting the impossible the way I have this set up, please tell me if I am:

Table 1 = chapt_id, date, chaptername, chapterbody, story_id, artist_id, author_id, image1_id, image2_id;
Table 2 = id, storyname;
(other tables not involved in this issue).

my previous page, requests a chapt_id to display a chapterbody on the page. However, I want to also list all chapternames that have the same story_id as that chapterbody(that equals the chapt_id). I hope this is making sense. I can get it to list all chaptername fields in order by story_id, but not just those that equal the chapt_id's story_id column. The code I have so far is as follows:

if ($_GET[chap_id]) {
$get_storyid = "SELECT * FROM chapter_db, story_db WHERE chapter_db.storyid = story_db.id ORDER BY story_id";
	$call_chapter = mysql_query($get_storyid, $db) or die (mysql_error());
	
	while ($result = mysql_fetch_array($call_chapter)) {
		$chaptid = $result['chapt_id'];
		$storyid = $result['story_id'];
		$id = $result['id'];
		$chaptername = $result['chaptername'];

		
		$display .= "$chaptername - $id || ";
	}
}

Any help will be greatly appreciated. Also, is there a chm file of the mySQL manual like there is for the PHP manual? That PHP manual has been incredibly helpful and I cannot seem to find a counterpart for mySQL.

Thank you,

D

This patient is ready for his medication!

just off the top of my head, in the where clause can you put AND story_ID = "ID" ?



Of all the things I've lost, I miss my mind the most (Ozzy Osbourne)

If I am following what you are saying, I think I already have this in the code. Where it says "chapter_db.storyid = story_db.id". Would I need to put another set like that again? I am confused. The problem I have is that if one chapter is called, I want all other chapters that are in that story to be listed. So, what else can I do to (is this the right term in DB?) Normalize the information and get out the columns I am wanting?

Thanks for any more help you can be.

D

This patient is ready for his medication!

Shouldn't this -

chapter_db.storyid = story_db.id

read

chapter_db.story_id = story_db.id

?

You should be seeing some detailed info from the mysql_error()....
Of course, you haven't specified exactly what the problem you are having is.



(Edited by DL-44 on 07-03-2005 05:52)

(Edited by DL-44 on 07-03-2005 05:52)

IXI's question is that this query will group ALL chapter id's with their story ids. So to get the correct query need an additional query to get the correct story_id first, probably on the previous page where you are getting the chapter id to begin with. so you can do something like so:

$get_storyid = "SELECT * FROM chapter_db, story_db WHERE chapter_db.storyid = '$_GET['story_id'"



You could also do this programmatically but it would be horribly inefficient.



.:[ Never resist a perfect moment ]:.

(Edited by bitdamaged on 07-04-2005 02:37)

OK (assuming I've understood the problem) just off the top of my head:

Pass in the story ID when you pass in the chapter ID. I'm assuming you are planning on having multiple stories each with a variety of chapters so this:

page.php?chapter=4

won't help - it'd have to be:

page.php?story=3&chapter=4

So you could then get the data:

if ($_GET[chap_id]) {

$story_id = $_GET[story_id];

$get_storyid = "SELECT *
FROM chapter_db, story_db
WHERE chapter_db.storyid = $story_id
ORDER BY story_id";

etc.

--------
Otherwise if you are going to do something a bit odd like make story 4 chapter 7 have the ID of say 56 (not very user friendly) you could then make a copy of the chapter table:

SELECT chapter_db. chapterbody,
chapt_copy.chapt_id, chapt_copy.chapt_name
FROM chapter_db, story_db, chapter_db AS chapt_copy
WHERE chapter_db.chapter_id = $chapter_id AND
chapter_db.story_id = story_db.story_id AND
story_db.story_id = chapt_copy.story_id
ORDER BY story_id

I suspect that'd work fine. You will get multiple copies of the same chapterbody out but that is no biggie:

while ($result = mysql_fetch_array($call_chapter)) {

if !empty ($chapt_body) {
$chapt_body = $result['chapterbody'];
}

//then get out the rest of the results which should be chapter names and IDs.

------------------------
Anyway I'd go with my first solution - have the chapter ID as a non-unique, non-autoincrementing field and pull it out using story and chapter ID. Its easier for people to understand that the seventh chapter of your fourth story is:

page.php?story=4&chapter=7

rather than:

page.php?chapter=56

Hope that makes sense - as I say it assumes I've understood the problem and there are no guarantees the more complicated solution will work as it is but if you want to carry on down that road some tinkering with that will give you the answer you seek.

Probably

___________________
Emps

The Emperor dot org | Justice for Pat Richard | FAQs: Emperor | Site Reviews | Reception Room

if I went 'round saying I was an Emperor just because some moistened bint had lobbed a scimitar at me, they'd put me away!

Sorry about the long time since my last post. Emperor, you got the nail on the head. I know I am setting up so that story 4, chapter 7 is listed as chapter 56, and I agree your way probably would be a far superior, but alas, that is why I'm only learning this as asking questions here. ::grin::
So should I turn my 2 tables (chapters, stories) into a STORY table and a CHAPTER table for every story? So if I have 7 stories, I would have 8 tables? Am I understanding you there? In that sense the table properties would be:

STORY_TABLE = storyid, storyname;
CHAPTER_TABLE = chapterid, chaptername, storyid, chapterbody, story_id, artist_id, author_id, image1_id, image2_id;

Is this the general practice in db design? I have no experience here, so I am looking for help wherever I can find it. I am now curious if I should add more tables though like Emperor suggested? The visitors won't ever work with it as story4chapter7 or just chapter 56. They will click on images and words. It is just how I have to work with it. Is is speed issue? More requests for one table or less requests for more tables? Should DB design go in another thread perhaps?

Thank you all for your help and patience...and Knowledge!

Dustin

P.S. And By the way I managed to realize after a week away from this specific code, that I was duplicating variables (is this the correct word for this $variable?). With it corrected, my code work without a problem.

This patient is ready for his medication!

dade: OK so its is working then? Good to hear

My solution didn't (as far as I remember) involve adding in anymore tables as this seems fine:

quote:

---

Table 1 = chapt_id, date, chaptername, chapterbody, story_id, artist_id, author_id, image1_id, image2_id;
Table 2 = id, storyname;

---

It actually referred to how you deal with the variables being passed in.

___________________
Emps

The Emperor dot org | Justice for Pat Richard | FAQs: Emperor | Site Reviews | Reception Room

if I went 'round saying I was an Emperor just because some moistened bint had lobbed a scimitar at me, they'd put me away!