Quick thoughts on IT, fun projects, and the singularities I come across.

Asynchronous Database Operations with PHP and jQuery

In a recent work project, we had to do some simple database calls to update records. I was going to do a simple form with a submit button, but decided checked in with one of my coworkers (who’s a far better developer than I) to see what he thought. Since we were developing a ‘flashier’ application, he pushed me to do it all asynchronously with jQuery. I had a hard time finding some good examples at the time, so I decided to create some demos and do a write up.

Before we start looking at the code, I need to mention two things. For this walk-through, we’ll start with a simple PHP form and add in jQuery components. If this doesn’t interest you, feel free to skip down to the final section. Second, if you want to follow my example to the letter, you’ll need to setup a database (I’m using MySQL) and add a table ‘customer’ with the following structure:

alexdglover_asynchDatabaseDemo_tableStructure

Here’s a quick script to create the table if you’re feeling lazy:

Of course you can use a different database, different table name, columns etc., just keep that in mind as you write your code.

Part 1 – Simple PHP Form and Submit Button

Before we add anything new, let’s just get a basic page working. This page will have a form with a text field input for each field in the database (except the ID, since it auto-increments), and a submit button. The page will also have a simple table to show the records currently in the database. If you’re totally new to PHP forms and MySQL, check out the W3Schools PHP tutorials on forms$_GET/$_POST, INSERT and SELECTCheck out the first demo page to see how it works and then check out the code:

So now we can insert new records, and see all the records in the table. This is all Web Development 101, but it’s important that we’ve built a working foundation. This way if we have issues later we can narrow it down to JavaScript or database issues.

Part 2 – Introducing jQuery and Serialize

OK, we’re going to take a small step. Instead of using a button within a form to submit the page and all of the form values, we’ll use JavaScript. Conveniently, jQuery has a serialize function we can use to grab all of the input values at once and format them in a convenient string. Then we’ll use an ajax call to submit that pre-formatted string to the server.

I haven’t removed any of the original code, simply added one JavaScript function insert() and one anchor tag that will call the insert function. The insert() function will actually POST the values back to the server, back to the very same page (see the url: “asynchDatabaseDemo2.php” line). Since our PHP is looking for POST values, it will ‘catch’ them and insert them into the database. Check out the demo and then let’s review the code:

Simple right? The data is inserted, we see the alert that shows the serialized string… but the table doesn’t show the new values. No worries, we just need to make the table operate asynchronously as well.

Part 3 – Asynchronous Search Results

This is a slightly more complicated step, so a couple quick notes before we get started:

  • JavaScript can’t call the PHP directly to re-query the database; JavaScript is a client-side language, PHP is a server-side language. However, we can ‘cheat’ by using HTTP POST or GET requests to act as a bridge between the two. To do this we need to create a separate PHP page to handle the searching, called “asynchDatabaseDemo_DBEngine.php”. This page will echo table tags identical to our original table structure
  • We’ll add a new JavaScript function, searchDB(), that will POST to the PHP page dedicated to querying the database. Then we’ll add a search field that calls the searchDB() function. Finally, we’ll modify the insert() function to call the searchDB() function – that way the table will be refreshed as soon as the insert completes
  • The ajax call within the searchDB() function has an additional attribute, ‘success’ that will handle the server response by placing the HTML within the table tags (by referencing the ID ‘#searchResults’)

Alright, hopefully that will make the code make a little more sense. Check out the demo, then let’s look at the asynchDatabaseDemo_DBEngine.php file to start:

And finally the aynchDatabaseDemo_frontEnd.php code:

Done! This tutorial came out a little more scatter-brained than I had hoped, so feel free to comment/contact me if you have any questions. As always, I hope this helps.

Social tagging: > > > >

4 Responses to Asynchronous Database Operations with PHP and jQuery

  1. transsi says:

    There are some issues I have to point out:
    - In “Part 2″ and after, there’s SQL DB hostname, username, password visible in the post. It would be smart to remove those from the blog post.
    - sanitize user input – never trust what you get from (especially) $_GET and $_POST. Now your SQL queries are open to SQL injections (f.ex extracting or deleting data)
    - never show DB errors to the end user – it may give hints to attackers about how to take advantage of vulnerabilities in either your code or software running on the server (DB, PHP version etc)
    - by default “PHP scripting” – mixing with HTML and JS is bad. You should keep them in separate files for better code readability and to separate the logic from the view code

    For more information about SQL Injections:
    http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php

    ..and other web service security issues, see:
    http://owasp.com/index.php/Category:OWASP_Top_Ten_Project

    • AlexDGlover says:

      You know, the worst part is that I told myself I was going to pull out the database credentials (as I did in the first code snippet).

      Thanks for catching that and the other great links.

  2. da_n13l says:

    I hate to be *that guy* but your guide is completely open to SQL injections and does not follow best practices. I blame W3Schools who are notorious for producing terrible guidelines which don’t consider or even mention basic security 101. You need to sanitise any queries sent to the database, otherwise malicious queries can be crafted to wreck all hell on your database and website. I recommend you use a framework like Laravel4 or CodeIgniter which have good methods for dealing with user submitted data and databases. Don’t want to make you feel bad but this is a really insecure example.

    • AlexDGlover says:

      You’re absolutely right, we’re not doing any sort of input validation, input sanitation, or parameter binding. But that’s not really what this post is about either. I generally try to limit the number of topics within each post, otherwise I’d be writing books and not short posts.

      If you (or any other reader) are looking for an example for how to secure a PHP/MySQL application, check out my MySQLi tutorial.

      Thanks for the framework tips, I’ll definitely check out Laravel4 and CodeIgniter. Let me know if you want to write a post about those frameworks ;)

Trackbacks/Pingbacks

  1. Alex D Glover “To Do List” App

Leave a Reply