php - 将mysql查询结果存储在数组中并在另一个查询中使用该数据

Question

数组和 foreach 与 SQL 查询结合使用时遇到一个小问题。

我有两 (2) 个表:users_followers 和表 user_activiy。

它们看起来像这样:Users_followers:

+-----+----------+-------------+
| id  | user_id  | follower_id |
+-----+----------+-------------+

用户事件:

+----+---------+---------+----------+----------+--------+
| id | user_id | post_id | activity | deleted  | status |
+----+---------+---------+----------+----------+--------+

我尝试首先从用户关注的 users_followers 中获取用户关注的内容，然后从 user_activity 中获取他们的“事件”。

    public static function getActivityFromUsers()
    {
        $database = DatabaseFactory::getFactory()->getConnection();

        $sql = "SELECT user_id, follower_since_timestamp FROM users_followers where follower_id = :user_id";
        $query = $database->prepare($sql);
        $query->execute(array(':user_id' => Session::get('user_id')));

        $_MYSQL_DATA = array();

                if ($query->rowCount() <= 0) {
                    echo 'Nothing';
                }

    foreach($query->fetchAll() as $follows) {
    $new_data = new stdClass;
    $new_data->user_id = $follows->user_id;
    $new_data->follower_since_timestamp = $follows->follower_since_timestamp;
    $_MYSQL_DATA[] = $new_data;

        /*  
        $follows =  new stdClass;
        $_MYSQL_DATA['user_id'] = $follows->user_id;
        $_MYSQL_DATA['follower_since_timestamp'] = $follows->follower_since_timestamp;
        */
}
        /* $i=0;
        foreach($_MYSQL_DATA as $_MYSQL_DATA){ 
            echo var_dump($_MYSQL_DATA[$i]).'<br>';
                $i++;
        }  */
        echo var_dump($_MYSQL_DATA[user_id]).'<br>';



        $sql = "SELECT activity 
                FROM user_activity WHERE user_id = :user_id AND activity_timestamp BETWEEN :follower_since_timestamp AND now()";
        $query = $database->prepare($sql);
        $query->execute(array(':user_id' => $_MYSQL_DATA['user_id'],
                       ':follower_since_timestamp' => $_MYSQL_DATA['follower_since_timestamp']));

        $user = $query->fetchAll();

        if ($query->rowCount() < 0) {
            return false;
            } else {         

            $all_users_profiles = array();
            foreach ($user as $user) {
            $all_users_profiles[$user->activity] = new stdClass();
            $all_users_profiles[$user->activity]->activity = $user->activity;
        }

        return $all_users_profiles;      
    }
    }

我正在尝试了解如何使用 $_MYSQL_DATA[user_id] 和 $_MYSQL_DATA[follower_since_timestamp]。

预期结果应该是:

1. 从users_followers(其中follower_id)获取所有ID和时间戳对应于session(id)
2. 根据第一个查询从 users_activity 获取所有数据。请注意Between 子句

(对代码中的 var_dumps 和其他故障排除感到抱歉)

Answer 1

我想我能解决这个问题:

    public static function getActivityFromUsers()
    {
        $database = DatabaseFactory::getFactory()->getConnection();

        $sql = "SELECT user_id, follower_since_timestamp FROM users_followers where follower_id = :user_id";
        $query = $database->prepare($sql);
        $query->execute(array(':user_id' => Session::get('user_id')));


                if ($query->rowCount() <= 0) {
                    echo 'Nothing';
                    return false;
                }

$_MYSQL_DATA = $query->fetchAll();

        /* Just for my own testing and troubleshoot */
foreach ($_MYSQL_DATA as $row) {
    echo $row->user_id . "-" . $row->follower_since_timestamp ."<br/>";    
}
        echo var_dump($_MYSQL_DATA).'<br><br>';
        /* End for my own testing and troubleshoot*/



    foreach ($_MYSQL_DATA as $row) {
        $sql = "SELECT activity, user_id, activity_timestamp FROM user_activity WHERE user_id = :user_id";
        $query = $database->prepare($sql);
        $query->execute(array(':user_id' => $row->user_id));
        $result = $query->fetchAll();

        foreach ($result as $result){
            echo 'User_id: ' . $result->user_id.'<br>';
            echo 'What: ' . $result->activity .'<br>';
            echo 'When: ' . date('m/d/Y H:i:s', $result->activity_timestamp) .'<br>';
        }

echo '<br>';

        }

但我不知道这是否是“最好”的方式，以及它是否足够安全。

请查看并发表评论。