php - 使用php pdo从for循环中的mysql数据库获取数据

Question

我有这个 php 代码 - for 循环和每一步，每次增量搜索 mysql 表中的数据 aktivnosti

PHP:

for ($i=1; $i<=30; $i++;){
    $temp = array();
    $temp['ID'] = $i;

// ATTEMP TO GET DATA FROM aktivnosti WHERE id_activity = $i

    $rs1 = $db->prepare('SELECT naziv FROM aktivnosti WHERE id_activity=:idd');
          $rs1->bindParam(':idd', $i); 

          $rs1->execute();
          $naz = $rs1->fetchColumn();

          $temp['activity'] =  '<button>'.$naz.'</button>';



    $output['data'][] = $temp;

}
$jsonTable = json_encode($output);

正如您从上面的代码中看到的那样，我尝试在每个 $i 增量上获取数据并搜索表 aktivnosti = $i 上的 id_activity

我只得到一个结果，所以我只得到第一个 'naziv'，我需要从表 aktivnosti 中获取所有 naziv 数据，其中 id_activity = $i 并创建:

<button>$naz[0]<button>
<button>$naz[1]<button>
<button>$naz[2]<button>
<button>$naz[how many times id_activity = $i]<button>

我该怎么做？一些想法？

对不起我的英文。谢谢

Answer 1

正如上面评论中所指出的，您在这里采取了错误的方法。您应该能够在单个查询中获取所有这些数据。如果您想要固定 30 天的概念，并且每一天都与 n 条记录相关，您可能还需要查看您的架构。我会建议两张 table

日程表

day_id  day_name (or any other day-related data fields)
1       ...
2       ...
...     ...
30      ...

days_records

record_id   day_id   other_data
1           1        ...
2           1        ...
3           3        ...
4           5        ...
...

然后你会这样查询:

SELECT
    d.day_id AS day_id
    dr.record_id AS record_id
    dr.other_date AS other_data
FROM day_list AS d
LEFT JOIN day_records AS dr
    ON d.day_id = dr.day_id

对于表名的更改，我们深表歉意，因为不知道您的数据库模式在现实世界中代表什么。

然后您进行一个查询，例如:

$query = <<<EOT
SELECT
    d.day_id AS day_id
    dr.record_id AS record_id
    dr.other_date AS other_data
FROM day_list AS d
LEFT JOIN day_records AS dr
    ON d.day_id = dr.day_id
EOT;

$rs1 = $db->execute($query);
if (false === $rs1) {
   // something went wrong. perhaps log an error
} else {
   while($row = $rs1->fetch(PDO::FETCH_ASSOC)) {
        $temp = $row;
        // check to see if this date has a record
        if (empty($temp['record_id'])) {
            // this is a day with no associated record.
            // do something
        }
        // not shown - continue to manipulate your $temp as desired
        // then add to output array
        $output['data'][] = $temp
   }
}