Is this an efficient way to create a post stream, or is there a better way?

Question

$FSQL = $pdo->query('SELECT * FROM `connections` WHERE `uid`="'.$my_id.'" && `type`="1" ORDER by `id` DESC');
$myfriends = '`uid`="'.$my_id.'" ';
while($po = $FSQL->fetch(PDO::FETCH_ASSOC)){
    $myfriends .= ' || `uid`="'.$po['cid'].'"';
}

$dsk = $pdo->query("SELECT * FROM `posts` WHERE ".$myfriends." ORDER by `id` DESC LIMIT ".$limitCount);

I have been trying to create a nice post stream, and I finally got my code down. But it seems so inefficient if you have a large amount of connections (connections being anything from friends, pages, or events).

Could someone tell me if there is a better way to do this?

--by the way: this is working perfectly already, but I feel like i'll run into issues down the line

score 0 · Answer 1 · answered Mar 07 '13 at 19:21

0

$FSQL = $pdo->query('SELECT * FROMconnectionsWHEREuid="'.$my_id.'" &&type="1" ORDER byidDESC');

This is vulnerable to SQL Injection. You should be using parameters and prepared statements. See the Documentation.

Worked Example

$sql = $pdo->prepare('SELECT * FROM `table` WHERE `uid`=:uid');
// Create the SQL statement, with the parameter prefixed by a ":".
$userID = "username";
// Grab the value you wish to bind to your parameter.
$sql->bindParam(':uid', $userID);
// Bind the values, using the bindParam method.
$sql->execute();
// Execute the statement with the parameters bound to the SQL query.

answered Mar 07 '13 at 19:21

christopher

26,815
5
55
89

I know how to prepare values, just it wasn't necessary because $my_id is generated by PHP/SQL and a user has no ability to change that value. – Jake Mar 07 '13 at 19:26
Using prepared statements is the standard method for Querying an SQL database. I apologize if my answer isn't of much help, but when someone concatenates string values in an SQL query, it's a BIG red flag to most developers. – christopher Mar 07 '13 at 19:28
Yeah, I understand that. It's a big red flag for me too, but like I said that can't be generated by a user, so it doesn't seem like a big deal to me. I prepare most of my other statements. – Jake Mar 07 '13 at 19:29
Aye, that's fair enough. My point was that it's understandable why it was the first thing that jumped out at me. That is all :) – christopher Mar 07 '13 at 19:30

score 0 · Answer 2 · edited Oct 24 '16 at 07:34

0

You don't want to use a subquery? Something like this...

$dsk = $pdo->query(
    "SELECT *
       FROM `posts` 
      WHERE uid IN (
            SELECT cid
              FROM `connections` 
             WHERE `uid`="'.$my_id.'" && `type`="1"
          ) 
      ORDER BY `id` DESC LIMIT " . $limitCount);

And try not to use * when you don't need all fields.

edited Oct 24 '16 at 07:34

Danila Ganchar

10,266
13
49
75

answered Mar 07 '13 at 19:24

rcorbellini

1,307
1
21
42

subquery isn't working for me :/ – Jake Mar 07 '13 at 19:28
Your statement would pass $my_id as a string literal; not as the variable type. – christopher Mar 07 '13 at 19:29

Is this an efficient way to create a post stream, or is there a better way?

2 Answers2