我有一个带有名字和姓氏列的 mySQL 数据库,目前我有一个搜索姓氏的搜索查询,当姓氏匹配时,它们会与它所在行中的名字一起在屏幕上回显,现在这很好,我想找出的是,如何使查询搜索输入字段中键入的全名, 请记住,我没有全名列,全名将是用户输入的内容,下面就是我目前工作的一个例子!谢谢。
if(isset($_GET['name'])){
$raw_name=$_GET['name'];
if(preg_match("/[A-Z | a-z]+/", $raw_name)){
$name=$raw_name;
include "connect.php";
//-query the database table
$sql="SELECT userId, FirstName, LastName FROM residential2 WHERE FirstName='" . $name ."'";
//-run the query against the mysql query function
$result=mysql_query($sql);
//-count results
$numrows=mysql_num_rows($result);
echo "<div align="center">" . $numrows . " results found for " . stripslashes($name) . "</div>";
while($row=mysql_fetch_array($result)){
$FirstName =$row['FirstName'];
$LastName=$row['LastName'];
$userId=$row['userId'];
//-display the result of the array
echo "<ul>n";
echo "<li>" . "<a href="index.php?id=$userId">" . $LastName . " " .$FirstName . " </a></li>n";
echo "</ul>";
}
}
else {
echo "<p>Please enter a search query</p>";
}
}
使用 concat()
$sql="SELECT userId, CONCAT(FirstName, ' ', LastName) AS fullname FROM residential2 WHERE FirstName='" . $name ."'";
<?php
$fullname = $row['fullname'];
?>
它非常简单!
从your_table中选择 *其中 CONCAT( 字段 1, ' ' , 归档 2 ) 如 '% $query %'
你可以这样做:
$sql="SELECT userId, FirstName, LastName
FROM residential2
WHERE concat(FirstName, ' ', LastName) like '%$name%'";
您必须将全名拆分为名字和姓氏,然后进行相应的搜索。为您提供的代码片段:
SELECT REVERSE(SUBSTR(REVERSE(name),INSTR(REVERSE(name),' ')+1)) AS first_name,
REVERSE(SUBSTR(REVERSE(name),1,INSTR(REVERSE(name),' '))) AS last_name
希望对您有所帮助。
$frnd_name = trim(preg_replace( "/[ ]+/", " ", $_GET['frnd_search'] ) );
$arr = explode(" ", $frnd_name, 2);
if(!empty($arr[1]))
$sql_where = "name LIKE '%".$arr[0]."%' AND lastname LIKE '%".$arr[1]."%'";
else
$sql_where = "name LIKE '%".$arr[0]."%' OR lastname LIKE '%".$arr[0]."%' OR username LIKE '%".$arr[0]."%' OR email LIKE '%".$arr[0]."%'";
$sql = "SELECT name,lastname,username,email FROM user WHERE ".$sql_where;
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
echo "".$row['name']." ".$row['lastname']."<br>";
}
}
SELECT lname, fname FROM TABLE where CONCAT(fname, ' ', lname) LIKE '%$fullname%'