How to get updated parameter values ​​from the database only when I click on the selection box?

Question

I want to get the values ​​of the updated option from the database only when I click on the selection box.

Suppose that two waiters simultaneously open the same page of the order panel. Then table no: 2 is displayed as free in both panels.

Now the waiter has booked the no: 2 table. Then another waiter, clicking on the selection field, he will not receive the no: 2 table in the settings.

<select name="table_id" class="form-control tablename">
<option disabled="disabled">Select Table</option>
<?php $result =  mysql_query("select * from rtable r
inner join table_status as ts 
on ts.status_id=r.status_id  
where ts.status!='Booked'
order by r.table_id desc")or die(mysql_error()); 
while ($row=mysql_fetch_array($result)){ ?>
<option value="<?php echo $row['table_id'];?>"><?php echo $row['table_name']; ?></option>
<?php } ?>
</select>

table_status

rtable

Answer 1

php ( html , ). functions.php printSelectOptions:

function printSelectOptions(){

  $result =  mysql_query("select * from rtable r
  inner join table_status as ts 
  on ts.status_id=r.status_id  
  where ts.status!='Booked'
  order by r.table_id desc")or die(mysql_error()); 

  echo "<option disabled='disabled'>Select Table</option>";

  while ($row=mysql_fetch_array($result)){

    echo "<option value=".$row['table_id'].">".$row['table_name']."</option>";
  }

}

html .

select (, functions.php printSelectOptions):

<?php
//db connection code
require_once("functions.php");//here we add our function to be available in this file

?>

<select name="table_id" class="form-control tablename">
<?php printSelectOptions() ?>
</select>

bind ( javascript):

document.addEventListener("DOMContentLoaded", function(event) {

 var select=document.querySelector("select"); //this is pure selector gets first select on page

 //function sends ajax and refresh options of select
 function refreshOptions(){

     //send ajax request
     select.innerHTML="<option>Loading..</option>"; //loading info

     var xmlhttp=new XMLHttpRequest();
     xmlhttp.open("GET", 'yourSecondPHPScript.php');//here example url where we get updated options
     xmlhttp.onreadystatechange = function() {
         if (xmlhttp.readyState == XMLHttpRequest.DONE) {
             if(xmlhttp.status == 200){
                 select.innerHTML = xmlhttp.responseText;//set new options
             }else{
                 console.log('Error: ' + xmlhttp.statusText )
                 select.innerHTML="<option>Connection problem</option>";
             }
         }
     }
     xmlhttp.send();  

 };

 //bind our select
 select.addEventListener("focus",function(){

     refreshOptions();        

 });

});

yourSecondPHPScript.php :

 <?php
 //db connection code
 require_once("functions.php");//here we add our function to be available in this file

 printSelectOptions();//outputs options

, , , - . , , select ( ajax refreshOptions()) , .

, , php (PHP CODE):

function tableCanBeTaken($optionId){

  //this code adds **and** to query with id to check but optionId should be validate before using in query

  $result =  mysql_query("select * from rtable r
  inner join table_status as ts 
  on ts.status_id=r.status_id  
  where ts.status!='Booked'
  and ts.table_id=$optionId ")or die(mysql_error()); 


  return mysql_fetch_array($result); //if row exists - will be false if not exists row with table_id==$optionId and not booked

  }

}

(PHP CODE):

if (tableCanBeTaken($youOptionId)){

    //here code for taking option

}else{

    //here option is taken

}

Answer 2

ajax . ( ) . "". , !

Answer 3

@Maciej Sikora

. printSelectOptions() , yourSecondPHPScript. URL.

xmlhttp.open("GET", 'yourSecondPHPScript.php');

I just paste the same code into your SecondPHPScript.php as below

<?php 
include("connect.php");

$result =  mysql_query("select * from rtable r inner join table_status as ts on ts.status_id=r.status_id where ts.status!='Booked' order by r.table_id desc")or die(mysql_error()); 
  echo "<option disabled='disabled'>Select Table</option>";
  while ($row=mysql_fetch_array($result))
      {
        echo "<option value=".$row['table_id'].">".$row['table_name']."</option>";
      }
?>