请教一个SQL语句替换问题，在线等！！！！！

[kth007]

做一个分页的。
想把形如：

SELECT u.id,u.name FROM user as u ORDER BY u.id DESC LIMIT 0 10
SELECT u.id,u.name FROM user as u WHERE u.id=12 ORDER BY u.id DESC

替换成

SELECT COUNT(*) FROM user as u ORDER BY u.id DESC LIMIT 1
SELECT COUNT(*) FROM user as u WHERE u.id=12 ORDER BY u.id DESC LIMIT 1


也就是形式如

SELECT xxxxx FROM ***** WHERE ***** ORDER BY **** LIMIT XXXXX
SELECT xxxxx FROM ***** WHERE ***** ORDER BY ****

其中 XXXXX 部分进行替换。



PHP 怎么写正则。。。

[jianglin28290]

sprintf

[jianglin28290]

$sql = "SELECT %s FROM %s WHERE %s ORDER BY %s LIMIT %s";

sprintf($sql, "u.id,u.name", "user as u", "u.id", " 0 10")

[Dleno]

$str="SELECT u.id,u.name FROM user as u WHERE u.id=12 ORDER BY u.id DESC";
echo preg_replace("~(SELECT )(.*?)( FROM(.*?))~is","\\1count(*)\\3",$str);

[kth007]

[Quote=引用 2 楼 jianglin28290 的回复:]
$sql = "SELECT %s FROM %s WHERE %s ORDER BY %s LIMIT %s";

sprintf($sql, "u.id,u.name", "user as u", "u.id", " 0 10")
[/Quote]

u.id,u.name
和
LIMIT 0, 10

并不是不变的 只是句个例子。。

[kth007]

[Quote=引用 3 楼 dingsongtao 的回复:]
$str="SELECT u.id,u.name FROM user as u WHERE u.id=12 ORDER BY u.id DESC";
echo preg_replace("~(SELECT )(.*?)( FROM(.*?))~is","\\1count(*)\\3",$str);
[/Quote]

后面的 LIMIT 没有替换掉。。
完整的 SQL 原句可能有 LIMIT 也可能没有
那不管有没有 都替换成 LIMIT 1

[jianglin28290]

那你换一句呀，我只是做个例子

$sql = "SELECT %s FROM %s WHERE %s ORDER BY %s LIMIT %d";


sprintf($sql, "count(*）", "user as u", "u.id", 1);

[jlzan1314]

<?php

$str="SELECT u.id,u.name FROM user as u WHERE u.id=12 ORDER BY u.id DESC limit 1,10";
echo preg_replace("/(select ).+? (from .+? limit) [0-9,]+/i","\\1 count(*) \\2 1",$str);
?>

[kth007]

[Quote=引用 6 楼 jianglin28290 的回复:]
那你换一句呀，我只是做个例子

$sql = "SELECT %s FROM %s WHERE %s ORDER BY %s LIMIT %d";


sprintf($sql, "count(*）", "user as u", "u.id", 1);
[/Quote]

形式如
SELECT xxxxx FROM ***** WHERE ***** ORDER BY **** LIMIT XXXXX
SELECT xxxxx FROM ***** WHERE ***** ORDER BY ****

[kth007]

[Quote=引用 7 楼 jlzan1314 的回复:]
PHP code

<?php

$str="SELECT u.id,u.name FROM user as u WHERE u.id=12 ORDER BY u.id DESC limit 1,10";
echo preg_replace("/(select ).+? (from .+? limit) [0-9,]+/i","\\1 count(*) \\2 1",$str);
?>……
[/Quote]


非常感谢回复，只能够替换 带 LIMIT 的 如果不带LIMIT 应该怎么修改。

[Dleno]

$str="SELECT u.id,u.name FROM user as u WHERE u.id=12 ORDER BY u.id DESC limit 10";
echo preg_replace("/^(select ).+? (from .+?)(limit (\d{1,}(\,\d{1,})?))?$/i","\\1 count(*) \\2 limit 1;",$str);

[jlzan1314]

<?php

$str="SELECT u.id,u.name FROM user as u WHERE u.id=12 ORDER BY u.id DESC limit 1,10";
echo preg_replace("/^(select ).+ (from.+?)( limit [0-9\,]+){0,1}$/i","\\1 count(*) \\2 limit 1",$str);
echo "<br />";
$str="SELECT u.id,u.name FROM user as u WHERE u.id=12 ORDER BY u.id DESC";
echo preg_replace("/^(select ).+ (from.+?)( limit [0-9\,]+){0,1}$/i","\\1 count(*) \\2 limit 1",$str);
?>

[kth007]

[Quote=引用 10 楼 dingsongtao 的回复:]
PHP code

$str="SELECT u.id,u.name FROM user as u WHERE u.id=12 ORDER BY u.id DESC limit 10";
echo preg_replace("/^(select ).+? (from .+?)(limit (\d{1,}(\,\d{1,})?))?$/i","\\1 count(*) \\2 limit 1;……
[/Quote]

我修改了下 基本OK 了

$sql = preg_replace("/^(SELECT).+? (FROM .+?)(\s*LIMIT (.+)?)?$/i","\\1 COUNT(*) AS counter \\2 LIMIT 1", $sql);