Combining two tables for while() loop

[smokehut]

Here's what I've currently got.

	$query = "SELECT * FROM deposits WHERE completed = 5 AND host = ? ORDER BY id DESC LIMIT 10";
	$stmt = $db->prepare($query);
	$stmt->bindValue(1, $user_data['username']);
	$stmt->execute();
				
	$fetchmethod = $stmt->fetch(PDO::FETCH_ASSOC);
			
	/*$query1 = "SELECT * FROM users WHERE username = ?";
	$stmt1 = $db->prepare($query1);
	$stmt1->bindValue(1, $fetchmethod['user']);
	$stmt1->execute();
	$fetch = $stmt1->fetch(PDO::FETCH_ASSOC);	
	*/

 That was before the while loop, hence I've ruled out which I need to include with the first query as if I don't, then the following loop only lists 'one' item.

echo '<table>';				
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {			
        /*
	if ($row['method'] == "RS07") {
	$coins = $fetch['coins'];
	} else
	if ($row['method'] == "EOC") {
	$coins = $fetch['eoc_coins'];
	}
	*/
	echo	'<tr>
	<td><font size="2" color="black">| ID: '.$row['id'].' |'.$row['user'].' | '.$row['method'].' | Deposit: '.$row['value'].'<font size="1">M/Gp</font> | Now has '.$coins.'<font size="1">M/Gp</font> |</font></td>
		</tr>';					
}
echo '</table><br/><br/><br/><br/>';

I need to combine these tables, so I can use an array in the echo'd table to be able to provide which type of coins are being used with the method, and relay that users current coins with the method.

[smokehut]

Cannot edit:

 

I've bodged this to get it to work and it does by doing this.

$query = "SELECT * FROM deposits WHERE completed = 5 AND host = ? ORDER BY id DESC LIMIT 10";
	$stmt = $db->prepare($query);
	$stmt->bindValue(1, $user_data['username']);
	$stmt->execute();			
			
echo '<table>';				
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {			
      $query2 = "SELECT * FROM deposits WHERE id = ?";
	$stmt2 = $db->prepare($query2);
	$stmt2->bindValue(1, $row['id']);
	$stmt2->execute();
					
	$fetchmethod = $stmt2->fetch(PDO::FETCH_ASSOC);
		
	$query1 = "SELECT * FROM users WHERE username = ?";
	$stmt1 = $db->prepare($query1);
	$stmt1->bindValue(1, $fetchmethod['user']);
	$stmt1->execute();
	$fetch = $stmt1->fetch(PDO::FETCH_ASSOC);	
				
				
	if ($row['method'] == "RS07") {
		$coins = $fetch['coins'];
	}
	if ($row['method'] == "EOC") {
		$coins = $fetch['eoc_coins'];
	}
	echo	'<tr>
		<td><font size="2" color="black">| ID: '.$row['id'].' |'.$row['user'].' | '.$row['method'].' | Deposit: '.$row['value'].'<font size="1">M/Gp</font> | Now has '.$coins.'<font size="1">M/Gp</font> |</font></td>
		</tr>';					
	}
echo '</table><br/><br/><br/><br/>';

Edited May 13, 2013 by smokehut

[Psycho]

I'm really not following your original question. But,if you are saying that the code in your second post works - you need to change it. You should NEVER run queries in loops. It is very inefficient and can cause significant performance problems. You need to learn how to use JOINs in your queries. I'm really confused by the first query inside the loop. The outer query gets a list of records from the deposits table. But, the first query in the loop then uses the ID of the record to find the same record. You can also replace this code

 

    if ($row['method'] == "RS07") {
        $coins = $fetch['coins'];
    }
    if ($row['method'] == "EOC") {
        $coins = $fetch['eoc_coins'];
    }

 . . . by creating a dynamic value in the query

 

Some other things:

Don't use "SELECT *" unless you really need all the fields in the tables

Don't use <FONT> tags. They've been deprecated for about a decade! Use classes or inline styles.

 

Here is a rewrite that should work better. Not guaranteeing it doesn't have an error or two. I can't test since I don't have your DB

 

$query = "SELECT d.id, d.user, d.method, d.value,
                 IF(d.method='RS07', u.coins, u.eoc_coins) AS coins_val
          FROM deposits AS d
          JOIN users AS u ON d.user = u.username
          WHERE d.completed = 5
            AND d.host = ?
          ORDER BY d.id DESC
          LIMIT 10";
    $stmt = $db->prepare($query);
    $stmt->bindValue(1, $user_data['username']);
    $stmt->execute();

echo "<table>\n";
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    echo "<tr>\n";
    echo "<td style=\"color:#000000; font-size:10pt;\">\n";
    echo "| ID: {$row['id']} |{$row['user']} | {$row['method']} ";
    echo "| Deposit: {$row['value']}<span style=\"font-size:8pt;\">M/Gp</span> | Now has {$row['coins_val']}<span style=\"font-size:8pt;\">M/Gp</span> |\n";
    echo "</td>\n";
    echo "</tr>\n";
}
echo "</table>\n<br/><br/><br/><br/>";

[smokehut]

Thank you very much for your post, and thank you for teaching me of new ways to code, looks like I was heading in the complete wrong direction ! I'll edit when I try this and let you know if it works.

 

 

This works absolutely fine, without any changes ! I'll now be re-doing most of my other while loops in this way, thank you.

Edited May 13, 2013 by smokehut