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

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!

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?

You could do a union of the three queries.

You could try something like this

• http://dev.mysql.com/doc/refman/5.0/en/from-clause-subqueries.html

Pseudo

SELECT A.Amex, M.MC, V.Visa 
FROM 
   (SELECT SUM(tip) AS Amex FROM Amex WHERE DATE(date) = CURDATE()) AS A,
   (SELECT SUM(tip) AS Mc FROM Mc WHERE DATE(date) = CURDATE()) AS M,
   (SELECT SUM(tip) AS Visa FROM Visa WHERE DATE(date) = CURDATE()) AS V

UPDATE: Pseudo Code as well

/*
'*CARD TYPES            *PREFIX           *WIDTH
'American Express       34, 37            15
'Diners Club            300 to 305, 36    14
'Carte Blanche          38                14
'Discover               6011              16
'EnRoute                2014, 2149        15
'JCB                    3                 16
'JCB                    2131, 1800        15
'Master Card            51 to 55          16
'Visa                   4                 13, 16
*/   

SELECT SUM(tip), CASE
    WHEN card_no >= 4 AND card_no <= 5 THEN 'visa'
    WHEN card_no >= 51 AND card_no <= 56 THEN 'mc'
    WHEN card_no >= 34 AND card_no <= 38 THEN 'amex'
    ELSE NULL
END AS credit_card_type
FROM Amex, Mc, Visa
WHERE DATE(date) = CURDATE()
GROUP BY CASE
    WHEN card_no >= 4 AND card_no <= 5 THEN 'visa'
    WHEN card_no >= 51 AND card_no <= 56 THEN 'mc'
    WHEN card_no >= 34 AND card_no <= 38 THEN 'amex'
    ELSE NULL
END AS credit_card_type

Haven't tested either query just a example to maybe think outside of the box

UPDATE from your comment:

If you're going to move to a transaction database and store the card_type you could use a query like this:

SELECT SUM(tip)
FROM transaction_tbl
WHERE DATE(date) = CURDATE()
GROUP BY card_type