Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
Can you post the mysql error which you are getting? – Danish Aug 12 at 2:32
1  
could be the lack of space between SELECT and * – Dave Aug 12 at 2:32
how do you execute those two queries? what error do you get? A single query is possible but it depends on what you want/need. One of your queries gets everything unlimited, the other limits, so it depends on if you need a limit or not for one thing and all data regardless for another. – James Aug 12 at 2:34
1  
do you need a negative limit? Also, an order by might be useful – James Aug 12 at 2:38
Getting both a limited set AND the complete set is impossible. The limited set is a subset, so by definition if you want the limited set, you won't get the complete set. You can get the complete set and filter the limited set yourself by just using the first $per_page results in your page. The error is because of you negative limit... – Nanne Aug 12 at 11:44

4 Answers

up vote 0 down vote accepted

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.

share|improve this answer
Thanks Cliff I was thinking about the same thing, but this seemed easier. I guess I'm gonna have to do it manually. – user1778459 Aug 12 at 2:34
1  
Hitting the database and hitting the filesystem are the two most costly things you can do in a web app, performance-wise, so it's always good to make fewer trips to the DB/file. Sometimes it takes more code, but your users will thank you. Good luck! – pessimisticinduction Aug 12 at 2:38
up vote 0 down vote

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';
share|improve this answer
type is an integer. – user1778459 Aug 12 at 2:37
up vote 0 down vote

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!

share|improve this answer
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.