Ajax POST to get items for a select

Question

I have some names of places inside a select. Some of this names have apostrophe. When user makes his choice with that select, Ajax makes a query to a table to find occurrencies of shops that match that location. My code is this:

<?php

echo "<select id='location' name='location'>";
echo "<option value='' selected>Select a location</option>";

$sql = "SELECT * FROM locations_list ORDER BY location_name;"; 
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "<option value='" . $row['location_name'] . "'>". $row['location_name'] . "</option>";
    }
}

echo "</select>";

echo "<select id='shop' name='shop'>";
echo "<option value='' selected='shop'>Select location first</option>";

?>

<script>

$(document).ready(function(){
    $('#location').on('change',function(){
        var locationID = $(this).val();
        if(locationID){
            $.ajax({
                type:   'POST',
                url:    'extract_shops.php',
                data:   {'location': locationID},
                success:function(html){
                    $("#shop").html(html);
                }
            });
        }else{
            $('#shop').html('<option value="">Select a location first!</option>');
        }
    });
});

</script>

<?php

echo "</select>";

Things run smoothly when the locations don't have apostrophe in their name. In such cases, Ajax passes location name to extract_shops.php (which has a simple "SELECT * FROM shops WHERE location = '$passedlocationname';") and gets back a list of shops.

But when location has an apostrophed name, Ajax does not pass anything to extract_shops.php. I have put an if inside extract_shops.php that returns this info inside the second select in case Ajax doesn't pass anything.

Where am I wrong?

remove SELECT * FROM locations_list ORDER BY location_name`;`"; — KUMAR, Jul 29 '20 at 15:14
It would be simpler if you inserted the location id as the `option` value attribute in the HTML instead of the name, you wouldn't have [this kind of problem](https://stackoverflow.com/questions/4015345/how-do-i-properly-escape-quotes-inside-html-attributes). (and it would be more proper given the var name `locationID `). Of course you'd need to slightly change your `extract_shops.php` to request by id if not already the case (and that will be quicker) — Kaddath, Jul 29 '20 at 15:25
The code you've shown does not contain any `WHERE` condition. If the missing code contains a query like shown above (`WHERE location = '$passedlocationname'`), you should have a look at prepared statements pretty soon, as this query is widely open for SQL injection — Nico Haase, Jul 29 '20 at 15:48
Maybe I've not been clear enough. My problem is: how to pass through ajax a string with apostrophe. The problem is not the extract_shops.php file. It works perfectly, here and in many other cases. The problem is that ajax does not pass strings inside POST, if they contain apostrophe. Or, more probably, ajax has some difficulties in getting value of a string when it's a string with apostrofe. And the question again is: how can I solve that? @Kaddah: how would you write the code - to get value on change - based on id instead of name? I've a bit of rust when it comes to js... ; ) — gab, Jul 29 '20 at 16:11
@Nico - Oh, I've made a long story short. You're right, that's why the query is already with a prepared statement and has not that syntax. — gab, Jul 29 '20 at 17:25
@gab no js change would be required, only PHP, simply replace `value='" . $row['location_name'] . "` by `value='" . $row['location_id'] . "` (or `$row['id']`, we don't know the name or your id field in the database). Of course, you would probably have to change a little `extract_shops.php` but we don't know what is the code. I assume it would be as simple as changing the `location_name` to `location_id` (or `id`) in your SELECT sql — Kaddath, Jul 30 '20 at 07:56

Ajax POST to get items for a select

0 Answers0