PROS! How to combine SQL selects???

[brianb]

Hello Pros,

Ok...this is probably really easy, so maybe someone can help here.

I want to SELECT a variable from the database in one table:

<?PHP
        {
			$sql = "SELECT filmID, title FROM Film WHERE filmID = $filmID ";
		}
        $res = mysql_query( $sql );
		while ( $arr = mysql_fetch_row($res) )
?>

Now, down the page a few different times, I want to call other variables from other tables, BUT I want the filmID to remain constant in all the selects.

How do I code such a thing?

My goal is to not have to do this sort of thing everytime I want to call something from a certain table in my database:

        {
			$sql = "SELECT F.filmID, F.title, R.reviewID, R.filmID, R.date, R.blurb, R.rating, R.person ";
			$sql.= "FROM Film F, Reviews R ";
			$sql.= "WHERE F.filmID = R.filmID ";
			$sql.= "ORDER BY R.date DESC ";
			$sql.= "LIMIT 1";
		}

	$res = mysql_query( $sql );
	while ( $arr = mysql_fetch_row($res) )

Any help would be appreciated!

[MrAlaska]

First of all, you do not need to have filmID in your first select
statement. You alread have that in your $filmID variable. You only
need to select title, which you can assign to a variable called
$title. Get all the variables you want from the Film table and you
never hafta call it again until you need to call a different film.

If you later want something from the reviews table, you do not
need to join it to the film table because you already have a
handle on the filmID, just:

select ....
from Reviews
where filemid=$filmID

[brianb]

Hmmm.....

I don't think I actuallly explained myself well enough...or maybe I still don't get it.

The title / FilmID call at the top of the page is variable.

Basically, the FilmID is gonna be different for EVERY call to a table I do, but I'm gonna be calling the FilmID in the same way for each table on the page.

so, my goal is to simplify getting the FilmID every time.

Does this make sense?

[MrAlaska]

I guess I don't understand exactly what you are trying to do. I
seem to be having that trouble lately.

Your second query will select all the films that have reviews
available and pair them up with all the reviews assiciated with
them. Is that what you want to do? If so, I don't know what you're
are trying to make easier.

EDITING: I forgot about the LIMIT, which means it will only show
the most recent film reviewd, but there is nothing to tell it which
filmID to pull up, unless I am missing something else.

[MrAlaska]

One other possibility, you can get rid of the first query by putting
the requested $filmID into the second query, which will then only
pull back the latest review for the film associated with your variable:

$sql = "SELECT F.title, R.reviewID, ";
$sql.= " R.date, R.blurb, R.rating, R.person ";
$sql.= "FROM Film F, Reviews R ";
$sql.= "WHERE F.filmID = R.filmID ";
$sql.= "AND F.filmID=$filmID ";
$sql.= "ORDER BY R.date DESC ";
$sql.= "LIMIT 1";

[brianb]

Hmmmm...ok.

Your last post is exactly what I'm trying to get away from....I'll have to do that SELECT a bunch of different time all around the page for different tables.

Here's what I was trying to find out if it's possible or not.

• The 'FilmID' and the 'title' are going to need to be pulled from the 'Film' table every time I I SELECT a different table on the page.

• I would like the FilmID and the title to be paired up for the corresponding SELECT of a table, whether is be Reviews, Trailers, etc.

• Depending on what I SELECT from whatever table, the FilmID and the title will be variable to that tables SELECT call.

• BUT the FilmID will always equal the same FilmID in the adjacent table...and title need to be pulled from the Film table.

For instance....

I want to print the last 5 Reviews that went into my database...

The FilmID will be stated in the 'Reviews' table....but there is NO title in the Reviews table, SO, I need to get that FilmID's 'title' from the 'Film' table and print it out.

Does this make sense? Otherwise I'm just gonna have to pair up the 'FilmID's for each table with the 'FilmID' in the 'Film' table every time I SELECT a table....like Reviews, or Trailers...etc.

This is what I didn;'t want to do. I am trying to see if there is a way to state that everytime a FilmID is called it pairs it up with it's matching 'title' from the 'Film' table.

Thanks for the responses by the way...

[MrAlaska]

Ok, I think I see, you want to get out of writing joins. What I
think you want is a stored procedure, which MySql does not
support. If your queries are always going to be similar, you could
build a function in PHP, then send the variables to the function
and call it every time you want to build a SQL statement. For
instance, you could name your function sql(), then when you it's
time to make a query you could call $sql=sql($fiilmID, 'Review') or
something like that. It would be slick, but a bit more work to set
up. Either way, though, there is gonna be a join going on, unless
you added redundant columns in your tables, but that would be
just wrong.

[brianb]

I figured as much once we started chatting....

Thanks for the input though!