How do I display correct data from mySQL?

Question

I am trying to query a database and display the results. The query is based on information from an html form. However, when I enter a name such as john in the form, for which the table has 2 entries with that name, I get 0 results. I don't know what the problem is.

Here is the html form:

<form action="cust_details_search.php" method="post">         
Name :
<input type="text" name="name_search" id="name_search" >
Email :
<input type="email" name="email_search" id ="email_search" >

Phone no. :

 <input type="phone" name="phone_search" id="phone_search" >


Address :
<input type="text" name="address_search" id="address_search" >

City :
<input type="text" name="city_search" id="city_search" >

State :
<input type="text" name="state_search" id="state_search" >
<br> <br>
Country :
<input type="text" name="country_search" id="country_search">

Product Enquired for :
<input type="text" name="prod_search" id="prod_search">  

<input type="submit" value="Submit">

</form>

And the php file:

 <?php 
$server = "127.0.0.1";
$dbUsername = "root";
$dbPassword = "";

//create connection
$dbconn = new mysqli($server, $dbUsername, $dbPassword, $dbname);

 $name_search = $_POST['name_search'];
 $email_search = $_POST['email_search'];
 $phone_search = $_POST['phone_search'];
 $address_search = $_POST['address_search'];
 $city_search = $_POST['city_search'];
 $state_search = $_POST['state_search'];
 $country_search = $_POST['country_search'];
 $prod_search = $_POST['prod_search'];

$run_query = mysqli_query($dbconn, 
            "SELECT * 
            FROM CustomerDetails 
            WHERE (Name  LIKE '%.$name_search.%') 
            OR (`Phone no.` LIKE '%.$phone_search.%') 
            OR (`Address` LIKE '%.$address_search.%') 
            OR (`City` LIKE '%.$city_search.%') 
            OR (`State` LIKE '%.$state_search.%') 
            OR (`Country` LIKE '%.$country_search.%') 
            OR (`Product Enq. For` LIKE '%.$prod_search.%') 
            OR (`Email` LIKE '%.$email_search.%')");

?>
<html>
<head>
<title>Search Resutls</title>
<style>
body {
background-color: rgb(131,41,54);
}
h1 { color:#FFFFFF
        }
h2 {  color:#FFFFFF
        }
p {  color:#FFFFFF
        } 

</style>

</head>
<body>
<center>
<h2> Customer Details </h2>
 <table style="width:100%">
<thead>
<tr>
    <th>Name</th>
    <th>Email</th>
    <th>Phone no. </th>
    <th>Address </th>
    <th>City </th>
    <th>State </th>
    <th>Country</th>
    <th>Product Enquired for </th>
    <th>Follow up details </th>
    </tr>
    </thead>
    <tbody>
<?php 
while($result = mysqli_fetch_assoc($run_query)) {
 ?>
<tr>
<td><?php echo $result['Name'] ?> </td>
<td><?php echo $result['Email'] ?></td>
<td><?php echo $result['Phone no.'] ?></td>
<td><?php echo $result['Address'] ?></td>
<td><?php echo $result['City'] ?></td>
<td><?php echo $result['State'] ?></td>
<td><?php echo $result['Country'] ?></td>
<td><?php echo $result['Product Enq. For'] ?></td>
<td><?php echo $result['Follow Up'] ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</center>
</body>
</html>

Any help is appreciated. Thank you in advance!

What are the dots for? `LIKE '%.$name_search.%')` remove the dots, like so: `LIKE '%$name_search%')` — HTMHell, Jun 24 '18 at 20:44

RiggsFolly · Accepted Answer · 2018-06-24T20:48:23.677

1

You are using the PHP concatenator . but you dont need to in a double quoted string. $variables are automatically expanded in a double quoted string so try

$run_query = mysqli_query($dbconn, 
            "SELECT * 
            FROM CustomerDetails 
            WHERE (Name  LIKE '%$name_search%') 
            OR (`Phone no` LIKE '%$phone_search%') 
            OR (`Address` LIKE '%$address_search%') 
            OR (`City` LIKE '%$city_search%') 
            OR (`State` LIKE '%$state_search%') 
            OR (`Country` LIKE '%$country_search%') 
            OR (`Product Enq For` LIKE '%$prod_search%') 
            OR (`Email` LIKE '%$email_search%')");

ALso some of your column names had a . in them? I assume these column names do not actually contain dots. However if the do, I suggest you remove them by editing your schema.

Your script is wide open to SQL Injection Attack Even if you are escaping inputs, its not safe! Use prepared parameterized statements in either the MYSQLI_ or PDO API's

edited Jun 24 '18 at 20:48

answered Jun 24 '18 at 20:45

RiggsFolly

93,638
21
103
149

+1. Also worth mentioning that although he doesn't need to do that in a double quoted string, it would still work if he did it correctly: `"...LIKE '%".$name_search."%')..." ` – HTMHell Jun 24 '18 at 20:48
@HtmHell In my opinion that just makes the string 10x more complicated and when the query gets complicated almost impossible to debug – RiggsFolly Jun 24 '18 at 20:50
Sure, but my point is that he should understand that a dot inside a string will not make a concatenation, only if he first add another quote/double quote – HTMHell Jun 24 '18 at 20:54

How do I display correct data from mySQL?

1 Answers1