Is there a proper way to do this query?

[imgrooot]

I have two tables. Table-1 is Campaigns and Table-2 is Earnings.

 

I simply want to list the Campaigns from high to low earnings or vice versa. But I don't know how to do that since it's two separate tables. 

 

Here are the two queries I have. The query shows that the Campaigns will be listed by their campaign_id. I want to be able to order them by their earnings. How do I do that with these two queries?

$find_campaign = $db->prepare("SELECT * FROM campaigns WHERE status = :status ORDER BY campaign_id DESC LIMIT 10");
$find_campaign->bindValue(':status', 1);
$find_campaign->execute();
$result_find_campaign = $find_campaign->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find_campaign) > 0) {
  foreach($result_find_campaign as $row) {
    $campaign_id           =	trim($row['campaign_id']);
    $campaign_goal          =	trim($row['campaign_goal']);

    $get_raised = $db->prepare("SELECT deposit FROM earnings WHERE campaign_id = :campaign_id");
    $get_raised->bindParam(':campaign_id', $campaign_id);
    $get_raised->execute();
    $result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_raised) > 0) {
      $ub = 0;
      foreach($result_raised as $key=>$row) {
        $campaign_raised = trim($row['deposit']);
      }
    } else {
      $campaign_raised = 0;
    }

    //show campaign html here
  }
}

[Barand]

JOIN the two tables using the campaign_id columns. You can then select the columns you need from both tables in a single query and ORDER BY deposit DESC

[imgrooot]

JOIN the two tables using the campaign_id columns. You can then select the columns you need from both tables in a single query and ORDER BY deposit DESC

 

My bad, I forgot a small detail. Check it out below. The earnings table have multiple rows for a single campaign id.  So I don't think simply joining two tables would work. Unless if you still think otherwise, then it would be great if you can show it.

$find_campaign = $db->prepare("SELECT * FROM campaigns WHERE status = :status ORDER BY campaign_id DESC LIMIT 10");
$find_campaign->bindValue(':status', 1);
$find_campaign->execute();
$result_find_campaign = $find_campaign->fetchAll(PDO::FETCH_ASSOC);
if(count($result_find_campaign) > 0) {
  foreach($result_find_campaign as $row) {
    $campaign_id           =	trim($row['campaign_id']);
    $campaign_goal          =	trim($row['campaign_goal']);

    $get_raised = $db->prepare("SELECT deposit FROM earnings WHERE campaign_id = :campaign_id");
    $get_raised->bindParam(':campaign_id', $campaign_id);
    $get_raised->execute();
    $result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_raised) > 0) {
      $campaign_raised = 0;
      foreach($result_raised as $key=>$row) {
        $campaign_raised += trim($row['deposit']);
      }
    } else {
      $campaign_raised = 0;
    }

    //show campaign html here
  }
}

[mac_gyver]

you would add SUM(deposit) AS total (the AS keyword is optional) to the SELECT term, add GROUP BY campaign_id after the WHERE status ... term, and use ORDER BY total DESC

 

btw - any trimming of the data should have occurred before it was inserted/updated, not when it is retrieved and unless you are dynamically inputting the status value, you might as well just put a literal 1 in the query or create a VIEW that only references data with status = 1.

Edited April 25, 2018 by mac_gyver

[imgrooot]

you would add SUM(deposit) AS total (the AS keyword is optional) to the SELECT term, add GROUP BY campaign_id after the WHERE status ... term, and use ORDER BY total DESC

 

btw - any trimming of the data should have occurred before it was inserted/updated, not when it is retrieved and unless you are dynamically inputting the status value, you might as well just put a literal 1 in the query or create a VIEW that only references data with status = 1.

 

I kind of understand what you are saying but I'm a little lost on how to integrate it into my code above. I have two queries as you can see. Can you please modify them according to your edit? That would really help.

 

And the "trim" part is a habit. I know I shouldn't use it.

Edited April 26, 2018 by imgrooot

[mac_gyver]

