CODEWITHSUNDEEP
phpmysqlleft-joinecho
sunny
sunny

Reputation: 1593

How to store a query result into a variable in mysql and then use it another query and echo result?

I have some complex situation. I have joined multiple tables using LEFT JOIN Now I want to store the result of my query in a variable then i want to call this result in my another SELECT query to fetch my desired data from mysql db. I tried this but i failed.

Using following query i can store result of query in variable @v1

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

Now in following query i use variable in which i store result of my 1st query

    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

When i execute this query in my PhpMyadmin it show my correct result

enter image description here

But when i use this query in my PHP page and try to echo some field of my table it does not display any thing.

<?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;

}




?>

A blank page is shown in my browser. Please can you suggest me where i am wrong?

Upvotes: 1

Views: 1831

Answers (2)

Neddage
Neddage

Reputation: 306

Variables only last for the current mysql "session" so when doing it in PHP the second query is treated as a new session and therefore the variable isn't set.

For example if in phpmyadmin I run:

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

It works fine.

However if I run:

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

and then

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

It will return 0 results as @v1 isn't set.

Therefore I would try:

$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();

Upvotes: 2

Daniel Alder
Daniel Alder

Reputation: 5382

I don't know mysql's SET @ syntax, but I see that your code doesn't make sense:

"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.[...]

The first line of these two is nothing (has the same effect as a comment). It's a string which is not assigned or executed somehow. You either need to assign it to a variable ($sql2 = "SET ...") or execute it using mysql commands

Upvotes: 1

Related Questions