时间戳

首页 > 解决方案 > MySQL query from PHP

php - MySQL query from PHP

问题描述

I need to make this query from PHP:

set @row:=-1;
SELECT * FROM `tablename`
INNER JOIN ( 
    SELECT `id` FROM ( 
        SELECT @row:=@row+1 AS rownum, `id` FROM ( 
            SELECT id FROM `tablename` ORDER BY `id` DESC 
        ) 
            AS sorted 
    ) as ranked WHERE rownum % 14 = 0 
) AS subset ON subset.id = `tablename`.id LIMIT 24;

Works fine in PHPMyAdmin, but I have an error for the "set@row=-1;". :(

Thanks in advance!

标签： phpmysqlmysqli

解决方案

您可能会尝试在单个语句中使用这些mysqli_query语句。这些是多个陈述；您需要在使用 PHP 代码时单独查询它们。

但是，您也可以使用以下方法将这两个查询转换为一个查询CROSS JOIN：

SELECT * FROM `tablename`
INNER JOIN ( 
    SELECT `id` FROM ( 
        SELECT @row:=@row+1 AS rownum, `id` FROM ( 
            SELECT id FROM `tablename` ORDER BY `id` DESC 
        ) AS sorted 
        CROSS JOIN (SELECT @row := -1) AS user_init_vars 
  /* shifted SET statement to the above line (remove the comment before using) */
    ) as ranked WHERE rownum % 14 = 0 
) AS subset ON subset.id = `tablename`.id LIMIT 24;

推荐阅读