Creating a table within a simple search engine + maximum limit of results

Question

I'm trying to make an simple search engine, and the results are displayed fine as it is now. The only problem I have is I want it to be a little more presentable.

This is the code as it stands (I also have a another .php where it gets the searchval, searchfunction and jQuery)

<?php
mysql_connect ("localhost","root","xxxxxxx") or die ("Connectionissues");
mysql_select_db ("xxxxxxxx") or die("Can't find database");
$output = '';

if(isset($_POST['searchVal']))  {
$searchq = $_POST['searchVal'];
$searchq = preg_replace ("#^0-9a-z#^1"," ",$searchq);

$query = mysql_query("SELECT * FROM ds_OrderItem WHERE idProduct LIKE 
'%$searchq%'") or die("Search incomplete!");
$count = mysql_num_rows ($query);

if($count == 0){
    $output = 'Order have never been made before';



}else{

    while($row = mysql_fetch_array($query)) {

         $idproduct = $row['idProduct'];
         $idorder = $row['idOrder'];
         $title = $row['title'];
         $qty = $row['qty'];


        $output .= '<div> '.$idproduct.' '.$idorder.' '.$title.' '.$qty.' 
</div>';

        }   




  if($_POST['searchVal'] == NULL) {
            $output = "";
        }

   }

}

echo ($output);
?>

To limit the searchresults I tried to make an if statement before the when statement like this:

if($count = <100){
    $output = 'Too many results!';

And for the table I have tried various methods, and I always end up making the simple HTML table, but I cant get the search results to post within the four columns.

Add a limit clause t the end of your SQL statement. ie. ```LIMIT 20```. — Sloan Thrasher, Apr 26 '17 at 09:03
@AhmedGinani I need a code as to how to format the table and where to put it in order for it to post the searchresults inside a table with four colums. (idProduct, idOrder, title and quantity) — easyquestions, Apr 26 '17 at 09:13
@SloanThrasher Do you mean inside the mysql_query, so after the LIKE '%$searchq%' I type LIMIT 20? Thank you for answers — easyquestions, Apr 26 '17 at 09:13
@easyquestions yes put it there. The `LIMIT 20` limits the results of your query to 20 results, so you do not end up with 100's of results — Florian Moser, Apr 26 '17 at 09:34

score 1 · Accepted Answer · edited May 23 '17 at 11:54

1st of all, you should really consider using PPS : Prepared Parameterized Statements. This will help Preventing SQL injection

if you want to limit and order, use the query, therefore, MySQL : LIMIT Query Optimization is useful.

For what you ask, use something like :

<?php

-> SELECT * FROM ds_OrderItem WHERE idProduct LIKE '%$searchq%' ORDER BY title ASC, quantity DESC LIMIT 20
// THIS IS NOT SAFE as you trust user data !!!
// then you have 20 results already ordered
// here, I used title alphabetical order + the most avalaible quantity, but you adapt it...

if ($result_of_num_rows > 0) { /* we have results */
echo"<table>"; // only raw, use a nicely formatted html output :)

while($row = mysql_fetch_array($query)) {

     $idproduct = $row['idProduct'];
     $idorder = $row['idOrder'];
     $title = $row['title'];
     $qty = $row['qty'];

echo"<tr>
      <td> $idorder </td>
      <td> $idproduct </td>
      <td> $title </td>
      <td> $qty </td>
</tr>";
}
echo"</table>";
}
else { echo"nothing yet !"; }
?>

much better would be making use of 'new' standard choosing an API and you have an example below that (I hope) will help you choose a new path :)

<?php

error_reporting(E_ALL); ini_set('display_errors', 1); /* PHP will help us */

/* connexion to db */
$mysqli = mysqli_connect("$host", "$user", "$pwd", "$db");

if (mysqli_connect_errno()) { echo "Error connecting to DB : " . mysqli_connect_error($mysqli); }

$param = "%{$_POST['searchq']}%";

$query = " SELECT idProduct, idOrder, title, qty FROM ds_OrderItem WHERE idProduct LIKE ? ORDER BY title ASC, quantity DESC LIMIT 20 ";

$stmt = $mysqli->prepare($query); /* prepare query */

$stmt->bind_param("s", $param); /* bind param wil sanitize */

print_r($stmt->error_list); /* check for error -> can be removed later */
print_r($stmt->get_warnings()); /* check for error -> can be removed later */
print_r($stmt->error); /* check for error -> can be removed later */

$results = $stmt->execute(); /* execute query */
$stmt->bind_result($idProduct, $idOrder, $title, $qty); /* bounded results */
$stmt->store_result();

if ($stmt->num_rows > 0) { /* we have results */

echo"<table>"; // start table

while($stmt->fetch()){ /* loop through results */

echo"<tr>
  <td> $idOrder </td>
  <td> $idProduct </td>
  <td> $title </td>
  <td> $qty </td>
  </tr>";

  }

echo"</table>";

}
else
{ echo"[ no data ]"; }
?>

@easyquestions : thx for UV :) just FMI which way did you actually use ? 1st (yours, modified) or 2nd (using PPS) ? — OldPadawan, Apr 26 '17 at 10:26

score 0 · Answer 2 · answered Apr 26 '17 at 09:25

Your possible solution is below and you can count variable and put condition in loop before closing while

    <table>
    <?php while($row = mysql_fetch_array($query)) {
         $idproduct = $row['idProduct'];
         $idorder = $row['idOrder'];
         $title = $row['title'];
         $qty = $row['qty'];
     ?>
     <tr>
          <td><?php echo $idproduct; ?></td>
          <td><?php echo $idorder; ?></td>
          <td><?php echo $title; ?></td>
          <td><?php echo $qty; ?></td>
    </tr>
    <?php } ?>
    </table>

Creating a table within a simple search engine + maximum limit of results

2 Answers2