Using jQuery+Ajax to update MySQL

32,573 · October 28, 2010

As the title suggests, i'm trying to update a database entry using jQuery and Ajax to accomplish this, however, the task won't be submitted through a form, checkbox or anything like that, but through a link.

How would I go about updating my DB through a link click?

For the record, the link doesn't actually have to go anywhere, it would just be a "press to update" sort of deal.

2,990 · October 28, 2010

This is a real simple example of using jquery and php.

&lt;?php
  if (isset($_POST['action'])) {
    $link = mysql_connect(...);
    mysql_query("...", $link);
    mysql_close($link);
  }
?&gt;
&lt;html&gt;
  &lt;head&gt;
    &lt;script type="text/javascript"&gt;
      function performAjaxSubmission() {
        $.ajax({
          url: 'file.php',
          method: 'POST',
          data: {
            action: 'save',
            arg1: 'val1',
            arg2: 'val2'
          },
          success: function() {
            alert("success!");
          }
        });
        return false; // &lt;--- important, prevents the link's href (hash in this example) from executing.
      }

      jQuery(document).ready(function() {
        $("#linkToClick").click(performAjaxSubmission);
      });
    &lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
    &lt;a href="#" id="linkToClick"&gt;Click here&lt;/a&gt;
  &lt;/body&gt;
&lt;/html&gt;

32,573 · October 28, 2010

is it possible to have a few different links, and each of them containing a different value that is passed through the jquery to update a specific field?

2,990 · October 28, 2010

Yes. I tend to do it like so:

&lt;?php
  if (isset($_POST['action'])) {
    $link = mysql_connect(...);
    mysql_query("...", $link);
    mysql_close($link);
  }
?&gt;
&lt;html&gt;
  &lt;head&gt;
    &lt;script type="text/javascript"&gt;
      function performAjaxSubmission() {
        $.ajax({
          url: 'file.php',
          method: 'POST',
          data: {
            action: 'save',
            field: $(this).attr("db_field"), 
            val: $(this).attr("db_value")
          },
          success: function() {
            alert("success!");
          }
        });
        return false; // &lt;--- important, prevents the link's href (hash in this example) from executing.
      }

      jQuery(document).ready(function() {
        $(".linkToClick").click(performAjaxSubmission);
      });
    &lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
    &lt;a href="#" class="linkToClick" db_field="field1" db_value="value1"&gt;Click here&lt;/a&gt;
    &lt;a href="#" class="linkToClick" db_field="field2" db_value="value2"&gt;Click here&lt;/a&gt;
    &lt;a href="#" class="linkToClick" db_field="field3" db_value="value3"&gt;Click here&lt;/a&gt;
  &lt;/body&gt;
&lt;/html&gt;

If you need me to explain how any of this works, just let me know.

32,573 · October 28, 2010

Just to make sure I got the database info correct, this should work, right?

&lt;?php
  if (isset($_POST['action'])) {
  	$field = $_POST['db_field'];
  	$value = $_POST['db_value'];
    $link = mysql_connect("localhost", "admin", "admin");
    mysql_query("UPDATE example SET $field='$value' WHERE username='yourusernamehere'", $link);
    mysql_close($link);
  }
?&gt;
&lt;html&gt;
  &lt;head&gt;
    &lt;script type="text/javascript"&gt;
      function performAjaxSubmission() {
        $.ajax({
          url: 'file.php',
          method: 'POST',
          data: {
            action: 'save',
            field: $(this).attr("db_field"), 
            val: $(this).attr("db_value")
          },
          success: function() {
            alert("success!");
          }
        });
        return false; // &lt;--- important, prevents the link's href (hash in this example) from executing.
      }

      jQuery(document).ready(function() {
        $(".linkToClick").click(performAjaxSubmission);
      });
    &lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
    &lt;a href="#" class="linkToClick" db_field="field1" db_value="value1"&gt;Click here&lt;/a&gt;
    &lt;a href="#" class="linkToClick" db_field="field2" db_value="value2"&gt;Click here&lt;/a&gt;
    &lt;a href="#" class="linkToClick" db_field="field3" db_value="value3"&gt;Click here&lt;/a&gt;
  &lt;/body&gt;
&lt;/html&gt;

October 29, 2010

On 28/10/2010 at 15:38, Andrew Lyle said:

Just to make sure I got the database info correct, this should work, right?

&lt;?php
  if (isset($_POST['action'])) {
  	$field = $_POST['db_field'];
  	$value = $_POST['db_value'];
    $link = mysql_connect("localhost", "admin", "admin");
    mysql_query("UPDATE example SET $field='$value' WHERE username='yourusernamehere'", $link);
    mysql_close($link);
  }
