像这样输入html(用户以html形式输入)
row_id | debit | credit |
---------------------------
1 |2610 | 2380
2 |2620 | 2310
3 |5310 | 2621
有这样的 mysql 表
TrackPayments | AccountNumber
---------------------------
1 |2610
1 |2310
0 |2380
1 |2620
1 |2621
1 |5310
想要获取每个debit
和credit
的列TrackPayments
值
首先创建数组$debit
并像这样$credit
:
foreach ($_POST['row_id'] as $i => $row_id) {
$debit[] = $_POST['debit'][$i];
}
foreach ($_POST['row_id'] as $i => $row_id) {
$credit[] = $_POST['credit'][$i];
}
然后执行 2 个查询
$stmt1 = $db->prepare('SELECT TrackPayments FROM users WHERE AccountNumber= ?');
$stmt1->execute( $debit );
$result1 = $stmt1->fetch(PDO::FETCH_ASSOC);
$stmt2 = $db->prepare('SELECT TrackPayments FROM users WHERE AccountNumber= ?');
$stm2->execute( $credit );
$result2 = $stmt1->fetch(PDO::FETCH_ASSOC);
并得到
$result1
= array("1", "1", "1");
和
$result2
= array("0", "1", "1");
代码相当长,我做了 2 个 mysql 查询。是否可以使用一个mysql查询获得相同的结果(以使用更少的系统资源)?
你可以做这样的事情
SELECT debit, u.TrackPayments track_debit,
credit, u2.TrackPayments track_credit
FROM
(
SELECT 2610 debit, 2380 credit UNION ALL
SELECT 2620, 2310 UNION ALL
SELECT 5310, 2621
) q JOIN users u
ON q.debit = u.AccountNumber JOIN users u2
ON q.credit = u2.AccountNumber
示例输出:
|借记 |TRACK_DEBIT |信用 |TRACK_CREDIT ||-------|-------------|--------|--------------|| 2620 | 1 | 2310 | 1 || 2610 | 1 | 2380 | 0 || 5310 | 1 | 2621 | 1 |
或
SELECT AccountNumber, TrackPayments, 'debit' type
FROM users
WHERE AccountNumber IN(2610, 2620, 5310)
UNION ALL
SELECT AccountNumber, TrackPayments, 'credit' type
FROM users
WHERE AccountNumber IN(2380, 2310, 2621);
示例输出:
|帐号 |跟踪付款 | 类型 ||---------------|---------------|--------|| 2610 | 1 | 借记 || 2620 | 1 | 借记 || 5310 | 1 | 借记 || 2310 | 1 |信用 || 2380 | 0 |信用 || 2621 | 1 |信用 |
这是 SQLFiddle 演示