Using jquery .ajax to retrieve records from mySQL DB

Question

I am a mySQL noob, and have a slightly stupid question...

I am using jquery to send form to a php script which then stores the data in a mySQL database. On another page in my app, I need to get all the values from one column of said mySQL DB, using jquery again (I assume I will also have to write another php script) so I can use the retrieved data. I am very familiar with using POST or GET to send data, but all of a sudden realized I have no idea how to retrieve it in a way that it can be used by a jquery callback function.

By the way, I'm using php4.

Any help would be appreciated!

Answer 1

You're looking for json_encode().

Here is an example of using PHP, JSON and AJAX that sends JSON to PHP. This tutorial sends JSON data back.

By the way, are you using jQuery or similar Javascript framework? If not, I'd highly recommend it as it can abstract a lot of the cross-browser differences and error-handling with the ajax() call. Here is an example of .ajax() in action:

$.ajax({
    url: 'document.xml',
    type: 'GET',
    dataType: 'xml',
    timeout: 1000,
    error: function(){
        alert('Error loading XML document');
    },
    success: function(xml){
        // do something with xml
    }
});

Now all that assumes you're using PHP 5.2+ (which is when json_encode()/json_decode() were addd). If that's not the case you may have to use XML instead.

Here is an introduction to Javascript and XML and the manual for the PHP XML parser. You are using jQuery so XML parsing gets a bit easier, see this article.

XML is more tedious to deal with (and the messages are slightly more verbose) but it's arguably safer. For completeness it's worth pointing out the security issues with JSON.