1

So I have a MySQL query that I want to count. Originally I use wordpress' get_results("") but I also need to count the total in a different query, because in my original query I LIMIT the results to create pages.

So this is my original query, does anyone know how I can count this?

SELECT wpp.ID, post_title, wp_terms.name AS category, wp_terms.slug AS slug, supplier_company, 
    GROUP_CONCAT(wp_terms.slug SEPARATOR ', ') AS allslug,
    GROUP_CONCAT(wp_terms.name SEPARATOR ', ') AS allcatname
    FROM wp_posts AS wpp
    LEFT JOIN wp_term_relationships ON wpp.ID = object_id
    LEFT JOIN wp_terms ON term_taxonomy_id = wp_terms.term_id
    LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID



    /* BASIC SEARCH on normal fields */
    WHERE post_type = 'post' 


    GROUP BY wpp.ID

    /* SEARCH on CONCAT FIELDS*/
    HAVING

    (post_title LIKE '%%'
    OR allcatname LIKE '%%'
    OR allslug LIKE '%%'
    OR supplier_company LIKE '%%')
    AND (allslug LIKE '%health-and-beauty%'
    ) AND (allslug LIKE '%%'
    ) AND 

    /* ADD EXTRA SEARCH TAGS: */
    /* Language tag */
     allslug LIKE '%english%' 
    /* ..... tag */

    /* AND allslug LIKE '%......... %' */
    ORDER BY post_date DESC
mesqueeb
  • 5,277
  • 5
  • 44
  • 77
  • You may want to have a look [here](http://stackoverflow.com/questions/12887266/get-total-number-of-rows-when-using-limit) – Joachim Isaksson Mar 17 '16 at 05:14
  • what count do you need? total count of output lines or count in each group? – Dylan Su Mar 17 '16 at 05:15
  • Dylan, I need the count of the outcome. Thanks Joachim. I will look! – mesqueeb Mar 17 '16 at 05:16
  • Joachim, I was able to get the value and return it through my AJAX callback, but the returned string now changed from `[{"a":1},{"b":2}]` to `{"0":{a:1},{b:2}},"count":1}` and without those straight brackets I cannot access the data like before!! What to do? – mesqueeb Mar 17 '16 at 06:11

1 Answers1

1

Just retrieve found_rows directly after your SELECT query.

select found_rows();
Dylan Su
  • 5,975
  • 1
  • 16
  • 25
  • thanks for your help. Sorry I marked it correct many years later. Your upvote on my question is much appreciated!! : D – mesqueeb Apr 13 '19 at 00:05