search and display in form PHP JQUERY AJAX

Question

I'm trying to search form input value in the mysql database and if the input value exist in the database then i want to display that matching input row details in the same form. Please Help i guess i'm doing something wrong in my php code.

Thanks

index.php

  <div class="col-sm-6">
    <div class="form-group">
      <label for="campaignname">Link</label>
      <input type="text" class="form-control" id="link" name="link" placeholder="Link" required>
    </div>
  </div>

  <div class="col-sm-4">
    <div class="form-group">
      <label for="campaignname">First Name</label>
      <input type="text" class="form-control" id="suppfirstname" name="suppfirstname" placeholder="First Name" required>
     </div>
   </div>
   <div class="col-sm-4">
     <div class="form-group">
       <label for="campaignname">Last Name</label>
       <input type="text" class="form-control" id="supplastname" name="supplastname" placeholder="Last Name" required>
     </div>

Jquery to call Ajax

<script>
 $(document).ready(function(){
      $('#link').change(function(){  
           var link = $(this).val();
           $.ajax({  
                url:"php_action/addnewlead/getlinkdata.php",  
                method:"POST",  
                data:{link:link},
                success:function(response){  
                            $("#suppfirstname").val(response.firstname);
                            $("#supplastname").val(response.lastname);
                }  
           });  
      });
 });
</script>

getlinkdata.php

<?php 

 $connect = mysqli_connect("localhost", "root", "", "test");  
 $output = '';
 if(isset($_POST["link"]))  
 {  
      if($_POST["link"] != '')
      {  
           $sql = "SELECT * FROM customertable WHERE link = '".$_POST["link"]."'";

      }  
      else
      {  
           $sql = "SELECT * FROM customertable WHERE link = 'jesusischrist'";
           // I dont want to load any data here so used wrong code

      }
      $result = mysqli_query($connect, $sql);  
      while($row = mysqli_fetch_array($result))  
      {  
           $output = $result;
      }  
      echo $output;
 }

`$output = $result;` this line is wrong. Should be `$output = $row['FIELDNAME'];` Additionally you don't need a loop if there is only 1 result. — modsfabio, May 18 '17 at 12:43
how user will enter exact name which is there in database?you could give dropdown list or jquery autocomplete — lalithkumar, May 18 '17 at 12:46
using user's data directly in a query like this is much more than dangerous! you should *really* consider using [PPS : Prepared Parameterized Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). This will help [Preventing SQL injection](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — OldPadawan, May 18 '17 at 12:47

score 0 · Answer 1 · answered May 18 '17 at 13:45

So to start, as someone mentioned you should really be sanitizing your inputs, prepared statements are also a good practice to get into. I altered your original code a bit here, as there is no reason to query the db when you know the link is missing. I think that part of your issue is you were echoing your output at the end, you can't echo array elements (use print_r or var_dump). I wrapped the response portion in a JSON encode to turn it into a Javascript parseable string, that allows you to keep the key/value structure intact.

<?php 
try {
    if (isset($_POST['link'] && !empty($_POST['link']) {
        $mysqli = connect();

        // Sanitize your inputs. I'm guessing link is a string?
        $link = filter_var($_POST['link'], FILTER_SANITIZE_STRING);
        // create query string for prepared statement
        $sql = "SELECT firstname, lastname FROM customertable WHERE link = ? LIMIT 1";

        // prepare statement and bind variables
        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param('s', $link);
        $stmt->execute();

        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $stmt->close();

        sendResponse(200, $row);
    }
    sendResponse(400, ['status' => 'Link not supplied']);
} catch (\Exception $e) {
    sendResponse(500, ['status' => $e->getMessage()]);
}

/**
 * Sends JSON encoded response to client side with response http code.
 */
function sendResponse($code, $response)
{
    http_response_code($code);
    echo json_encode($response);
    exit();
}

/**
 * Handles connecting to mysql.
 *
 * @return object $connect instance of MySQLi class
 */
function connect()
{
    try {
        $connect = new \mysqli("localhost", "root", "", "test");
        return $connect;
    } catch (mysqli_sql_exception $e) {
        throw $e;
    }
}

to update your javascript for that i might try something like this:

jQuery(document).ready(function($){
  $('#link').on('change', function(){  
    let link = $(this).val()
    ,   jqxhr = $.post(url:"php_action/addnewlead/getlinkdata.php", { link: link }); 

    jqxhr.done((response) => {
      $("#suppfirstname").val(response.firstname);
      $("#supplastname").val(response.lastname);
    });
    jqxhr.fail((error) => { throw new Error(`${error.status}: ${error.text}`)});
  });
});

search and display in form PHP JQUERY AJAX

1 Answers1