drew.d.lenhart

programming, software, technology, anything on my mind...

More PHP and Ajax Plus SQLite

2015/11/10

In an old article I showed how to use Ajax to populate multiple selection boxes along with PHP & MySQL. This time I'll quickly show how to use Ajax to query a SQLite DB and populate in a div box.

Example (button is clicked & data populates below):

Screen-Shot-2015-11-09-at-7.55.27-PM

HTML/jQuery

Create a button and an empty DIV box with ID.


<button id="click" name="click" class="btn btn-primary">Click - using Ajax</button>

Now create a function and set up Ajax code. Also create a jQuery click method.


    //load db content
    $("#click").click(function (){
        $("#resultsAjax").html("Loading data........");
        loadBox();
    });

    function loadBox (){
        $.ajax({
            type:"GET",
            url:"load_content.php",
            data: "",
            success: function(data) {
                $("#resultsAjax").html(data);
            }
        });
    }
    

Notice the .html("Loading Data...."), this is good to have a message if you expect a lot of data to be queried. Also notice the success, the data from load_content.php is displayed in the #resultsAjax DIV box.


SQLite

Now we need to create a SQLite database. PHP makes it really easy to get started. SQLite is like a flat file database and pretty self contained. Theres no installation of a SQLite instance needed! Which makes it pretty handy and portable for small projects.

Heres a quick script to create a database as well as create a table called "EMPLOYEE".


<?php
class database extends SQLite3{
    function __construct(){
        $this->open('testdb.db');
    }
}

$db = new database();

if(!$db){
    echo $db->lastErrorMsg();
}else {
    $stat = "Database Accessed!";
}

//Create Table
$sql = "CREATE TABLE EMPLOYEE (
    ID INT PRIMARY KEY NOT NULL,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    STATE CHAR(2),
    ZIP INT NOT NULL,
    PHONE INT NOT NULL
    )";

$createTable = $db->exec($sql);

if(!$createTable){
    $success = $db->lastErrorMsg();
} else {
    $success = "Table has been created!";
}

//Close connection
$db->close();

?>
<html><head></head><body>
<h3>SQLite Install Script</h3>
<div style="border: 2px solid black; padding: 10px; background: #eeeeee">

<?php echo $stat; ?>
<br /><br />
<?php echo $success; ?>
<br /><br />
<a href="/">Go to Main</a>
</div>   
</body></html>

Open up a browser and access the page. You should receive a success message. And check the directory the script above is in, you should see example.db created!

Now we need some data. We could type out a bunch of SQL queries to INSERT data (I have included one of these scripts in the project files). But to be lazy, go download this handy tool called SQLite Database Browser. This is a pretty handy tool to create/update/delete data from SQLite dbs. Here's the example.db created with the above script opened in the program:

Screen Shot 2015-11-09 at 8.20.31 PM



Go ahead and add some moch data to your database. And don't forget to click Write Changes button at the top to save!


PHP

Lastly, a PHP file to query the database is needed. Notice this page, load_content.php is called in the Ajax function above.

Create a database class that can be re-used elsewhere (database.class.php):


<?php
class database extends SQLite3{
    function __construct(){
        $this->open('example.db');
    }
}
?>

Now query all records in the table EMPLOYEE and echo results into a table (load_content.php).


<?php
include("database.class.php");

$db = new database();
if(!$db){
    echo $db->lastErrorMsg();
}

$sql = "SELECT * from EMPLOYEE";

$stmt = $db->query($sql);

echo "<br /><table class='table'><tr><td>NAME</td><td>AGE</td><td>ADDRESS</td><td>STATE</td><td>ZIP</td><td>PHONE</td></tr>";
while($row = $stmt->fetchArray(SQLITE3_ASSOC) ){
    echo "<tr>";
    echo "<td>". $row['NAME'] ."</td>";
    echo "<td>". $row['AGE'] ."</td>";
    echo "<td>". $row['ADDRESS'] ."</td>";
    echo "<td>". $row['STATE'] ."</td>";
    echo "<td>". $row['ZIP'] ."</td>";
    echo "<td>". $row['PHONE'] ."</td>";
    echo "</tr>";
}
echo "</table>";

$db->close();
?>

You should now be able to click the button and results should post below it. Thanks for reading.

Download Code - 11/08/2015