使用php避免多个MySql查询

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.

但是,当我运行这个时,我收到mysql错误。是否有可能在不使用多个查询的情况下获取所需的数据。

This is mysql error I'm getting;

这是我得到的mysql错误;

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

数据库失败...您的SQL语法中有错误;检查与MySQL服务器版本对应的手册,以便在第1行的“-2,2”附近使用正确的语法

4 个解决方案

#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.

如果我理解正确,您将在第二个查询中获取您的类别中的整个产品集,但在第一个查询中仅获取一个页面的价值(例如,项目10到19)。我只是用第二个查询获取所有项目,然后将行加载到PHP数组中并使用array_slice()来获取当前页面所需的数组段。

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.

编辑:正如其他人所说,实际的MySQL错误可能是缺少SELECT和*之间的空间,但你也可以做你想做的事情,而不必两次击中数据库。

#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';

#3

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

该错误是由于在limit子句中使用负数。关于选择语法的MySQL文档的片段:

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).

LIMIT子句可用于约束SELECT语句返回的行数。 LIMIT需要一个或两个数字参数,它们都必须是非负整数常量(使用预准备语句时除外)。

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.

因此,如果您确实需要负面限制,那么该错误的解决方案将是使用预备语句,正如@James在您对您的问题的评论中所提出的那样。

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

请注意,选择*不会产生任何错误,但肯定会混淆!

#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.

您可以调用此单个调用存储过程。

#1