Jump to content

Perform a join


I-AM-OBODO

Recommended Posts

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)

Link to comment
Share on other sites

  • 2 weeks later...

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 by Muddy_Funster
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.