How do I execute a PHP query on select option choice using AJAX?

Okay I know this has been answered before (Execute PHP script on same page after selecting a dropdown list option using Ajax or JavaScript) but the answers weren't very helpful for someone who has never used AJAX before. How do I run a query like the one created if someone selects an option from the drop down?

<head>
 <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
</head>

<h3>Subject</h3>
<select name="allbooks" >
              <option value="none" ></option>
              <option value="allbooks" >All Books</option>
    </select>

<?php 
$query = "SELECT * FROM books" or die("Error in the consult.." . mysqli_error($connection)); 
    $books = $connection->query($query);
?>

First is, you have to trigger the AJAX request by using Javascript. But I'll be guiding you by using jQuery (a Javascript library).

Your HTML:

<select name="allbooks" id="allbooks">
  <option value="none" ></option>
  <option value="allbooks" >All Books</option>
</select>
<div id="show">
  <!-- ITEMS TO BE DISPLAYED HERE -->
</div>

After that, download jQuery.

Then lets do the script:

<script src="jquery-1.9.1.min.js"></script> <!-- CHANGE THE JQUERY FILE DEPENDING ON THE VERSION YOU HAVE DOWNLOADED -->
<script type="text/javascript">
  $(document).ready(function(){ /* PREPARE THE SCRIPT */
    $("#allbooks").change(function(){ /* WHEN YOU CHANGE AND SELECT FROM THE SELECT FIELD */
      var allbooks = $(this).val(); /* GET THE VALUE OF THE SELECTED DATA */
      var dataString = "allbooks="+allbooks; /* STORE THAT TO A DATA STRING */

      $.ajax({ /* THEN THE AJAX CALL */
        type: "POST", /* TYPE OF METHOD TO USE TO PASS THE DATA */
        url: "get-data.php", /* PAGE WHERE WE WILL PASS THE DATA */
        data: dataString, /* THE DATA WE WILL BE PASSING */
        success: function(result){ /* GET THE TO BE RETURNED DATA */
          $("#show").html(result); /* THE RETURNED DATA WILL BE SHOWN IN THIS DIV */
        }
      });

    });
  });
</script>

Then lets create the get-data.php which will receive the data sent through AJAX.

if(!empty($_POST["allbooks"])){
  /* DO YOUR QUERY HERE AND GET THE OUTPUT YOU WANT */
  echo $output; /* PRINT THE OUTPUT YOU WANT, IT WILL BE RETURNED TO THE ORIGINAL PAGE */
}

You can check this example - JSfiddle.

Check this simple tutorial Hope this will help.

      <html>
    <head>
    <script>
    function showUser(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 (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
                    document.getElementById("txtHint").innerHTML = xmlhttp.responseText;
                }
            }
// getuser.php is seprate php file. q is parameter 
            xmlhttp.open("GET","getuser.php?q="+str,true);
            xmlhttp.send();
        }
    }
    </script>
    </head>
    <body>

    <form>
    <select name="users" onchange="showUser(this.value)">
      <option value="">Select a person:</option>
      <option value="1">Peter Griffin</option>
      <option value="2">Lois Griffin</option>
      <option value="3">Joseph Swanson</option>
      <option value="4">Glenn Quagmire</option>
      </select>
    </form>
    <br>
    <div id="txtHint"><b>Person info will be listed here...</b></div>

    </body>
    </html>

The getuser.php file

   <!DOCTYPE html>
    <html>
    <head>
    <style>
    table {
        width: 100%;
        border-collapse: collapse;
    }

    table, td, th {
        border: 1px solid black;
        padding: 5px;
    }

    th {text-align: left;}
    </style>
    </head>
    <body>

    <?php
// don't use intval if your select value is not numberic
    $q = $_GET['q'];

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

    mysqli_select_db($con,"ajax_demo");
    $sql="SELECT * FROM user WHERE id = '".$q."'";
    $result = mysqli_query($con,$sql);

    echo "<table>
    <tr>
    <th>Firstname</th>
    <th>Lastname</th>
    <th>Age</th>
    <th>Hometown</th>
    <th>Job</th>
    </tr>";
    while($row = mysqli_fetch_array($result)) {
        echo "<tr>";
        echo "<td>" . $row['FirstName'] . "</td>";
        echo "<td>" . $row['LastName'] . "</td>";
        echo "<td>" . $row['Age'] . "</td>";
        echo "<td>" . $row['Hometown'] . "</td>";
        echo "<td>" . $row['Job'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";
    mysqli_close($con);
    ?>
    </body>
    </html>

 var id="1";
 $.ajax({
    type: 'POST',
    url: 'yourphppage',
    dataType: "json",
    data: {
        idofrow:id
    },
    success: function(data) {
        alert(data);
    },
    error: function(data) {
        alert(data);
    }
});

This is a samle of ajax request you can use this and just change the other fields as need when the query is success you can retrieve that data in the success you can manipulate what data you want to use you can return json,text.

In your php page you can retrieve the id as

$id = ($_POST['idofrow']);

you can then you this id to select like this

SELECT * FROM table where rowid = $id

and you can just echo the result.

for additional info just check on this documentation

How do I execute a PHP query on select option choice using AJAX?

Related

Recent Posts