Search and Display result with database

Question

I have MySQL Database with tablename 'user' . I want to search data on the basis of "ID"number. For example: If I want to search an ID say 14, it should display all the record(firstname, lastname, gender, country,email etc.)for ID=14 .

Here is my code:

data.php

<?php
require_once ('connect.php');
if(isset($_POST['done']))
{
    $FirstName = $_POST['firstname'];
    $LastName = $_POST['lastname'];
    $DateofBirth =$_POST['dateofbirth'];
    $Email = $_POST['email'];
    $Gender = $_POST['gender'];
    $Country = $_POST['country'];
    $Website = $_POST['website'];
    $Bio = $_POST['bio'];

$CreateSql="INSERT INTO `user` (`First Name`, `Last Name`, `Gender`,`Date of Birth`, `Country`, `Email`, `Website`, `Bio`,`Created Date`)
        VALUES ('$FirstName', '$LastName', '$Gender','$DateofBirth', '$Country', '$Email', '$Website', '$Bio',NOW())";
$insert = mysqli_query($connection,$CreateSql) or die(mysqli_error($connection));
if($insert)
    {
        echo  'Data Inserted';
    }else
    {
        echo  'mysqli_error()';
    }
}

//header ("refresh:2; url=index.php");

?>

<html>
  <head>
    <meta name="viewport" content="width=device-width, initial-scale=1">
      <title>View Data</title>
      <link href="css/bootstrap.min.css" rel="stylesheet">

    <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
    <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
    <!--[if lt IE 9]>
      <script src="https://oss.maxcdn.com/html5shiv/3.7.3/html5shiv.min.js"></script>
      <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
  </head>
    <body>
      <table width="1200" class="table-bordered" border="1" cellpadding="1" cellspacing="5">
        <tr>
          <th><center>ID</center></th>
          <th><center>First Name</center></th>
          <th><center>Last Name</center></th>
          <th><center>Gender</center></th>
          <th><center>Date of Birth</center></th>
          <th><center>Registration Date</center></th>
          <th><center>Action</center></th>
        </tr>

<?php
$select = "SELECT * FROM `user` ";
$record = mysqli_query($connection,$select);
while($data = mysqli_fetch_array($record))
{
?>
  <tr>
    <td><center><?php echo $data['ID']; ?></center></center></td>
    <td><center><?php echo $data['First Name']; ?></center></td>
    <td><center><?php echo $data['Last Name']; ?></center></td>
    <td><center><?php echo $data['Gender']; ?></center></td>
    <td><center><?php echo $data['Date of Birth']; ?></center></td>
    <td><center><?php echo $data['Created Date']; ?></center></td>
    <td><center><a href="view.php?ID=<?php echo $data['ID']; ?>">View</a>
            <a href="search.php?ID=<?php echo $data['ID']; ?>">Search</a>
        <a href="edit.php?ID=<?php echo $data['ID']; ?>"><span class="glyphicon glyphicon-edit" aria-hidden="true">Edit</span></a>
        <a href="delete.php?ID=<?php echo $data['ID']; ?>"><span class="glyphicon glyphicon-remove" aria-hidden="true">Delete</span></a></center></td>
  <tr>
<?php
}
?>
  </table>
  <br>
  <div class="row">
    <div class="col-md-12">
      <center><a href="index.php"><input type="submit" name="done" class="btn btn-success btn-send" value="Add User"></a></center>

            <a href="search.php?ID=<?php echo $data['ID']; ?>"><input type="submit" name="search" class="btn btn-success btn-send" value="Search ID"></a>
            <input type="text" name="search" value="<?php echo $data['ID']; ?>"

search.php

<?php
require_once ('connect.php');
 $ID = $_GET['ID'];
?>
<html>
    <head>
      <meta name="viewport" content="width=device-width, initial-scale=1">
        <title>View Data</title>
        <link href="css/bootstrap.min.css" rel="stylesheet">

      <!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
      <!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
      <!--[if lt IE 9]>
        <script src="https://oss.maxcdn.com/html5shiv/3.7.3/html5shiv.min.js"></script>
        <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
      <![endif]-->
    </head>
      <body>
        <table width="1200" border="2" cellpadding="1" cellspacing="1">
          <tr>
            <th><center>ID</center></th>
            <th><center>First Name</center></th>
            <th><center>Last Name</center></th>
            <th><center>Email</center></th>
            <th><center>Gender</center></th>
            <th><center>Date of Birth</center></th>
            <th><center>Country</center></th>
            <th><center>Website</center></th>
            <th><center>Bio </center></th>
            <th><center>Registration Date</center></th>
          </tr>

<?php
$search = mysqli_query($connection,"SELECT * FROM `user` WHERE `ID` = '$ID' ");
while($data= mysqli_fetch_array($search))
{
?>
  <tr>
    <td><center><?php echo $data['ID']; ?></center></center></td>
    <td><center><?php echo $data['First Name']; ?></center></td>
    <td><center><?php echo $data['Last Name']; ?></center></td>
    <td><center><?php echo $data['Email']; ?></center></td>
    <td><center><?php echo $data['Gender']; ?></center></td>
    <td><center><?php echo $data['Date of Birth']; ?></center></td>
    <td><center><?php echo $data['Country']; ?></center></td>
    <td><center><?php echo $data['Website']; ?></center></td>
    <td><center><?php echo $data['Bio']; ?></center></td>
    <td><center><?php echo $data['Created Date']; ?></center></td>
  <tr>
<?php

if($search)
      {
        echo  'Record Found';
      }else
      {
        echo  'mysqli_error()';
      }
}
?>
</table>
</body>
</html>

With the above code I am getting this output:It shows no record for the selected ID.

[Little Bobby](http://bobby-tables.com/) says **[your script is at risk for SQL Injection Attacks](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)**. Learn about [Prepared Statements](http://en.wikipedia.org/wiki/Prepared_statement) for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even **[escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string)** is not safe! — GrumpyCrouton, Aug 07 '17 at 19:40
Some sensible code indentation would be a good idea. It helps us read the code and more importantly it will help **you debug your code**. [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — GrumpyCrouton, Aug 07 '17 at 19:41

score 0 · Answer 1 · answered Aug 07 '17 at 20:54

Debug your code with error_reporting(E_ALL);. It can help you to find the error. Put it on the top of your search.php
echo your SQL statement to check if its valid. You can also copy and run it in phpadmin or whatever you use.

echo $sql = "SELECT * FROM ...

PHP error_reporting

Search and Display result with database

1 Answers1