CODEWITHSUNDEEP
phpjquerymysqlajaxweb
Jason Fung
Jason Fung

Reputation: 69

AJAX script not parsing variable to php correctly

UPDATE 19/01/2017 - PROBLEM RESOLVED - Removed 'intval' on get request within get.php file and the value q is now capturing the correct data.

What i'm trying to achieve can be summed up in 2 points:

1.) A dropdown box which dynamically populates according to a MySql table

2.) Depending on whats chosen in the dropdown the textfield below will display a corresponding id via a SQL query.

I've been trying to follow W3Schools guide and modifying it accordingly to match my needs, but i'm getting stuck.

Here is what I currently have:

PHP -

<?php
    mysql_connect('localhost', 'root', '');
    mysql_select_db('core_log');

    $sql = "SELECT local_authority FROM ons_code_tbl";
    $result = mysql_query($sql);

    echo "<tr>";
    echo "<td> Local Authority: </td>";
    echo "<td>";
    echo "<select name='local_authority'onchange='showLocal_authority(this.value)'>";
    echo '<option value=""></option>';
       while ($row = mysql_fetch_array($result)) {
            echo "<option value='" . $row['local_authority'] . "'>" . $row['local_authority'] . "</option>";
        }
    echo "</select>";
    echo "</td>";
    echo "</tr>";
?>

Script -

<script>
        function showLocal_authority(str) {
            if (str == "") {
                document.getElementById("txtHint").innerHTML = "";
                return;
            } else { 
                if (window.XMLHttpRequest) {
                    // code for IE7+, Firefox, Chrome, Opera, Safari
                    xmlhttp = new XMLHttpRequest();
                } else {
                    // code for IE6, IE5
                    xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
                }
                xmlhttp.onreadystatechange = function() {
                    if (this.readyState == 4 && this.status == 200) {
                        document.getElementById("txtHint").innerHTML = this.responseText;
                    }
                };
                xmlhttp.open("GET","get.php?q="+str,true);
                xmlhttp.send();
            }
        }
</script>

get.php -

<?php
   $q = intval($_GET['q']);

   $con = mysqli_connect('localhost','root','','core_log');
   if (!$con) {
       die('Could not connect: ' . mysqli_error($con));
   }

   mysqli_select_db($con,"ons_code_tbl");
   $sql="SELECT ons_code FROM ons_code_tbl WHERE local_authority = '".$q."'";
   $result = mysqli_query($con,$sql);
   $row = mysqli_fetch_array($result);
   $name = $row['ons_code'];

   echo "<td>" . $q . "</td>";

   mysqli_close($con);
?>

I did a test and simply echoed the value q to see what its outputting, it should be the value of whatever they pick in the dropdown, but instead its displaying 0?

In the screenshot below, it should display 'Amber Valley' with the correct ONS code pulled from the MySQL table.

mysqlajax

If anyone can tell me why the value q is not storing 'Amber Valley' for example, that would be great, thanks!

Upvotes: 1

Views: 78

Answers (1)

Felippe Duarte
Felippe Duarte

Reputation: 15131

Replace this line:

$q = intval($_GET['q']);

for

$q = $_GET['q'];

Then make a query with prepared statement, to avoid SQL Injection:

mysqli_select_db($con,"ons_code_tbl");
$sql="SELECT ons_code FROM ons_code_tbl WHERE local_authority = ?";
$stmt = mysqli_prepare($con, $sql);
mysqli_stmt_bind_param($stmt, "s", $q);
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $ons_code);
mysqli_stmt_fetch($stmt);

Just replace echo "<td>" . $q . "</td>"; for echo "<td>" . $ons_code . "</td>";

Upvotes: 1

Related Questions