使用中的技术:php通过PDO访问MySQL。
我有以下疑问:
try {
$stmt = $conn->prepare("SELECT * FROM $database.appsLodged WHERE `uID` = :uid");
$stmt->bindValue(':uid', $uid);
$stmt->execute();
$result = $stmt->fetchAll();
} catch(PDOException $e) { catchMySQLerror($e->getMessage()); }
这是相当直接的。目前,相关数据库中有 7 行,每行都可能为 $result[0][5] 返回不同的字符串值。我希望能够计算有多少结果具有"鞋子和袜子"的$result[0][5],然后有多少结果具有"靴子和裙子"的$result[0][5],有多少结果具有"毛衣和开衫"的$result[0][5] - 而无需求助于多个数据库查询(我知道我们可以通过添加额外的"AND item
= :item"语法和绑定:item到"鞋袜",但这似乎非常重复和不必要的。
敬请提出建议,欢迎大家! :)
C
按照下面的要求,以下是列出的$result数组:
Array
(
[0] => Array
(
[id] => 1
[0] => 1
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Pending
[5] => Pending
[dateStarted] => 1421773018
[6] => 1421773018
[lastModified] => 1421773018
[7] => 1421773018
)
[1] => Array
(
[id] => 2
[0] => 2
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1421774389
[6] => 1421774389
[lastModified] => 1421774389
[7] => 1421774389
)
[2] => Array
(
[id] => 3
[0] => 3
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1421776146
[6] => 1421776146
[lastModified] => 1421776146
[7] => 1421776146
)
[3] => Array
(
[id] => 4
[0] => 4
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1421777460
[6] => 1421777460
[lastModified] => 1421777460
[7] => 1421777460
)
[4] => Array
(
[id] => 5
[0] => 5
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1421781756
[6] => 1421781756
[lastModified] => 1421781756
[7] => 1421781756
)
[5] => Array
(
[id] => 6
[0] => 6
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422213946
[6] => 1422213946
[lastModified] => 1422213946
[7] => 1422213946
)
[6] => Array
(
[id] => 7
[0] => 7
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422238026
[6] => 1422238026
[lastModified] => 1422238026
[7] => 1422238026
)
[7] => Array
(
[id] => 8
[0] => 8
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422369458
[6] => 1422369458
[lastModified] => 1422369458
[7] => 1422369458
)
[8] => Array
(
[id] => 9
[0] => 9
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422369473
[6] => 1422369473
[lastModified] => 1422369473
[7] => 1422369473
)
[9] => Array
(
[id] => 10
[0] => 10
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422371233
[6] => 1422371233
[lastModified] => 1422371233
[7] => 1422371233
)
[10] => Array
(
[id] => 11
[0] => 11
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422371291
[6] => 1422371291
[lastModified] => 1422371291
[7] => 1422371291
)
[11] => Array
(
[id] => 12
[0] => 12
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422372793
[6] => 1422372793
[lastModified] => 1422372793
[7] => 1422372793
)
[12] => Array
(
[id] => 13
[0] => 13
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422373414
[6] => 1422373414
[lastModified] => 1422373414
[7] => 1422373414
)
[13] => Array
(
[id] => 14
[0] => 14
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422373681
[6] => 1422373681
[lastModified] => 1422373681
[7] => 1422373681
)
[14] => Array
(
[id] => 15
[0] => 15
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422373927
[6] => 1422373927
[lastModified] => 1422373927
[7] => 1422373927
)
[15] => Array
(
[id] => 16
[0] => 16
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422374004
[6] => 1422374004
[lastModified] => 1422374004
[7] => 1422374004
)
[16] => Array
(
[id] => 17
[0] => 17
[appID] => 4
[1] => 4
[applicationKey] => ConSupAp
[2] => ConSupAp
[applicationName] => Conference Support Application
[3] => Conference Support Application
[uID] => 1
[4] => 1
[status] => Data Acquisition
[5] => Data Acquisition
[dateStarted] => 1422374587
[6] => 1422374587
[lastModified] => 1422374587
[7] => 1422374587
)
)
重要的是要记住 - 它实际上是我想计算的"状态"列,对于除第一个示例之外的所有这些示例,都设置为"数据采集"。第一个设置为"挂起"。这可能是另外三个值,如果当时这些值不在数据库中,我需要对它们进行零计数,以便用户知道它们将零数据集设置为其他潜在的"状态"设置。
我建议你执行以下操作:首先,你从数据库中获取所有这些字符串,所以你会有类似$result[0][5]
、$result[1][5]
、$result[2][5]
等的东西,然后你foreach()
它们并匹配不同变量中的字符串,每个字符串对应一个你想要匹配的字符串......我认为,根据您在查询中所说的内容,每个 uID 在 [5] 位置都有一种类型的字符串。在女巫的情况下,我建议你有一串id,所以你的查询变成这样:
$uid = '1,2,3,4,5'; // for example
$stmt = $conn->prepare("SELECT * FROM $database.appsLodged WHERE `uID` IN (:uid)");
$stmt->bindValue(':uid', $uid);
您可能需要一个额外的查询来获取所有这些 ID,但仅此而已:D
希望这有帮助!