Search sql data and retrieve using 2 tables

[cr7489]

Hi i have 2 tables:

film - filmid, filmname
review - id, reviewtitle, filmreview, filmid

i basically want to search for a film name thats in the film table then retrieve the review from the review table that matches the film, the match is found using the filmid as the id for the film name

so far i have something like this but it only returns the film id and filmname

//$searching=$_POST;
//$searchfilm=$_POST;

//This is only displayed if they have submitted the form
// if ($searching =="yes")
// {
// echo "<h2>Film Review</h2><p>";

//If they did not enter a search term we give them an error
// if ($searchfilm == "")
// {
// echo "<p>You forgot to enter a search term";
// echo
// exit;
// }

// Otherwise we connect to our Database
// mysql_connect("", "", "") or die(mysql_error());
// mysql_select_db("") or die(mysql_error());

// We preform a bit of filtering
// $searchfilm = strtoupper($searchfilm);
// $searchfilm = strip_tags($searchfilm);
// $searchfilm = trim ($searchfilm);

//Now we search for our search term, in the field the user specified
// $data = mysql_query("SELECT filmid, filmname FROM film WHERE filmname LIKE '$searchfilm'");

//$result = mysql_query($data) or die(mysql_error());

//And we display the results
// while($row = mysql_fetch_array($data))
// {
// echo $row;
// echo "<br>";
// echo $row;
// echo "<br>";
// echo $row;
//echo "<br>";
//}

//This counts the number or results - and if there wasn't any it gives them a little message explaining that
//$anymatches = mysql_num_rows($data);
//if ($anymatches == 0)
//{
//echo "Sorry, but we can not find an entry to match your query<br><br>";
//}

//And we remind them what they searched for
//echo "<b>Searched For:</b> " .$searchfilm;
//}

any help would be appreciated thanks

[fobos]

In order to do this, we have to join the table when we execute the query.

<?php
//Connection
$query = "SELECT film.filmname, review.filmreview FROM film, review WHERE film.filmid= review.filmid";
	 
$result = mysql_query($query) or die(mysql_error());


// Print out the contents of each row into a table 
while($row = mysql_fetch_array($result)){
	echo $row['filmname']. " - ". $row['filmreview'];
	echo "<br />";
}
?>

Hope this helps

[cr7489]

thanks, think i have managed to get it to work but when i search for a film in my DB i have to enter the full word ie not 'inc' for inception so how can i make it so that it would bring up inception

[fobos]

My bad thought thats what you wanted.
Well then try making two querys. Ex.

movie.html
<a href="get_reviews.php?filmid=1">Indiana Jones</a>

get_revies.php

<?php//Connection
$query = "SELECT * FROM film WHERE filmid=".GET['filmid']."; 
$result = mysql_query($query) or die(mysql_error());  
// Print out the contents of each row into a table 
$film = mysql_fetch_array($result)
?>

<?php//Connection
$query1 = "SELECT * FROM review WHERE filmid=".GET['filmid']."; 
$result1 = mysql_query($query1) or die(mysql_error());  
// Print out the contents of each row into a table 
$review = mysql_fetch_array($result1)
?>

<?php echo $film['filmid'] - $film['filmname'] - $review['filmreview'] ?>

This will only work if the film id is the same for both tables.

Another solution till you find another one.

[cr7489]

have you used 2 forms there ? i think i will stick to the above solution for now and keep these in mind thanks anyway
seems to be an error in your select statements with the use of the " "