0

I'm trying to count the number of rows returned by a query, which includes Sum, Match, Against and Group By statements.

I've written an SQL statement that performs a query on a search index table and then groups the results.

I'm trying to count the results, so that I can set up pagination.

Whenever I've used the Count functionality before, it's been to count the rows in a table and not the results of a query.

I've also explored the SQL_CALC_FOUND_ROWS option with SELECT FOUND_ROWS() afterwards, but I'm not getting the results required.

Search Query...

SELECT *, SUM(MATCH(`Field1`, `Field2`, `Field3`, `Field4`) AGAINST('test' IN BOOLEAN MODE)) as score FROM `search_index` WHERE MATCH(`Field1`, `Field2`, `Field3`, `Field4`) AGAINST('test' IN BOOLEAN MODE) GROUP BY `Field1`, `Field2` ORDER BY score DESC;

Count Attempt 1...

SELECT COUNT(*), SUM(MATCH(`Field1`, `Field2`, `Field3`, `Field4`) AGAINST('test' IN BOOLEAN MODE)) as score FROM `search_index` WHERE MATCH(`Field1`, `Field2`, `Field3`, `Field4`) AGAINST('test' IN BOOLEAN MODE) GROUP BY `Field1`, `Field2`

Count Attempt 2... (Based on this post How can I count the numbers of rows that a mysql query returned?)

SELECT SQL_CALC_FOUND_ROWS *, SUM(MATCH(`Field1`, `Field2`, `Field3`, `Field4`) AGAINST('test' IN BOOLEAN MODE)) as score FROM `search_index` WHERE MATCH(`Field1`, `Field2`, `Field3`, `Field4`) AGAINST('test' IN BOOLEAN MODE) GROUP BY `Field1`, `Field2`;
SELECT FOUND_ROWS();

Attempt 1 - The result I was looking for was 89, but I was returned 89 rows with a count of 1 in each.

Attempt 2 - The result I was looking for was 89, but I was returned 1 row with a count of 1 in it.

Any advice would be greatly appreciated!

miken32
  • 42,008
  • 16
  • 111
  • 154
Kirk Johnston
  • 71
  • 8
  • Why `Field3`, `Field4` is not in the `GROUP BY`? – Eric May 10 '19 at 15:24
  • You've tagged this question php, but not shown any PHP code. Why are you not simply checking the number of rows in the result set? – miken32 May 10 '19 at 15:31
  • Sorry, I'd taken the field names out to try and make it less specific to myself. The first table is products with codes (field 1) and names (field 2). The 2nd table contains details relating to the product. Each product has about 5 different pieces of information, such as Description, Tech Spec etc. Field 3 is the type of info and Field 4 is the text content. I just want to group the products. – Kirk Johnston May 10 '19 at 15:32
  • @miken32 I'm trying to do this via PHP Code, but also resolve the issue without PHP first. My 2nd attempt had to be done via PHP with my current knowledge. Technically there's a php variable in my code :) Probably didn't warrant a tag though! – Kirk Johnston May 10 '19 at 15:34
  • Currently searching "checking the number of rows in the result set?" – Kirk Johnston May 10 '19 at 15:37
  • @mike32 searching for "checking the number of rows in the result set?" leads me to articles such as https://www.navicat.com/en/company/aboutus/blog/695-getting-row-counts-in-mysql-part-1 which seems to be what I've tried in attempt 1. – Kirk Johnston May 10 '19 at 15:40
  • 1
    OK, this seems worth a read... https://www.php.net/manual/en/mysqli-result.num-rows.php – Kirk Johnston May 10 '19 at 15:41
  • Think I've cracked it... Many thanks for sending me in the right direction @miken32 – Kirk Johnston May 10 '19 at 15:58
  • Glad to help, but I would advise using PDO instead of mysqli, in which case you'd be looking at https://php.net/manual/en/pdostatement.rowcount.php – miken32 May 10 '19 at 15:59
  • And don't forget to use prepared statements when you implement this, no matter which database API you choose. – miken32 May 10 '19 at 15:59

0 Answers0