bhavin_85 Posted April 9, 2007 Share Posted April 9, 2007 hey guys please can some1 help me to create a query using joins I have been trying to figure out the best way to make a search on this site and found that joins are the best way to do it. I need the search to work as a compound search but also work if only some of the fields are entered ive written this so far, which does the compound search but not the other type....can some1 help me covert this into a join query? i have been reading up about joins all day and i cant figure out how to do it ??? <?php include ('config.php'); $action = $_POST['action']; $comedy = $_POST['comedy']; $drama = $_POST['drama']; $thriller = $_POST['thriller']; $actor_actress = $_POST['actor_actress']; $minprice = $_POST['minprice']; $maxprice = $_POST['maxprice']; $year = $_POST['year']; $sql = "SELECT actorid FROM actor WHERE name='$actor_actress'"; $query = mysql_query($sql) or die(mysql_error()); $row =mysql_fetch_assoc($query); $actorid = $row['actorid']; echo $actorid; $sql2 = "SELECT filmid FROM actorfilm WHERE actorid='$actorid'"; $query2 = mysql_query($sql2) or die(mysql_error()); while ($row2 = mysql_fetch_assoc($query2)) { $filmid = $row2['filmid']; echo $filmid; $sql3 = "SELECT filmid FROM film WHERE releaseyear='$year' AND filmid='$filmid'"; $query3 = mysql_query($sql3) or die(mysql_error()); while ($row3 = mysql_fetch_assoc($query3)) { $filmid1 = $row3['filmid']; echo $filmid1; $sql4 = "SELECT filmid FROM film WHERE filmid='$filmid1' AND price>='$minprice' AND price<='$maxprice'"; $query4 = mysql_query($sql4) or die(mysql_error()); while ($row4 = mysql_fetch_assoc($query4)) { $filmid2 = $row4['filmid']; echo $filmid2; $sql5 = "SELECT filmid FROM filmtype WHERE filmid='$filmid2' AND typeid='$action' || filmid='$filmid2' AND typeid='$comedy' || filmid='$filmid2' AND typeid='$drama' || filmid='$filmid2' AND typeid='$thriller'"; $query5 = mysql_query($sql5) or die(mysql_error()); while ($row5 = mysql_fetch_assoc($query5)) { $filmid3 = $row5['filmid']; echo $filmid3; } } } } This is the form :http://www.xxx.yyyy.zz.uk/~abcde/search.php i realla appreciate any help thats given Quote Link to comment Share on other sites More sharing options...
bhavin_85 Posted April 9, 2007 Author Share Posted April 9, 2007 ive jsut had a go at writtin some of it SELECT actor.actorid, actorfilm.filmid, film.filmid, filmtype.filmid From actor, actorfilm, film, filmtype INNER JOIN actorfilm ON actor.actordid = actorfilm.actorid WHERE thats all ive got so far but is that right? Quote Link to comment Share on other sites More sharing options...
Barand Posted April 10, 2007 Share Posted April 10, 2007 Change checkbox code to <tr> <td>Film Type</td> <td colspan="2"> <input name="filmtype[]" type="checkbox" value="1">Comedy</input> <input name="filmtype[]" type="checkbox" value="2">Thriller</input> <input name="filmtype[]" type="checkbox" value="3">Drama</input> <input name="filmtype[]" type="checkbox" value="4">Action</input> </td> </tr> then <?php if (isset($_POST['filmtype'])) { $types = join(',', $_POST['filmtype']); $where[] = "(f.filmtype IN ($types))"; } Quote Link to comment Share on other sites More sharing options...
bhavin_85 Posted April 10, 2007 Author Share Posted April 10, 2007 thanks 4 that barand, how do i get it to display the results? ive tried this but i get an error $sql = "SELECT * FROM film f INNER JOIN actorfilm af ON f.id = af.film_id INNER JOIN actor a ON af.actor_id = a.id $whereclause "; $query = mysql_query($sql); $row = mysql_fetch_assoc($query); $filmid = $row['filmid']; echo $filmid; error: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /export/XXX-1/students/u9999/abcde/web/search1.php on line 37 Quote Link to comment Share on other sites More sharing options...
joshi_v Posted April 10, 2007 Share Posted April 10, 2007 Hi, Try by adding die part to the query..it will show where and what is the error. $query = mysql_query($sql) or die ($sql.':'.mysql_error()); Regards, Joshi Quote Link to comment Share on other sites More sharing options...
bhavin_85 Posted April 10, 2007 Author Share Posted April 10, 2007 the error that im getting is this: SELECT * FROM film f INNER JOIN actorfilm af ON f.id = af.film_id INNER JOIN actor a ON af.actor_id = a.id WHERE (f.filmtype IN (1)) AND (a.name = 'Meryl Streep') AND (f.price BETWEEN '0' AND '5') :Unknown column 'f.filmtype' in 'where clause' The filmtype is stored in a table called field type...would i need to modify the query to add another join? heres the table set up create table film ( filmid char(12) not null, availability char(30), itemsinstock int(3), price float(5,2), title char(50), releaseyear int(4), extrainfo char(36), picture char(36), primary key (filmid)); create table actor ( actorid char(12) not null, name char(24), primary key (actorid)); create table actorfilm ( filmid char(12) not null references film, actorid char(12) not null references actor, primary key(filmid,actorid)); **this stores the film types i.e comedy, drama, thriller*** create table type ( typeid int(12) not null auto_increment, type char(50), primary key(typeid)); ** this holds the filmid and the typeid*** create table filmtype ( filmid char(12) not null references film, typeid int(12) not null references type, primary key(filmid,typeid)); so what do i need to change? Quote Link to comment Share on other sites More sharing options...
joshi_v Posted April 10, 2007 Share Posted April 10, 2007 filmtype(?) i didn't find in that table too!!! ??? If you think it is in separate table , add it too in the query with another join. Regards, Joshi. Quote Link to comment Share on other sites More sharing options...
bhavin_85 Posted April 10, 2007 Author Share Posted April 10, 2007 the filmtype will come from the form, it should be matched with the typeid in the filmtype table so do i need to create another inner join to make that connection? Quote Link to comment Share on other sites More sharing options...
Barand Posted April 10, 2007 Share Posted April 10, 2007 Does this do it? <?php if (isset($_POST['filmtype'])) { $types = join(',', $_POST['filmtype']); $where[] = "(ft.typeid IN ($types))"; } Quote Link to comment Share on other sites More sharing options...
bhavin_85 Posted April 10, 2007 Author Share Posted April 10, 2007 there was a few naming errors but ive fixed those, and put this echo in but its not printing anything heres the code <?php include ('config.php'); if (isset($_POST['filmtype'])) { $types = join(',', $_POST['filmtype']); $where[] = "(ft.typeid IN ($types))"; } if (!empty($_POST['actor_actress'])) { $act = $_POST['actor_actress']; $where[] = "(a.name = '$act')"; } if ($_POST['year'] != 'Year') { $year = $_POST['year']; $where[] = "(f.releaseyear='$year')"; } $minp = $_POST['minprice']; $maxp = $_POST['maxprice']; $where[] = "(f.price BETWEEN '$minp' AND '$maxp')"; $whereclause = 'WHERE ' . join (' AND ', $where); $sql = "SELECT * FROM film f INNER JOIN actorfilm af ON f.filmid = af.filmid INNER JOIN actor a ON af.actorid = a.actorid INNER JOIN filmtype ft ON ft.filmid = f.filmid $whereclause "; $query = mysql_query($sql) or die ($sql.':'.mysql_error()); $filmid = $row['filmid']; echo $filmid; ?> heres the url http://www.xxx.yyyy.zz.uk/~abcde/search.php Quote Link to comment Share on other sites More sharing options...
Barand Posted April 10, 2007 Share Posted April 10, 2007 <?php $query = mysql_query($sql) or die ($sql.':'.mysql_error()); while ($row = mysql_fetch_assoc($query)) { echo $row['filmid']; } ?> Quote Link to comment Share on other sites More sharing options...
bhavin_85 Posted April 10, 2007 Author Share Posted April 10, 2007 ok the compound search works perectly there is only a few smal lthings not working now when you serach jsut by the following it doesnt echo anything even though there is a value in the database search by film type search by acrot/actress name search by year the serach by price works perfectly though newest code: <?php include ('config.php'); if (isset($_POST['filmtype'])) { $types = join(',', $_POST['filmtype']); $where[] = "(ft.typeid IN ($types))"; } if (!empty($_POST['actor_actress'])) { $act = $_POST['actor_actress']; $where[] = "(a.name = '$act')"; } if ($_POST['year'] != 'Year') { $year = $_POST['year']; $where[] = "(f.releaseyear='$year')"; } $minp = $_POST['minprice']; $maxp = $_POST['maxprice']; $where[] = "(f.price BETWEEN '$minp' AND '$maxp')"; $whereclause = 'WHERE ' . join (' AND ', $where); $sql = "SELECT * FROM film f INNER JOIN actorfilm af ON f.filmid = af.filmid INNER JOIN actor a ON af.actorid = a.actorid INNER JOIN filmtype ft ON ft.filmid = f.filmid $whereclause "; $query = mysql_query($sql) or die ($sql.':'.mysql_error()); while ($row = mysql_fetch_assoc($query)) { echo $row['filmid']; } ?> <html> <head> <title>Untitled Document</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> </head> <body> </body> </html> Quote Link to comment Share on other sites More sharing options...
Barand Posted April 10, 2007 Share Posted April 10, 2007 Change this bit. Last time I looked at your form I thought there was always a min and max price. <?php $minp = $_POST['minprice']; $maxp = $_POST['maxprice']; $where[] = "(f.price BETWEEN '$minp' AND '$maxp')"; ?> to check if each have a value and include checks for them individually in the $where array. Quote Link to comment Share on other sites More sharing options...
bhavin_85 Posted April 10, 2007 Author Share Posted April 10, 2007 thats giving me this error: Warning: join(): Bad arguments. in /export/XXX-1/students/u9999/abcde/web/search1.php on line 25 SELECT * FROM film f INNER JOIN actorfilm af ON f.filmid = af.filmid INNER JOIN actor a ON af.actorid = a.actorid INNER JOIN filmtype ft ON ft.filmid = f.filmid WHERE :You have an error in your SQL syntax near '' at line 5 <?php include ('config.php'); if (isset($_POST['filmtype'])) { $types = join(',', $_POST['filmtype']); $where[] = "(ft.typeid IN ($types))"; } if (!empty($_POST['actor_actress'])) { $act = $_POST['actor_actress']; $where[] = "(a.name = '$act')"; } if ($_POST['year'] != 'Year') { $year = $_POST['year']; $where[] = "(f.releaseyear='$year')"; } if ($_POST['minprice'] != 'Min Price') { $minp = $_POST['minprice'] ; $where[] = "(f.price >= '$minp')"; } if ($_POST['maxprice'] != 'Max Price') { $maxp = $_POST['maxprice'] ; $where[] = "(f.price <= '$maxp')"; } $whereclause = 'WHERE ' . join (' AND ', $where); $sql = "SELECT * FROM film f INNER JOIN actorfilm af ON f.filmid = af.filmid INNER JOIN actor a ON af.actorid = a.actorid INNER JOIN filmtype ft ON ft.filmid = f.filmid $whereclause"; $query = mysql_query($sql) or die ($sql.':'.mysql_error()); while ($row = mysql_fetch_assoc($query)) { echo $row['filmid']; } ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted April 10, 2007 Share Posted April 10, 2007 try changing this $whereclause = 'WHERE ' . join (' AND ', $where); to if (count($where) > 0) $whereclause = 'WHERE ' . join (' AND ', $where); Quote Link to comment Share on other sites More sharing options...
bhavin_85 Posted April 10, 2007 Author Share Posted April 10, 2007 hey mate ok the query is loading and its working....mostly http://xxxx.yyyy.xxxx.zz.uk/~abcde/search.php if you take a look at the url and do a serach for year 1999 ull see it brings up 4 versions of the same film ??? another example is say u search for just comedy films it brings up some films twice any idea how to resolve it? thanks for all your help btw u r a life saver Quote Link to comment Share on other sites More sharing options...
Barand Posted April 10, 2007 Share Posted April 10, 2007 try $sql = "SELECT DISTINCT f.* FROM film f INNER JOIN actorfilm af ON f.id = af.film_id INNER JOIN actor a ON af.actor_id = a.id INNER JOIN filmtype ft ON ft.filmid = f.filmid $whereclause "; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.