Display address associated with a city in a dropdown using Ajax

Question

(I will just try to show the relevant parts)

I am trying to follow the Ajax example given here:

https://www.w3schools.com/xml/tryit.asp?filename=tryajax_database

https://www.w3schools.com/xml/ajax_database.asp

This is the table I am using right now:

My interface, in summary my $rowAddress gets the column from my table (merchant_address)

   <div class="row">
     <div class="col-md-12">
       <select name="province-list" onchange="showAddress(this.value)">
       <?php
         foreach($lstAddress as $rowAddress) {
           echo '<option value="'.$rowAddress['city'].'">'.$rowAddress['province'].' - '.$rowAddress['city'].'</option>';
         }
        ?>
       </select>
     </div>
     <div class="col-md-12">
       <div id="city_address">Clinic Address Goes Here</div>
     </div>
   </div>

My for each loop is already doing its job at displaying the options available (inspect element):

Im trying to display the addresses for each city:

ajax-list-address.php

<?php
  $routePath = "../";

  require_once($routePath . "_config/db.php");
    $dbConfig = new config_db();
    $db = $dbConfig->init();

  $display_address = $db->prepare("SELECT clinic_address FROM merchant_address WHERE city = ?");

  $stmt = $db->prepare($display_address);
  $stmt->bind_param("s", $_GET['q']);
  $stmt->execute();
  $stmt->store_result();
  $stmt->bind_result($clinic_address);
  $stmt->fetch();
  $stmt->close();

  echo "<p>".$clinic_address."</p>"
?>

my ajax code:

<script>
  function showAddress(str) {
    var xhttp;
    if (str == "") {
      document.getElementById("city_address").innerHTML = "";
      return;
    }
    xhttp = new XMLHttpRequest();
    xhttp.onreadystatechange = function() {
      if (this.readyState == 4 && this.status == 200) {
        document.getElementById("city_address").innerHTML = this.responseText;
      }
    };
    xhttp.open("GET", "ajax-list-address.php?q="+str, true);
    xhttp.send();
  }
</script>

I get the error shown below when I select something from the dropdown. Would like to know what I am missing, already using "city_address" and "showAddress" in my ajax javascript.

Warning: PDO::prepare() expects parameter 1 to be string, object given in C:\xampp\htdocs\mwc_j\mwc_j\cards\ajax-list-address.php on line 11

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean in C:\xampp\htdocs\mwc_j\mwc_j\cards\ajax-list-address.php:12 Stack trace: #0 {main} thrown in C:\xampp\htdocs\mwc_j\mwc_j\cards\ajax-list-address.php on line 12

line 12 is: $stmt->bind_param("s", $_GET['q']);

UPDATE: after calling prepare() only once, I now get this error:

Fatal error: Uncaught Error: Call to undefined method PDOStatement::bind_param() in C:\xampp\htdocs\mwc_j\mwc_j\cards\ajax-list-address.php:11 Stack trace: #0 {main} thrown in C:\xampp\htdocs\mwc_j\mwc_j\cards\ajax-list-address.php on line 11

line 11 is still the same as the one from the top: $stmt->bind_param("s", $_GET['q']);

Which parts of that error message are unclear? How is that error message related to AJAX? — Nico Haase, Sep 12 '19 at 07:45
Your connection is PDO but your code is written for mysqli. So you need a PDO example to follow up. Here is one I wrote: [SELECT query with PDO](https://phpdelusions.net/pdo_examples/select#prepare) — Your Common Sense, Sep 12 '19 at 09:18

matwr · Answer 1 · 2019-09-12T07:46:43.943

0

You are calling prepare() twice. You should only call once.

Solution:

//just store query as string
$display_address = "SELECT clinic_address FROM merchant_address WHERE city = ?";
$stmt = $db->prepare($display_address);
$stmt->bind_param("s", $_GET['q']);
$stmt->execute();

EDIT

To provide a solution for the further issue being reported, either ensure you are sending a string from within your AJAX script, or, force the q param to a string within the PHP script (currently you are receiving a bool, which suggests something wrong in the AJAX, or you are not sending any value at all which is providing a false bool value)

$stmt->bind_param("s", (string) $_GET['q']);

It would be better to fix the ajax script so that it only ever sends a string. One further solution would be to test the GET q value using PHP function is_string and changing the query accordingly during runtime.

edited Sep 12 '19 at 07:46

answered Sep 12 '19 at 07:33

matwr

1,548
1
13
23

updated my question, I get a different error now – Button Press Sep 12 '19 at 07:40
ok, but this answer resolved the original question. I'll take a quick look at the further issue for you. – matwr Sep 12 '19 at 07:41
Take a look in your browser's dev console, under Nertwork. You should see the XHR request and response. Can you confirm the request params. In other words what value are you sending for q? – matwr Sep 12 '19 at 07:43
I suspect you are sending the AJAX request without any value and this is resolving to a boolean FALSE. Whereas the bind method requires a string in your case. You can force the GET q param to a string by casting it (as per updated answer) or better still, ensure trhe AJAX script always sends a valid string. – matwr Sep 12 '19 at 07:49

Display address associated with a city in a dropdown using Ajax

1 Answers1