ICode9

精准搜索请尝试: 精确搜索
首页?>?数据库> 文章详细

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

2019-10-03 11:15:41??阅读:568??来源: 互联网

标签:php?mysql


我有一张桌子,我正在阅读它的n%记录.为此,我准备了一个查询:

SET @rows := (SELECT COUNT(*)*0.5 FROM trending);
PREPARE STMT FROM 'SELECT * FROM trending ORDER BY Count LIMIT ?';
EXECUTE  STMT USING @rows;

我得到的输出是:

SET @rows := (SELECT COUNT(*)*0.5 FROM trending);# MySQL returned an empty result set (i.e. zero rows). PREPARE STMT FROM 'SELECT * FROM trending ORDER BY Count LIMIT ?';# MySQL returned an empty result set (i.e. zero rows). EXECUTE STMT USING @rows;# Rows: 8

我怎样才能从表中获取记录?

解决方法:

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.

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 = <<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-querymysqli.store-result.



标签:php,mysql

专注分享技术,共同学习,共同进步。侵权联系[admin#icode9.com]

Copyright (C)ICode9.com, All Rights Reserved.

ICode9版权所有