compound search

[bhavin_85]

hey guys

 

 

i have to create a compound search and im just wondering the best way to do it....

http://www.xxx.yyyy.zz.uk/~abcde/search.php

 

thats the link

 

basically the user should be able to search for multiple film types, an actress/actor name, between a certain price range and year of release

 

so how should i be doing this? if statements?

 

cheers

[Barand]

You would use a query like

SELECT * FROM movies
WHERE film_type IN (1, 2, 3)
AND actor = 'A Name'
AND price BETWEEN ($p1 AND $p2)
AND year = '2001'

 

Method here

 

http://www.phpfreaks.com/forums/index.php/topic,89842.msg360739.html#msg360739

[bhavin_85]

hi

 

i dont think that way will work, because there are quite a few different tables that i need to get data from.

 

I have written this so far...it works perfectly as a compound search(every field is filled) however I need to adapt the search so that if only a few of the fields are entered it will still produce results using those parameters

 

<?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;
}
}
}
}
?>

 

I have used while loops, can any1 point me in the right direct about how to do this?