CODEWITHSUNDEEP
phpmysqlsearch
compilingJohnny
compilingJohnny

Reputation: 107

PHP search in MySQL

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.

Upvotes: 0

Views: 88

Answers (4)

WinterTime
WinterTime

Reputation: 183

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']);

Upvotes: 0

rharvey
rharvey

Reputation: 2005

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!

Upvotes: 0

iJamesPHP
iJamesPHP

Reputation: 171

This is how your query should be:

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

Should work

Upvotes: 0

rahul
rahul

Reputation: 849

Your query should be like:-

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

Upvotes: 3

Related Questions