Need help about Search Bar with dropdown result using mysql query PDO

Question

I have search bar on my facebox. It will search for the lastName or firstName every time the user will input a character or name in the textbox.

For more clarification here's the screenshot:

and here's the code:

UPDATE

This is the code I found in the web on those 3 days of in-activity

this code made by Malik Naik (I modified some of his code)

addBookRecord.php

<?php
session_start();
$recordType = $_GET['status'];  
?>
<!-- this code made by Malik Naik -->
<script>
function searchResult(string){
    var xmlhttp;
    if(window.XMLHttpRequest){
        xmlhttp = new XMLHttpRequest();
    }else{
        xmlhttp = new ActiveXObject("XMLHTTP");
    }
    xmlhttp.onreadystatechange = function(){
        if(xmlhttp.readyState == 4 && xmlhttp.status == 200){
            document.getElementById("result").innerHTML = xmlhttp.responseText;
        }
    }
    xmlhttp.open("GET", "search.php?search="+string, true);
    xmlhttp.send(null);
}
</script>

<style>
    #result{
        width: 350px; margin: 0 auto; max-height: 150px; overflow: hidden;
    }
</style>

<!DOCTYPE html>
<html lang="en">
<head>
<!--<link rel="stylesheet" type="text/css" href="../css/bootstrap.min.css">-->
<link rel="stylesheet" type="text/css" href="../css/bootstrap-glyphicons.css">
</head>

<span style="color:#B22222"><strong>Add Record</strong></span>
<form action = "" method="post">
 <div style="margin-top: 10px; margin-left: 10px;">
  <strong>Name:</strong>
  <!--<form name="form1" method="post">-->
   <div class="input-group stylish-input-group">
    <input type="text" class="form-control" name="searchStud" placeholder="Search" onkeydown="searchResult(this.value)" autocomplete="off">
    <span class="input-group-addon">
     <span class="glyphicon glyphicon-search"></span>
    </span>
   </div>
    <div id="result"></div>
  <!--</form>-->
  <?php
   if ($recordType=='book'){  
  ?>
    <strong>Item:</strong><br>
    <input type="text" name="addBookItem" value="e-math (K to 12)"> <input type="text" name="searchID" value="<?php echo $_SESSION['addBorrowID'];?>"><br>
    <strong>Description:</strong> <br>
    <input type="text" name="addBookDescription" value="Worktext in Mathematics"><br>
    <strong>Author:</strong> <br>
    <input type="text" name="addBookAuthor" value="Orlando A. Orence & Marilyn O. Mendoza"><br>
    <strong>Publisher:</strong> <br>
    <input type="text" name="addBookPublisher" value="REX Book Store"><br>
    <strong>ISBN:</strong> <br>
    <input type="text" name="addBookISBN" value="9789712361982"><br>
  <?php
   }
   else{
  ?>
    <strong>Item:</strong> <br>
    <input type="text" name="addBookItem" value="e-math (K to 12)"><br>
    <strong>Model:</strong> <br>
    <input type="text" name="addBookAuthor" value="Orlando A. Orence & Marilyn O. Mendoza"><br>
    <strong>Serial:</strong> <br>
    <input type="text" name="addBookPublisher" value="REX Book Store"><br>
  <?php
   }
  ?>
  <br>
 </div>
    <button class="btn btn-success btn-block btn-large" name="addRecordButton" >Save Changes</button>
</form>

search.php

<?php
session_start();
include("../db/dbCon.php");

