How to query a database with AJAX and display as a tooltip

This post began as an answer on Stack Overflow to a question on ‘How to query a database with AJAX and display as a tooltip‘. I have put the answer here for future reference.

The original question asked for more information on the steps :

User clicks on a link
Variable passed to external file using AJAX
External file uses variable to query MySQL database
Query result appears in tooltip

and so I have turned each step into a heading and added content to each to describe the process.

User clicks on a link

So the first thing you need is a bit of front end code which tells a link to make an ajax request and not an actual link. I would recommend that you use jQuery to do the hard work for you so you will first need to include the jQuery script in your page if you haven’t already, to do this add this line to the head of your HTML file.

<script src="http://code.jquery.com/jquery-latest.min.js">

1	<script src="http://code.jquery.com/jquery-latest.min.js">

Next you will need to give the link a class so for example your link may look like

<a href="ajax.php?var=value" class="ajax_link">Link</a>

1	<a href="ajax.php?var=value" class="ajax_link">Link</a>

You will see that the link will be to the ajax page which is going to provide the data, you are passing a variable on to that page called var via GET which we will come to later and you also have the class set to “ajax_link” which we will use to reference the link from jQuery.

Now you will need to write some jQuery to handle the link. To do this you will need to open up a <script language=”javascript”> tag at the end of your body and put some code along the lines of:

$(".ajax_link").click(function(e) {

    e.preventDefault(); //Stops link from changing the page

    var link = $(this).attr('href'); //Gets link url

    $.ajax({ //Make the ajax request
      url: link,
      cache: false
    }).done(function( html ) { //On complete run tooltip code

        //Display tooltip code goes here, returned text is variable html

    });
});

