CODEWITHSUNDEEP
phpmysqlselect
Nick Hogan
Nick Hogan

Reputation: 54

MySQL Select Data from Multiple Unrelated Tables

I have a page on my site where I would like to list out the separate instances in which a customer ordered a part or brought their bike in for service. The data for each of these is contained in separate tables. Ultimately I'll be sorting these by date and using ORDER BY and COALESCE to make this work, but first I have to pull the data and I can't figure out how to do it.

Since the two tables are unrelated I won't be using a JOIN to combine data and I thought I might use a UNION but then learned that the data and number of columns for unions needs to be similar. I saw someone somewhere just throwing up a comma but I can't get mine to work.

Here's what I've got:

$result = mysql_query("SELECT 
        p.part_id,
        p.part_num,
        p.descr,
        p.vendor,
        p.date_entered,
        p.date_ordered,
        p.date_rcvd,
        s.serv_id,
        s.make,
        s.model,
        s.yr,
        s.vin,
        s.mileage,
        s.in_date,
        s.out_date
    FROM parts p, services s 
    WHERE cust_id = '$cust_id'");

if (mysql_num_rows($result) == 0) {
    $transactions = array();
} else {
    while ($row = mysql_fetch_assoc($result)) {
        $transactions[] = $row;
    }
}

Later on

<? foreach ($transactions as $transaction): ?>
    <? if($transaction['part_id'] && $transaction['part_id'] != "") { ?>
        [DISPLAY PART INFO]
    <? } elseif($transaction['serv_id'] && $transaction['serv_id'] != "" { ?>
        [DISPLAY SERVICE INFO]
    <? } ?>
<? endforeach; ?>

Any ideas?

Upvotes: 0

Views: 2267

Answers (2)

Carlos
Carlos

Reputation: 21

Waht about using null columns ? Ih the first table has columns name, date, pieces and the second name_cust, date_bike_entered you could do 

Select name, date, pieces, null, null from table1
Union All
Select null, null, null, name_cust, date_bike_entered from table2

Upvotes: 2

Kermit
Kermit

Reputation: 34055

Solution A

You will need two queries to handle a request for (1) parts and (2) services. You will need some logic to differentiate the two.

Solution B

SELECT COUNT(*) FROM parts WHERE part_id = $transaction['part_id']
SELECT COUNT(*) FROM services WHERE serv_id = $transaction['serv_id']

This will give you the number of results for each. Add logic to pull data based on the result. However, you'll need logic to handle if you have results in both queries.

Solution C

Have a radio form that allows only part_id or serv_id, and query based on the user's selection.

Upvotes: 0

Related Questions