1

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.

Community
  • 1
  • 1
Bionic Bundtcake
  • 13
  • 4

2 Answers2

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);
trincot
  • 317,000
  • 35
  • 244
  • 286
  • Plz remove ending $ %$search$% – devpro Jan 22 '16 at 21:49
  • I substituted the code by @trincot for lines 15-21, and now I can't get onto my PHP page after all. (I didn't change anything else.) – Bionic Bundtcake Jan 25 '16 at 02:33
  • @BionicBundtcake, please explain what you mean with *I can't get onto my PHP page"*? Note that you should only replace 2 of your original lines with the code above: in fact only the line `$sql = ` should be replaced by my code, but I repeated your `$result = ...` to make clear where your code remains as it is. – trincot Jan 25 '16 at 11:02
  • I mean that instead of getting "0 results," I now get a blank page, on which I can't even view the code. – Bionic Bundtcake Jan 25 '16 at 18:28
  • Sorry, here's what it looks like after line 14: `code` $sql = "SELECT ID, FirstName, LastName FROM `create` "; 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); } else { echo "0 results"; } mysqli_close($conn); ?> `code` – Bionic Bundtcake Jan 25 '16 at 18:33
  • Well, you removed all code that outputs the result. I did not say to remove that. Please read my answer again: **extend** the SQL code. I never wrote to remove the code you already had to output the results. – trincot Jan 25 '16 at 18:37
-1
$sql1=mysqli_query("SELECT*FROM table ORDER BY id DESC LIMIT 1");
$result1=mysqli_fetch_array($sql1);
Sachithra Gayan
  • 1
  • 2
  • Plz add explanation and what r u doing mixing mysql and mysqli?? – devpro Jan 23 '16 at 08:44
  • Welcome to Stack Overflow! Could you explain how your answer addresses the problem(s) from the question? Code-only answers are not very useful, especially for further readers that stumble upon this post. – Cristik Jan 23 '16 at 12:44