mysql 如何通过存储在列中的值访问列名

ffscu2ro  于 2023-01-25  发布在  Mysql
关注(0)|答案(1)|浏览(110)

我有一个表,它的名称是'specialization'数据库名称是'hackathon'在表内它有一行和8列“id”“name”“soft_dev”“Data_analytics”“DB_manager”“sys_engineer”“research”“networks_admin”第一行数据是“5,gourav,59. 7,72. 6,73. 87,59. 14,43. 14,50.74”现在这些分别是值。I am on $row ['Data_analytics']表示我现在可以访问值72.6。我想找出存储该值的列的名称,就像名称是“Data_analytics”一样。我想返回该值,我们可以怎么做

<?php
$value = 72.6; // the value you have access to
// Connect to the database
$db = new mysqli("localhost", "root", "", "hackathon");
// Prepare the query
$query = "SELECT COLUMN_NAME FROM information_schema.columns 
WHERE table_name = 'specialization' AND table_schema = 'hackathon' 
AND (column_name LIKE 'soft_dev' OR column_name LIKE 'Data_analytics' OR column_name LIKE 'DB_manager' OR column_name LIKE 'sys_engineer' OR column_name LIKE 'research' OR column_name LIKE 'networks_admin') 
AND EXISTS (SELECT * FROM specialization WHERE Data_analytics = ?)";
$stmt = $db->prepare($query);
$stmt->bind_param("d", $value);
$stmt->execute();
// Retrieve the result
$result = $stmt->get_result();
$row = $result->fetch_assoc();
// Get the column name
$column_name = $row['COLUMN_NAME'];
echo "The value " . $value . " is stored in the column " . $column_name;
$stmt->close();
$db->close();
?>

当我打印列名称时,它给出了一个错误,说试图访问空变量或空值

h4cxqtbf

h4cxqtbf1#

您没有检查结果集是否为空,甚至没有检查它是否有多行。
一般使用此模式...

while($row = $result->fetch_assoc())
{
    $column_name = $row['COLUMN_NAME'];
    echo "The value " . $value . " is stored in the column " . $column_name;
}

或者...

if ($result->num_rows === 0) {
    echo 'No results';
} else {
    while ($row = $result->fetch_object()) {
        $column_name = $row['COLUMN_NAME'];
        echo "The value " . $value . " is stored in the column " . $column_name;
    }
}

相关问题