Help with complex mysql query

Question

I have an existing mysql query that I need to add to and I'm not sure how to go about it.

Here is my current sql query.

SELECT tbl_brokerage_names.brokerage_id, tbl_brokerage_names.short_name,
    b.indication, b.max_indication
  FROM tbl_brokerage_names 
  LEFT JOIN (
    SELECT * FROM tbl_recommendation_brokerages
    WHERE recommendation_id = {$_GET['id']}
  ) b ON (tbl_brokerage_names.brokerage_id = b.brokerage_id) 
ORDER BY tbl_brokerage_names.short_name ASC

Here is the query that I need to work into the previous query.

 SELECT * , COUNT( * )
   FROM tbl_streetaccounts
   JOIN tbl_brokerage_names
 WHERE tbl_brokerage_names.brokerage_id = tbl_streetaccounts.brokerage_id

Basically I need to return a count, so I need to combine these two queries.

Bill Karwin · Accepted Answer · 2011-09-20 18:40:08Z

1

You should run these as two separate queries.

The COUNT(*) query will return a single row, so there's no way to "combine" it with the first query while preserving the multi-row result of the first query.

Also, when you SELECT *, COUNT(*) you will get columns from some arbitrary row.

By the way, you have a glaring SQL injection vulnerability. Don't interpolate $_GET parameters directly in your SQL query. Instead, coerce it to an integer:

<?php
$id = (int) $_GET['id'];
$sql = "SELECT ... WHERE recommendation_id = {$id}";

answered Sep 20, 2011 at 18:40

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Carl Weis Over a year ago

Is it possible if I change the COUNT(*) to COUNT(tbl_streetaccounts.brokerage_id)

Johan Over a year ago

@Bill, I don't approve of this solution, it breaks when working with strings. Why not use mysql_real_escape_string() and "SELECT ... WHERE recommendation_id = '$id'";. That is always safe, be it integer or string.

Bill Karwin Over a year ago

@Johan: in cases where the "id" column should be compared to an integer (which is practically always true), it's simpler and quicker to just coerce the value to (int). If the id column is a non-integer, one can use mysql_real_escape_string as you say.

Johan · Accepted Answer · 2011-09-20 20:01:18Z

Like @Bill said, you cannot get the count in every row without really weird syntax, but you can get an overall count using GROUP BY ... WITH ROLLUP.

e.g.:

<?php
$id = mysql_real_escape_string($_GET['id']); //works with anything, not just numbers
$query = "
  SELECT tbl_brokerage_names.brokerage_id
         , tbl_brokerage_names.short_name
         , b.indication
         , b.max_indication
         , count(*) as rowcount 
    FROM tbl_brokerage_names 
    LEFT JOIN (
      SELECT * FROM tbl_recommendation_brokerages
      WHERE recommendation_id = '$id'  //The single quotes are essential for safety!
    ) b ON (tbl_brokerage_names.brokerage_id = b.brokerage_id) 
  GROUP BY tbl_brokerage_names.brokerage_id WITH ROLLUP
  ORDER BY tbl_brokerage_names.short_name ASC
";

The GROUP BY .. WITH ROLLUP will add an extra line to the result with all NULL's for the non aggregated columns and a grand total count.

If you have any lines where rowcount > 0 then you need to add extra clauses from table b to the group by clause to prevent MySQL from hiding arbitrary rows.
Table tbl_brokerage_names is already fully defined because you are grouping by the primary key.

Collectives™ on Stack Overflow

Help with complex mysql query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest