php - 如何将查询结果存储到 mysql 中的变量中，然后将其用于另一个查询和回显结果？

Question

我有一些复杂的情况。我已经使用 LEFT JOIN 连接了多个表 现在我想将我的查询结果存储在一个变量中然后我想在我的另一个 SELECT 查询中调用这个结果来获取我的来自 mysql 数据库的所需数据。 我试过了，但我失败了。

使用下面的查询，我可以将查询结果存储在变量@v1

SET @v1 := (SELECT `opinion_id` FROM `pacra_client_opinion_relations` WHERE `client_id` = 97 LIMIT 1);

现在在下面的查询中我使用变量来存储我的第一个查询的结果

    SELECT @v1, r.client_id,c.id,t.id,a.id,o.id,c.name as opinion, r.notification_date, t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title as cname
FROM og_ratings r 
LEFT JOIN og_companies c
ON r.client_id = c.id
LEFT JOIN og_rating_types t
ON r.rating_type_id = t.id
LEFT JOIN og_actions a
ON r.pacra_action = a.id
LEFT JOIN og_outlooks o
ON r.pacra_outlook = o.id
LEFT JOIN og_lterms l
ON r.pacra_lterm = l.id
LEFT JOIN og_sterms s
ON r.pacra_sterm = s.id
LEFT JOIN   companies_contact co
ON c.id = co.companies_id
LEFT JOIN pacra_client_opinion_relations pr
ON pr.opinion_id = c.id
LEFT JOIN pacra_clients pc
ON pc.id = pr.client_id
LEFT JOIN city
ON city.id = pc.head_office_id
WHERE r.client_id= @v1
ORDER BY r.id DESC
LIMIT 1

当我在我的 PhpMyadmin 中执行这个查询时，它显示了我的正确结果

但是当我在我的 PHP 页面中使用此查询并尝试回显我的表的某些字段时，它没有显示任何内容。

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "pacra1";


$conn = new mysqli($servername, $username, $password, $dbname);

"SET @v1 := (SELECT `opinion_id` FROM `pacra_client_opinion_relations` WHERE `client_id` = 97 LIMIT 1)";
$sql= " SELECT @v1, r.client_id,c.id,t.id,a.id,o.id,c.name as opinion, r.notification_date, t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title as cname
FROM og_ratings r 
LEFT JOIN og_companies c
ON r.client_id = c.id
LEFT JOIN og_rating_types t
ON r.rating_type_id = t.id
LEFT JOIN og_actions a
ON r.pacra_action = a.id
LEFT JOIN og_outlooks o
ON r.pacra_outlook = o.id
LEFT JOIN og_lterms l
ON r.pacra_lterm = l.id
LEFT JOIN og_sterms s
ON r.pacra_sterm = s.id
LEFT JOIN   companies_contact co
ON c.id = co.companies_id
LEFT JOIN pacra_client_opinion_relations pr
ON pr.opinion_id = c.id
LEFT JOIN pacra_clients pc
ON pc.id = pr.client_id
LEFT JOIN city
ON city.id = pc.head_office_id
WHERE r.client_id= @v1
ORDER BY r.id DESC
LIMIT 1";

$result = $conn->query($sql);
while ($row = $result->fetch_assoc()){

    $liaison_one = $row['liaison_one'];
    echo $liaison_one;

}




?>

我的浏览器中显示一个空白页面。你能告诉我哪里错了吗？

Answer 1

变量仅在当前 mysql“ session ”中有效，因此在 PHP 中执行此操作时，第二个查询被视为新 session ，因此未设置变量。

例如，如果我在 phpmyadmin 中运行:

SET @v1 := (SELECT id FROM `mytable` WHERE field='something' LIMIT 1);
SELECT * FROM `mytable` WHERE id = @v1;

它工作正常。

但是如果我运行:

SET @v1 := (SELECT id FROM `mytable` WHERE field='something' LIMIT 1);

然后

SELECT * FROM `mytable` WHERE id = @v1;

由于未设置 @v1，它将返回 0 个结果。

因此我会尝试:

$sql = "SET @v1 := (SELECT `opinion_id` FROM `pacra_client_opinion_relations` WHERE `client_id` = 97 LIMIT 1);
SELECT @v1, r.client_id,c.id,t.id,a.id,o.id,c.name as opinion, r.notification_date, t.title as ttitle,a.title as atitle,o.title as otitle, l.title as ltitle, s.title as stitle, pr.opinion_id, pc.id, pr.client_id as pr_client, pc.address, pc.liaison_one, city.id, pc.head_office_id, city.city, pc.title as cname
FROM og_ratings r 
LEFT JOIN og_companies c
ON r.client_id = c.id
LEFT JOIN og_rating_types t
ON r.rating_type_id = t.id
LEFT JOIN og_actions a
ON r.pacra_action = a.id
LEFT JOIN og_outlooks o
ON r.pacra_outlook = o.id
LEFT JOIN og_lterms l
ON r.pacra_lterm = l.id
LEFT JOIN og_sterms s
ON r.pacra_sterm = s.id
LEFT JOIN   companies_contact co
ON c.id = co.companies_id
LEFT JOIN pacra_client_opinion_relations pr
ON pr.opinion_id = c.id
LEFT JOIN pacra_clients pc
ON pc.id = pr.client_id
LEFT JOIN city
ON city.id = pc.head_office_id
WHERE r.client_id= @v1
ORDER BY r.id DESC
LIMIT 1";

$conn->multi_query($sql);
$conn->next_result(); //as the first query we don;t need anything from
$result = $conn->use_result();