使用字符串连接的 SQL 查询
我是 php 新手，我有一个表“abc”，其中有列：id、姓名和年龄。我想做以下事情：
仅当在输入字段中输入姓名时，才应使用字符串连接来构建 SQL 查询来显示相应的数据（id 和年龄）。
这是用于搜索功能的
这个问题的 SQL 查询应该是什么？
// Create connection
$conn = new mysqli($SERVER_NAME, $USER_NAME, $PASSWORD, $DATABASE_NAME);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
$sql = " SELECT name, CONCAT(id,'.',age) FROM personene WHERE name = 'VALUE_FROM INPUT_NAME'";
$result = $conn->query($sql);
$error = mysqli_error($conn);
// Store results
while($row = $result->fetch_assoc()) {
$data[] = $row;
}
?>
<!DOCTYPE html>
<html>
<head>
</head>
<body>
<?php if(!empty($error))
echo "<p style='color:red'>$error</p>";
?>
<p>Please enter the name:</p>
<form action="<?=$_SERVER['PHP_SELF']?>" method="GET">
<input type="input" name="name" value="" />
<br/>
<input type="submit" name="sendbtn" value="Send" />
</form>
<?php
if(!empty($data)) {
echo "<h1>Persons:</h1><table border='1'><tr><th>Id</th><th>Firstname</th><th>Age</th></tr>";
foreach($data as $row) {
echo "<tr><td>".$row["id"]."</td>";
echo "<td>".$row["name"]."</td>";
echo "<td>".$row["age"]."</td></tr>";
}
echo "</table>";
}
else
echo "No data available";
echo '(Query: '.$sql.')';
?>
</body>
</html>
慕妹32420032回答
-
慕的地10843
目前尚不清楚为什么要concatenate在 SQL 查询中使用字段，而在 html 中这些字段显然显示在它们自己的列中。您拥有的代码对SQL 注入完全开放，因此您需要考虑使用 aprepared statement来安全地处理用户提供的输入。<?php $data=[]; error_reporting( E_ALL ); if( $_SERVER['REQUEST_METHOD']=='GET' && !empty( $_GET['name'] ) ){ $SERVER_NAME=''; $USER_NAME=''; $PASSWORD=''; $DATABASE_NAME=''; mysqli_report( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT ); $conn=new mysqli( $SERVER_NAME, $USER_NAME, $PASSWORD, $DATABASE_NAME ); try{ $sql = 'select `name`, `id`, `age` from `personene` where `name` = ?'; $stmt=$conn->prepare( $sql ); $stmt->bind_param('s', $_GET['name'] ); $stmt->execute(); $stmt->bind_result( $name, $id, $age); while( $stmt->fetch() )$data[]=[ 'name' => $name, 'id' => $id, 'age' => $age ]; $stmt->free_result(); $stmt->close(); $conn->close(); }catch( mysqli_sql_exception $e ){ exit( $e->getMessage() ); } }?><!DOCTYPE html><html> <head> <title>Fetch user details</title> </head> <body> <p>Please enter the name:</p> <form method='GET'> <input type='input' name='name' /> <br/> <input type='submit' name='sendbtn' value='Send' /> </form> <?php if( !empty( $data ) ) { echo " <h1>Persons:</h1> <table border='1'> <tr> <th>Id</th> <th>Firstname</th> <th>Age</th> </tr>"; foreach( $data as $row ) { echo " <tr> <td>{$row["id"]}</td> <td>{$row["name"]}</td> <td>{$row["age"]}</td> </tr>"; } echo "</table>"; } else { echo "No data available"; } ?> </body></html> -
慕姐4208626
对于要查找与名称相关的记录的 SQL 查询，您必须使用此查询，select concat('id', '-', 'age') as user_data from abc where name = $REQUEST['search_name'];或者您可以使用LIKE条件从表中获取记录。select concat('id', '-', 'age') as user_data from abc where name like $REQUEST['search_name'];这是您的代码的更新，这是您的代码的更新，<?php// Create connection$conn = new mysqli($SERVER_NAME, $USER_NAME, $PASSWORD, $DATABASE_NAME);// Check connectionif ($conn->connect_error) { die("Connection failed: " . $conn->connect_error);} // echo "Connected successfully";$data = [];$sql = "Please submit the form.";if(isset($_GET['sendbtn']) ) { $sql = " SELECT id, name, age FROM personene WHERE name = '". $_GET['name'] ."'"; $result = $conn->query($sql); $error = mysqli_error($conn); // Store results while($row = $result->fetch_assoc()) { $data[] = $row; }}?><!DOCTYPE html> <html> <head></head> <body> <?php if(!empty($error)) echo "<p style='color:red'>$error</p>"; ?> <p>Please enter the name:</p> <form action="<?=$_SERVER['PHP_SELF']?>" method="GET"> <input type="input" name="name" value="" /> <br/> <input type="submit" name="sendbtn" value="Send" /> </form> <?php if(isset($data) && !empty($data)) { echo "<h1>Persons:</h1><table border='1'><tr><th>Id</th><th>Firstname</th><th>Age</th></tr>"; foreach($data as $row) { echo "<tr><td>".$row["id"]."</td>"; echo "<td>".$row["name"]."</td>"; echo "<td>".$row["age"]."</td></tr>"; } echo "</table>"; } else { echo "No data available"; } echo '(Query: '.$sql.')';?></body></html>
随时随地看视频慕课网APP
PHP