HELLO
I AM
JOHN

I've been breaking making websites for over 15 years.

PHP/AJAX – Instant DB Query and Results

Posted on July 10, 2007 at 8:00 pm

This tutorial will teach you how to use PHP and the XMLHttpRequest object [AJAX] to query a database for a value and display a result on the screen without having to refresh the page.

In this particular example, we’re doing a simple query in the style of Google(tm) – Checking for the availability of a username during account registration.

First thing’s first. Let’s set up a database. Assuming you already have access to one, we have to create a table. Do this however you like, however, I recommend using phpMyAdmin.

That should suffice for our purposes. While we’re at it, insert a name in there, just so we have something to search for:

Ok, moving right along. So now we have a simple database set up. Now we create two files. A html file, and a php file. The html file will display the form for the user. The PHP file will handle the database calls where appropriate and return the results.

db_search.html

Obviously the form in db_search.html wont do anything upon submission, but what we’re looking at here is the “Check availability” link. It calls a javascript function called “check_availability” and sends it two arguments. The first argument is the id of the input field that contains the text we are checking against the database. The second argument is the id of the div in which we will display the result.


**UPDATE: It was brought to my attention that the way I was grabbing the $_GET variables was leaving me (you) open to SQL Injection attacks. Sorry about that. I went ahead and fixed it by adding a call to the mysql_real_escape_string() function.**

check_name.php

check_name.php starts off by connecting to the database (make sure you change the values to the appropriate ones for your database setup, otherwise this wont work for you!), and grabbing the querystring variables sent to it from db_search.html through the check_availability javascript function. Making sure the text we’re searching for isn’t blank or null, we construct our SQL statement to search the table we created. Once we ascertain whether or not the name exists, we respond appropriately.

I left the ajax_functions.js file for last, because I had a few things to say about this. First off, I do not take credit for the ajaxpage and loadpage functions, because I simply found them on the net. But there are so many variations of the same thing, that one cannot truly say who wrote it. I sure dont. I dont even recall where I found this exact variation of it. But they all do the same thing. Some more or less wordy than others, but the end result is the same. All I did was create the check_availability function at the bottom, and linked it all together. So without further ado:

ajax_functions.js

Easy enough, right? Now, lets load up the db_search.html and type in a name, and click “Check Availibity”. Assuming everything went ok (no typos on either your, or my part), if you type in anything but ‘zippy’, you
should get a “that name is available” response, or “not available” otherwise.

I hope this is useful to someone out there, if so, please respond and let me know. Also if you have suggestions or comments, I’d love to hear them.

~ digifuzz

Share

Written by

John is a web-developer by day, dashing, handsome, and death-defying super-hero by night. Based out of Silver Spring, MD, he does his best to make things do things other than the things they were supposed to on a daily basis, in the process, making the world a better place.

There are 25 comments.

Comment by 3dcreature

cool this is really cool
perfect for some one like me “AJAX beginner I wud say” or may be 4 some big guys there toooo..

thankz a lot.

JUL15 2007 23:13:00
Comment by digifuzz

Glad you found it useful! πŸ˜€

JUL17 2007 09:48:00
Comment by vinit

great ,
easy to use script.
thank you very much ..

in check_name.php ,
i changed ‘siteLogin to ‘username’ and it worked well.
also in db_search.html ,
i added onkeyup=”javascript:check_availability(‘username’, ‘answer’);” for ‘username’ input,
now checking is done without clicking on ‘Check Availability’

thank you again
Vinit

AUG06 2007 10:18:00
Comment by digifuzz

vinit:

Aah, yeah.. that’s what happens when i post things in the middle of the night. I over-look things like variable names. siteLogin should definitely have been username. Sorry about that. The post will be updated accordingly.

ps: good call on the onkeyup. sparing the user any additional clicks is generally a good thing.

~ digifuzz.

AUG06 2007 10:46:00
Comment by Ester

We are all hungry for such codes and such methods…

Easy to use code, and good technical writer is explaining!

THATS IT

Thanks alot!!!

AUG24 2007 14:03:00
Comment by sijith

hey, the code is well written. good job.

You can replace the lines

$found_user = mysql_fetch_object( $result );

if( strtolower($found_user->username) == strtolower($check_name) )
{

}

with this one

if(mysql_num_rows ($result)>0)
{

}

basically here we check if there is any record being returned or not for a given user name.

You code is used (with the above modification) in the registration page of my site http://www.libimail.com.

sijith

DEC08 2007 05:41:00
Comment by RamaKrishnan

