Sql查询只打印第一行

I am coding in php and the code takes data from an array to fetch additional data from a mysql db. Because I need data from two different tables, I use nested while loops. But the code below always only prints out (echo "a: " . $data3[2]; or echo "b: " . $data3[2];) one time:

我在php中编码,代码从数组中获取数据以从mysql数据库中获取其他数据。因为我需要来自两个不同表的数据,所以我使用嵌套的while循环。但是下面的代码总是只打印出来(echo“a:”。$ data3 [2];或echo“b:”。$ data3 [2];)一次:

foreach($stuff as $key)
{
    $query3 = "SELECT * FROM foobar WHERE id='$key'";
    $result3 = MySQL_query($query3, $link_id);

    while ($data3 = mysql_fetch_array($result3))
    {
        $query4 = "SELECT * FROM foobar_img WHERE id='$data3[0]'";
        $result4 = MySQL_query($query4, $link_id);

        while ($data4 = mysql_fetch_array($result4))
        {   
            $x += 1;
            if ($x % 3 == 0)
            {
                echo "a: " . $data3[2];
            }
            else
            {
                echo "b: " . $data3[2];
            }
        }
    }
}

2 个解决方案

#1

First and foremost, improve your SQL:

首先,改进你的SQL:

SELECT
    img.*
FROM
    foobar foo
    INNER JOIN foobar_img img ON
        foo.id = img.id
WHERE
    foo.id = $key

You will only have to iterate through one array.

您只需迭代一个数组。

Also, it appears that you're actually only selecting one row, so spitting out one row is expected behavior.

此外,看起来您实际上只选择了一行,因此吐出一行是预期的行为。

Additionally, please prevent yourself from SQL injection by using mysql_real_escape_string():

另外,请使用mysql_real_escape_string()防止自己注入SQL:

$query3 = "SELECT * FROM foobar WHERE id='" . 
     mysql_real_escape_string($key) . "'";

Update: As Dan as intimated, please run this query in your MySQL console to get the result set back, so you know what you're playing with. When you limit the query to one ID, you're probably only pulling back one row. That being said, I have no idea how many $keys are in $stuff, but if it spins over once, then it will be one.

更新:正如Dan所暗示的那样,请在您的MySQL控制台中运行此查询以获取结果集,以便您知道自己在玩什么。当您将查询限制为一个ID时,您可能只会撤回一行。话虽这么说,我不知道有多少$ key在$ stuff中,但如果它旋转一次,那么它就是一个。

You may be better off iterating through $stuff and building out an IN clause for your SQL:

你可能最好迭代$ stuff并为你的SQL构建一个IN子句:

$key_array = "";
foreach($stuff as $key)
{
    $key_array .= ",'" . mysql_real_escape_string($key) . "'";
}
$key_array = substr($key_array, 1);

...

WHERE foo.id IN ($key_array)

This will give you a result set with your complete list back, instead of sending a bunch of SELECT queries to the DB. Be kind to your DB and please use set-based operations when possible. MySQL will appreciate it.

这将为您提供一个包含完整列表的结果集,而不是向DB发送一堆SELECT查询。善待您的数据库,请尽可能使用基于集合的操作。 MySQL会很感激。

I will also point out that it appears as if you're using text primary keys. Integer, incremental keys work best as PK's, and I highly suggest you use them!

我还要指出,好像你正在使用文本主键。整数,增量键最适合PK,我强烈建议你使用它们!

#2

You should use a JOIN between these two tables. It the correct way to use SQL, and it will work much faster. Doing an extra query inside the loop is bad practice, like putting loop-invariant code inside a loop.

您应该在这两个表之间使用JOIN。它是使用SQL的正确方法,它可以更快地工作。在循环中执行额外的查询是不好的做法,比如将循环不变代码放在循环中。

#1