Can't search for a specific record in database

Question

I want to search for a specific record in database and show it on html page. I have inserted a search bar with a search button. I want to enter let's say Student Name and view the record of that student in an html table. But it's not working, It shows nothing in the table. Here is the code for search:

<?php
include("connection.php");
if (isset($_POST['search'])) {
 $valueToSearch=$_POST['valueToSearch'];
 $query="SELECT * FROM 'table_name' WHERE Student_Name LIKE '%".$valueToSearch."%";
 $search_result=filterTable($query);

}
else{
 $query="SELECT * FROM 'table_name'";
 $search_result=filterTable($query);
}
function filterTable($query)
{
 $connect=@mysql_connect("localhost","root","","db");
 $filter_Result=@mysql_query($connect,$query);
 return $filter_Result;
}
?>
<!DOCTYPE html>
<html>
<head>
 <title>Search Record</title>
 <style>
 table,tr,th,td
 {
  border:1px solid black;
 }
</style>
</head>
<body>
 <form action="search.php" method="post">
  <input type="text" name="valueToSearch" placeholder="ValueToSearch"><br><br>
  <input type="submit" name="search" value="Filter"><br><br>
  <table>
     <tr>
                    <th>Id</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Age</th>
                </tr>
                <?php while($row = mysqli_fetch_array($search_result)):?>
                <tr>
                    <td><?php echo $row['id'];?></td>
                    <td><?php echo $row['fname'];?></td>
                    <td><?php echo $row['lname'];?></td>
                    <td><?php echo $row['age'];?></td>
                </tr>
                <?php endwhile;?>
            </table>
        </form>
        
    </body>
</html>

`phpmyadmin` is not a database, it is just an web interface that provides you access to your databases and its tables. Alternatives are `adminer` and stand alone applications `DBeaver` , `MySQL Workbench` `HeidiSQL` and many more... — ino, Mar 21 '18 at 06:40
Maybe you're missing a single quote `'` after the last `%` in your query? For testing, I'd also recommend letting php show errors for easier debugging: https://stackoverflow.com/questions/5438060/showing-all-errors-and-warnings — Dhruv Murarka, Mar 21 '18 at 06:44

Rahul · Answer 1 · 2018-03-21T07:40:31.277

0

escape your keywords with mysql_escape_string();, like second thing don,t use mysql* use mysqli or pdo because mysql* has been removed from php 7.*

$valueToSearch= mysqli_real_escape_string($connect,$_POST['valueToSearch']);

after that use this query

$query="SELECT * FROM table_name WHERE Student_Name LIKE '%$valueToSearch%'";

because you have syntax error if you echo it you need to get like this

SELECT * FROM table_name WHERE Student_Name LIKE 'something';

i am giving you example with mysqli paste it in connection.php

$connect=mysqli_connect("localhost","root","","db");

now your code should be

include("connection.php");
if (isset($_POST['search'])) {
    $valueToSearch= mysqli_real_escape_string($connect,$_POST['valueToSearch']);
    $query="SELECT * FROM table_name WHERE Student_Name LIKE '%$valueToSearch%'";
    $search_result=filterTable($query,$connect);

}
else{
    $query="SELECT * FROM table_name";
    $search_result=filterTable($query,$connect);
}
function filterTable($query,$connect)
{

    $filter_Result=mysqli_query($connect,$query);
    if (!$filter_Result) {
        die('query is not valid '.mysqli_error($connect));
    }
    else{
        return $filter_Result;
    }

}

edited Mar 21 '18 at 07:40

answered Mar 21 '18 at 06:49

Rahul

1,617
1
9
18

why are you using function – Rahul Mar 21 '18 at 06:58
To run the query – Cara J Mar 21 '18 at 06:59
you need to remove mysql use mysqli or pdo – Rahul Mar 21 '18 at 07:00
I assume connection is not established in connection.php but in the function below.. – Yuri Mar 21 '18 at 07:16
how can you assume like that – Rahul Mar 21 '18 at 07:17
Because he is establishing the connection in the function `filterTable()` – Yuri Mar 21 '18 at 07:17
he is establishing the connection in the function because that is not accessible in function – Rahul Mar 21 '18 at 07:18
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/167234/discussion-between-rahul-shrivastava-and-yuri). – Rahul Mar 21 '18 at 07:24
It shows undefined variable connect and query is not valid – Cara J Mar 21 '18 at 07:37
why the query is not valid – Cara J Mar 21 '18 at 07:38
$connect=mysqli_connect("localhost","root","","db"); paste it in connection.php – Rahul Mar 21 '18 at 07:38
I have resolved it but now it's showing query is not valid – Cara J Mar 21 '18 at 07:39
$query="SELECT * FROM table_name WHERE Student_Name LIKE '%$valueToSearch%'"; try it – Rahul Mar 21 '18 at 07:40

Yuri · Answer 2 · 2018-03-21T07:27:23.797

Forget any mysql_ feature. You are establishing connection to database with mysql but trying to read results with mysqli_.. Try with

<?php
include("connection.php");
if (isset($_POST['search'])) {
    $valueToSearch=$_POST['valueToSearch'];
    $query="SELECT * FROM 'table_name' WHERE Student_Name LIKE '%".$valueToSearch."%'";

    $search_result=filterTable($query);

}
else{
    $query="SELECT * FROM 'table_name'";
    $search_result=filterTable($query);
}
function filterTable($query) {
    $connection = new mysqli("localhost", "root", "","db");
    $filter_Result = $connection->query($query)
    return !$filter_Result ? null : $filter_Result;
}
?>

Then, down in your form, replace:

while ($row = $search_result->fetch_array()) {
    ...
    ...
}

Hint:

Consider about moving your connection establishment from filterTable() function to the connection.php file and use GLOBAL $connection; in the function
Moving your connection to that file will allow you to escape string at any time with $myVar = $mysqli->real_escape_string( $myVar ) which will prevent injection

Can't search for a specific record in database

2 Answers2