the following is the SELECT query syntax definition, with the relevant parts in red -
 

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [sTRAIGHT_JOIN]
      [sql_SMALL_RESULT] [sql_BIG_RESULT] [sql_BUFFER_RESULT]
      [sql_CACHE | SQL_NO_CACHE] [sql_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [iNTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

 

the FROM table_references part is where any JOIN tables and join conditions go. JOINs (there are a handful of types, which you can research on the web or in the mysql database documentation to find out about) are used to get data from related tables or even within the same table. the two tables you have are related through the campaign_id columns. if you only want results that have entries in both tables (you can join as many tables as needed), you would use just a JOIN. if you want results from the 1st table, regardless of any entries in the 2nd table, you would use a LEFT JOIN (this would correspond to having a campaign without any earnings row(s)).

 

i recommend that you copy the relevant parts from the syntax definition above and try to fill in the information based on your tables. one thing that will help simplify the query is to use alias names for the tables. you can use alias names of 'c' for the campaigns table, and 'e' for the earnings table. the alias names are defined when you list the tables names in the FROM table_references part of the query. any place you reference a column in the query, use the table_alias_name.column (even when the table part is not necessary.) this will serve to help document what the query is trying to accomplish and force you to reference the columns from the correct table (important if you have same name columns in different tables with different meanings.)

 

to get you started, here is the FROM table_references part for your tables using a LEFT JOIN - 

FROM campaigns AS c LEFT JOIN earnings AS e ON c.campaign_id = e.campaign_id

note: the AS keyword is optional (once you understand aliases, you can leave the AS out). when you have the same name columns in both tables and you are only matching column values, with no other conditions, instead of the ON keyword syntax shown, you can use USING(campaign_id)

Edited April 26, 2018 by mac_gyver

[imgrooot]

the following is the SELECT query syntax definition, with the relevant parts in red -

 

 

the FROM table_references part is where any JOIN tables and join conditions go. JOINs (there are a handful of types, which you can research on the web or in the mysql database documentation to find out about) are used to get data from related tables or even within the same table. the two tables you have are related through the campaign_id columns. if you only want results that have entries in both tables (you can join as many tables as needed), you would use just a JOIN. if you want results from the 1st table, regardless of any entries in the 2nd table, you would use a LEFT JOIN (this would correspond to having a campaign without any earnings row(s)).

 

i recommend that you copy the relevant parts from the syntax definition above and try to fill in the information based on your tables. one thing that will help simplify the query is to use alias names for the tables. you can use alias names of 'c' for the campaigns table, and 'e' for the earnings table. the alias names are defined when you list the tables names in the FROM table_references part of the query. any place you reference a column in the query, use the table_alias_name.column (even when the table part is not necessary.) this will serve to help document what the query is trying to accomplish and force you to reference the columns from the correct table (important if you have same name columns in different tables with different meanings.)

 

to get you started, here is the FROM table_references part for your tables using a LEFT JOIN - 

FROM campaigns AS c LEFT JOIN earnings AS e ON c.campaign_id = e.campaign_id

note: the AS keyword is optional (once you understand aliases, you can leave the AS out). when you have the same name columns in both tables and you are only matching column values, with no other conditions, instead of the ON keyword syntax shown, you can use USING(campaign_id)

 

Based on what you wrote, here is my new query.  Seems to work. Need to do more testing. What you think?

$get_raised = $db->prepare("SELECT SUM(deposit) AS total, campaigns.campaign_id FROM campaigns
LEFT JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id
WHERE campaigns.campaign_id > :campaign_id GROUP BY campaigns.campaign_id ORDER BY total DESC");
$get_raised->bindValue(':campaign_id', 0);
$get_raised->execute();
$result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC);
if(count($result_raised) > 0) {
  foreach($result_raised as $row) {
    $campaign_id = trim($row['campaign_id']);
    $campaign_raised = trim($row['total']);

    ?>
    <div>
      <?php echo $campaign_id; ?><br>
      $<?php echo $campaign_raised; ?>
    </div>
    <br>
    <?php
  }
} else {
  echo 'no';
}

Edited April 26, 2018 by imgrooot