Preg_replace()除去查询末尾的所有内容 [英] Preg_replace() removing all but the end of query

问题描述

在一切之前，为我的英语不好对不起.

Before everything, sorry for my bad english.

我有这个查询:

SELECT t1.*,(SELECT COUNT(*) FROM table_a t2 WHERE t1.id=t2.id_c AND t2.status=1) AS aula 
FROM table_c t1 
WHERE t1.status=1 
AND t1.id IN (SELECT t3.id_c FROM table_cu t3 WHERE t3.id_c=t1.id AND t3.id_u=1) 
AND t1.id IN (SELECT t4.id_c FROM table_cp t4 WHERE t4.id_c=t1.id AND t4.id_p=3) 
LIMIT 0,25

此查询向我返回了一组结果，到现在为止都没有问题，但是我有一个具有非常基本过程的分页类:它计算我得到的条目数，然后将这些条目分成几部分，然后使用该值分页的.

This query returns me a set of results, no problems until now, but I have a pagination class with a very basic process: It counts how many entries I got, then it breaks the entries into pieces, then with this values I paginate.

问题出在分页类"中的此方法内部，这是代码:

The issue is inside this method in the 'pagination class', here's the code:

function total_entries()
{

    $db = new db(DB_USER, DB_PASS, DB_NAME, DB_HOST);

    $qtdEntries = 0;

    $queryQtd = $this->sql;
    $queryQtd = preg_replace("/SELECT (.*) FROM /sei", "'SELECT COUNT(*) as qtd FROM '", $queryQtd);
    $queryQtd = preg_replace("/ORDER BY (.+)/", "", $queryQtd);
    $queryQtd = preg_replace("/LIMIT (.+)/", "", $queryQtd);

    $resultAll = $db->get_row($queryQtd); //Gets the result, in this case, it returns the number of entries

    if($resultAll){
        $qtdEntries = $resultAll->entries; //Number of entries
    }

    return $qtdEntries;
}

基本上，当我运行此方法时，其内部的查询返回如下:

Basically when I run this method, the query inside of it returns like this:

SELECT COUNT(*) as qtd FROM table_cp t4 WHERE t4.id_c=t1.id AND t4.id_p=3)

何时应返回如下内容:

SELECT COUNT(*) as qtd FROM table_c t1 WHERE t1.status=1

有什么想法可以解决此问题吗?

Any ideas how to correct this issue?

推荐答案

解决此问题的更好方法是使用MySQLs内置功能:

A better approach to this problem is to use MySQLs builtin functionality:

您只需在 SQL_CALC_FOUND_ROWS >关键字与功能 FOUND_ROWS :

You add simply SQL_CALC_FOUND_ROWS after the SELECT key word in combination with the function FOUND_ROWS:

SELECT SQL_CALC_FOUND_ROWS t1.*,(SELECT COUNT(*) FROM table_a t2 WHERE t1.id=t2.id_c AND t2.status=1) AS aula 
FROM table_c t1 
WHERE t1.status=1 
AND t1.id IN (SELECT t3.id_c FROM table_cu t3 WHERE t3.id_c=t1.id AND t3.id_u=1) 
AND t1.id IN (SELECT t4.id_c FROM table_cp t4 WHERE t4.id_c=t1.id AND t4.id_p=3) 
LIMIT 0,25

要获取没有LIMIT子句的行数，现在可以使用语句

To obtain the count of rows if there were no LIMIT clause, you can now use the statement

SELECT FOUND_ROWS();

作为下一个查询.

正则表达式方法的问题是正确获取正则表达式.匹配通常是贪婪的，它将尽可能匹配.在这种情况下，它将恰好与WHERE子句的最后一个条件匹配，因为此条件使用带有FROM关键字的子选择.因此，模式

The problem with the regexp approach is to get the regexp right. Matching is usually greedy, it will match as much as possible. In this case it will just match right to the last condition of the WHERE clause because this condition uses a subselect with the FROM key word. Because of that the pattern

SELECT (.*) FROM

在

$queryQtd = preg_replace("/SELECT (.*) FROM /sei", "'SELECT COUNT(*) as qtd FROM '", $queryQtd);

与SELECT的字段列表(也包含带有FROM的子选择)的匹配项相比，匹配得多.

matches much more than the field list of the SELECT (that contains a subselect with FROM too).

这篇关于Preg_replace()除去查询末尾的所有内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！