PHP 和 MySQL 选择单个值

PHP and MySQL Select a Single Value

提问by user3055501

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

我想知道如何从我的 MySQL 表中选择一个值。该表包括列username和id在其他之中（id是自动增量，username是唯一的）。给定用户名，我想设置一个会话变量$_SESSION['myid']，该变量等于id与给定用户名对应的列中的值。这是我已经尝试过的代码：

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.

可捕获的致命错误：无法将类 stdClass 的对象转换为字符串。

Casting $valueto type string does not fix the problem.

强制转换$value为字符串类型并不能解决问题。

回答by Awlad Liton

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.

1. 不要在查询中的字段名或表名中使用引号。

2. 获取对象后，您需要通过属性/属性名称访问对象属性/属性（在您的情况下为 id）。

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

一个注意事项：请使用 mysqli_* 或 PDO，因为 mysql_* 已弃用。这里使用的是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 :)

额外提示：对于这种类型的场景使用限制 1，它将节省执行时间 :)

回答by Wes Cossick

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

mysql_* 函数已弃用且不安全。您问题中的代码容易受到注入攻击。这是强烈建议您使用PDO扩展代替，就像这样：

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 $PDOis your PDO object variable. See https://www.php.net/pdo_mysqlfor more information about PHP and PDO.

$PDO您的 PDO 对象变量在哪里。有关 PHP 和 PDO 的更多信息，请参阅https://www.php.net/pdo_mysql。

For extra help:

如需额外帮助：

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

这是有关如何使用 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
}

回答by sas

You do this by using mysqli_fetch_fieldmethod.

您可以通过使用mysqli_fetch_field方法来做到这一点。

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

注意：你也可以通过使用mysql_fetch_field()方法来做到这一点，但它会在 php v5.5 中被弃用

回答by Khawer Zeshan

Try this

尝试这个

$value = mysql_result($result, 0);

回答by Alex Siri

When you use mysql_fetch_object, you get an object (of class stdClass) with all fields for the row inside of it.

当您使用 时mysql_fetch_object，您将获得一个对象（属于 stdClass 类），其中包含该行的所有字段。

Use mysql_fetch_fieldinstead of mysql_fetch_object, that will give you the first field of the result set (idin your case). The docs are here

使用mysql_fetch_field代替mysql_fetch_object, 将为您提供结果集的第一个字段（id在您的情况下）。文档在这里

回答by Dharman

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

mysql_*扩展已于 2013 年弃用，并于 2018 年从 PHP 中完全删除。您有两种选择PDO或MySQLi。

PDO

PDO

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

更简单的选项是 PDO，它有一个简洁的辅助函数fetchColumn()：

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

Proper PDO tutorial

正确的 PDO 教程

MySQLi

MySQLi

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

你可以用 MySQLi 做同样的事情，但它更复杂：

$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.

fetch_assoc()如果没有从数据库返回的行，则可能返回 NULL，这就是为什么我用三元检查是否有任何数据返回。

回答by Satish Sharma

try this

尝试这个

session_start();
$name = $_GET["username"];
$sql = "SELECT 'id' FROM Users WHERE username='$name' LIMIT 1 ";
$result = mysql_query($sql) or die(mysql_error());
if($row = mysql_fetch_assoc($result))
{
      $_SESSION['myid'] = $row['id'];
}

回答by Rajesh Paul

It is quite evident that there is only a single idcorresponding to a single usernamebecause usernameis unique.

很明显，只有一个单一的id对应一个单一的，username因为username是唯一的。

But the actual problem lies in the query itself-

但实际问题在于查询本身——

$sql = "SELECT 'id' FROM Users WHERE username='$name'";

O/P

开/关

+----+
| id |
+----+
| id |
+----+

i.e. 'id'actually is treated as a string not as the idattribute.

即'id'实际上被视为字符串而不是id属性。

Correct synatx:

正确的语法：

$sql = "SELECT `id` FROM Users WHERE username='$name'";

i.e. use grave accent(`) instead of single quote(').

即使用重音符号(`) 而不是单引号(')。

or

或者

$sql = "SELECT id FROM Users WHERE username='$name'";

Complete code

完整代码

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

回答by delive

This is a correct old version.

这是一个正确的旧版本。

From documentation correct.

从文档正确。

$sql2 = mysql_query("SELECT nombre FROM prov WHERE id_prov = '$array'");
$row2 = mysql_fetch_array($sql2);
$nombre = $row2['nombre'];

Have fun.

玩得开心。

mysql_ (Old version)
mysqli_ (New version)