?&gt;
&lt;html&gt;
  &lt;head&gt;
    &lt;script type="text/javascript"&gt;
      function performAjaxSubmission() {
        $.ajax({
          url: 'file.php',
          method: 'POST',
          data: {
            action: 'save',
            field: $(this).attr("db_field"), 
            val: $(this).attr("db_value")
          },
          success: function() {
            alert("success!");
          }
        });
        return false; // &lt;--- important, prevents the link's href (hash in this example) from executing.
      }

      jQuery(document).ready(function() {
        $(".linkToClick").click(performAjaxSubmission);
      });
    &lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
    &lt;a href="#" class="linkToClick" db_field="field1" db_value="value1"&gt;Click here&lt;/a&gt;
    &lt;a href="#" class="linkToClick" db_field="field2" db_value="value2"&gt;Click here&lt;/a&gt;
    &lt;a href="#" class="linkToClick" db_field="field3" db_value="value3"&gt;Click here&lt;/a&gt;
  &lt;/body&gt;
&lt;/html&gt;

Looks like that should work fine :)

3,178 · October 29, 2010

On 29/10/2010 at 15:02, AdamLC said:

Looks like that should work fine :)

No. Until those inputs are sanitised the code is not good :p

32,573 · October 30, 2010

&lt;?php
  if (isset($_POST['action'])) {
  	$field = $_POST['db_field'];
  	$value = $_POST['db_value'];
    $link = mysql_connect("localhost", "admin", "admin");
mysql_select_db("database", $link);
    mysql_query("UPDATE example SET $field='$value' WHERE username='yourusernamehere'", $link);
    mysql_close($link);
  }
?&gt;
&lt;html&gt;
  &lt;head&gt;
    &lt;script type="text/javascript"&gt;
      function performAjaxSubmission() {
        $.ajax({
          url: 'file.php',
          method: 'POST',
          data: {
            action: 'save',
            field: $(this).attr("db_field"), 
            val: $(this).attr("db_value")
          },
          success: function() {
            alert("success!");
          }
        });
        return false; // &lt;--- important, prevents the link's href (hash in this example) from executing.
      }

      jQuery(document).ready(function() {
        $(".linkToClick").click(performAjaxSubmission);
      });
    &lt;/script&gt;
  &lt;/head&gt;
  &lt;body&gt;
    &lt;a href="#" class="linkToClick" db_field="field1" db_value="value1"&gt;Click here&lt;/a&gt;
    &lt;a href="#" class="linkToClick" db_field="field2" db_value="value2"&gt;Click here&lt;/a&gt;
    &lt;a href="#" class="linkToClick" db_field="field3" db_value="value3"&gt;Click here&lt;/a&gt;
  &lt;/body&gt;
&lt;/html&gt;

just for record purposes, you need to add

mysql_select_db("database", $link);

to make this work :p

3,178 · October 30, 2010

Mr Lyle, sanitise those inputs!

32,573 · October 30, 2010

I already have lol, i was just eliminating the variables in anything that could possibly go wrong.

1,375 · October 30, 2010

Hi Andrew. If you allow me to suggest one more thing:

It is that you should look into parameterized queries whenever you have time to get some info on it. They've been around quite some time but I RARELY hear any developer talk about them. It seems only good developers know about it since it might be a little different than what we're used to for querying databases.

I've learned them just recently and I can't live without them now. It's so much cleaner and they have way too many advantages for you not to use them. You wouldn't need to waste extra space (neater code!) or waste CPU cycles on sanitizing code plus sanitizing code is prone to human error (it's only as good as your manual sanitation of the code). Not to mention that parameterized queries are faster as well. A parameterized query would look something like this:

$user_id = $_POST['uid'];

$stmt = Database :: prepare ( 'SELECT user_id FROM table1 WHERE user_id = :user_id_ ;' ) ;

$stmt -&gt; bindParam( ':user_id_', $user_id, PDO::PARAM_INT);

$stmt -&gt; execute ( ) ;

$user_id_info = $stmt -&gt; fetch ( PDO::FETCH_ASSOC );

$stmt -&gt; closeCursor ( ) ;

echo $user_id_info;

Where Database :: is a class to manage the PDO (PHP Data Object) which can be found here. http://www.php.net/manual/en/class.pdo.php

I'll post the database class I use if you're interested because I won't post it otherwise. If you want to learn more about it let me know if you have any questions.

As Jeff Atwood from codinghorror.com said: Give me parameterized SQL, or give me death

Sign In

Using jQuery+Ajax to update MySQL

Question

Andrew Lyle Global Moderator

Link to comment

Share on other sites

10 answers to this question

Recommended Posts

jimbo11883

Link to comment

Share on other sites

Andrew Lyle Global Moderator

Link to comment

Share on other sites

jimbo11883

Link to comment

Share on other sites

Andrew Lyle Global Moderator

Link to comment

Share on other sites

AdamLC

Link to comment

Share on other sites

Mike

Link to comment

Share on other sites

Andrew Lyle Global Moderator

Link to comment

Share on other sites

Mike

Link to comment

Share on other sites

Andrew Lyle Global Moderator

Link to comment

Share on other sites

Tekkerson

Link to comment

Share on other sites

Recently Browsing 0 members

Posts

Recent Achievements

Popular Contributors

Tell a friend

Choose your Ad Blocker