if(isset($_GET['search']) && $_GET['search'] != ''){
    $search=$_GET['search'];
    $qry = $conn->prepare("
                            SELECT e.enrld_id, CONCAT(i.lastName,', ', i.firstName,' ',i.middleName)
                            AS fullName
                            FROM user_info i
                            JOIN enrolled e ON e.userID=i.userID 
                            WHERE i.lastName LIKE '%$search%' OR i.firstName LIKE '%$search%'
                        ");
    $qry->execute();
    $result = $qry->fetchAll(PDO::FETCH_ASSOC); 
    foreach($result as $row){
        if ($row['enrld_id']!=0){
                //$_SESSION['addBorrowID']=(int)$row['enrld_id'];
                $fullName = $row['fullName'];
                echo '<div id="result" onclick="getSearchID("'.$row['enrld_id'].'")">'.$fullName.'</div>';
        }
    }
}

?>

<?php
    function getSearchID($enrldID){
        $_SESSION['addBorrowID'] = $enrldID;
    }
?>

This scenario is just to test if the $_SESSION['addBorrowID'] has a value

if (isset($_POST['addRecordButton'])){
echo '<script type="text/javascript">alert("'.$_SESSION['addBorrowID'].'");</script>';
}

MAIN QUESTION

My problem is I can't store the enrld_id in the searchID textbox whenever I click the result below the search bar... I use the Mouse event but it's not working:

foreach($result as $row){
        if ($row['enrld_id']!=0){
                //$_SESSION['addBorrowID']=(int)$row['enrld_id'];
                $fullName = $row['fullName'];
                echo '<div id="result" onclick="getSearchID("'.$row['enrld_id'].'")">'.$fullName.'</div>';
        }
    }

<?php
function getSearchID($enrldID){
    $_SESSION['addBorrowID'] = $enrldID;
}
?>

but the result is empty. I want to pass the value of $_SESSION['addBorrowID'] to this textbox

------- END -------

this is OPTIONAL to answer

Here is my little question:

How can I prevent an SQL injection using a LIKE query? I know how to use bindParam but I don't know if my theory is right:

lastName LIKE '%?%' OR firstName LIKE '%?%'");
$qry->bindParam(1, $find);
$qry->bindParam(2, $find);

Is this right?

what is that `this.value` in your PHP script? and regarding the `LIKE` clause, no you don't need to put the wildcards in the prepared statement, just take it out and just use the placeholders alone. http://stackoverflow.com/questions/11117134/implement-like-query-in-pdo — Kevin, Jan 29 '16 at 00:30

Iwnnay · Answer 1 · 2016-02-01T15:56:44.440

ANSWER TO PRIMARY QUESTION

Here is the problem that you're running into. You're trying to connect front-end code with server-side code in a way that does not work. Server side code processes the PHP and then hands the resulting code to the browser. At this point only front-end or client-side code is available. This is the code snippet that I'm talking about:

<?php
session_start();
include("../db/dbCon.php");

if(isset($_GET['search']) && $_GET['search'] != ''){
    $search=$_GET['search'];
    $qry = $conn->prepare("
                            SELECT e.enrld_id, CONCAT(i.lastName,', ', i.firstName,' ',i.middleName)
                            AS fullName
                            FROM user_info i
                            JOIN enrolled e ON e.userID=i.userID 
                            WHERE i.lastName LIKE '%$search%' OR i.firstName LIKE '%$search%'
                        ");
    $qry->execute();
    $result = $qry->fetchAll(PDO::FETCH_ASSOC); 
    foreach($result as $row){
        if ($row['enrld_id']!=0){
                //$_SESSION['addBorrowID']=(int)$row['enrld_id'];
                $fullName = $row['fullName'];
                echo '<div id="result" onclick="getSearchID("'.$row['enrld_id'].'")">'.$fullName.'</div>';
        }
    }
}

?>

<?php
    function getSearchID($enrldID){
        $_SESSION['addBorrowID'] = $enrldID;
    }
?>

You're adding an onclick="getSearchId... but your defining the function in <?php ... >? tags. This is no longer accessible by the time the page is delivered to the browser. If you have time and see yourself doing a lot of front end development I would highly recommend taking thie quick course; it will save you a lot of headaches and is great investment. This problem would be become apparent if you had checked the HTML on the page, so forth and so on. At the top of your code I see you've included a very barebones AJAX call. you'll want to utilize something like that in this case... Here is a rough idea of what you'll want to do.

<script>
    var xmlhttp;
    function ajaxCall (string, callback) {
        if(window.XMLHttpRequest){
            xmlhttp = new XMLHttpRequest();
        }else{
            xmlhttp = new ActiveXObject("XMLHTTP");
        }
        xmlhttp.onreadystatechange = callback
        xmlhttp.open("GET", , true);
        xmlhttp.send(null);
    }

    function searchResult(string){
        ajaxCall("search.php?search="+string, function(){
            if(xmlhttp.readyState == 4 && xmlhttp.status == 200){
                document.getElementById("result").innerHTML = xmlhttp.responseText;
            }
        });
    }

    function getSearchId(id) {
        ajaxCall( 'path_to_php_file_that_returns_search_id?searchId=' + id, function() {
            if(xmlhttp.readyState == 4 && xmlhttp.status == 200){
                //code here to put the id in the right spot
            }
        });
    }
</script>

This will be the file_that_returns_search_id.php

<?php
    session_start();
    echo $_SESSION['addBorrowID'] = $_GET['searchId'];

Now that I look at this, I have no idea what you're trying to accomplish. This looks a lot more like a setting of searchId, but I'm just going off what's here.

ANSWER TO OPTIONS QUESTION

Am I reading this right? Does 99% of this stuff not have to do with your question? I feel like it's one of the trick questions where they tell you several exchanges of people getting on and off a bus and then ask you how old the bus driver is.

If I'm right your question only needed this part:

$qry = $conn->prepare("
                    SELECT userID, lastName, firstName, middleName
                    FROM user_info WHERE lastName LIKE '%".$find."%' OR firstName LIKE '%".$find."%'
                ");
$qry->execute();

To answer your question. I'm going to assume that your $conn variable is using PDO to query the database. If that's the case then the information you're looking for is here. But your new code could look something like this.

$qry = $conn->prepare(
  "SELECT userId, lastName, FirstName, MiddleName
  FROM user_info where lastName LIKE '%?%' OR firstName LIKE '%?%'"
);

$qry->execute([$find, $find]);

please re-check my code.. I update it so that you can also edit your answer :) sorry for the confusion — MeSH, Feb 01 '16 at 13:48
There are a couple things that still don't make sense to me, but I think that's because you're going to have to write more code past this point. I hope this helps you get to a point where you can continue on. — Iwnnay, Feb 01 '16 at 15:58
thanks for the snippet! The textbox beside the item textbox is to test if the code will work that's my target by click the name below search bar the id will pass to that textbox... i am vbnet users so i think that will work... I forgot I'm dealing with php/html that can't do a live changes not like vbnet... please keep in touch for some of my clarification about your code.... thanks for the reply I really appreciate it :) — MeSH, Feb 02 '16 at 00:17
I tried your code last night but it's is not working... the dropdown is gone. I use my original code then I use your function getSearchId(id) function... Base on w3school tutorials the "onmouseup" mouse key event is available to all HTML elements except for: "Supported HTML tags: All HTML elements, EXCEPT: , ,
, , , , <meta/>, <param/>, <script>, <style>, and <title>" so I use "<p>" tag to activate the function but it's not working.</script> — MeSH, Feb 03 '16 at 10:44
This is an incorrect implementation of a prepared statement for a LIKE condition with wildcards. Researchers must not use this advice. See: https://stackoverflow.com/q/18527659/2943403 and https://stackoverflow.com/q/13832941/2943403 and many more. — mickmackusa, Aug 17 '21 at 21:45

Need help about Search Bar with dropdown result using mysql query PDO

1 Answers1