Live search not working with apostrophies php/javascript

Question

I have this live search which works perfect until a company name in my mysql db has an apostrophie in it. an example- loo's up. The search im using is from some-one who has developed it with php5. i have managed to amend the code to work with mine but have failed when it comes to selecting a name from the list with an apostrophie in it. When i select the name, the input box is left blank.

<div>
    <?  print"<form method=\"post\" action=ordernew.php?action=startneworder>"; ?>
        <div>
            Customer Name:
            <br />
            <input type="text" size="30" name="company" value="" id="inputString" onkeyup="lookup(this.value);" onblur="fill();" />
        </div>

        <div class="suggestionsBox" id="suggestions" style="display: none;">
            <img src="images/upArrow.png" style="position: relative; top: -12px; left: 30px;" alt="upArrow" />
            <div class="suggestionList" id="autoSuggestionsList">
                &nbsp;
            </div>
        </div>
        <? print"<input type=submit value=Continue />"; ?>
    </form>
</div>

the code above is what is on my form. the code below is what does all the work.

<?php
require_once("functions.php");

//dbconn(false); //loggedinonly();
// PHP5 Implementation - uses MySQLi. // mysqli('localhost', 'yourUsername', 'yourPassword', 'yourDatabase'); $db = new mysqli($mysql_host, $mysql_user ,$mysql_pass, $mysql_db);

if(!$db) {
    // Show error if we cannot connect.
    echo 'ERROR: Could not connect to the database.';
} else {
    // Is there a posted query string?
    if(isset($_POST['queryString'])) {
        $queryString = $db->real_escape_string($_POST['queryString']);

        // Is the string length greater than 0?

        if(strlen($queryString) >0) {
            // Run the query: We use LIKE '$queryString%'
            // The percentage sign is a wild-card, in my example of countries it works like this...
            // $queryString = 'Uni';
            // Returned data = 'United States, United Kindom';

            // YOU NEED TO ALTER THE QUERY TO MATCH YOUR DATABASE.
            // eg: SELECT yourColumnName FROM yourTable WHERE yourColumnName LIKE '$queryString%' LIMIT 10

            $query = $db->query("SELECT company FROM eventcompany WHERE company LIKE '$queryString%' LIMIT 10");
            if($query) {
                // While there are results loop through them - fetching an Object (i like PHP5 btw!).
                while ($result = $query ->fetch_object()) {
                    // Format the results, im using <li> for the list, you can change it.
                    // The onClick function fills the textbox with the result.

                    // YOU MUST CHANGE: $result->value to $result->your_colum
                    echo '<li onClick="fill(\''.$result->company.'\');">'.$result->company.'</li>';
                    //echo '<li onClick="fill(\"'.$result->company.'\");">'.$result->company.\"</li>';
                }
            } else {
                echo 'ERROR: There was a problem with the query.';
            }
        } else {
            // Dont do anything.
        } // There is a queryString.
    } else {
        echo 'There should be no direct access to this script!';
    }
}?>

this part is in my header.

<script type="text/javascript">
function lookup(inputString) {
    if(inputString.length == 0) {
        // Hide the suggestion box.
        $('#suggestions').hide();
    } else {
        $.post("backend/rpc.php", {queryString: ""+inputString+""}, function(data){
            if(data.length >0) {
                $('#suggestions').show();
                $('#autoSuggestionsList').html(data);
            }
        });
    }
} // lookup

function fill(thisValue) {
    $('#inputString').val(thisValue);
    setTimeout("$('#suggestions').hide();", 200);
}

i think the problem lies in the on click event. it works with & characters but not ' . any suggestions are welcome. many thanks

steve

i tried to display an image but i low rep points. thanks for your response.i get this now loo\'s displayed but when i select the choice input box is still blank. this only happens whith companies with ' in them. i know not many companies have ' in them but its more or a challenge now .

sorted u were nearly there.

$company = addslashes($result->company);
   echo "<li onClick=\"fill('".$company."');\">".$result->company."</li>";

with the help of firebug. many thanks by the way i noticed the first column needed the addslashes and the 2nd one didnt. so i think you may have just got them the wrong way round ;) thanks for the help .

steve

Answer 1

The answer is that the extra quote is messing up your HTML.

Using your company example of "loo's", this line:

echo '<li onClick="fill(\''.$result->company.'\');">'.$result->company.'</li>';

will print the HTML:

<li onClick="fill('loo's');">loo's</li>';

The problem is with the extra single quote here:

onClick="fill('loo's');"

What needs to print instead is:

onClick="fill('loo\'s');"

The simplest way to fix it is to change the PHP to:

$company = addslashes($result->company);
echo "<li onClick=\"fill('".$result->company."');\">".$company."</li>";

I have not tested the code to verify, but that should resolve the issue.

Also, try running the page in Firefox. Install the Firebug addon. You can view what is returned via Ajax in the console tab in Firebug. This will help you in the future.