Avoid multiple MySql queries using php

Question

I'm trying to select both the total number and the limited number of products from the database.

Example:

$result = $database->query("SELECT* FROM products WHERE type = $category limit $start,$per_page");

$all_data = $database->query("SELECT* FROM products WHERE type = $category");

However, when I run this I'm getting mysql error. Is it possible to get the data I need without using multiple queries.

This is mysql error I'm getting;

Database failed...You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2,2' at line 1

Answer 1

If I understand you correctly, you're fetching the entire set of products in your category in the second query, but fetching just one page's worth in the first query (e.g., items 10 through 19). I would just fetch all the items with the second query, then load the rows into a PHP array and use array_slice() to grab the segment of the array you need for the current page.

EDIT: As others have said, the actual MySQL error may be the lack of the space between SELECT and *, but you can also do what you're trying to do without hitting the database twice.

Answer 2

If you just need the counts, then use:

SELECT count(*)
FROM products
WHERE type = '$category' limit $start,$per_page");

SELECT count(*)
FROM products
WHERE type = '$category';

Answer 3

The error is due to the use of negative numbers in limit clause. Snippet from MySQL documentation on Select syntax :

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

So the resolution to that error would be to use prepared statements if you really need negative limits as also asked by @James in one of his comments on your question.

Note that select* does not produce any errors but certainly does confuse!

Answer 4

You create a procedure then you call this procedure. I hope it work for you.

CREATE PROCEDURE `test_proc`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare name1 TEXT;
declare id1 TEXT;
select name,id into name1,id1 from my_tbl WHERE name='sam';
select * from my_tbl;
select name1,id1;
END

You can call this single call store procedure.