16

Is it possible to run a MySQL query using jQuery? I'm trying to emulate the functionality of voting on SE sites.

The vote counter on SE automatically updates without the need to reload the page (which is what I currently have, a hidden form that re-submits to the current page but runs a small block on PHP that updates the score of a question in the database). I'm assuming that is being done using Javascript/jQuery seeing as it is dynamic.

How can I do this? Is there a library which makes it easy and simple (like PHP)?

2 Answers2

16

You can use ajax to call a server page (PHP / ASP /ASP.NET/JSP ) and in that server page you can execute a query.

http://api.jquery.com/jQuery.ajax/

HTML

<input type='button' id='btnVote' value='Vote' />

Javascript

This code will be excuted when user clicks on the button with the id "btnVote". The below script is making use of the "ajax" function written in the jquery library.It will send a request to the page mentioned as the value of "url" property (ajaxserverpage.aspx). In this example, i am sending a querystring value 5 for the key called "answer".

 $("#btnVote").click(function(){     
    $.ajax({
            url: "ajaxserverpage.aspx?answer=5",
            success: function(data){
                alert(data)
             }
          });

  });

and in your aspx page, you can read the querystring (in this example, answer=5) and build a query and execute it againist a database. You can return data back by writing a Response.Write (in asp & asp.net )/ echo in PHP. Whatever you are returning will be coming back to the variable data. If your query execution was successful, you may return a message like "Vote captured" or whatever appropriate for your application. If there was an error caught in your try-catch block, Return a message for that.

Make sure you properly sanitize the input before building your query. I usually group my functionalities and put those into a single file. Ex : MY Ajax page which handles user related stuff will have methods for ValidateUser, RegisterUser etc...

EDIT : As per your comment,

jQuery support post also. Here is the format

 $.post(url, function(data) {
        alert("Do whatever you want if the call completed successfully")
 );

which is equivalent to 

 $.ajax({
        type: 'POST',
        url: url,           
        success: function(data)
                  {
                    alert("Do whatever you want if the call completed successfully")
                  }           
       });

This should be a good reading : http://en.wikipedia.org/wiki/Same_origin_policy

Shyju
  • 214,206
  • 104
  • 411
  • 497
  • 10
    And don't give in to the temptation to not make a separate server action for every query but have a generic action that executes any query as specified by jQuery. That would be a huge security hole. There should always be server-side code specifically for every single action that can check permissions. – Thilo Dec 22 '11 at 02:06
  • A quick question: what if the file I want to send data to is outside of my webroot directory? Is this possible? Also, can I send POST data instead of GET? –  Dec 22 '11 at 05:47
  • Re: quick question. You can send data to anywhere *within the same domain*. Cross-domain is difficult. And jQuery does POST as well. – Thilo Dec 22 '11 at 08:23
6

It's just a few lines in your favorite language.

Javascript

$.post('script.php', { id: 12345 }, function(data) {
    // Increment vote count, etc
});

PHP (simplified)

$id = intval($_POST['id']);
mysql_query("UPDATE votes SET num = num + 1 WHERE id = $id");

There are many different ways to accomplish this.

Interrobang
  • 16,984
  • 3
  • 55
  • 63