[Resolved] Need HELP multi-words query on a multi-tables database

[thedream]

Hy there and hi Tim,

I am a young programmer on Mysql and PHP. Here is my problem I can't find a solution:
I have a principal table named 'phototek' having some descriptions and some columns id linked to others descriptives tables.
My problem is, how do I do a multi words query in a simple form that search in all linked tables and return not the ID link key but the value linked to every differents ID.
Is it enough clear?
I can list correctly the correpondant values linked by ID to the principal table, but I don't or I can't do a search like "blonde italy sky", where "blonde" is in the phototek table, "italy" is linked by ID key from phototek table to country table and "sky" is linked to by another ID key from phototek table to backgrounds table.
How do I do this miracle trick?

And this is only a short example, because a have some tables linked to others tables where this latest tables are linked to the principal one.

Please Help,

Marc-Andre
and thank you

[matto]

something like this would do it, I suppose:

select * from phototek where
name like '%$search%' or
id_category1 in(select id from category1 where name like '%$search%') or
id_category2 in(select id from category2 where name like '%$search%') or
id_category3 in(select id from category3 where name like '%$search%')

hope this helps!

---

sorry, I had to edit this. I think this suits you better:

<?

$searchstring = "blonde italy sky";
$keywords = explode(' ', $searchstring);

$clause =       "name like '%"
                .implode("%' or name like '%", $keywords)
                ."%'";

$sql =  "select * from phototek where "
        ."$clause or "
        ."id_category1 in(select id from category1 where $clause) or "
        ."id_category2 in(select id from category2 where $clause) or "
        ."id_category3 in(select id from category3 where $clause)";

echo $sql;

?>

output is:

select * from phototek where name like '%blonde%' or name like '%italy%' or name like '%sky%' or id_category1 in(select id from category1 where name like '%blonde%' or name like '%italy%' or name like '%sky%') or id_category2 in(select id from category2 where name like '%blonde%' or name like '%italy%' or name like '%sky%') or id_category3 in(select id from category3 where name like '%blonde%' or name like '%italy%' or name like '%sky%')

[thedream]

Thank you very much Matto, to don't let me alone in this hudge world of lines code...

I have try your second message and the outpout seems correct, but:
When I try to include it in my resaerch page I get the following code:

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 'select catgen.im_format from catgen where im_catgen like '%%')'

I have read on the PHP/Mysql O'Reilly book that Mysql doesn't like this kind of expression like:
select * from xy
where abc in(select abc from efg where klm="%search%"

I am loosing my mind.

To be sure to not make a mistake, here is a correct draw of my tables:

Phototek:
+----------------+--------------+------------------+-------------+------------+
| Image ID | im_name | comments | formatID | catgenID |
+----------------+--------------+------------------+-------------+------------+
| 1 | 006.jpg | blonde | 2 | 1 |
+----------------+--------------+------------------+-------------+------------+

format
+------------+---------------+
| formatID | im_format |
+------------+---------------+
| 1 | color |
| 2 | B & W |
+------------+---------------+

catgen
+-------------+---------------------+
| catgenID | im_catgen |
+-------------+---------------------+
| 1 | backgrounds |
| 2 | business |
| 3 | people

etc.... |
+------------+----------------------+

So if I understand your code it wil be now (to test i have done it only on 2 tables:
$keywords = explode(' ', $searchstring);

$clause = "im_name like '%"
.implode("%' or im_name like '%", $keywords)
."%'";
$clause2 = "im_catgen like '%"
.implode("%' or im_catgen like '%", $keywords)
."%'";

$query_rsSearch = "select * from phototek where "
."$clause or "
."formatID in(select catgen.im_format from catgen where $clause2) or ";
."catgenID in(select im_catgen from catgen where $clause)";
// ."id_category3 in(select id from category3 where $clause)";

Am I right or am I wrong?

Sincerely yours,
Marc-André

[tsinka]

Hi,

MySQL does indeed not support subselects. One of the most recent versions might support them but I don't know it exactly.

Use only %value% if you want to search for substrings and use ='value' if the values in the fields exactly match what you want to find. Using % would prevent MySQL from using any indexes at all (at least if you use %value%, not if you use value%).

[thedream]

Thank you Tsinka, for this point.

Marc-André