如何从第一个选择查询中获取两个id，然后将其用于第二个选择查询？

I'm using below code

我正在使用下面的代码

<?php
$site_id = '7AF099D94576F8C4';
// Check Monitor ID from site id
$sql = "SELECT * FROM status Where site_id='$site_id'";
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
    $st_id = $row['st_id'];

    $mon_id = $row['mon_id'];

    $mon_site_id = $row['mon_site_id'];
    echo $mon_id;

    $result = mysql_query("SELECT * FROM monitors WHERE mon_id='$mon_id'")
            or die(mysql_error());

// keeps getting the next row until there are no more to get
    while ($row = mysql_fetch_array($result)) {
        // Print out the contents of each row
        echo $row['name'] . "<br />";
    }
}
?>

And I'm getting two mon_id like as 1 and 2 so next I want to use these two mon_id in 2nd select query but 2nd query give me just one result mean give result just one name. How can I get 2nd name ?

我得到两个mon_id,如1和2所以接下来我想在第二个选择查询中使用这两个mon_id,但是第二个查询只给出一个结果意味着给结果只有一个名字。我怎样才能得到第二个名字?

2 个解决方案

#1

You are overwriting your $result variable in your second query, thus losing the data from your first query. Change the names for the second query variables and you're done.

您在第二个查询中覆盖了$ result变量,从而丢失了第一个查询中的数据。更改第二个查询变量的名称,您就完成了。

<?php
$site_id = '7AF099D94576F8C4';
// Check Monitor ID from site id
$sql = "SELECT * FROM status Where site_id='$site_id'";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
    $st_id = $row['st_id'];
    $mon_id = $row['mon_id'];
    $mon_site_id = $row['mon_site_id'];
    echo $mon_id;

    /// CHANGE HERE
    $result2 = mysql_query("SELECT * FROM monitors WHERE mon_id='$mon_id'") or die(mysql_error()); 
    /// CHANGE HERE 

    // keeps getting the next row until there are no more to get

    /// CHANGE HERE
    while($row2 = mysql_fetch_array( $result2 )) {
    /// CHANGE HERE
        // Print out the contents of each row
    /// CHANGE HERE
        echo $row2['name']."<br />";
    /// CHANGE HERE
    }
}
?>

#2

First: your desired result can be achieved by combining the queries into one query.
There are tons of ways to get it faster, but in your case the most understandable query looks like this:

第一:通过将查询组合到一个查询中,可以实现所需的结果。有很多方法可以让它更快,但在您的情况下,最容易理解的查询如下所示:

SELECT * FROM monitors WHERE mon_id IN ( "SELECT mon_id FROM status Where site_id='$site_id'");`

You will not get any data from your status table, but as you are only interested in the mon_id for your monitors query, that will not matter much.

您不会从状态表中获取任何数据,但由于您只对监视器查询的mon_id感兴趣,因此无关紧要。

Second: you should take a look at JOIN Queries in MySQL

第二:你应该看看MySQL中的JOIN查询

Third: you must have a look into escaping strings for queries, a good idea is to used bound variables and PDO: a good start is http://www.w3schools.com/php/php_mysql_prepared_statements.asp

第三:你必须要查看转义字符串的查询,一个好主意是使用绑定变量和PDO:一个好的开始是http://www.w3schools.com/php/php_mysql_prepared_statements.asp

#1