How to use ajax to update mysql db when checkbox condition is changed?

Question

I have a table of articles that displays in rows on client side. Each articles has a unique id and contains a checkbox to indicate if this article is checked as a favorite. If it is a favorite, then the checkbox is already checked. If not, it is unchecked. Now I need either js or jquery and ajax to update the table in the DB if and when the checkbox condition changes specific to each row. Another challenge is that I am working in cakePHP MVC environment.

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

    function checkbox_click (id, favorite)
    {
        // see if checkbox is checked
        if(favorite==1)
        {
            $.ajax({
                type:'POST',
                url:'check_favorite.php', // this external php file isn't connecting to mysql db
                data:'id= ' + id + '&amp;favorite=1',
            });
        }// if
        // the checkbox was unchecked
        else
        {
            $.ajax({
                type:'POST',
                url:'check_favorite.php', // this external php file isn't connecting to mysql db
                data:'id= ' + id + '&amp;favorite=0',
            });
        }//else
    }
</script>

--html-- this is within a foreach loop.

echo "<input type='checkbox' id='$rowid;' name='favorite' checked='checked' onclick='checkbox_click('id','favorite',this();' />";


else
echo "<input type='checkbox' id='$rowid;' name='favorite'  onclick='checkbox_click('id','favorite',this.checked);' />";

--php file called by ajax--

<?php
//Database Variables - with the variables entered it doesn't connect
$dbhost = 'localhost';   // usually localhost
$dbuser = 'username';      // database username
$dbpass = 'password';      // database password

//Establish connection to MySQL database

$con = @mysql_connect($dbhost, $dbuser, $dbpass);
if (!$con)
die('Unable to connect.' . mysql_error());

mysql_select_db('devcake', $con);

// Get the variables.
$query = "UPDATE mytable SET favorite=".$_POST['favorite'] . "
WHERE id=".$_POST['id'] . ";";

mysql_query($query);
mysql_close($con);
?>

Solution 1

This is the code i use (thanks to samuel)

 $('input[name=favorite]').live("click",function(){
    var id    = $(this).attr('id');

    if($(this).attr('checked')) {
        var favorite = 1;
    } else {
        var favorite = 0;
    }

    $.ajax({
        type:'GET',
        url:'favorites.php',
        data:'id= ' + id + '&favorite='+favorite
    });
    //console.log('id: ' + id + ' Publico: '+publico + 'Value: '+value);

 });

Solution 2

This is kind of a doozy!

First, let's sharpen up the html. Checkboxes, by default, send the value 'on' when checked (unless you have specified value in the input tag, which you have not). Go ahead and remove the onClick event entirely, as we'll be doing it with jQuery to simplify things and better separate your behavior from your presentation.

Second, let's get your php file setup properly. If you haven't actually modified the username and password, it's likely that their values should actually be

$dbhost='localhost';
$dbuser='root';
$dbpass='';

Once that connection is working, we need to fix the way we're dealing with the posted data. The way I like to do it is to pull it out into separate variables so that, should something go wrong, I can just do a print variable; and see what value (if any) it has.

A better way to get the posted data inside your php file, and query it, would be the following:

$id=$_POST['id'];
$favorite=$_POST['favorite'];
$query="UPDATE mytable SET favorite='$favorite' WHERE id='$id'";

What this does is changes the value of the favorite column to either 'on' or ''. So, turn to where you actually output the checkboxes and insert some php to check the box if it is checked already in the database. Like so.

 echo "<input type='checkbox' id='$rowid;' name='favorite'";
if(//check in database if 'favorite' column is set to 'on'){
print "checked='checked'";
 }
    echo "/>";

Note the lack of an onClick event. We'll handle that with jquery.

    $('input[name=favorite]').click(function(){
    //if a checkbox with name 'favorite' is clicked, do the following.
    //grab the id from the clicked box
    var id=$(this).attr('id');
    //grab the value from the checkbox (remember, if it is checked it will be 'on', else ''
    var favorite=$(this).val();
    //setup the ajax call
    $.ajax({
            type:'POST',
            url:'check_favorite.php',
            data:'id= ' + id + '&favorite='+favorite
        });
    }
    });

THERE. Whew. I think that ought to take care of some of it, if not the lot of it.