How to Limit Single Results From SQL Table in PHP

PHP
1

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.

2

Hi there. Basically, you want something like

SELECT whatever FROM table WHERE name LIKE 'smith'

with your LIMIT clause if needed.

I’m not sure what the PHO in your title means.

3

Mistyped ‘PHP’, sorry.

4

Fixed! :smile:

5

By “end user” do you mean the user who is conducting the search? If so, you’ll need some kind of “login” approach on your web site so that user can be identified, then you can use the WHERE clause to limit the results. Obviously your table will also need to keep a record of who entered a particular entry.

6

@droopsnoot: I don’t have to register with IMDB or Allmusic.com to search their databases. I don’t understand why users would have to register to do a simple search at our site, when we just want them to find medical professionals in their area.

7

Sorry, my mistake - I was reading your requirement of “limit to those the end-user has entered” to mean “those records that this particular user had created”.

8
  1. First, you have to make your mind, which query you want to run. Whether it will be LIKE or equality and which field you want to test against the term.
  2. Next, you have to use PDO instead of mysqli.

For searching a in the last name the code would be

$stmt  = $pdo->prepare("SELECT ID, FirstName, LastName FROM `table` WHERE LastName LIKE ? LIMIT 0, 30 ");
$stmt->execute(["%".$_GET['search']."%"]);
$data = $stmt->fetchAll();

if ($data) { 
    foreach($data as $row) { 
        echo "ID: " . $row["ID"]. " - Name: " . $row["FirstName"]. " " . $row["LastName"]. "<br>"; 
    } 
} else { 
    echo "0 results"; 
}
9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.