joining two sql statements
| From: | Merlin | Date: | Mon, 25 Aug 2003 11:50:57 +0000 |
| Subject: | joining two sql statements | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-30083@lists.php.net to get a copy of this message | ||
Hello,
I am having a problem on how to get products out of 2 diffenent mysql tables to display them on one php site. Goal is to display 10 listings
each site which can contain data out of both tables. Problem though is,
that I want to fill the same variable with values from both tables.
Right now it kind of works with 2 statements. I would like to join them
into one statement, as there are more products comming up, which would complety confuse the whole thing.
Has anybody an idea on how to "join" those 2 statements?
Here they come:
$stmt="
SELECT
p.*,
u.first_name AS company_name,
u.branch,
UNIX_TIMESTAMP( p.start_date )AS start_date,
c.*,
p.currency,
u.user_name,
u.user_id,
co.country_name_safe,
co.country,
pr.province,
ci.city
FROM
$T5 u,
$T110 p
LEFT JOIN $T111 AS c ON p.ID = c.product_id AND c.branch = 03
LEFT JOIN $DB3.$geo_T1 AS co ON co.country_code = p.country
LEFT JOIN $DB3.$geo_T2 AS pr ON pr.country_code = p.country AND pr.province_id = p.province
LEFT JOIN $DB3.$geo_T3 AS ci ON ci.ID = p.city
WHERE
p.online = 1
AND p.owner_id = u.user_id
/* AND u.premium_terminates >= $today */
$where_stmt
ORDER BY
p.timestamp desc
LIMIT $limit_bottom,$hits_per_site
";
// apartments
$stmt="
SELECT
p.*,
u.first_name AS company_name,
u.branch AS branch_x,
c.*,
p.currency,
u.user_name,
u.user_id,
co.country_name_safe,
co.country,
pr.province,
ci.city
FROM
$T5 u,
$T112 p
LEFT JOIN $T111 AS c ON p.ID = c.product_id AND c.branch = 06
LEFT JOIN $DB3.$geo_T1 AS co ON co.country_code = p.country
LEFT JOIN $DB3.$geo_T2 AS pr ON pr.country_code = p.country AND pr.province_id = p.province
LEFT JOIN $DB3.$geo_T3 AS ci ON ci.ID = p.city
WHERE
p.online = 1
AND p.owner_id = u.user_id
/* AND u.premium_terminates >= $today */
$where_stmt
ORDER BY
p.timestamp desc
LIMIT $limit_bottom,$hits_per_site
";
Thanx for any help on that,
Merlin