Search problem

[mikkex]

I have a big problem (for me, hopefully not for anybody else).

I have a databse where every data is strored in one table, and they are grouped by 'item'. every data that have the same item belongs toghether, and represent different kind of data accourding to the row header.

http://gimmikk.se/tempfiler/mysqlstruct_1.gif

(I have not build this structure - the firm before went bankrupted...)

Now I want to do a search, for instance search for a play that I now is called Cabaret [somethin] (header column 91) and that have 30 actors in it (header column = 294). I want to have a advanced search page where I could tell exactly what to search for.

http://gimmikk.se/tempfiler/mysqlstruct_2.gif

I have this code, but it does not work...

if(isset($_GET[tbActorsTotal])) {
 $S1 = "AND item_facts.fact LIKE '$_GET[tbActorsTotal]' ";
} else {
   $S1 = "";
}
if(isset($_GET[tbActorsFemale])) {
 $S2 = "AND item_facts.fact LIKE '$_GET[tbActorsFemale]' ";
} else {
   $S2 = "";
}

if(isset($_GET[tbActorsMale])) {
 $S3 = "AND item_facts.fact LIKE '$_GET[tbActorsMale]' ";
} else {
   $S3 = "";
}


$SokString = "$S1 $S2 $S3";


$sqlsokresultat = "SELECT item_category.item, item.name, item.number  FROM item_category, item, item_facts WHERE item_category.category = 12200 AND item.number = item_category.item AND (item.name LIKE '%$_GET[tbTitle]%' AND item_facts.fact = '$_GET[tbActorsTotal]') LIMIT 0,30";

$sokresultatet = mysql_query($sqlsokresultat);

<?php
while(list($mera, $titel, $nummer) = mysql_fetch_row($sokresultatet)) 
{

?>

<p><?=$nummer?>"> - <?=$titel?></p>

<?php
}
?>

Does anyone understand what I want to do, and does anyone have a suggestion?

[coreyp_1]

this is a mess! (not your code, the database!)

basically, for each option, you need to create an INNER JOIN subquery (you must be using MySQL 4.1+)

$partial = '';

if(isset($_GET[tbActorsTotal])) {
 $partial .= "INNER JOIN (SELECT item FROM item_facts WHERE header = 294 AND i.fact LIKE $_GET[tbActorsTotal]) USING item ";
}

if(isset($_GET[tbActorsFemale])) {
 $partial .= "INNER JOIN (SELECT item FROM item_facts WHERE header = ??? AND i.fact LIKE $_GET[tbActorsFemale]) USING item ";
}

if(isset($_GET[tbActorsMale])) {
 $partial .= "INNER JOIN (SELECT item FROM item_facts WHERE header = ??? AND i.fact LIKE $_GET[tbActorsMale]) USING item ";
}

$query = "SELECT item_category.item, item.name, item.number FROM item_category, item $partial LIMIT 0,30";

obviously, you need to replace the ??? with the correct number. use similar code for each option. you could streamline it using a function:

function query_string ($num, $val){
  return "INNER JOIN (SELECT item FROM item_facts WHERE header = $num AND i.fact LIKE '$val') USING item ";
}

$partial = '';

if(isset($_GET[tbActorsTotal])) {
 $partial .= query_string (294, $_GET[tbActorsTotal]);
}

if(isset($_GET[tbActorsFemale])) {
 $partial .= query_string (???, $_GET[tbActorsFemale]);
}

if(isset($_GET[tbActorsMale])) {
 $partial .= query_string (???, $_GET[tbActorsMale]);
}

$query = "SELECT item_category.item, item.name, item.number FROM item_category, item WHERE item.number = item_category.item $partial LIMIT 0,30";

[mikkex]

I can not get it to work - and when I check the mysql server is version 4.0.16 (on linux), but I can not get it to work on my localmachine either - and that is 4.1.14-nt.

I tried to do the query local with 'static' input:

SELECT item_category.item, item.name, item.number FROM item_category, item WHERE item.number = item_category.item INNER JOIN (SELECT item FROM item_facts WHERE header LIKE '294' AND item.fact LIKE '2') USING item LIMIT 0,30

And I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN (SELECT item FROM item_facts WHERE header LIKE '294' AND item.fact LI' at line 1 

What have I missed?

[coreyp_1]

subquerys will only work on MySQL 4.1+

Here is your code, slightly re-worked. try it now:

SELECT item_category.item, item.name, item.number
FROM item_category
LEFT JOIN item ON item.number = item_category.item
INNER JOIN (SELECT item FROM item_facts WHERE header LIKE '294' AND item.fact LIKE '2') USING item
LIMIT 0,30

[mikkex]

Thansk for taking your time, but that did not do it either (I must take some time and study more about mysql) this time the error is:

Every derived table must have its own alias 

[coreyp_1]

I researched the message, and think I found my error. Unfortunately, I can't test the proposed solutions, b/c I don't have the database on my machine. :queasy: If you're willing, here's another try of your code:

SELECT item_category.item, item.name, item.number
FROM item_category
LEFT JOIN item ON item.number = item_category.item
INNER JOIN (SELECT item FROM item_facts WHERE header LIKE '294' AND item.fact LIKE '2') AS tbl_1 USING item
LIMIT 0,30