PHP SQL how i can get total number of rows when using CASE and table JOIN

Question

After googling I founded that SQL_CALC_FOUND_ROWS can result the total number of rows available in table before applying limit.

But I have very complex sql query with php when searching for some text in database and also want to know total available result in database.

SELECT    i.name 'title',i.add_time 'time',round(i.price) 'price',round(i.new_price) 'new_price',
          s.store_address 'address', s.name 'name'
FROM      store_items i,stores s
WHERE     i.store_id = s.store_id 
AND       (i.name like '%samsung glaxy%' AND 
          i.name like '%samsung%' AND i.name like '%galaxy%' 
          OR i.name like '%samsung%' OR i.name like '%galaxy%')
          ORDER BY
          CASE WHEN i.name like '%%'
          AND i.name like '%samsung%' AND i.name like '%galaxy%' THEN 1
          WHEN i.name like '%samsung%' OR i.name like '%galaxy%' THEN 2
          END,
          i.price ASC
          LIMIT 0,25"

How it can be possible to get total number of available rows by matching the following results.

Is it can be possible by using SQL_CALC_FOUND_ROWS sql function. or some other way is available in PHP.

do you mean the count of what you have above for your exact query (up to 25) ? — Drew, Jul 19 '16 at 17:04
I want to count all matched rows in my database but I am limiting 25 rows to show data and also want to show the user how much result are available in database, I am using `pagination` — Syed Aqeel, Jul 19 '16 at 17:15
I want the total rows returned from my query before using `LIMIT` — Syed Aqeel, Jul 19 '16 at 17:17

score 2 · Accepted Answer · answered Jul 19 '16 at 17:22

2

I will present the below. Hopefully you can find something helpful. Your query is no different, just bigger. Look into found_rows() perhaps. Or a derived table like xDerived.

Also, the Obligatory Reading of the Percona article.

create table n1
(   thing int not null
);
insert n1(thing) values 
(1),(3),(1),(1),(17),(1),(17),(11);
-- 8 rows inserted

select SQL_CALC_FOUND_ROWS thing from n1 where thing<12 order by thing limit 0,2;
select found_rows() as foundRows;
+-----------+
| foundRows |
+-----------+
|         6 |
+-----------+

select count(*) as theCount 
from 
(   select thing from n1 where thing<12 order by thing limit 0,2 
) xDerived; 
+----------+
| theCount |
+----------+
|        2 |
+----------+

answered Jul 19 '16 at 17:22

Drew

24,851
10
43
78

I want to use only one query which I showed above and don't want to use it twice. – Syed Aqeel Jul 19 '16 at 17:27
I want I want I want. Not much more I can give you. – Drew Jul 19 '16 at 17:28
Your case stmt is irrelevant, and the query is not complex. The issue is wanting too much from a programming environment that does not please out of the box. A stored proc needs to be written with an `OUT` parameter. Then about after an hour or more of debugging where the guy doesn't know how to do that, it may work for him. But we are free servants here. So, well, I guess tinker away or make an extra call or two. This topic is well known by those who have struggled with it; I estimate probably 10M people worldwide. – Drew Jul 19 '16 at 17:55
so then should I use another call to database for count? – Syed Aqeel Jul 19 '16 at 18:01
And I wouldn't dwell too much on making one call. Because the performance of your query is going to be awful anyway with large data and a `LIKE`. People use Full text search and [Junction Tables](http://stackoverflow.com/a/32620163) who seek performance and the use of indexes. – Drew Jul 19 '16 at 18:03
I would use another call. Some people would say why bother knowing the count of the max set, just use what you get with pagination until the last requestion is – Drew Jul 19 '16 at 18:04
I need to head out to help someone. Good luck my friend. – Drew Jul 19 '16 at 18:05
Thank you very much. You helped me a lot and gave me your importent time – Syed Aqeel Jul 19 '16 at 18:08

PHP SQL how i can get total number of rows when using CASE and table JOIN

1 Answers1