PHP and MySQL Select a Single Value

Question

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.

Solution 1

1. Don't use quotation in a field name or table name inside the query.

2. After fetching an object you need to access object attributes/properties (in your case id) by attributes/properties name.

One note: please use mysqli_* or PDO since mysql_* deprecated. Here it is using mysqli:

session_start();
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = new mysqli('localhost', 'username', 'password', 'db_name');
$link->set_charset('utf8mb4'); // always set the charset
$name = $_GET["username"];
$stmt = $link->prepare("SELECT id FROM Users WHERE username=? limit 1");
$stmt->bind_param('s', $name);
$stmt->execute();
$result = $stmt->get_result();
$value = $result->fetch_object();
$_SESSION['myid'] = $value->id;

Bonus tips: Use limit 1 for this type of scenario, it will save execution time :)

Solution 2

The mysql_* functions are deprecated and unsafe. The code in your question in vulnerable to injection attacks. It is highly recommended that you use the PDO extension instead, like so:

session_start();

$query = "SELECT 'id' FROM Users WHERE username = :name LIMIT 1";
$statement = $PDO->prepare($query);
$params = array(
    'name' => $_GET["username"]
);
$statement->execute($params);
$user_data = $statement->fetch();

$_SESSION['myid'] = $user_data['id'];

Where $PDO is your PDO object variable. See https://www.php.net/pdo_mysql for more information about PHP and PDO.

For extra help:

Here's a jumpstart on how to connect to your database using PDO:

$database_username = "YOUR_USERNAME";
$database_password = "YOUR_PASSWORD";
$database_info = "mysql:host=localhost;dbname=YOUR_DATABASE_NAME";
try
{
    $PDO = new PDO($database_info, $database_username, $database_password);
}
catch(PDOException $e)
{
    // Handle error here
}

Solution 3

You do this by using mysqli_fetch_field method.

session_start();
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
$name = $_GET["username"];
$sql = "SELECT 'id' FROM Users WHERE username='$name' limit 1";
$result = mysqli_query($link, $sql);
if ($result !== false) {
    $value = mysqli_fetch_field($result);
    $_SESSION['myid'] = $value;
}

Note: you can do that by using mysql_fetch_field() method as well, but it will be deprecated in php v5.5

Solution 4

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

Solution 5

Try this

$value = mysql_result($result, 0);

View more solutions

Comments

[Wes Cossick]

Just a reminder... The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead

[elixenide]

This has serious security problems. As @WesC points out, don't use the mysql_* functions. Also, you are wide open to SQL injection attacks.

[user3055501]

This worked in eliminating the error message, but printing $_SESSION['myid'] to the screen now displays "id" rather than a specific int.

[Awlad Liton]

@user3055501 : try above code. I have changed in $sql line and provide your feedback. It should work :)

[user3055501]

Thanks for the answer Wes. I'll definitely look into PDO when I have more time.

[Wes Cossick]

From personal experience, I recommend you look into it sooner rather than later. The more code you write using the mysql_* functions, the more you'll find out that you'll be required to entirely rewrite. Plus, it's actually pretty convenient and more organized in other areas.

[Anders M.]

in case someone else sees this and panics, you can change to mysqli instead which is better. note the i at the end of mysql

[Gusstavv Gil]

mysqli_fetch_field "Returns an object which contains field definition information or FALSE if no field information is available." so this won't work as suggested by the answer. -1 because of this.

[ToolmakerSteve]

@GusstavvGil: I've corrected the code by making sure there is a valid $result, before the call to mysqli_fetch_field. I've also added the missing $link parameter.

[ToolmakerSteve]

Like @AndersM said - I also find mysqli easier to use/read than PDO, for simple queries. Note however that Wes is making a very important point: if any part of your query includes text that is not 100% under your control, then use a prepared statement. In mysqli, that is prepare, manual discussion of prepared statements

[commonpike]

@ToolmakerSteve mysqli_fetch_field does not return the value. It returns the field definition, like name, table, flags. -1 because of this... php.net/manual/en/mysqli-result.fetch-field.php

[Brian Fleming]

Needs a ; on the fifth line.

[mickmackusa]

...and never use raw user input in your query.

[mickmackusa]

This answer should not be used because it is insecure/unstable.

[mickmackusa]

stackoverflow.com/questions/59893254/… (it is surprisingly hard to find secure queries to close new questions with.)

[Dharman]

Warning: mysql_* extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

[Dharman]

Warning: mysql_* extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

[Dharman]

Warning: This answer is totally wrong. mysql_fetch_field() returns metadata about a table column, not the value itself.

[Dharman]

Warning: This answer is totally wrong. mysqli_fetch_field() returns metadata about a table column, not the value itself.

[Dharman]

Warning: mysql_* extension is deprecated as of PHP 5.5.0, and has been removed as of PHP 7.0.0. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

[mickmackusa]

"Try this" answers are low-value on Stackoverflow because they do a poor job of educating/empowering the OP and thousands of future researchers. Even if you feel your solution is ultra-basic/self-explanatory, ALL answers should include some form of plain English supporting text. Tell researchers how it works, why it works, how it is different from other answers, why you think it is a good idea, provide link(s) to documentation, etc. Simply be generous when you answer with the intent to help people and role model good answer posting behaviour to others.