从同一语句中的2个MySQL表中选择

Question

I need to select data from one MySQL table and match an ID from the query to get an email address from the second table. Then output the results. My current code is:

$sql = "SELECT fk_i_user_id, i_amount FROM osei_t_payment_pro_wallet
        WHERE i_amount > 0 order by i_amount  DESC";

$rows = mysqli_query($conn, $sql);

while ($rs = mysqli_fetch_array($rows, MYSQLI_ASSOC)){
    $uid = $rs["fk_i_user_id"];
    $cash = $rs["i_amount"] / 1000000;
    echo "User ID - ".$uid." Wallet Val - ".$cash.chr(10).chr(13);
}

I would like to incorporate in this query:

"SELECT s_email FROM osei_t_user where pk_i_id =".$uid;

And output the results:

echo "User ID - ".$uid." Wallet Val - ".$cash." - Email: ".(value from above query).chr(10).chr(13);

Answer 1

You can use a join. Use an outer join if the second table's record may not be present:

SELECT fk_i_user_id,i_amount, s_email 
FROM osei_t_payment_pro_wallet 
LEFT OUTER JOIN osei_t_user ON pk_i_id=fk_i_user_id
WHERE i_amount > 0 order by i_amount DESC

The outer join selects records that match the ON clause, but those fields will be null if there is no matching record for the parent table (osei_t_payment_pro_wallet).

Updated output (from OP):

$email = $rs["s_email"]; 
echo "User ID - ".$uid." Wallet Val - ".$cash." Email -".$email.chr(10).chr(13); 

Though I would write this:

echo "User ID - {$uid} Wallet Val - {$cash} Email - {$email}
";

You could also use the results directly:

echo "User ID - {$rs["fk_i_user_id"]} Wallet Val - {$cash} Email - {$rs["s_email"]}
";

$cash still needs to be calculated.