• Sign in to Neowin Faster!

    Create an account on Neowin to contribute and support the site.

  • 0
Sign in to follow this  

Using jQuery+Ajax to update MySQL

Question

Andrew Lyle    340

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.

Share this post


Link to post
Share on other sites

10 answers to this question

Recommended Posts

  • 0
jimbo11883    1

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

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

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

Share this post


Link to post
Share on other sites
  • 0
Andrew Lyle    340

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?

Share this post


Link to post
Share on other sites
  • 0
jimbo11883    1

Yes. I tend to do it like so:

<?php
  if (isset($_POST['action'])) {
    $link = mysql_connect(...);
    mysql_query("...", $link);
    mysql_close($link);
  }
?>
<html>
  <head>
    <script type="text/javascript">
      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; // <--- important, prevents the link's href (hash in this example) from executing.
      }

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

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

Share this post


Link to post
Share on other sites
  • 0
Andrew Lyle    340

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

<?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);
  }
?>
<html>
  <head>
    <script type="text/javascript">
      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; // <--- important, prevents the link's href (hash in this example) from executing.
      }

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

Share this post


Link to post
Share on other sites
  • 0
AdamLC    12

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

<?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);
  }
?>
<html>
  <head>
    <script type="text/javascript">
      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; // <--- important, prevents the link's href (hash in this example) from executing.
      }

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

Looks like that should work fine :)

Share this post


Link to post
Share on other sites
  • 0
Mike    89

Looks like that should work fine :)

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

Share this post


Link to post
Share on other sites
  • 0
Andrew Lyle    340

<?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);
  }
?>
<html>
  <head>
    <script type="text/javascript">
      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; // <--- important, prevents the link's href (hash in this example) from executing.
      }

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

just for record purposes, you need to add

mysql_select_db("database", $link);

to make this work :p

Share this post


Link to post
Share on other sites
  • 0
Mike    89

Mr Lyle, sanitise those inputs!

Share this post


Link to post
Share on other sites
  • 0
Andrew Lyle    340

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

Share this post


Link to post
Share on other sites
  • 0
Tekkerson    65

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 -> bindParam( ':user_id_', $user_id, PDO::PARAM_INT);

$stmt -> execute ( ) ;

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

$stmt -> 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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.