[SOLVED] Join Query....PLEASE HELP

[bhavin_85]

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

[bhavin_85]

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?

[Barand]

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))";
}

[bhavin_85]

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

[joshi_v]

 

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

[bhavin_85]

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?

[joshi_v]

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.

[bhavin_85]

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?

[Barand]

Does this do it?

 

<?php

if (isset($_POST['filmtype'])) {
    $types = join(',', $_POST['filmtype']);
    $where[] = "(ft.typeid IN ($types))";
}

 

[bhavin_85]

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

[Barand]

<?php
$query = mysql_query($sql) or die ($sql.':'.mysql_error());
while ($row = mysql_fetch_assoc($query)) {
          echo $row['filmid'];
}
?>

[bhavin_85]

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>

[Barand]

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.

 

[bhavin_85]

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'];
}

?>

[Barand]

try changing this

$whereclause =  'WHERE ' . join (' AND ', $where);

to

if (count($where) > 0) $whereclause =  'WHERE ' . join (' AND ', $where);

[bhavin_85]

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

[Barand]

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 ";