2

I am trying to find a way to select and count some rows in my database. I was checking the MySQL docs and this is what I came up with:

$query = "SELECT *, COUNT(*) as total WHERE IMAGE != '' AND CATEGORY = '$category' $limit";

But this outputs a single row when done... :(

Than I have tried:

$query = "SELECT * FROM PRODUCTS,
        (SELECT COUNT(*) as total FROM PRODUCTS WHERE IMAGE != '' AND CATEGORY = '$category') as x
        WHERE IMAGE != '' AND CATEGORY = '$category' $limit";

This works, however I don't really know if this will perform well, because of the two SELECT statements (lus I don't need 'as x', but without this the query fails).

Is there a better way? Thanks

Edmond Tamas
  • 3,148
  • 9
  • 44
  • 89
  • Can you provide some sample data and expected results from your query? – mba12 Jun 29 '16 at 15:34
  • 1
    It would be better if the code didn't appear to be vulnerable to SQL Injection. – spencer7593 Jun 29 '16 at 15:35
  • 1
    I guess you are looking for [SQL_CALC_FOUND_ROWS](http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows), see e.g. [this question](http://stackoverflow.com/questions/12887266/get-total-number-of-rows-when-using-limit) – Solarflare Jun 29 '16 at 16:41
  • 1
    Oblig [Percona article](https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/) reference. – Drew Jun 29 '16 at 22:52
  • thanks! Could someone please add the SQL_CALC_FOUND_ROWS related solution as an answer? – Edmond Tamas Jun 30 '16 at 04:10

1 Answers1

0

Another way is using sub-query but not sure about the performance should be executed only once since there is no correlation from outer table 

SELECT *, (select COUNT(*) from PRODUCTS) as total
WHERE IMAGE != '' 
AND CATEGORY = '$category' $limit
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172