MySQL run query inside a query
Question by Daniel O
I have a query that gets 5 lines of data like this example below
$query = "SELECT ref,user,id FROM table LIMIT 0, 5";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$ref = $row['ref'];
}
I want to run a query inside each results like this below
$query = "SELECT ref,user,id FROM table LIMIT 0, 5";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$ref = $row['ref'];
$query = "SELECT domain,title FROM anothertable WHERE domain = '$ref'";
$result = mysql_query($query) or die(mysql_error());
if (mysql_num_rows($result) )
{
$title = $row['title'];
} else {
$title = "No Title";
}
echo "$ref - $tile";
}
but for some reason it’s only display the first line when I add the query inside it. I can seem to make it run all 5 queries.
Answer by user1109719
You change the value of your $query in your while loop.
Change the variable name to something different.
Ex:
$query = "SELECT ref,user,id FROM table LIMIT 0, 5";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$ref = $row['ref'];
$qry = "SELECT domain,title FROM anothertable WHERE domain = '$ref'";
$rslt = mysql_query($qry) or die(mysql_error());
if (mysql_num_rows($rslt) )
{
$title = $row['title'];
} else {
$title = "No Title";
}
echo "$ref - $tile";
}
Answer by Starx
This is a logical problem. It happens that way, because you are same variable names outside and inside the loop.
Explanation:
$query = "SELECT ref,user,id FROM table LIMIT 0, 5";
$result = mysql_query($query) or die(mysql_error());
// Now $results hold the result of the first query
while($row = mysql_fetch_array($result))
{
$ref = $row['ref'];
//Using same $query does not affect that much
$query = "SELECT domain,title FROM anothertable WHERE domain = '$ref'";
//But here you are overriding the previous result set of first query with a new result set
$result = mysql_query($query) or die(mysql_error());
//^ Due to this, next time the loop continues, the $result on whose basis it would loop will already be modified
//..............
Solution 1:
Avoid using same variable names for inner result set
$query = "SELECT ref,user,id FROM table LIMIT 0, 5";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$ref = $row['ref'];
$query = "SELECT domain,title FROM anothertable WHERE domain = '$ref'";
$sub_result = mysql_query($query) or die(mysql_error());
// ^ Change this variable so that it does not overrides previous result set
Solution 2:
Avoid the double query situation. Use joins to get the data in one query call. (Note: You should always try to optimize your query so that you will minimize the number of your queries on the server.)
SELECT
ref,user,id
FROM
table t
INNER JOIN
anothertable t2 on t.ref t2.domain
LIMIT 0, 5
