How to put search box in the database table using PHP and Mysql

Question

I've created the "Customers' Information" in the database. I also put and view it on my customers.php page. Help me to make some "Search box" inside the page. The search box will only search and view what keyword nearer in my database table.

Example: When I search "Jenny" the result of search box will be all "Jenny" in my database table. When my search box don't find "Jenny," the search box will say "no results found"

Here is my code in customers.php

<?php
    $conn = mysqli_connect('localhost','root','','newcartdb');
        if(mysqli_connect_errno()){
            echo 'Failed to Connect: '.mysqli_connect_error();
        }

        if(isset($_POST['delete'])){
            $DeleteQuery = "DELETE FROM customers WHERE id='$_POST[hidden]'";
            mysqli_query($conn,$DeleteQuery);
        }

        $query = "SELECT * FROM customers ORDER BY id ASC";
        $results = mysqli_query($conn,$query);

            echo '<table class="table table-bordered">
                          <thead>
                            <tr>
                              <th width="40px">ID</th>
                              <th>Email</th>
                              <th>Firstname</th>
                              <th>Lastname</th>
                              <th>Gender</th>
                              <th>Titlename</th>
                              <th>BirthMonth</th>
                              <th>BirthDay</th>
                              <th>BirthYear</th>
                              <th>Cellphone Number</th>
                              <th>Phone Number</th>
                              <th>Address1</th>
                              <th>Address2</th>
                              <th></th>
                            </tr>
                          </thead>
                          <tbody>

                          </tbody>';


                    while($userData = mysqli_fetch_array($results)){
                        echo '<form action="customers.php" method="POST">';
                            echo '<tr>';
                                echo '<td>'.$userData['id'].'</td>';
                                echo '<td>'.$userData['Email'].'</td>';
                                echo '<td>'.$userData['Firstname'].'</td>';
                                echo '<td>'.$userData['Lastname'].'</td>';
                                echo '<td>'.$userData['Gender'].'</td>';
                                echo '<td>'.$userData['Titlename'].'</td>';
                                echo '<td>'.$userData['BirthMonth'].'</td>';
                                echo '<td>'.$userData['BirthDay'].'</td>';
                                echo '<td>'.$userData['BirthYear'].'</td>';
                                echo '<td>'.$userData['CellphoneNumber'].'</td>';
                                echo '<td>'.$userData['PhoneNumber'].'</td>';   
                                echo '<td>'.$userData['Address1'].'</td>';
                                echo '<td>'.$userData['Address2'].'</td>';

                                echo '<td><input type="hidden" name="hidden" value="'.$userData['id'].'">';
                                echo '<td><input type="submit" name="delete" value="Delete" class="btn btn-info" /  ></td>';
                            echo '</tr>';
                        echo '</form>';
                    }
                    echo '</table>';
    ?>

wow, you're slowly building your project here on SO haha, read, update, delete. anyway create a form and query a `where like`, its still too broad anyway — Kevin, Sep 14 '14 at 01:24
Sir @SLaks how can I make it invulnerable in SQL Injection? :) — None other, Sep 14 '14 at 01:29
Sir @Ghost tomorrow is my thesis defense. :) I'm doing a rush thing . I pray that the defense will be move. It's so rush right now. I need help from the professional out there. — None other, Sep 14 '14 at 01:30
How to use parameters sir @SLaks to avoid SQL Injection in all of my PHP code. :) — None other, Sep 14 '14 at 01:31
if this is a thesis you should have doing this way ahead of the submission time hehe, anyway, there alot of floating answers around here in SO, just take the time to search, instead of waiting for answers to pop out. to piggyback on SLaks' comment, here is done thru prepared/parameterized statements http://stackoverflow.com/questions/18527659/php-mysqli-prepared-statement-like — Kevin, Sep 14 '14 at 01:36
hahaha you know sir. Student's strategy. To comply near the deadline. Me, I did so much project in other major subjects. Sir I'm a graduating college student. :D Anyway sir $stmt is to avoid "SQL Injection" right? — None other, Sep 14 '14 at 01:41
Sir the code there, I paste it on the top of my codes in my question. I've got error in $param = "%{$_POST[email]}%"; — None other, Sep 14 '14 at 01:46
@DeanilVicente no the whole process of preparing a statement instead of directly concatenating your user input is what makes you create safer queries. anyway, if you're cramming and you think you don't have enough time to rewrite the whole project then stick to what you know already, we don't want to be the one to be blamed for you running out of time. this is off topic anyway, [here](http://stackoverflow.com/questions/10133450/how-to-use-php-string-in-mysql-like-query) is an idea of what you want right now — Kevin, Sep 14 '14 at 01:48
Anyway sir. Maybe sir I'll use my previous search. But I think that's too vulnerable. :) — None other, Sep 14 '14 at 02:03
@ceejayoz: I've noticed a lot of non-native English speakers, especially from India, tend to do this. I see no reason to take offense. — siride, Sep 15 '14 at 00:09
@siride I'm not sure how you picked up offense from my comment. "By the way" is both casual and neutral. — ceejayoz, Sep 15 '14 at 05:01

score 1 · Accepted Answer · answered Sep 14 '14 at 15:11

There are a few things you will need to know to be able to do this. Firstly the security bit...

Basically you want to never trust data that is submitted to your application. When accepting data for use in a MySQL statement, you could use PHP's built in escape functions for MySQL. ref: http://php.net/manual/en/mysqli.real-escape-string.php

You already seem to know how to get data from the submitted form as I see you accessed the $_POST superglobal. For your search, you will do something similar. It would be something like $_POST['search'] where you are getting data posted from a form with a text element with the name "search".

You will need an SQL query to search for results. Without seeing you database schema it's hard to say for user, but I suspect this would work.

$search = mysqli_real_escape_string($_POST['search']);
$query= "
    SELECT * 
    FROM customers 
    WHERE 
        Firstname LIKE '%{$search}%'
        OR Lastname LIKE '%{$search}%'
        OR Email LIKE '%{$search}%'
";
$results = mysqli_query($conn,$query);

Once you have the results, you should be able to display them the same way you did with the example you gave.

Hope this helps!

How to put search box in the database table using PHP and Mysql

1 Answers1

Linked