joining db tables

Umm... Try including a JOIN clause (with an ON statememt). I'm not sure what's wrong.

How would I use an ON with a JOIN on the above?

What do your tables look like?

A mess

There are 3 tables with 2 of them having 4 columns each and one having 5 columns.
I just used the db1, sub1 etc above to make the lines shorter in the example, so using the same method

db1
-sub11 (id)
-sub12
-sub13
-sub14

db2
-sub21 (id)
-sub22
-sub23
-sub24

db3
-sub31 (id)
-sub32
-sub33
-sub34
-sub35

If you want the proper names I'll have to do a db dump, but can't at the moment as I have an annoying CMOS error that pops up when I try open anything

something like this

SELECT * FROM db1 JOIN db2 ON db2.sub21=db1.sub11 WHERE db1.sub1 LIKE '%apple%' and db1.sub2 LIKE '%food%' ORDER BY db2.sub3 LIMIT 0,10

Thanks I'll try that tonight, It's not a simple case of cut and paste as it's all if else statements controling the db** and sub** calls (it's an advanced search option - well for me anyways )

Tried it but gave sql error after ON
I think the reason it's not working is because I'm not actually using the id's of any of the tables, it's all being done by the other sections.
I'm not on my pc at the moment so can't get the code
I think it could be my setup as now I'm having trouble with '%stuff%' and 'stuff' not working properly in search terms.

I was playing around with it on paper last night and think the culprit is the 'and'
SELECT * FROM db1,db2 WHERE db1.sub1 LIKE '%apple%' and db1.sub2 LIKE '%food%' ORDER BY db2.sub3 LIMIT 0,10

Can I change the 'and' to anything else like a 'where' or '=' even

Do db1, db2, and db3 have any common field? If not, then you shouldn't be retrieving info fromt them all in one query.

By doing a "FROM db1,db2" the comma is a INNER JOIN, but your not specifying how they are joined.

mySQL Docs:
6.4.1.1 JOIN Syntax

Quote: INNER JOIN and , (comma) are semantically equivalent. Both do a full join between the tables used. Normally, you specify how the tables should be linked in the WHERE condition.

db1, db2, and db3 do have a common field but it's not being used in the SELECT statement as the contents are being matched not the id's
If I do it by matching id's I get random results - not the ones I want and if i try do two queries I can't match (display) them with the right results. the above works, just doubles (repeats) the ouput

Then you might have to exec 3 queries, and populate an array. You can then do array_unique() to remove duplicates and sort() to arrange your results.

so much for simple lol
I'll try get a working copy of it uploaded so you can check it out, you might be able to see an easier way.

update:
Well it seems I no longer have this problem as I no longer have the files I was uploading and backing up (to zip disk) when the good old blue screen got me (Thanks MS). so I ended up loosing it all.
Thanks for all the help.
Back to the drawing board