Loading and saving data dynamically using PHP, jQuery and MySQL

2011-10-26 18:45:00 CET - kafoso

This article illustrates how data may be loaded and stored dynamically in a MySQL database using PHP and jQuery (AJAX). Dynamic data exchange means that a web page can store and retrieve data from a database without the current web page being reloaded.

Table of contents

  1. Basics of dynamic data exchange
  2. Code
  3. Running the script
  4. Files

No single PHP web page can exchange data dynamically. This may seem strange and come as a surprise, but once a PHP page has loaded the server has done its job and will not modify the same page, unless it is reloaded completely. This is due to PHP being a server-side script language.

So how do we actually load and save data dynamically, without reloading the displayed page?

The secret is a combination of a client-side script and some additional PHP files which handle the data exchange with the database.

A powerful client-side scripting language is Javascript, which works in most browsers. But Javascript alone can be quite cumbersome to work with. To ease things we will use a Javascript library called jQuery, which will reduce the code we have to write ourselves dramatically and thus it will give us great overview.

Basics of dynamic data exchange

In order to exchange data dynamically we need a minimum of two files:

  • default.php
    The page at which all informations will be displayed and loaded into dynamically. Contains only HTML and Javascript code (jQuery).
  • dynload.php
    A helper-filer for loading data from a MySQL database. Contains only PHP code.

Once the default.php file has loaded it will not be reloaded again. This is where the dynload.php file comes in, because this file can process the desired requests on the server, and then send the data to the Javascript in the default.php, changing the contents of the page.

Example

  1. The default.php page loads the first and only time in an Internet browser. The only content in this page is a button with the text "Load".
  2. When a user clicks this button, the Javascript will send a request to the dynload.php file. This file then fetches a text string from a database table, and returns this text string to the Javascript.
  3. Finally, the Javascript displays the text string in the default.php file, which still hasn't reloaded.

Code

The code below is an example of how to both load and store data dynamically between a MySQL database and a HTML file.

To try this out for yourself you must have access to a MySQL database and a server capable of handling PHP. You can turn your own computer into a server with a MySQL database by installing XAMPP.

In this example a "localhost" database is used. The database itself is called "dynload" and the table in this database is called "atable".

 

default.php

<html>
<head>
    <script type="text/javascript" src="jquery-1.6.4.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            $('#get_button').click(function() {
                $.ajax({
                    type: "GET",
                    cache: false,
                    url: "dynload.php",
                    dataType: "json",
                    success: function(data) {
                        $('#get_text').text(data[0].text);
                    }
                });
            });
            $('#post_button').click(function() {
                $text = $('#post_text').val();
                $.ajax({
                    type: "POST",
                    cache: false,
                    url: "dynsave.php",
                    data: "text="+$text,
                    dataType: "json",
                    success: function(data) {
                        alert('Success: The text is now stored in the database.');
                    }
                });
            });
        });
    </script>
</head>
<body>
 
    <table style="width: 440px;">
        <tr>
            <td style="background-color: #99bbff; padding: 10px; width: 50%;">
                <button id="get_button">Load content dynamically</button>
                <div id="get_text" style="display: block; clear: both; height: 200px;">Nothing loaded from database yet</div>
            </td>
            <td style="background-color: #ffbb99; padding: 10px; width: 50%;">
                <button id="post_button">Save content dynamically</button>
                <textarea id="post_text" style="display: block; clear: both; height: 200px;">This text will be saved dynamically in the database. Try changing this text, save it and then load it afterwards.</textarea>
            </td>
        </tr>
    </table>
 
</body>
</html>

 

dynload.php

<?php

$connection = @mysql_connect('localhost', 'root', '')
    or die (mysql_error());
mysql_select_db('dynload', $connection)
    or die (mysql_error());

$query = "SELECT * FROM atable";
$resource = mysql_query($query)
    or die (mysql_error());

if (!$resource || !is_resource($resource)) die('$resource != a resource');

$data = array();
while ($result = mysql_fetch_assoc($resource)) {
    $data[] = $result;
}

header("Content-type: text/plain");
echo json_encode($data);

?>

 

dynsave.php

<?php

if (isset($_POST['text'])) {

    $connection = @mysql_connect('localhost', 'root', '')
        or die (mysql_error());
    mysql_select_db('dynload', $connection)
        or die (mysql_error());

    $query = "UPDATE atable SET text = '".$_POST['text']."'";
    $resource = mysql_query($query)
        or die (mysql_error());

}

?>

Running the script

When running the default.php file you will see the contents in the image below.

dynload-save.jpg

Clicking the "Load content dynamically" button will load contents already in the database table called "atable" and display this text below the button. Clicking the "Save content dynamically" will store the contents of the textarea on the right side in the database and a notificaiton will pop up when the contents have been saved.

Files

Download: dynamic_load.zip

This ZIP-file contains the PHP script files default.php, dynload.php and dynsave.php, and the jQuery library jquery-1.6.4.min.js.