Busy — Mon, 03 Feb 2003 20:14:21 +0000

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

Busy — Thu, 30 Jan 2003 09:07:57 +0000

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.

Mark Hensler — Thu, 30 Jan 2003 07:04:56 +0000

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.

Busy — Thu, 30 Jan 2003 03:32:51 +0000

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

Mark Hensler — Wed, 29 Jan 2003 20:51:38 +0000

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.

Busy — Wed, 29 Jan 2003 20:23:14 +0000

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

Busy — Wed, 29 Jan 2003 09:10:51 +0000

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.

Busy — Wed, 29 Jan 2003 03:23:58 +0000

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 )

ROB — Tue, 28 Jan 2003 22:20:22 +0000

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

Busy — Tue, 28 Jan 2003 20:29:06 +0000

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