0

I have a form that currently is able to auto complete base on user input, it queries the MySQL database and successfully lists all possible matches in the table and give suggestions. Now I want to handle rows that do not exist. I am having trouble to get my PHP file to echo the error. Here is what I have so far:

I'm guessing in my auto search function in my javascript in main.php I need to return the error message to the page?

search.php

<?php
//database configuration
$host = 'user';
$username = 'user';
$password = 'pwd';
$name = 'name';

 //connect with the database
$dbConnection = new mysqli($host,$username,$password,$name);


if(isset($_GET['term'])){
            //get search term
    $searchTerm = '%'.$_GET['term'].'%';

    //get matched data from skills table
    if($query = $dbConnection->prepare("SELECT * FROM nametbl WHERE name LIKE ? ORDER BY name ASC")) {

        $query->bind_param("s", $searchTerm);
        $query->execute();
        $result = $query->get_result();

        //$row_cnt = $result->num_rows;
        //echo $row_cnt;
        if($result -> num_rows){
            while ($row = $result->fetch_assoc()) {
                $data[] = $row['name'];
            }

            //return json data
            echo json_encode($data);
            mysqli_close($dbConnection);
        }
        else { echo '<pre>' . "there are no rows." . '</pre>'; }
    }
    else {
        echo '<pre>' . "something went wrong when trying to connect to the database." . '</pre>';
    }


}
?>

main.php

<div id="gatewayInput">
<form method="post">
      <input type="text" id="name" name="name" placeholder="Name..."><br><br>
      <?php 
        include("search.php"); 
      ?>    
</div>

...
...
...

<script src ="../../../jqueryDir/jquery-3.2.1.min.js"></script>
<script src ="../../../jqueryDir/jquery-ui.min.js"></script>
<script type="text/javascript">

//auto search function
$(function() {
      $( "#name" ).autocomplete({
          source: 'search.php'
      });
});
tiger_groove
  • 956
  • 2
  • 17
  • 46
  • Your code is vulnerable to SQL injection, you need to fix this. – Enstage Sep 06 '17 at 00:05
  • @Enstage Where is my code vulnerable to SQL injection and how can it be done? – tiger_groove Sep 06 '17 at 00:09
  • 2
    A query that returns no results is not a failure. Your $query variable is going to contain a result object unless your query actually produces some kind of error (like a syntax error) – Patrick Evans Sep 06 '17 at 00:17
  • @PatrickEvans I see what you mean. Let me try to use num rows like Chris suggested below. – tiger_groove Sep 06 '17 at 00:22
  • @kkmoslehpour you are inserting user input directly into a query. See here: https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Enstage Sep 06 '17 at 00:23
  • First check to see if there are found rows; all this in an `if/else`. Btw, `mysql_error()` doesn't mix with the `mysqli_` api. – Funk Forty Niner Sep 06 '17 at 01:09
  • @PatrickEvans I updated my code above for vulnerabilities, however, after updating it, I am not getting my auto suggest on my page anymore, but I can still connect to the database if I type the full name in the text field and click the button. – tiger_groove Sep 06 '17 at 01:16
  • 1
    Now you should be checking `$query` for an error, your syntax is wrong for the LIKE statement. Your `$searchTerm` variable should include the `%`, ie `$searchTerm = '%'.$_GET['term'].'%'`. and have `LIKE ?` in the query – Patrick Evans Sep 06 '17 at 01:22
  • @PatrickEvans Okay, that worked (updated code)! Now I am seeing the auto suggest when typing the first few letters. However, now I need to notify the user when `searchTerm` doesn't exist. It seems like when I add `$row_cnt = $result->num_rows;` the auto suggest stops working. Is it because I cannot use the same `$result` variable for both the `fetch_assoc()` and the get `num_rows` functions? – tiger_groove Sep 06 '17 at 01:50
  • You can use it for both. `num_rows` is a property while `fetch_assoc()` is a method. The bigger question is what you are doing with `$row_cnt`. – Chris Thorsvik Sep 06 '17 at 03:00
  • @ChrisThorsvik If there is a name in the table that does not exist, then we do something. So something like `if (!num_rows) echo "the name is not in the table";` but every time i add my `num_rows` comment in. The auto suggests stops working in `main.php` – tiger_groove Sep 06 '17 at 04:34
  • So I tried adding the '
    '  '
    ' tags in my code to see if it will show the echo, but still no luck :(     – tiger_groove Sep 06 '17 at 17:38

2 Answers2

0

1.your method type is post in the form in main.php

and in the search.php, you have used "if(isset($_GET['term'])){"

this needs to be fixed I guess. either both needs to be POST or GET.

  1. Again you are using include method which the whole code in search.php will be made in-line and treated as a one file main.php. so you need not use GET or Post method.
  2. How does get and Post methods work is 3.1) you have a html or PHP which submits the data from browser(main.php), and this request is being served by an action class(search.php)

example :- in main.php 3.2) now in search.php you can use something like if(isset($_POST['term'])){

Jag
  • 9
  • 5
0

You can use num_rows (e.g. if ($result -> num_rows)) to see if the query returned anything.

Chris Thorsvik
  • 450
  • 7
  • 15
  • yeah, my number rows doesn't return anything to my page for non-existent rows. When i add the `$row_cnt = $db->num_rows;` in my it stop auto suggesting when i type in the field. And when I remove just that like the auto suggest works again.. weird – tiger_groove Sep 06 '17 at 00:35
  • Instead of storing the `$row_cnt`, why not just put a clause in to notify the user when there is no result? Eg: `if($result -> num_rows) {while (...) {...}} else { // Notify user that there are no suggestions available }` – Chris Thorsvik Sep 06 '17 at 03:04
  • Okay, I updated my code. My code works for existing tables, however, for non-existing tables, where am I supposed to see the error? I don't see it anywhere on the page, nor in the console.. Is it cause I am using GET in my search.php, that is why it cannot send the echo back to the main.php? – tiger_groove Sep 06 '17 at 16:30