mysql交叉表查询到php数组 - 而不是三个sql查询

Question

I'd like to select sum of three columns from three tables in mysql and then get it as php array.

Tables:

Amex:
incr_id,check_no, card_no, tip, total, date;
Mc:
incr_id,check_no, card_no, tip, total, date;
Visa
incr_id,check_no, card_no, tip, total,date ;

Pseudo code :

 $query = 
 select sum(tip) from Amex,
 select sum(tip) from Mc,
 select sum(tip) from Visa
 WHERE DATE(date) = CURDATE()

 mysql_fetch_array($query);

and then 'foreach' or 'while' to get three values into php array:

$ccpayments_tip['Amex']
$ccpayments_tip['Mc']      
$ccpayments_tip['Visa']

Is this possible or I will have to execute three select queries? Thanks!

Answer 1

You can use a Union query to do all three at once, with a derived field to indicate which table the sums are coming from:

$sql = <<<EOL
SELECT 'Amex' AS source, SUM(tip) AS sum FROM Amex
UNION
SELECT 'MC' AS source, SUM(tip) AS sum FROM Mc
UNION
SELECT 'Visa' AS source, SUM(tip) AS sum FROM Visa

EOL;

$result = mysql_query($sql) or die(mysql_error());

$ccpayments_tip = array();
while($row = mysql_fetch_assoc($result)) {
    $ccpayments_tip[$row['source']] = $row['sum'];
}

Of course, why do you have 3 seperate tables for this? Wouldn't it make more sense to have a single creditcard table with a type field to indicate if it's visa/amex/mc?