gpt4 book ai didi

php - 在选择查询中使用变量

转载 作者:行者123 更新时间:2023-11-29 06:29:36 26 4
gpt4 key购买 nike

问题
我正在尝试创建一个网站,用户可以在其中发布帖子,然后他们的 friend 可以看到这些帖子。类似于 facebook, twitter 等
我已经到了用户可以成为 friend 并且他们可以发布内容的地步。但是,我无法将显示的帖子限制为仅显示用户 friend 的帖子。目前每个用户都可以看到每个帖子。
我在 MVC 架构中使用 PDO。

数据库结构

TABLE `friends` (
`friendship_id` int(11) NOT NULL AUTO_INCREMENT,
`user_one` int(11) NOT NULL,
`user_two` int(11) NOT NULL,
PRIMARY KEY (`friendship_id`),
FOREIGN KEY (user_one) REFERENCES users(user_id),
FOREIGN KEY (user_two) REFERENCES users(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


在上表中,根据谁向谁发送了好友请求,“user_one”可以是我自己或我的 friend ,或者“user_two”可以是我自己或我的 friend 。

TABLE `posts` (
`post_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`post_text` text NOT NULL,
`user_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


模型

    /**
* Get all posts from the suer's friends
* @return array an array with several objects (the posts)
*/
public static function getAllPosts()
{
$my_id = Session::get('user_id');

$database = DatabaseFactory::getFactory()->getConnection();

// get all of the user's friends
$friendsQuery = $database->prepare("
SELECT user_one FROM friends WHERE user_two = '$my_id';
SELECT user_two FROM friends WHERE user_one = '$my_id';
");

$friendsQuery->execute();
$friends = $friendsQuery->fetchAll();
$friendsQuery->closeCursor();

$query = $database->prepare("SELECT * FROM posts WHERE user_id = '$my_id' OR user_id = '$friends'");
$query->execute(array());

return $query->fetchAll();
}


在上面的代码中,我的问题在于 $friends 变量。如果我用我 friend 的 user_id 手动替换它,它会按预期工作。
例如:

"SELECT * FROM posts WHERE user_id = '$my_id' OR user_id = '1'"; 

Controller

    /**
* Gets all posts (of the user and his friends).
*/
public function index()
{
$this->View->render('post/index', array(
'posts' => PostModel::getAllPosts()
));
}

View

       <?php if ($this->posts) { ?>
<?php foreach($this->posts as $key => $value) { ?>
<p><?= htmlentities($value->post_text); ?></p>
<?php } ?>
<?php } ?>


总结
我无法创建一个 sql 查询,它只选择由我自己或我的 friend 发布的帖子,而不选择任何其他帖子。

如果有人能帮我解决这个问题,我将不胜感激!




更新
我的模型现在看起来像这样:

    /**
* Get all posts from the suer's friends
* @return array an array with several objects (the posts)
*/
public static function getAllPosts()
{
$my_id = Session::get('user_id');

$database = DatabaseFactory::getFactory()->getConnection();

// get all of the user's friends
$friendsQuery = $database->prepare("
SELECT user_one FROM friends WHERE user_two = '$my_id';
SELECT user_two FROM friends WHERE user_one = '$my_id';
");

$friendsQuery->execute();
$friends = $friendsQuery->fetchAll();
print_r($friends);
$friendsQuery->closeCursor();

foreach($friends as $friend)
{
$friend_ids[$friend->key] = $friend->val;
}
print_r($friend_ids);
$friendsSQL = implode(',',$friend_ids);

$query = $database->prepare("SELECT * FROM posts WHERE user_id = '$my_id' OR user_id IN('$friendsSQL')");
$query->execute();

return $query->fetchAll();
}


这是我的输出:

Array ( [0] => stdClass Object ( [user_one] => 1 ) )

Notice: Undefined property: stdClass::$key

Notice: Undefined property: stdClass::$val

Array ( [] => )

最佳答案

您可以将所有 friends_id 添加到一个数组中,然后使用此代码。

//$friends is an array.
$friendsSQL = implode(',',$friends);
$query = $database->prepare("SELECT * FROM posts WHERE user_id = '$my_id' OR user_id IN($friendsSQL)");

关于php - 在选择查询中使用变量,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/28742764/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com