$(".ajax_link").click(function(e) {

e.preventDefault(); //Stops link from changing the page

var link = $(this).attr('href'); //Gets link url

$.ajax({ //Make the ajax request

url: link,

cache: false

}).done(function( html ) { //On complete run tooltip code

//Display tooltip code goes here, returned text is variable html

});

I will come back to the tooltip code later on.

Variable passed to external file using AJAX
Now we are going to need to create the PHP file ajax.php, this file will need to connect to the database and make any queries necessary. First I will start the file and get the variable from the GET. See the code below.

$var = mysql_real_escape_string($_GET['var']);

1	$var = mysql_real_escape_string($_GET['var']);

This will create the variable $var and it will contain the value ‘value’ as was set in the above HTML link. You will probably want to escape the value, do some validation to protect yourself from injection but I will not cover that here.
UPDATE: Added most basic string escape function to stop people from copying and pasting and opening themselves up to injection but I would recommend seeing this stack overflow answer for more information.

External file uses variable to query MySQL database
Next you will want to connect to and query the database and then echo what ever it is that you have returned. Here is some code for returning the value of column ‘information’ from a table called ‘data_table’ where some value equals your $var.

//connection to the database
    $dbhandle = mysql_connect($hostname, $username, $password) 
      or die("Unable to connect to MySQL");

    //select a database to work with
    $selected = mysql_select_db("examples",$dbhandle) 
      or die("Could not select examples");

    //execute the SQL query and return records
    $result = mysql_query("SELECT information FROM data_table WHERE value='$var'");
    //fetch tha data from the database
    while ($row = mysql_fetch_array($result)) {
       echo $row{'information'};
    }

    //close the connection
    mysql_close($dbhandle);

//connection to the database

$dbhandle = mysql_connect($hostname, $username, $password)

or die("Unable to connect to MySQL");

//select a database to work with

$selected = mysql_select_db("examples",$dbhandle)

or die("Could not select examples");

//execute the SQL query and return records

$result = mysql_query("SELECT information FROM data_table WHERE value='$var'");

//fetch tha data from the database

while ($row = mysql_fetch_array($result)) {

echo $row{'information'};

}

//close the connection

mysql_close($dbhandle);

For more information on querying a database please see this tutorial.

Query result appears in tooltip
Once you have both of these pages set up the first page will send some value to the ajax page, the ajax page will echo the corresponding information from the database which will be received by the first page in the JavaScript variable html.

The final stage of this is now to display the returned text as a tooltip. First it may be a good idea to test that your pages are working and one easy way to do this is to display an alert with the returned information in (you could also log to the console). You would do this by adding some code to the ajax .done function like so:

.done(function( html ) { 
        alert("text: " + html);
    });

.done(function( html ) {

alert("text: " + html);

});

Once you have done this you should be able to open up the page, click the link and you should see an alert box pop up showing the required text. If there is nothing after the ‘text:’ in the alert then something has gone wrong and you may want to check the code is correct. If it has succeeded then continue on to displaying the tooltip.

There are many available jQuery tooltip plugins one of which is called qTip. This will allow you to create the tooltips and display them when clicking on them. You will need to download the JavaScript file and related CSS for the tooltips and then add some code into the .done function to create and display the tooltip.

To add qTip to your page place visit the qTip site and download the latest version of the js and the css. Then you must reference the files in the head like this:

<link type="text/css" rel="stylesheet" href="jquery.qtip.min.css" />

1	<link type="text/css" rel="stylesheet" href="jquery.qtip.min.css" />

and the js like this:

<script type="text/javascript" src="jquery.qtip.min.js"></script>

1	<script type="text/javascript" src="jquery.qtip.min.js"></script>

This assumes that the css and js files are in the same folder as your index page, if they aren’t please amend these links to point to where you have put those files.

Now all you need to add to the .done function is the following:

$(this).qtip({
    content: {
        text: html
    }
});
$(this).qtip('toggle', true);

$(this).qtip({

content: {

text: html

}

});

$(this).qtip('toggle', true);

This code will create a qTip containing whatever was returned from your AJAX request. Then once the tooltip has been created it will toggle it to show the tooltip initially once clicked. Then the tooltip will show/hide when you hover on/off.

For more information on styling a qTip see their website.

Written by Jacob Tomlinson

Jacob Tomlinson is a linux professional by day and a website designer/developer by night. With experience in technologies such as RHEL, Bash, HTML5, CSS3, JavaScript, jQuery, PHP, Perl, MySQL, CodeIgniter, Wordpress and much more. When he is not tapping away at his keyboard you can usually find him making music, gaming or spending time with his wonderful fiancée.

6 comments

hemanth May 16, 2013 at 7:50 am

How can do the same thing in jsp instead of using php. Can you please give the solution

Jacob Tomlinson May 16, 2013 at 8:45 am

@hemanth
I know nothing about JSP but based on information found here, here and here I’ve put together the following code. It may not work but it should get you started.

<%@ page import="java.util.*" %>
<%@ page import="javax.sql.*;" %>
<% 

java.sql.Connection con;
java.sql.Statement s;
java.sql.ResultSet rs;
java.sql.PreparedStatement pst;

con=null;
s=null;
pst=null;
rs=null;

// Remember to change the next line with your own environment 
String url= "jdbc:jtds:sqlserver://nameofyourdatabaseserver.or.ipaddress/yourdatabasename";
String id= "username";
String pass = "password";

try{
    Class.forName("net.sourceforge.jtds.jdbc.Driver");
    con = java.sql.DriverManager.getConnection(url, id, pass);
}catch(ClassNotFoundException cnfex){
    cnfex.printStackTrace();
}

String var = request.getParameter("var")
try{
    s = con.prepareStatement("SELECT information FROM data_table WHERE value='?'");
    s.setString(1, var);
    rs = s.executeQuery(sql);

    while( rs.next() ){
        out.print(rs.getString("information")); 
    }
}
%>

<%@ page import="java.util.*" %>

<%@ page import="javax.sql.*;" %>

java.sql.Connection con;

java.sql.Statement s;

java.sql.ResultSet rs;

java.sql.PreparedStatement pst;

con=null;

s=null;

pst=null;

rs=null;

// Remember to change the next line with your own environment

String url= "jdbc:jtds:sqlserver://nameofyourdatabaseserver.or.ipaddress/yourdatabasename";

String id= "username";

String pass = "password";

try{

Class.forName("net.sourceforge.jtds.jdbc.Driver");

con = java.sql.DriverManager.getConnection(url, id, pass);

}catch(ClassNotFoundException cnfex){

cnfex.printStackTrace();

}

String var = request.getParameter("var")

try{

s = con.prepareStatement("SELECT information FROM data_table WHERE value='?'");

s.setString(1, var);

rs = s.executeQuery(sql);

while( rs.next() ){

out.print(rs.getString("information"));

}

hemanth May 20, 2013 at 6:03 am

Thank you very much. I have one more problem
While testing my page as you suggested (using alert box), the alert box is displaying entire html page instead of the text. Can you suggest me something on this problem

jordan mcdonald May 20, 2013 at 6:05 am

Cool. I am using an ASPX page and the entire HTML of the page is showing up in the qtip.
I just want a few bits of data to display.

Should I be using a code behind page instead of ASPX which is an HTML based page?
My thought was that just the body of the aspx page would return but the entire set of HTML is returning.

thanks

jordan mcdonald May 20, 2013 at 8:04 pm

I got it working I believe! thanks

Reply
- Jacob Tomlinson May 20, 2013 at 9:08 pm
  
  Great. Perhaps you could share how you got it working?
  
  Reply

Jacob Tomlinson