1

I have built a Twitter like following system using PHP and Mysql and I would like to list the followers a user has in order in which they followed the user. There are two relational tables that pertain to this action: USERS and FOLLOW.

user_id the one that is being followed.

follow_id the one that is following.

created date in which the following happened.

FOLLOW

--------------------------------------------------
|user_id |follow_id |  created  |
--------------------------------------------------
| 23      | 5       |2016-08-09 |
--------------------------------------------------

USERS

--------------------------------------------------
|user_id |  name  |
-------------------------------------------------
| 5      |John Doe|
--------------------------------------------------

This the php Mysql function to get the followers for a certain user.

<?php 
public static function find_followers($user_id){
    global $database;

    $followers_ids = array();
    $sql = "SELECT user_id 
            FROM follow 
            WHERE followed_id = '{$user_id}' 
            ORDER BY created";
    $result = $database->query($sql);
    while($follower = $database->fetch_array($result)){
        array_push($followers_ids, $follower['user_id']);
    }
    if(count($followers_ids)){
        $id_strings = join(',', $followers_ids);

        $sql = "SELECT * FROM users WHERE id IN ($id_strings)";
        $followers = self::find_by_sql($sql);
        return $followers;
    }
} 

?>

Code to dislay followers

<?php 
$followers = find_followers($id);

foreach($followers as $follower){

echo $follower->full_name() . "followed you.<br/>"; 
?>

Currently the list of followers is being ordered by the creation on which users were created, and I would like to display them based upon when the order in which they followed the user. How can this be done?

Thanks in advance!

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Arturo Rosales Baez
  • 159
  • 1
  • 13
  • 1
    You're using some kind of framework here that provides `find_by_sql` but it's not clear which one. You're also constructing queries with [SQL injection bugs](http://bobby-tables.com/) because you're not using prepared statements. Your framework should have a recommended way of doing this that's safe. – tadman Aug 16 '16 at 19:26
  • @tadman Looks like a simple class to me just didnt show us all of it – RiggsFolly Aug 16 '16 at 19:38
  • 1
    **WARNING**: You probably have several [SQL injection bugs](http://bobby-tables.com/) in this code. Whenever possible use **prepared statements**. These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. – tadman Aug 16 '16 at 19:39
  • 1
    You should look up the sql JOIN syntax, with that you could do this all in one query. – RiggsFolly Aug 16 '16 at 19:40
  • @tadman Like Riggs said, they are just classes written to make the code less heavy. The escaping of characters to avoid SQL injections are happening inside those clases. Do you think it would be relevant to state them based on this question? Just asking. – Arturo Rosales Baez Aug 16 '16 at 19:41
  • Surely the `created` column is the date that userid 5 followed userid 23 – RiggsFolly Aug 16 '16 at 19:43
  • 1
    If you're interested in abstraction, which it appears you are, you probably want to look at what an ORM like [Doctrine](http://www.doctrine-project.org/) or [Propel](http://propelorm.org/) can do for you. The escaping should not happen "inside those classes", it should happen in the query itself. Using prepared statements with placeholder values strongly recommended. – tadman Aug 16 '16 at 19:44
  • 1
    Its actually BAD Practice to use `global` in a class method, it destroys the encapsulation. Instead either make the `$database` variable a class property or pass it as a parameter to the methods that require it. – RiggsFolly Aug 16 '16 at 19:45
  • 2
    And as to your comment about escaping parameters! Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared statement and parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Aug 16 '16 at 19:48
  • @RiggsFolly Thanks for your suggestions. Will check my SQL statements to make them safer. – Arturo Rosales Baez Aug 16 '16 at 20:24
  • @tadman Thanks for your suggestions man. – Arturo Rosales Baez Aug 16 '16 at 20:25

1 Answers1

1

You could use a join on the two table rather then having multiple calls to the DB. The SQL would be:

SELECT user.user_id, user.name /*The user that is followings details*/
FROM users
JOIN follow ON user.user_id = follow.follow_id /*You want the info for follower*/
WHERE follow.user_id = '{$user_id}' /*Only where the user is this user*/
ORDER BY follow.created /*Based on question this is the date a follow happened*/

Now the PHP can become (assuming that the $database is mysqli but not sure): 

<?php 
    public static function find_followers($user_id){
        global $database;

        $followers_ids = array();
        $sql = "SELECT user.user_id, user.name
            FROM users
            JOIN follow ON user.user_id = follow.follow_id
            WHERE follow.user_id = '{$user_id}' 
            ORDER BY follow.created";

        //If $user_id is user input you should do a prepared statement.
        $result = $database->query($sql);
        $followers = $database->mysqli_fetch_all($result);

        return $followers;
    }
} 

?>

Your view can stay the same:

<?php 
    $followers = find_followers($id);

    foreach($followers as $follower){

        echo $follower->full_name() . "followed you.<br/>"; 
    }
    ?>
nerdlyist
  • 2,842
  • 2
  • 20
  • 32