Try something like this.
select
fof.id AS fof_id, fof.information_sent AS fof_info,
pub.id AS pub_id, pub.information_sent AS pub_info,
gov.id AS gov_id, gov.information_sent AS gov_info,
med.id AS med_id, med.information_sent AS med_info,
ngo.id AS ngo_id, ngo.information_sent AS ngo_info,
own.id AS own_id, own.information_sent AS own_info,
sup.id AS sup_id, sup.information_sent AS tup_info,
tra.id AS tra_id, tra.information_sent AS tra_info,
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
then in your loop you will need to look for something like this.
while($row = mysql_fetch_array($results)){
if(!empty($row['fof_id']))
echo "found";
if(!empty($row['fof_id']))
echo "found";
etc...
}
You get my point, but the big part is the select * <-- part of the sql
call
you have to identify each column that you want back with an alias otherwise
they stomp all over one another.
Jim Lucas
----- Original Message -----
From: "Petre Agenbag" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2003 2:13 PM
Subject: [PHP] MySQL query/PHP logic?
> 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
>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
