php中的execute,php – 如何在mySQL中的EXECUTE语句之后得到语句的结果？

SET @rows := (SELECT COUNT(*)*0.5 FROM trending);

PREPARE STMT FROM 'SELECT * FROM trending order by somecolumn LIMIT ?';

EXECUTE STMT USING @rows;

在我的系统上工作正常.奇数行可以得到一个综合

编辑：

create table thing2

( id int auto_increment primary key,

theWhat varchar(40) not null,

`count` int not null

);

插入5行：

insert thing2(theWhat,`count`) values ('anchovies',6),('tomato',1),('cat',99),('mouse',8),('spoon',70);

SET @rows := (SELECT COUNT(*)*0.5 FROM thing2);

PREPARE STMT FROM 'SELECT * FROM thing2 LIMIT ?';

EXECUTE STMT USING @rows;

count might need to be in backticks (not on my system), if that is actually a column and you are doing

the following :

SET @rows := (SELECT COUNT(*)*0.5 FROM thing2);

PREPARE STMT FROM 'SELECT * FROM thing2 order by `count` LIMIT ?';

EXECUTE STMT USING @rows;

+----+-----------+-------+

| id | theWhat | count |

+----+-----------+-------+

| 2 | tomato | 1 |

| 1 | anchovies | 6 |

| 4 | mouse | 8 |

+----+-----------+-------+

SET @rows := (SELECT COUNT(*)*0.5 FROM thing2);

PREPARE STMT FROM 'SELECT * FROM thing2 order by theWhat LIMIT ?';

EXECUTE STMT USING @rows;

+----+-----------+-------+

| id | theWhat | count |

+----+-----------+-------+

| 1 | anchovies | 6 |

| 3 | cat | 99 |

| 4 | mouse | 8 |

+----+-----------+-------+

Revision1(显示PHP)

此修订是由于提到PHP.因此,这显示了一个多查询,使用从该查询的特定行给出的结果集.显示使用明确保留其值的@变量.并且为多查询本身设置了一个heredoc变量$theSql.

error_reporting(E_ALL);

//mysqli_report(MYSQLI_REPORT_ALL);

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

ini_set('display_errors', 1);

try {

$mysqli= new mysqli('localhost', 'dbusername', 'thePassword', 'theDbname'); // tweak accordingly

if ($mysqli->connect_error) {

die('Connect Error (' . $mysqli->connect_errno . ') '

. $mysqli->connect_error);

}

echo "I am connected and feel happy.
";

// Note the 3rd statment below (the EXECUTE) and the code below it that cares about output for the 3rd one only

$theSql = <<

SET @rows := (SELECT COUNT(*)*0.5 FROM thing2);

PREPARE stmt123 FROM 'SELECT * FROM thing2 LIMIT ?';

EXECUTE stmt123 USING @rows;

DEALLOCATE PREPARE stmt123;

SQL;

$shouldDebug=false; // change to true to see more debug info (such as the skipped multi-query results)

$theCounter=0;

$weCareAbout=3; // the 3rd line of the command: "EXECUTE stmt123 USING @rows; "

if ($mysqli->multi_query($theSql)) {

do { // Note this loop poached from http://php.net/manual/en/mysqli.multi-query.php

if ($shouldDebug) echo "1a.
";

$theCounter++;

// store first result set

if ($result = $mysqli->store_result()) {

if ($shouldDebug) echo "1b.
";

if ($theCounter==$weCareAbout) {

while ($row = $result->fetch_row()) {

echo $row[0]." ".$row[1]." ".$row[2]."
";

}

}

if ($shouldDebug) echo "1c.
";

$result->free();

if ($shouldDebug) echo "1d.
";

}

// print divider

if ($mysqli->more_results() && $shouldDebug) {

printf("-----------------\n");

}

if ($shouldDebug) "1e.
";

} while ($mysqli->next_result() && $mysqli->more_results());

// above line to avoid error: Strict Standards: mysqli::next_result(): There is no next result set. ...

// ...Please, call mysqli_more_results()/mysqli::more_results() to check whether to call ...

//

// the Manual page is not exactly clear on this. In fact, faulty.

// http://php.net/manual/en/mysqli.multi-query.php

}

$mysqli->close(); // just showing it, what the heck

} catch (mysqli_sql_exception $e) {

throw $e;

}

?>

浏览器输出：

I am connected and feel happy.

1 anchovies 6

2 tomato 1

3 cat 99

上面做的/有时间的长短是因为我们在该循环中基于传递的查询进行了4次.

请参见手册页面mysqli.multi-query和mysqli.store-result.