Perform a join

[I-AM-OBODO]

Hi all.
I have two tables where the username is what they have in common. i want to perform a join for both tables but i'm having problems with mysql joins.

//to get the desire result individually i did

//table one
$stmt = $pdo->query("SELECT * FROM tableone WHERE username = '$_GET[id]'");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$credit_score = $row['credit_score'];
$acct_num =  $row['acct_num'];
$acct_name = ucwords($row['surname']) ." ". ucwords($row['firstname']);
$username = $row['username'];

if($credit_score ==3){
    $bill_limits = 2000;
}elseif($credit_score ==2){
    $bill_limits = 1000;
}elseif($credit_score ==1){
    $bill_limits = 500;
}

//table two
$stmt=$pdo->query("SELECT SUM(amt) as bill FROM tabletwo WHERE username = '$_GET[id]' AND relationship = 'PARENT'");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$bill = $row['bill'];
$service_charge_for_limits = '0.05' * $bill;
$tax_rate_for_limits = '0.13' * $service_charge_for_limits;
$bill_sum = $tax_rate_for_limits + $service_charge_for_limits + $bill;


Approved Bill Limits = $<?php echo number_format($bill_limits,2); ?>
<br>
Bill Limits Used = $<?php  echo number_format($bill_sum,2); ?>
<br>
<?php $available_limits = $bill_limits - $bill_sum; ?>
Bill Limits Available = $<?php echo number_format($available_limits,2); ?>

The above gives me the correct result, but now i have another page where i want to all the clients and their corresponding available limits, used limits and approve limits form table two and other information from table one

On the page i have

$stmt = $pdo->prepare("SELECT * FROM tableone WHERE status = 'COMPLETED' ORDER BY id DESC LIMIT $start, $limit");
    $stmt->execute();
    $num_rows = $stmt->rowCount();

echo "<table width='100%' class='table-responsive table-hover table-condensed table-striped'>";
echo "<tr>
    <th bgcolor='#444444' align='center'><font color='#fff'>Account Number</th>
    <th bgcolor='#444444' align='center'><font color='#fff'>Subscriber's Name</font></th>
    <th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th>
    <th bgcolor='#444444' align='center'><font color='#fff'>Limits ($)</font></th>
    <th bgcolor='#444444' align='center'><font color='#fff'>View Profile</font></th>
    <th bgcolor='#444444' align='center'><font color='#fff'>Delete Account</font></th>
    </tr>";
// keeps getting the next row until there are no more to get
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Print out the contents of each row into a table
    echo "<tr><td>";
    echo $row['acct_num'];
    echo "</td><td>";
    echo ucwords($row['surname']." ". $row['firstname']);
    echo "</td><td>";
    echo $row['username'];
    echo "</td><td>";
    $credit_score = $row['credit_score'];
    if($credit_score ==3){
    $bill_limits = 2000;
    }elseif($credit_score ==2){
    $bill_limits = 1000;
    }elseif($credit_score ==1){
    $bill_limits = 500;
    } echo number_format($bill_limits, 2);
    echo "</td><td>";
    echo "<a href='view-client-profile.php?id={$row['username']}'>view more</a>";
    echo "</td><td>";
    echo "<a href='delete-account.php?id={$row['username']}'>Delete Account</a>";
    echo "</td></tr>";

    //echo "</td><td>";
    //echo "<a href='settle.php?id={$row['acct_num']}'>Points</a>";

}
echo "</table>";

How ca i join tableone and tabletwo (plus sum)

[Muddy_Funster]

because you want to perform the aggregate function on the table before it's joined you need to join to the SELECT statement itself, and no the table directly.

 

 

SELECT tableone.name, list, your, other, column, names, from, tableone, tempBillTbl.bill
FROM tableone
INNER JOIN (SELECT name, sum(amt) as bill FROM tabletwo GGROUP BY name) as tempBillTbl)
ON (tableone.name = tempBillTbl.name)

 

also, look into prapared statements in PDO, rather than passing in variables directly to the sql string.

Edited April 20, 2015 by Muddy_Funster