我是 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>
2 回答
慕的地10843
TA贡献1785条经验 获得超8个赞
目前尚不清楚为什么要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
TA贡献1852条经验 获得超7个赞
对于要查找与名称相关的记录的 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 connection
if ($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>
- 2 回答
- 0 关注
- 95 浏览
添加回答
举报
0/150
提交
取消