MySQL while 循环查询在其他 while 循环查询中 [英] MySQL while loop query inside other while loop query

问题描述

<?php

include 'config.php';

$conn = mysql_connect("$hostname", "$username", "$password") or die ("Failed to connect to DB.");

mysql_select_db("$dbname",$conn);

    $sql="SELECT * FROM opdrachten";
    $result=mysql_query($sql,$conn);

while ($row= mysql_fetch_array($result))
{
    echo $row['name'];
    echo "<br>";
    $opdrachtid = $row["id"];

    $sql2="SELECT * FROM resultaten WHERE(opdrachtid='".$opdrachtid."')";
    $result2=mysql_query($sql2,$conn);

    while ($row2= mysql_fetch_array($result2))
    {
        echo "
                    <li>
                        <a href=\"result.php?id=".$row2["id"]."\">
                            <img src=\"".$row2["img"]." \"width=\"150\" height=\"150\">
                            <div><span>TEXT HERE</span></div>
                        </a>
                    </li>";
    }


}           
?>

我希望我的代码做的是(在循环中):

What I want my code to do is (in a loop):

1. 从表opdrachten"中获取所有行并回显它们的名称".
2. 从opdrachten"中的每一行中获取id"> 存储在变量 $opdrachtid 中
3. 从表 'resultaten' 中获取行，其中 'opdrachtid == $opdrachtid'(我刚刚存储的)
4. 从 3 中提取的行中回显 'id' 和 'img'.

我想在我的页面上看到什么:

What I want to see on my page:

1. 'opdrachten' 表中 'opdrachten'(目标)的名称直接在下面
2. 分配给这些目标的图像(其 url 存储在表 'resultaten' 中)(WHERE opdrachten.id = resultaten.opdrachtid)

我已经研究了 JOIN，因为这是我在这里阅读的大多数相关主题的答案，但这似乎不是我要找的东西，因为我在第一时间回应并声明用于第二个查询的变量.

I've looked into JOIN since that's the answer to most of the related topic's I've read here, but that doesn't seem to be what I'm looking for since I'm echo'ing in the first while and declare variables that are used for the second query.

使用的表格:

'结果'身份证 |opdrachtid |名字

'resultaten' id | opdrachtid | name

'opdrachten'身份证 |姓名

'opdrachten' id | name

同样，resultaten.opdrachtid == opdrachten.id

Again, resultaten.opdrachtid == opdrachten.id

提前致谢，感谢您的帮助:)

Thanks in advance, appreciate the help :)

推荐答案

我当然建议在此处使用 JOIN，这样您就可以一次性获得所有这些数据.但是，我将添加一个警告 - 这将需要更多的内存，就像您目前所做的那样在循环中查询数据库，因此如果您在任一表中有大量行，这可能不是一种合适的方法.

I would certainly suggest using a JOIN here that way you can get all this data in one go. I will add one caveat however - this would take more memory that querying the database in a loop like you are currently doing, so if you have a large number of rows in either table, this might not be a suitable approach.

查询将是:

SELECT * FROM opdrachen
INNER JOIN resultaten ON opdrachen.id = resultaten.opdrachenid

然后，您可以非常简单地设置一个以每个 id 为键的数组，如下所示:

You can then set up an array that is keyed on each id very simply like this:

$array = array();
while ($row = mysql_fetch_array($result)) {
    $array[$row['id']][] = $row;
}

这为您提供了一种迭代结果的简单方法，一次一个 ID，如下所示:

This give you an easy way to iterate through the results, one id at a time like this:

foreach ($array as $id => $rows_for_id) {
      // output stuff for row group
      foreach ($rows_for_id as $resultaten_row) {
          // output stuff for resultaten rows
      }
}

其他一些建议:

• 不要使用 mysql_* 函数，因为它们已被弃用.了解如何使用 mysqli 或 PDO.
• 不要使用 SELECT * 查询.它很懒惰，浪费带宽、传输时间和系统内存.仅查询您实际感兴趣的特定字段.因此，将我示例中的 * 替换为您需要的实际字段.

• Don't use mysql_* functions as they are deprecated. Learn how to use mysqli or PDO.
• Don't use SELECT * queries. Its is lazy and wastes bandwidth, transfer time, and system memory. Only query for the specific fields you are actually interested in. So replace the * in my example with the actual field you need.

这篇关于MySQL while 循环查询在其他 while 循环查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！