[RESOLVED] query question.

seco

So i have 2 tables in 1 database.

dvd and scene.

within those, dvd_id, dvd_title, ect....
and
scene_id, scene_title, ect..

I am able to pull in all the dvd info, I have scenes that are in relation to each dvd.

scene_title and dvd_title are both that same, how can i pull in just the scenes that corrospond with that dvd?

view.php?dvd_id=1

will bring in dvd 1, but you know that.

here is a sample of what i have tried.

"SELECT * FROM scene WHERE scene_title = '".$_GET['dvd_id']."'";

"SELECT * FROM scene WHERE scene_title = '$dvd_title'";

both are a no go.

any ideas?

bradgrafelman

I don't understand what the relationship between the two tables is. I presume that a single DVD has multiple rows in the "scene" table (unless a DVD happens to be composed of only one scene, but I doubt there are many that are). How do all of these rows relate to the single entry in the dvd table? Is there some scene_dvd_id column that points to the dvd_id?

seco

bradgrafelman wrote:
I don't understand what the relationship between the two tables is. I presume that a single DVD has multiple rows in the "scene" table (unless a DVD happens to be composed of only one scene, but I doubt there are many that are). How do all of these rows relate to the single entry in the dvd table? Is there some scene_dvd_id column that points to the dvd_id?

scene_dvd_id column, that might actually work, i dont know why i didnt think of that before.

ill give it a try.

EDIT, that worked beautifully! THANK YOU!

"SELECT * FROM scene WHERE scene_dvd_id = '".$_GET['dvd_id']."'";

bradgrafelman

You could even get fancy with it and, if InnoDB tables are available, use foreign key constraints to actually link rows in the scene table with a row in the DVD table. I guess the main advantage for that would be if you delete a DVD, you could easily let SQL take care of deleting the appropriate "scenes" for you.

At any rate, don't forget to mark this thread resolved.

seco

bradgrafelman wrote:
You could even get fancy with it and, if InnoDB tables are available, use foreign key constraints to actually link rows in the scene table with a row in the DVD table. I guess the main advantage for that would be if you delete a DVD, you could easily let SQL take care of deleting the appropriate "scenes" for you.

At any rate, don't forget to mark this thread resolved.

Thank you for the info, I will definitly look into that.