php sql trying to pass username variable to sql array

Question

Apologies as I am not very good with PHP and SQL. I have a username which seems to work ok as I can echo it and that is correct. This is demonstrated below.

  echo 'Username: ' . $current_user->user_login . "\n";
  echo 'User email: ' . $current_user->user_email . "\n";
  echo 'User first name: ' . $current_user->user_firstname . "\n";
  echo 'User last name: ' . $current_user->user_lastname . "\n";
  echo 'User display name: ' . $current_user->display_name . "\n";
  echo 'User ID: ' . $current_user->ID . "\n";

?>

This works fine.

I have a function in a class. I thought that I could just pass a SQL statement where username is equal to this variable as shown below. However this does not seem to work.

protected function _scandir($id) {
        $files = array();
        $sql   = 'SELECT f.id, f.parent_id, f.name, f.size, f.mtime, f.mime, f.width, f.height, f.username, ch.id AS dirs 
                WHERE f.username = $username
                FROM '.$this->tbf.' AS f 
                LEFT JOIN '.$this->tbf.' AS ch ON ch.parent_id=f.id 
                AND f.parent_id="'.$id.'"
                GROUP BY f.id';

        if ($res = $this->query($sql)) {
            while ($r = $res->fetch_assoc()) {
                $id = $r['id'];
                $this->stat($id, false, $r);
                $files[] = $id;
            }
        }
        return $files;
    }

Am I missing something stupid or obvious here. I just need to pass the username variable and use this to query the database.

This was the original query $

protected function _scandir($id) { $files = array(); $sql = 'SELECT f.id, f.parent_id, f.name, f.size, f.mtime, f.mime, f.width, f.height, f.username, ch.id AS dirs FROM '.$this->tbf.' AS f LEFT JOIN '.$this->tbf.' AS ch ON ch.parent_id=f.id WHERE f.parent_id="'.$id.'" AND f.username ="'.$username.'" GROUP BY f.id';

    if ($res = $this->query($sql)) {
        while ($r = $res->fetch_assoc()) {
            $id = $r['id'];

            $this->stat($id, false, $r);
            $files[] = $id;
        }
    }

    return $files;
}

Answer 1

you cannot add variables to string with single qoute. In addition to that when you add a string to query, you must put it into qoutes.

In addition to that, your query is wrong too. I

Try this :

 $sql   = "
    SELECT f.id, f.parent_id, f.name, f.size, f.mtime, f.mime, f.width, f.height, f.username, ch.id AS dirs 
    FROM ".$this->tbf." AS f 
    LEFT JOIN ".$this->tbf." AS ch ON (ch.parent_id=f.id AND f.parent_id=".$id.")
    WHERE f.username = '".$username."'
    GROUP BY f.id
    ";

assuming that $username and f.username are a strings, $id and f.parent_id are integers