0

I am trying to develop a program that search in mysql database and return the search results but in when I hit the submit it displays all the results I don't know why this is happening, so any help will be appreciated 

    <?php
include('connect2.php');
 ?>

<?php
echo "<center>";
echo "<table border = '3'>";
echo "<thead>";
echo "<tr>";
echo "<th><u>id</u></th>";
echo "<th><u>name</u></th>";
echo "<th><u>countrycode</u></th>";
echo "<th><u>district</u></th>";
echo "<th><u>population</u></th>";

echo "</center>";
echo "</tr>";
echo "</thead>";

    $name = isset($_POST['search']) ? $_POST['search'] : '';
    $result = mysqli_query($conn, "SELECT id, name, district, population, countrycode FROM city WHERE concat(id, name, district, population, countrycode) LIKE '%$name%' ");
    if (mysqli_num_rows($result) > 0) {
      while ($row = mysqli_fetch_array($result))   {
        echo "<tr><td>" . $row['id'] . "</td><td>" . $row['name'] . "</td><td>" . $row['countrycode'] . "</td><td>" . $row['district'] . "</td><td>" . $row['population'] . "</td></th>";
   }
}
else {
    header( "Location:error page.html" ); die;
}
echo "</table>";
mysqli_close($conm);
?>

the search page search.php

<?php
include('connect2.php') ;
?>

<!DOCTYPE html>
<head>
  <title>city results</title>
  <link href="style-table.css" rel="stylesheet">
  <link href="animate.css" rel="stylesheet">
  <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet" integrity="sha384-wvfXpqpZZVQGK6TAh5PVlGOfQNHSoD2xbE+QkPxCAFlNEevoEH3Sl0sibVcOQVnN" crossorigin="anonymous">
  <script>
  function validateform() {
      var x = document.forms["myform"]["input"].value;
      if (x == "") {
          alert("input must be filled out");
          return false;
      }
  }
  </script>
</head>

<body>
  <form method="POST" action="display-results-city.php" name="myform" onsubmit="return validateform()">
<input type="text" name="input" placeholder="search.........">
<button  name="submit" type="submit">go</button>
</form>


<?php
echo '<div class="animated fadeInUp">';
echo '<a href="search.php"><i class="fa fa-arrow-left fa-lg" aria-hidden="true" ></i></a>';

echo "<div id='records'>";
echo "<table border = '0'>";

echo "<tr>";
echo "<th>ID</th>";
echo "<th>name</th>";
echo "<th>countrycode</th>";
echo "<th>district</th>";
echo "<th>population</th>";
echo "</tr>";

echo "</div>";
echo '</div>';

$sql = "select * from city limit 50";
$result = $conn->query($sql);

if( $result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row['ID']. "</td><td>" . $row['Name']. "</td><td>" . $row['CountryCode'] . "</td><td>" . $row['District'] . "</td><td>" . $row['Population'] . "</td></tr>";
  }
} else {
  echo " 0 results";
}

echo '</table>';
$conn->close();
 ?>


 </body>
 </html>
chris85
  • 23,846
  • 7
  • 34
  • 51
Puzant Bakjejian
  • 67
  • 4
  • 9
  • See `name="input"` vs. `$_POST['search']`. Use error reporting and I think you'll get an undefined variable notice. You also are open to SQL injections. Use parameterized queries. Your `header` also will fail because you are outputting before the `header` call. Final note, https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html. – chris85 Mar 03 '17 at 21:55
  • **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Mar 03 '17 at 22:00
  • Possible duplicate of [PHP: "Notice: Undefined variable", "Notice: Undefined index", and "Notice: Undefined offset"](http://stackoverflow.com/questions/4261133/php-notice-undefined-variable-notice-undefined-index-and-notice-undef) – chris85 Mar 03 '17 at 22:37

1 Answers1

0

Try running this query..

   $result = mysqli_query($conn, "SELECT id, name, district, population, countrycode FROM city WHERE UPPER(concat(id, name, district, population, countrycode)) LIKE UPPER('%$name%') ");

It will match both "Name" and "name".

Muhammad Saqlain
  • 2,112
  • 4
  • 33
  • 48