PHP search in MySQL

Question

I'm trying to make a search function on this website that loops through my $result_array. I've tried a bunch of different approaches on my own but to no avail. Thought it was about time I asked here. This is my code:

<?php

            include 'database_info.php';

            $search_string = $_POST['search1'];
            $query = "SELECT * FROM customers_info WHERE first_name='$search_string'";

            //Try to query the database
            if($result = $data_connect->query($query)){
                echo '<br /><br />Successfully sent query!<br /><br />';   
            }
            else {
                echo 'Error getting customers from the database: '.mysqli_error($data_connect).'<br />'; 
            }

            //Create Table
            echo "<table id='Grid'><tr>";
            echo "<th>customer_id</th>";
            echo "<th>First Name</th>";
            echo "<th>Last Name</th>";
            echo "<th>Email</th>";
            echo "<th>Country</th>";
            echo "<tr>\n";

            $class = "odd"; //Keep track of whether a row is equal or odd

            //Loop through all the rows returned by the query, creating a table row for each
            while($result_array = mysqli_fetch_assoc($result)){


                echo "<tr class=\"$class\">";
                echo "<td>".$result_array['customer_id']."</td>";
                echo "<td><a href='view_customer.php?email=".$result_array['email']."'>" .$result_array['first_name']. "</a></td>";
                echo "<td>" .$result_array['last_name']. "</td>";
                echo "<td>" .$result_array['email']. "</td>";
                echo "<td>" .$result_array['country']. "</td>";
                echo "</td></tr>\n";

                //If the last row was even make the next one odd
                if($class =="odd"){
                    $class ="even";
                }
                else{
                    $class = "odd";
                }
            }



            echo "</table>";

            $data_connect->close();

        ?>

Can anybody tell me a way I could accomplish this? A function or approach I could use?

P.S.

My current approach is to alter the query, this does work but then I can only search for the customer first_name for example. I want to be able to search for email address or last_name or country. These are all columns in my database.

could you not just alter the where to have OR last_name.. etc — krisph
– krisph, Commented Mar 4, 2016 at 12:23
Add a OR condition to query the query to search in multiple columns eg : $query = "SELECT * FROM customers_info WHERE first_name='$search_string' OR last_name='$search_string' OR email='$search_string'"; and so on — Sanoj Sharma
– Sanoj Sharma, Commented Mar 4, 2016 at 12:24
I can't believe it was something so simple, i was searching for some complicated magic function the whole time. Thank you so much! — compilingJohnny
– compilingJohnny, Commented Mar 4, 2016 at 12:27

rahul · Accepted Answer · 2016-03-04 12:25:38Z

3

Your query should be like:-

$query = "SELECT * FROM customers_info WHERE (first_name LIKE '%$search_string%' OR email LIKE '%$search_string%')";

answered Mar 4, 2016 at 12:25

rahul

9191 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

iJamesPHP · Accepted Answer · 2016-03-04 12:28:50Z

0

This is how your query should be:

$query = "SELECT * FROM customers_info WHERE first_name='".$search_string."';";

Should work

answered Mar 4, 2016 at 12:28

iJamesPHP

1711 silver badge12 bronze badges

Comments

rharvey · Accepted Answer · 2016-03-04 12:32:27Z

If your PHP loop works then it's your SQL that needs to change.

Try something like:

$search_string = str_replace(" ","|",$search_string);

$query = sprintf("SELECT * FROM customers_info WHERE (
first_name REGEXP '%s' OR
last_name REGEXP '%s' OR
email REGEXP '%s'", $search_string, $search_string, $search_string)

I prefer using sprintf but that's just a personal preference. So basically I replace any white space in your $search_query with a pipe '|'. This is so REGEXP in the SQL query can search for more than one thing e.g. search Foo Bar will now search Foo and Bar which expands your results. This is optional, however.

The query simply uses a series of OR statements so that it searches in different columns.

Hope this helps!

WinterTime · Accepted Answer · 2016-03-04 12:40:17Z

The most secure practice for any database related stuff, is to use the PDO. The PDO will escape your query that will avoid SQL Injections, and moreover it is easy to fetch the details.

For example of the connetivity and fetching the data:

$db = new PDO("$type:host=localhost;dbname=x;charset=utf8","user","password");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$query = $db->select("SELECT * FROM customers_info WHERE first_name LIKE %:first_name%");
$query->bindParam(':first_name', $_POST['search1']);
$query->execute();
$result = $query->fetchAll();

var_dump($result);

Moreover to get it work with few attributes like email etc use for example this query:

$query = $db->select("SELECT * FROM customers_info WHERE first_name LIKE %:search% OR email LIKE %:search% OR second_name LIKE %:search%");
$query->bindParam(':search', $_POST['search1']);

Collectives™ on Stack Overflow

PHP search in MySQL

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related