从PHP中的SQL表中获取单个结果

Question

First I am grateful for the help I received with my question last week, but there's still something I haven't quite figured out.

When I enter a word or term in my search box, to be handled by the code below...

<?php
$servername = "localhost";
$username = "user";
$password = "password";
$dbname = "dbname";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {    
  die("Connection failed: " . mysqli_connect_error());
}

$sql = "SELECT ID, FirstName, LastName FROM `table` LIMIT 0, 30 ";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result)>0) { 
  // output data of each row
  while($row=mysqli_fetch_assoc($result)) { 
    echo "ID: " . $row["ID"]. " - Name: " . $row["FirstName"]. " " . $row["LastName"]. "<br>"; 
    } 
} else { 
echo "0 results"; 
} 

mysqli_close($conn);
?>

I get all the possible results listed from my SQL table.

So, how do I limit the results to those the end user has entered? I know that I should use either a LIKE or WHERE statement.

If it helps, I am creating a database of medical professionals, their practice names, and locations.

Answer 1

Assuming you have a form that looks like this:

<form action="" method="post">
    Search: <input name="search" type="text">
</form>

You could extend your SQL as follows:

$sql = "SELECT ID, FirstName, LastName FROM `table` ";
if (isset($_POST['search'])) {
    $search = mysqli_real_escape_string($conn, $_POST['search']);
    $sql .= " WHERE FirstName LIKE '%$search%'
                 OR LastName LIKE '%$search%' ";
}
$sql .= " LIMIT 0, 30 ";
$result = mysqli_query($conn, $sql);
// ... etc.

To be clear, the "... etc." means that your outputting code remains as-is:

if (mysqli_num_rows($result)>0) { 
    // output data of each row
    while($row=mysqli_fetch_assoc($result)) { 
        echo "ID: " . $row["ID"]. " - Name: " . $row["FirstName"]. " " . $row["LastName"]. "<br>"; 
    } 
} else { 
    echo "0 results"; 
} 

mysqli_close($conn);