select data from database based on the data selected from dropdown

Question

I have a select element populated from database, when I select one element, I want to show me some other data from another column related to that row.

For example: on the dropdown I have: apples, bananas, pears

When I select apple I want to select from database the column 'total' that shows me how many apples do I have left.

I think, I should use Javascript-Ajax? Or is it another way to do this with php.

Thanks in advance!

<?php include("db.php"); ?>
<!DOCTYPE html>
<html>
<head>
    <script type="text/javascript">
    $(document).ready(function() {
        $('select').material_select();
    }); 

    function myFunction() {
        if (document.getElementById('selectid').value == "other") {
            var x = document.createElement("INPUT");
            x.setAttribute("type", "text");
            x.setAttribute("value", "");
            x.setAttribute("placeholder", "Write another");
            x.setAttribute("class", "input-field col s12");
            document.getElementById("myDIV").appendChild(x);
        }
    } 
    </script>
</head>

<body>
    <div id="myDIV" class="input-field col s4">
    <?php
    $sql12 = "select * from fruitswhere total>0 && spent=1";
    $res1 = mysqli_query($conn, $sql12) or die("Error");
    $select = '<select id="selectid" name="name" onchange="myFunction()"><option value = "" disabled selected> Choose </option>';
    while($row1 = mysqli_fetch_assoc($res1)) {
        $emri = $row1['name'];
    // $select.='<option value = "'.$row1['name'].'">'.$row1['name '].'</option>';
        $select .= '<option id="my_option" value="'.$name.'">'.$name.'</option>';
    }
    $select .= '<option value="other">other</option>';
    $select .= '</select>';
    echo $select;
    ?>
    </div>

    <div class="input-field col s4">
    <?php
    $query2 = "SELECT total FROM fruits WHERE name='$name'";
    $res2 = mysqli_query($conn, $query2);
    while($row2=mysqli_fetch_assoc($res2)){
        $all = $row2['total'];
    }
    ?>
    <input name="total" type="number" min="1">
    </div>
</body>
</html>

Yes, javascript (or jQurey $.post is simpler, IMHO) to ajax. — Apps-n-Add-Ons
– Apps-n-Add-Ons, Commented Dec 19, 2017 at 13:26

sorak · Accepted Answer · 2017-12-19 15:25:03Z

You can set up a simple script to respond to the request. For example, you can make fruitCount.php:

<?php
include('db.php');

$sql = 'SELECT total '.
       'FROM fruits '.
       'WHERE name="'.mysqli_real_escape_string($_POST['name']).'"';
$result = mysqli_query($conn, $sql);
if ($result != NULL) {
    $row = mysqli_fetch_assoc($result);
    print($row['total']);
} else {
    print(0);
}
die();

And then on the page, you can use jQuery to set a click event that requests data from this resource:

$('#selectid').change(function(){
        $.post('fruitCount.php',           // Server target for request
               { name: $(this).val() },    // POST payload
               function(resp){             // Handle response from server
                   $('#myDiv').html(resp); // Populate value to page
               });
    });

This is an implementation that allows for up-to-date values in an environment where things are changing server-side. If you only need the value at the time of loading, you can set a data-count attribute on the OPTION element and update your #myDiv from that:

$('#selectid').change(function(){
        $('#myDiv').html($(this).data('count'));
    });

How you implement it depends upon how realtime you want your data to be.

Nda.S · Accepted Answer · 2017-12-22 10:14:56Z

Hello I found a solution using AJAX, here it goes, works perfect!

         <script language = "javascript" type = "text/javascript">
                  <!--
                     //Browser Support Code
                     function ajaxFunction(){
                        var ajaxRequest;  // The variable that makes Ajax possible!

                        try {
                           // Opera 8.0+, Firefox, Safari
                           ajaxRequest = new XMLHttpRequest();
                        }catch (e) {
                           // Internet Explorer Browsers
                           try {
                              ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
                           }catch (e) {
                              try{
                                 ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
                              }catch (e){
                                 // Something went wrong
                                 alert("Your browser broke!");
                                 return false;
                              }
                           }
                        }

                        // Create a function that will receive data
                        // sent from the server and will update
                        // div section in the same page.

                        ajaxRequest.onreadystatechange = function(){
                           if(ajaxRequest.readyState == 4){
                              var ajaxDisplay = document.getElementById('result');
                              ajaxDisplay.innerHTML = ajaxRequest.responseText;
                           }
                        }

                        var fruitName= document.getElementById('selectid').value;
                        var queryString = "?name=" + fruitName;

                        ajaxRequest.open("GET", "ajax_totali.php" + queryString, 
                        true);
                        ajaxRequest.send(null);

                      }
                     }
               </script>
    <body>
      <div id="myDIV" class="input-field col s4">

       <?php $sql12="select * from fruits";
     $res1=mysqli_query($conn,$sql12) or die( "Error");
    $select= '<select id="selectid" name="name onchange="ajaxFunction()"><option value="" disabled selected>Choose one fruit</option>';
    while ($row1=mysqli_fetch_assoc($res1)){
          $select.='<option value="'.$row1['name'].'">'.$row1['name'].'</option>';
      }
    $select.='</select>';
    echo $select; ?>
      </div>
      <div class="input-field col s4">
                <div id='result'></div>
              </div>

        </body>
      </html>

ajax_total.php

     <?php
      include('db.php');
      $sql = 'SELECT total '.
      'FROM fruits'.
      'WHERE name="'.$_GET['name'].'"';
      $result = mysqli_query($conn, $sql);
      if ($result != NULL) {
        $row = mysqli_fetch_assoc($result);
        // print($row['total']);
        $result=$row['total'];
        echo "<div style='color:red'>.$result.</div>";
      } else {
        print(0);
      }
      die();

     ?>

Collectives™ on Stack Overflow

select data from database based on the data selected from dropdown

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related