Jump to content

What's the best way to get a single record's info from multiple tables?


imgrooot

Recommended Posts

So I have two tables. 

 

Table 1 - Records

Table 2 - Earnings

 

I basically want to retrieve 6 active records from highest to lowest earnings.

 

Here are the table setups.

Records Table

record_id      |   record_name       |   status

1                   record_1                1
2                   record_2                0
3                   record_3                1
4                   record_4                1
5                   record_5                1
6                   record_6                1
7                   record_7                1
8                   record_8                1



--------------------------------------------

Earnings Table

earning_id     |    record_id     |    amount

1                      1               $100
2                      2               $200 
3                      3               $300
4                      4               $400
5                      5               $500
6                      6               $600
7                      7               $700
8                      8               $800
9                      1               $100
10                     1               $100

As you can see I have total of 8 records. Only 7 of them are active. And record_1 has multiple earning rows. This is the tricky part. Normally I can retrieve the records seperatly and the earnings seperatly but I would like to know how can I combine this into a single query to achieve the same result so that I can list 6 active records from highest to lowest earnings?

 

Here is my way so far.

$find_records = $db->prepare("SELECT record_id, record_name, status FROM records WHERE status = :status");
$find_records->bindValue(':status', 1);
$find_records->execute();
$result_find_records = $find_records->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find_records) > 0) {
  foreach($result_find_records as $row) {
    $record_id           =	$row['record_id'];
    $record_name         =	$row['record_name'];
    $record_status       =	$row['record_status'];

    $get_earnings = $db->prepare("SELECT amount FROM earnings WHERE record_id = :record_id");
    $get_earnings->bindParam(':record_id', $record_id);
    $get_earnings->execute();
    $result_earnings = $get_earnings->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_earnings) > 0) {
      $ub = 0;
      foreach($result_earnings as $key=>$row) {
        $ub+= $row['deposit'];
      }
      $record_amount = $ub;
    }
  }
} 
Link to comment
Share on other sites

you need to research JOINed queries. this will let you get related data in a single query.

 

if you want 'records' results that do have entries in the earnings table, you would use JOIN. if you want 'records' results, regardless of if they have entries in the earnings table, you would use LEFT JOIN.

 

to get aggregate values, research GROUP BY and the aggregate functions. you would use GROUP BY record_id and select SUM(amount) as total (assuming the column name is amount or is it deposit?)

 

if you want a limited number of rows in the result set (your sample data would give 7 active results, but you have stated you want 6 in two places), you would use ORDER BY total DESC (to get the data in the correct order) and LIMIT 6 (to get the 6 highest.)

 

lastly, your sample data implies you have a $ stored with the values. any currency symbol is a human convention and doesn't mean anything to the database and should not be stored with the values. the amount/deposit column should be a numerical data type, integer if only using whole values (what your sample data shows) or decimal for, well, decimal xxx.xx values.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.