MySQL run query inside a query

SELECT 
    t1.ref, 
    t1.user,
    t1.id,
    t2.domain,
    t2.title
FROM 
    table AS t1
    LEFT JOIN anothertable AS t2 ON
        t2.domain = t1.ref
LIMIT
    0, 5

The problem is that inside the while-cycle you use the same variable $result, which then gets overridden. Use another variable name for the $result in the while cycle.

Use the following :

$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_domain = "SELECT domain,title FROM anothertable WHERE domain = '$ref'";
    $result_domain = mysql_query($query_domain) or die(mysql_error());
    if (mysql_num_rows($result_domain) )
    {
        $row_domain = mysql_fetch_row($result_domain);
        $title = $row_domain['title'];
    } else {
        $title = "No Title";
    }
    echo "$ref - $title";
}

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

Learn about SQL joins:

SELECT table.ref, table.user, table.id, anothertable.title
FROM   table LEFT JOIN anothertable ON anothertable.domain = table.ref
LIMIT  5

You're changing the value of $result in your loop. Change your second query to use a different variable.

it is not give proper result because you have used same name twice, use different name like this edit.

$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'];
    $query1 = "SELECT domain,title FROM anothertable WHERE domain = '$ref'";
    $result1 = mysql_query($query1) or die(mysql_error());
    if (mysql_num_rows($result1) )
    {
        $title = $row['title'];
    } else {
        $title = "No Title";
    }
    echo "$ref - $tile";
}