php - 如何在 PHP 中使用 PDO 将连接的结果作为两个数组获取
问题描述
我目前正在获取一个连接查询以获取此输出：
Array
(
[gp_id] => 103
[pid] => 0
[author_gp] => aboutthecreator
[gname] => MEP news
[ty1] => 0
[tit1] => 2
[dat1] => <div>Etiam iaculis nunc ac metus. Praesent egestas tristique nibh.</div>
<div> </div>
<div>Fusce ac felis sit amet ligula !qwerty pharetra condimentum. Integer ante arcu, accumsan a, consectetuer eget, posuere ut, mauris.</div>
<div> </div>
<div>Proin viverra, ligula sit amet ultrices semper, ligula arcu tristique sapien !bart, a accumsan nisi mauris ac eros. Sed in libero ut nibh placerat accumsan.</div>
<div> </div>
<div>Phasellus leo dolor, tempus non, auctor et, hendrerit quis, nisi. Sed a libero. !qwerty</div>
[pdate] => 2019-12-19 22:28:04
[group_id] => 25
[author_id] => 142
[up1] => 381
[update_body] => Lorem ipsum dolor sit amet,consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Lorem ipsum dolor sit amet, <a href="home.php?u=shan2batman">@shan2batman</a><br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam voluptua. !qwerty Lorem ipsum dolor sit amet, consetetur sadipscing elitr, Lorem ipsum dolor sit amet, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, consetetur sadipscing elitr, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. no sea takimata sanctus est Lorem ipsum dolor sit amet. <br /><br />Lorem ipsum dolor sit amet,sed diam voluptua. Lorem ipsum dolor sit amet, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, consetetur sadipscing elitr, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam voluptua. Stet clita kasd gubergren, Lorem ipsum dolor sit amet, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, <br /><br />Lorem ipsum dolor sit amet,At vero eos et accusam et justo duo dolores et ea rebum. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, sed diam voluptua. Stet clita kasd gubergren, <br /><br />
[time] => 2018-11-10 22:58:36
[tit2] => qwertyu
[account_name] => aboutthecreator
[author] => aboutthecreator
[ty2] => a
[dat2] =>
)
如何将其更改为：
Array
(
[gp_id] => 103
[pid] => 0
[author_gp] => aboutthecreator
[gname] => MEP news
[ty1] => 0
[tit1] => 2
[dat1] => <div>Etiam iaculis nunc ac metus. Praesent egestas tristique nibh.</div>
<div> </div>
<div>Fusce ac felis sit amet ligula !qwerty pharetra condimentum. Integer ante arcu, accumsan a, consectetuer eget, posuere ut, mauris.</div>
<div> </div>
<div>Proin viverra, ligula sit amet ultrices semper, ligula arcu tristique sapien !bart, a accumsan nisi mauris ac eros. Sed in libero ut nibh placerat accumsan.</div>
<div> </div>
<div>Phasellus leo dolor, tempus non, auctor et, hendrerit quis, nisi. Sed a libero. !qwerty</div>
[pdate] => 2019-12-19 22:28:04
[group_id] => 25
[author_id] => 142
)
array( [up1] => 381
[update_body] => Lorem ipsum dolor sit amet,consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Lorem ipsum dolor sit amet, <a href="home.php?u=shan2batman">@shan2batman</a><br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam voluptua. !qwerty Lorem ipsum dolor sit amet, consetetur sadipscing elitr, Lorem ipsum dolor sit amet, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, consetetur sadipscing elitr, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. no sea takimata sanctus est Lorem ipsum dolor sit amet. <br /><br />Lorem ipsum dolor sit amet,sed diam voluptua. Lorem ipsum dolor sit amet, sed diam voluptua. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. Lorem ipsum dolor sit amet, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, Stet clita kasd gubergren, <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. sed diam voluptua. <br /><br />Lorem ipsum dolor sit amet,consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam voluptua. consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, At vero eos et accusam et justo duo dolores et ea rebum. At vero eos et accusam et justo duo dolores et ea rebum. no sea takimata sanctus est Lorem ipsum dolor sit amet. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, consetetur sadipscing elitr, <br /><br />Lorem ipsum dolor sit amet,Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam voluptua. Stet clita kasd gubergren, Lorem ipsum dolor sit amet, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, <br /><br />Lorem ipsum dolor sit amet,At vero eos et accusam et justo duo dolores et ea rebum. sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, At vero eos et accusam et justo duo dolores et ea rebum. consetetur sadipscing elitr, no sea takimata sanctus est Lorem ipsum dolor sit amet. Lorem ipsum dolor sit amet, Lorem ipsum dolor sit amet, sed diam voluptua. Stet clita kasd gubergren, <br /><br />
[time] => 2018-11-10 22:58:36
[tit2] => qwertyu
[account_name] => aboutthecreator
[author] => aboutthecreator
[ty2] => a
[dat2] =>
)
在使用 PDO 的 PHP 中，目前我有以下类来获取上述输出如何更改它以使用 PHP PDO 获取以下输出：
public function totalUpdates($friend,$session,$var_id, $g, $load)
{
try{
$sql2="select distinct g.gp_id ,g.pid,g.author_gp,g.gname,g.type as ty1,g.title as tit1,g.data as dat1,g.pdate,g.group_id,g.author_id,"
. "up.update_id as up1,up.update_body,up.time,up.title as tit2,up.account_name,up.author,up.type as ty2,up.data as dat2 "
. "from group_posts as g "
. "join updates as up on g.author_id=up.user_id_u"
. " where "
. "g.gname=:g "
. "and up.update_id not in(:update_id_all) and up.author in(:friend, :session) order by time,pdate desc limit $load,5";
$stmth= $this->_db->prepare($sql2);//Check here syntax of $db
$stmth->bindValue(":friend",$friend);
$stmth->bindValue(":session",$session);
$stmth->bindValue(":update_id_all",$var_id);
$stmth->bindValue(":g",$g);
$stmth->execute();
return $stmth->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $ei){
echo $ei->getMessage();
}
}
如果有人知道怎么做，请告诉我。（我不是 PHP 或 MYSQL 专家）
更新：-
遵循@adams 的建议，我创建了一个存储过程，但问题是现在我不知道如何通过 php 类函数获取值。
如果我在 phpmyadmin 中运行它，我会得到以下结果（点击图片放大）：
CALL timeline('shan2batman','aboutthecreator', @updates, @group_posts)
现在这是我为获取结果而编写的 php 类函数，但在遵循此示例时不是
public function totalProcedures($friend_name,$session_id)
{
/*
*query to fetch stored procedure
*/
try
{
//executing the stored procedure
$sql_sp="CALL timeline (:friend, :session,@updates, @group_posts)";
$stmt_sp= $this->_db->prepare($sql_sp);
$stmt_sp->bindValue(":friend",$friend_name);
$stmt_sp->bindValue(":session",$session_id);
$stmt_sp->execute();
$rows=$stmt_sp->fetch(PDO::FETCH_ASSOC);
$stmt_sp->closeCursor(); // closing the stored procedure
//trying to get values from OUT parameters.
$stmt_sp_2=$this->_db->prepare("select @updates ,@group_posts");
$stmt_sp_2->execute();
return var_dump( $stmt_sp_2->fetch(PDO::FETCH_ASSOC));
}
catch (PDOException $ei)
{
echo $ei->getMessage();
}
}
实例化类：
$totalProcedures=$project->totalProcedures($imp_id,$session_uname);
UPDATE2： - 在帮助解决问题后，我终于能够获得第一个表的结果，但不能获得第二个表的结果。我知道我错过了一些东西，但无法弄清楚。任何帮助表示赞赏
这是仅获取更新值的类函数需要获取 group_posts 值如何在 pdo 中执行（phpmyadmin 产生两个结果）
public function totalProcedures($friend_name, $session_id)
{
/*
* query to fetch stored procedure
*/
try {
//executing the stored procedure
$sql_sp = "CALL timeline (:friend,:session)";
$stmt_sp = $this->_db->prepare($sql_sp);
$stmt_sp->bindValue(":friend",$friend_name);
$stmt_sp->bindValue(":session",$session_id);
$stmt_sp->execute();
do {
try {
$rows = $stmt_sp->fetchAll(PDO::FETCH_ASSOC);
if ($rows) {
return $rows;
} else {
echo die().'no data try rows';
}
} catch (PDOException $exc) {
echo $exc->getMessage();
}
} while ($stmt_sp->nextRowset());
} catch (PDOException $ei) {
echo $ei->getMessage();
}
}
UPDATE2:-在参考了这个stackoverflow页面后，我将代码更改为：
public function totalProcedures($friend_name, $session_id) {
/*
* query to fetch stored procedure
*/
try {
//executing the stored procedure
$sql_sp =
"CALL timeline (:friend,:session)"
;
$stmt_sp = $this->_db->prepare($sql_sp);
$stmt_sp->bindValue(":friend",$friend_name);
$stmt_sp->bindValue(":session",$session_id);
$stmt_sp->execute();
$row=$stmt_sp->nextRowset();
do {
try {
$rows = $stmt_sp->fetchAll(PDO::FETCH_ASSOC);
if ($rows) {
return $rows;
} else {
echo die().'no data try rows';
}
} catch (PDOException $exc) {
echo $exc->getMessage();
}
} while ($stmt_sp->nextRowset());
} catch (PDOException $ei) {
echo $ei->getMessage();
}
}
现在仍然无法获得结果，即使更新结果也没有出现。有人可以指出错误在哪里。Ajax 带有 200 条消息，但现在没有结果。
解决方案
您可以使用存储过程并在存储过程中运行 2 个选择查询以返回多个数组。在这里阅读更多
推荐阅读
- c++ - 使用 basic_ofstream 写入字节数据序列
- python - Tweepy 流式传输错误，从用户目标中删除推文后收到通知错误“id”
- c# - 将分隔字符串数组转换为 ValueTuple 列表
- javascript - Discord guilds.join OAuth2 in JS
- css - 修改css以免改变文字颜色
- vuforia - 模型目标 Vuforia Studio
- c# - 识别图像不属于其他 2 个预测模式 ML.NET
- entity-framework - Dotnet Core - 实体框架 - 在这种情况下，空大括号是什么意思，结果是什么
- ios - iOS 标签未更新
- kubernetes - 如何在 K3s 上使用 Traefik 将端口 80 转发到服务端口 3000？