Re: [PHP] MySQL query/PHP logic?

Don't know whether it's just because it's late at night here in the UK or
whether I'm being a bit dim, but wouldn't it be much, much easier to store
all the data in your non-main tables in a single table and distinguish the
type of data (i.e. fof, pub, gov, etc....) by a field. This would simplify
your query and no doubt improve performance.


On Tuesday 29 Jul 2003 10:13 pm, Petre Agenbag wrote:
> Hi List
> OK, I've posted this on the MySQL list as well, but it seems a bit quiet
> there, and arguably it could definately be a PHP rather than mysql question
> depending on whether it can be done with one query (then it was/is a mysql
> query), or whether it should be done with some structures and multiple
> queries ( then it's arguably PHP).
>
> So, here goes.
>
>
> I'm trying to return from multiple tables, the records that have field
> "information_sent" between two dates.
> The tables are all related by means of the id of the entry in the main
> table, ie..
>
> main
> id entity_name ...
>
> fof
> id_fof id information_sent ...
>
> pub
> id_pub id information_sent ...
>
> etc.
>
> So, I tried the following join
>
> select * from main
> left join fof on main.id = fof.id
> left join pub on main.id = pub.id
> left join gov on main.id = gov.id
> left join med on main.id = med.id
> left join ngo on main.id = ngo.id
> left join own on main.id = own.id
> left join sup on main.id = sup.id
> left join tra on main.id = tra.id
> where (
> (fof.information_sent > '$date1' and fof.information_sent < '$date2')
> OR
> (pub.information_sent > '$date1' and pub.information_sent < '$date2')
> OR
> (gov.information_sent > '$date1' and gov.information_sent < '$date2')
> OR
> (med.information_sent > '$date1' and med.information_sent < '$date2')
> OR
> (ngo.information_sent > '$date1' and ngo.information_sent < '$date2')
> OR
> (own.information_sent > '$date1' and own.information_sent < '$date2')
> OR
> (sup.information_sent > '$date1' and sup.information_sent < '$date2')
> OR
> (tra.information_sent > '$date1' and tra.information_sent < '$date2')
> )
> order by entity_name
>
>
> BUT, although it seems to be "joining" the tables correctly AND only
> returning the ones with the correct date criteria, it does NOT return
> the "id" or the "information_sent" fields correctly ( due to duplication
> in the result )
>
> Can this be done in one query without sub-selects, or should it be broken
> up (in which case I would still need help with the logic and to minimize
> the amount of queries inside loops)
>
>
> Thanks