UPDATE database after select option change

Question

The "Reference No , Status" both are from Database. The below codes displayed the result in top link. Here's my question: How do I update my Database from "pending" to "deliver" straight away after I change the select Option from "Pending" to "Delivered"?

<?php
echo '<tr>
    <td>Reference No</td>
    <td>Status</td>
</tr>';
$result = mysql_query("select * FROM orderhistory"); 
while($row=mysql_fetch_array($result)){
    $shopReference = $row['reference'];
    $status = $row['status'];
    if($status == 'pending')
    {$status = 'Pending';}
    elseif($status == 'delivered')
    {$status = 'Delivered';}
    if($q==0) continue;
?>
    <tr>
    <td><?php echo $shopReference?></td>
    <td>
        <select id="status" name="status" size="1" required>
            <option value="" style="display:none">Status</option>
                <option value="pending" <?php if($status == 'Pending') echo "selected"; ?>>Pending</option>
                <option value="delivered" <?php if($status == 'Delivered') echo "selected"; ?>>Delivered</option>
            </optgroup>
        </select>
    </td>

<?php                   
}
?>

The SQL to update the database most probably will be this

$sql = "UPDATE orderhistory SET status= '$status' WHERE reference = '$reference'";

But what jQuery or any other thing should I use?

I guess should be some function on onChange? I'm not very sure how to use it. I tried to search online, but can't get any idea about it... Sorry, I am new...

Answer 1

I see this question quite often, so I have written a generalized answer based on my understanding of the concept to redirect the future questions of similar type to here.

First thing you should know as a newbie is, when you open a PHP page, the PHP code is the first one getting executed by the server and then the HTML and JavaScript by the browser. Now when you interact with the HTML elements such as changing the content of an input box or selecting an option from dropdown or even clicking on a button etc., these actions/events can be detected by JavaScript but not PHP. Thus, you need a way to have the client side (JavaScript) interacting with the server side (PHP). This way is called AJAX.

In simple terms of what AJAX does is when the user performs any action on the page such as a button click, using events (and event handlers) you get to capture the user input and pass it to PHP via AJAX.

A skeleton preview of AJAX:

$.ajax({ // ajax block starts
   url: 'send.php', // The PHP file that you want to send your user input to
   type: 'POST', /*
                    by default the values will be sent as GET - 
                    through address but if it's sensitive data,
                    use POST 
                 */
   data: {data1: $('#textbox').val(), data2: $('#select').val()}, // The data to be sent
   dataType: 'text', /*
                        Over here you set the type of response that will be sent
                        back by the PHP file. It could be 'text','html' or
                        if you have to send back values in array format 
                        you can use'json' type
                     */
   success: function(data) 
   {
    /* 
       This block will be called once the PHP file is executed and 
       the 'data' parameter here holds
       the values returned from the PHP file
    */
   }
});

Like mentioned before, you can call AJAX using events and event handlers either on load of page or on interaction with HTML elements.

Let's say for example, we have a button as:

<input type="button" id="button" value="Click" />

We can detect the click event by:

$('#button').click(function(){
  // AJAX here will run on click of button 
}

Or if we have a select dropdown:

<select id="dropdown">
  <option value=1>1</option>
  <option value=2>2</option>
</select>

The change method would get triggered when you select an option

$('#dropdown').change(function(){
   // AJAX here will run on change of select
}

The hash # here denotes id attribute. You cannot have same id multiple times, if such a case arrives, you should use the class attribute and then you would use dot . with the class name like the following:

<input type="button" class="button" value="Click">
<input type="button" class="button" value="Click Again">

$('.button').click(function(){ 
    //clicking any of the above button will trigger this code
}

Now since you have several buttons with the same class, how would the function know to identify which button has been clicked? For that you use $(this). $(this) is an jQuery element object that refers to the current object on which the method was invoked.

Another important point to note is, if you have dynamic HTML elements loaded, then you need to add a on() event handler. More on it here.

Now the crucial part that is to access the values in our PHP that we have passed from AJAX:

/*
   This if block here detects if the request is sent through AJAX,
   it's not necessary but use this if you want to prevent direct access
   to this PHP file and allow it ONLY through AJAX.
*/
if ( !empty($_SERVER['HTTP_X_REQUESTED_WITH']) && strtolower($_SERVER['HTTP_X_REQUESTED_WITH']) == 'xmlhttprequest' )
{
    /*
       Now depending on the 'type' we set in our AJAX code, 
       we would have to call it by $_GET if the type is 'GET' 
       and $_POST if the type is 'POST', in the above we have 
       set it to POST so the following code demonstrates the same
    */

    # So the data can be accessed as:
    echo $_POST['data1'];
    echo $_POST['data2'];
}

data1,data2 here is the identifier through which we refer to the values passed in the AJAX.

There's a lot more to useful functions in AJAX such as accessing the PHP file at regular intervals (timeout), returning data in array format(json) etc.

Alternatively, you can also use $.get and $.post which are based again on the concept of AJAX but have lesser functionalities.

Answer 2

Try using ajax with jquery like this

$(this).on('change', function() {
    var id = $(this).html();
    alert(id);
    $.ajax({
        type:'POST',
        //url: 'updateurl
        success: function(data) {
             //open dialog box and fill it with data
        }
});

Answer 3

$(document).on('change', '#status', function(event) {
    event.preventDefault();
    // make ajax call for update field in db 
    // or submit form (put select in form)
});