CODEWITHSUNDEEP
phpmysqlpdo
thenoob
thenoob

Reputation: 67

select id from table where it equals to row name

I am trying to store the id of a username which I got from $_SESSION to a variable but I can't get the SQL statement to work. The usernames are stored in a database called users and have an ID as primary key. Can someone tell me how I can correct this? Thanks

$name = $_SESSION['username']; //get username of user currently logged in

$rid = $db->exec("SELECT id FROM users WHERE username = '$name'");

Upvotes: 1

Views: 1484

Answers (1)

Qirel
Qirel

Reputation: 26450

From the PHP documentation on PDO::exec():

PDO::exec() does not return results from a SELECT statement. For a SELECT statement that you only need to issue once during your program, consider issuing PDO::query(). For a statement that you need to issue multiple times, prepare a PDOStatement object with PDO::prepare() and issue the statement with PDOStatement::execute().

This means that you cannot use exec() on a SELECT query - instead, you must use query() or prepare(). For any queries using variables or user-input, use prepare() and placeholders in the query for variables, like below, to protect your database against SQL-injection. 

$stmt = $db->prepare("SELECT id FROM users WHERE username = :name");
$stmt->execute(["name" => $name]);
if ($row = $stmt->fetch()) {
    // $row holds the id
} else {
    // No rows were returned at all! No matches for $name
}

Now $row holds the id(s) if the query returned any result at all. Depending on your fetch-type, it might be $row['id'], $row[0], $row->id or a combination of these.

If you expect more than one result, you need to loop while ($row = $stmt->fetch()), or use $stmt->fetchAll();

Upvotes: 1

Related Questions