select id from table where it equals to row name

Question

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'");

Either there are no id's matching the tested username or the exec function isn't doing it's job properly. — Andrew Larsen, Apr 10 '17 at 22:25
@AndrewLarsen It is the latter and I think the query is flawed — thenoob, Apr 10 '17 at 22:28
Am I right to assume this is PDO? @thenoob If thats the case, set PDO to throw exceptions, use a try/catch and get the actual error thrown back at you. Without knowing that, its just a wild guess. Is the session started? Does that variable hold a value at all? Is it the right value? — Qirel, Apr 10 '17 at 22:29
can you print or display the session username to confirm if the session is not empty — RoMEoMusTDiE, Apr 10 '17 at 22:32
@Qirel the session is started and the variable does not hold a value. When I used var_dump I got false — thenoob, Apr 10 '17 at 22:33
Enable error-reporting, by adding `error_reporting(E_ALL); ini_set("display_errors", 1);` at the top of your file, directly after ` — Qirel, Apr 10 '17 at 22:35
@maSTArHiAn yes I have another statement to just echo the username from session and it displays the username. Its the second line and I think the query isn't right but do not know how to solve it — thenoob, Apr 10 '17 at 22:35
Add var_dump($rid); to the end and see what kind of data you receive from the function call. And it would help alot if you could show us the code for the function exec. — Andrew Larsen, Apr 10 '17 at 22:36
and you reckon your connection is working? can you replace $name with a static value and see if you get a result back from the db. — RoMEoMusTDiE, Apr 10 '17 at 22:36
@Qirel I get no no errors. Basically I am trying to add the id of the username as a FK in another but I need to get the ID first — thenoob, Apr 10 '17 at 22:38
@AndrewLarsen I get a boolean false. Does that mean it's empty? — thenoob, Apr 10 '17 at 22:39
It means it returns false. If you add the code from the function "exec" we can see if we figure it out. — Andrew Larsen, Apr 10 '17 at 22:39
@AndrewLarsen Its this one http://php.net/manual/en/pdo.exec.php — Qirel, Apr 10 '17 at 22:40
@thenoob If you look at my two comments above, combine those and you will get error-reporting from PHP and PDO. This will allow you to figure out exactly what went wrong. Without the errors, we can't do much but guess. — Qirel, Apr 10 '17 at 22:40
How can we be certain he doesn't have his own class with his own function? However we need to see the code in order to solve this. — Andrew Larsen, Apr 10 '17 at 22:40
@maSTArHiAn I do not get a return back when it is of a static value — thenoob, Apr 10 '17 at 22:40
@AndrewLarsen By all means, but it seems highly unlikely that this is an extension of the PDO class... There's little to extend that particular method with anyway. — Qirel, Apr 10 '17 at 22:42
@Qirel what would I catch for? Can you write the code I'm not familiar with handling erros — thenoob, Apr 10 '17 at 22:43
Well if it is PDO you can try to add var_dump($db->errorInfo()); at the bottom and see what you get. — Andrew Larsen, Apr 10 '17 at 22:44
@thenoob Docs: http://php.net/manual/en/class.pdoexception.php - basically wrap your connection and the `exec()` method in a `try` block, with `catch (PDOException $e) { print_r($e); }` after it. And of course PHP error reporting, check your logs etc. You need to set PDO to throw exceptions though. — Qirel, Apr 10 '17 at 22:45
@maSTArHiAn it does work, it adds other variables in but struggling with the username one — thenoob, Apr 10 '17 at 22:46
@AndrewLarsen when I use var_dump($db->errorInfo());, I get array (size=3) 0 => string '00000' (length=5) 1 => null 2 => null — thenoob, Apr 10 '17 at 22:49
@thenoob Then what exactly is the value of `$name`? `var_dump($_SESSION);` — Qirel, Apr 10 '17 at 22:52
when I do var_dump I get a 0 not false because i mistyped the code — thenoob, Apr 10 '17 at 22:54
@Qirel its the username i created to log in, zxcv123. var_dump($name) gives me the username as a string so it has to be the second line of code thats gone wrong — thenoob, Apr 10 '17 at 22:54
Can't believe we missed this and went down a bunch of other rabbit-holes, but `exec()` doesn't work for `SELECT` queries. See my answer below. — Qirel, Apr 10 '17 at 23:01

score 1 · Accepted Answer · edited May 23 '17 at 10:30

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();

Thank you its fixed it but when I try to insert $row into db, I get an error of array to string conversion — thenoob, Apr 10 '17 at 23:09
That's because you need to use `$row['id']` or `$row[0]`, not `$row` (which is an array!), this depends on your fetch-type - read the last two paragraphs in the answer, just above the two links. — Qirel, Apr 10 '17 at 23:10

select id from table where it equals to row name

1 Answers1