Yes really this is so cool and great.

MAR06 2008 23:59:00
Comment by salomi

db_search.html error in this page.
What error means object expected.
I just copied three files and run it .
i changed only js script name as username in php name as username
thats all.your script is very easy to understand.
Thank you.

MAR25 2008 01:49:00
Comment by Radek

Hi,
very useful script, thank you πŸ™‚
I’m using this ajax code to check if a unix user exists.
If anyone wants it here’s my check_name.php for checking the existence of a unix user in the system:
<?php
$user=$_GET[‘user’];
$logincheck=shell_exec(‘id -un ‘.$user);
if (trim($logincheck)==trim($user))
{echo ‘User exists.’;return;}else{echo ‘User doesn’t exist.’;return;}
return;
?>

Cheers.

JUN08 2008 03:55:00
Comment by digifuzz

Hey Radek, thanks for sharing. Just make sure you escape your $user variable and trim out potentially dangerous characters. For example….

What would happen if the $user variable consisted of “digifuzz; rm -rf /”? Would it then execute the 2nd command? I’m not too sure off the top of my head. Are you? πŸ˜‰

Escape your data!

~ d

JUN13 2008 19:05:00
Comment by GP

Hi, after deleting the ./
in
var address_string = “./check_name.php?name=” + myobj.value
ajax_functions.js works fine for me with IE;
However it does not work with firefox…
Any idea why?
Thanks,
GP

JUL06 2008 23:13:00
Comment by digifuzz

Strange. My guess is that the problem lies in whatever directory or sub-directories you’re placing your files in, or where the files that are calling the scripts, etc are placed. Firefox is a little more finicky about relative-paths, while IE is more forgiving.

Is there any particular reason you’re removing the “./” from the address_string? If all else fails, you can always try an absolute path ie: http://www.yoururl.com/scripts/check_name.php?name=” etc, etc, etc.

~ digifuzz

JUL07 2008 10:39:00
Comment by GP

Turns out my input tag was missing the id= on my html page. It works now!
Thank You!!!
GP

JUL07 2008 21:06:00
Comment by Dan Bishop

The best tutorial out there! I’ve read through about 20 of these, but none of them were as simple and easy to understand as yours! Thank you!!!

SEP07 2008 03:51:00
Comment by Bishi

grate work man thnx for sharing u knowledge
god bles thnnxxx

NOV29 2008 10:55:00
Comment by zvr

Great example, thanks.

Although I’d like to add that using mysql_real_escape_string() doesn’t do that much in the long run. Its only an illusion of security. You could convert ” or ‘ into %22 or %27, use char or 16-bit encoding (bin2hex) to avoid filtering. Also, there are some other exceptions that are not filtered by escape_string functions.
You can’t just rely on a single function and hope that it takes care of ALL problems and issues.

Its much better to filter everything to the values you are expecting. Like $something = preg_replace(“/[^a-zA-Z0-9]/”, “”, $something); or so.

Take care.

NOV29 2008 23:26:00
Comment by David Chambers

Very helpful article. I particularly appreciated the inclusion of the table creation query which made testing this on my own site a piece of cake.

Keep up the good work!

DEC08 2008 15:03:00
Comment by FearTec

Cheers

Googled PHP and AJAX and this is the first one I clicked on..

You saved me heaps of time, works great.

MAR14 2009 22:59:00
Comment by viatavesnica[dot]ro

Thanks alot!
Works well.

JUN06 2009 06:19:00
Comment by burak

Can you write how to query a database for a value as an array?

JUN11 2009 13:03:00

Hi i was wondering how much you would to set your blog theme up on my web log for me, because i really like the look of your web site but i do not know how to build such a good theme.

NOV01 2009 21:10:00
Comment by Manish

Hello Sir,
Can you provide working source file.

NOV09 2010 23:59:00
Comment by Geo

Hi, followed all the steps above but it didn’t seem to work. I’ve read the comments as well. What seems to be the problem?

AUG14 2011 22:33:00
Comment by Geo

Oh i got it. Seems that the problem was with the quotations. >.>
Thanks anyways πŸ˜€

AUG14 2011 22:48:00
Comment by david

Ive Done everything you said but it just doesnt work πŸ™
Does it still work with PHP5?
Does there need to be special requirements?

David

JAN30 2012 13:42:00

Allowed tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

By submitting a comment you grant digifuzz.net a perpetual license to reproduce your words and name/web site in attribution. Inappropriate and irrelevant comments will be removed at an admin’s discretion. Your email is used for verification purposes only, it will never be shared.