PHP and MySQL Select a Single Value

user_3055501 0
Viewed 255961

I'd like to know how to select a single value from my MySQL table. The table includes columns username and id amongst others (id is auto-increment and username is unique). Given the username, I want to set a session variable $_SESSION['myid'] equal to the value in the id column that corresponds to the given username. Here's the code that I've already tried: 

session_start();
$name = $_GET["username"];
$sql = "SELECT 'id' FROM Users WHERE username='$name'";
$result = mysql_query($sql);
$value = mysql_fetch_object($result);
$_SESSION['myid'] = $value;

So far I'm getting:

Catchable fatal error: Object of class stdClass could not be converted to string.

Casting $value to type string does not fix the problem.

user-accounts mysql sql php database
8 Answers

mysql_* extension has been deprecated in 2013 and removed completely from PHP in 2018. You have two alternatives PDO or MySQLi.

PDO

The simpler option is PDO which has a neat helper function fetchColumn():

$stmt = $pdo->prepare("SELECT id FROM Users WHERE username=?");
$stmt->execute([ $_GET["username"] ]);
$value = $stmt->fetchColumn();

Proper PDO tutorial

MySQLi

You can do the same with MySQLi, but it is more complicated:

$stmt = $mysqliConn->prepare('SELECT id FROM Users WHERE username=?');
$stmt->bind_param("s", $_GET["username"]);
$stmt->execute();
$data = $stmt->get_result()->fetch_assoc();
$value = $data ? $data['id'] : null;

fetch_assoc() could return NULL if there are no rows returned from the DB, which is why I check with ternary if there was any data returned.

Since PHP 8.1 you can also use fetch_column()

$stmt->execute();
$value = $stmt->get_result()->fetch_column();
user_18394390
Related