?
Solved

Updating MySql database using jQuery with checkboxes in a repeat region

Posted on 2009-05-01
22
Medium Priority
?
3,751 Views
Last Modified: 2013-12-12
I am trying to find a way to jQuery to enable updating of a MySQL value which is simply 1 or 0. I am using a repeat region behaviour in Dreamweaver CS4 to produce a table of items with columns, one of which has a checkbox whose value is set according to the relevant entry in the MySQL table. If this was a simply html list with checkboxes then jQuery would loop through a form or a div and pick them up, but since I have a repeat region taking MySQL data, and every checkbox in the table has to have a unique identifier, I am having trouble working out how to format the jQuery code.

This is what I have so far in the code (pertinent excerpts only are shown), and I have attached a screen shot of the table so that you can see what I am after. Click in the checkbox should update the database accordingly.
<!-- the jQuery -->
<script type="text/javascript">
$(document).ready(function() 
{
	$('#docVisible').click(function() 
	{
		var cbValue = $('#docVisible').attr('checked') ? 1 : 0;
		$.post('setVisibility.php',cbValue,processData);
		function processData(visValue) 
		{
        	if (visValue=='1') 
			{
           		alert("The document is now visible on the intranet");
        	} 
			else 
			{
           		alert("The document is now not visible on the intranet");
           	}
     	} // end processData
       	return false;
	}); // end click
}); // end ready
</script>
 
<!-- The calling file -->
<input name="docVisible"
    <?php echo $row_rstDocOwnerList['doc_pk']; ?>" 
    id="docVisible<?php echo $row_rstDocOwnerList['doc_pk']; ?>" 
    type="checkbox" 
    value="<?php echo $row_rstDocOwnerList['doc_pk']; ?>" 
    <?php if ($row_rstDocOwnerList['docVisible'] == 1) echo 'checked="checked"'; ?> 
/>
 
<!-- The PHP file -->
<?php
// setVisibility.php - jQuery/php routine to change the visibility of the document
@ $db = new mysqli('server', 'user', 'password', 'database');
if (!$db) die ('Could not connect to the database: '.mysqli_error($db));
// insert the new visibility value
$query1 = sprintf("UPDATE document SET docVisible = %s WHERE docOwnerPK = %s", $cbValue, $_SESSION['MM_Username']);
// Check the new visibility value
$query2 = sprintf("SELECT docVisible FROM document WHERE docOwnerPK = %s", $_SESSION['MM_Username']);
$result2 = $db->query($query2);
$row2 = $result2->fetch_assoc();
$visValue = $row2['docVisible'];
$result2->free();
?>

Open in new window

table.jpg
0
Comment
Question by:kcalder
  • 12
  • 9
22 Comments
 

Author Comment

by:kcalder
ID: 24282637
Sorry, I didn't include the entire repeat region code but only the <input> tag. The repeat region reads as below.
do { ?>
					<tr>
						<td class="revDocListVisibleTitle"><?php echo $row_rstDocOwnerList['docTitle']; ?></td>
						<td class="revDocListVisibleRevision"><?php echo $row_rstDocOwnerList['docRevision']; ?></td>
						<td class="revDocListVisibleZone"><?php echo $row_rstDocOwnerList['zoneTitle']; ?></td>
						<td class="revDocListVisibleVisible">
									<input name="docVisible
										<?php echo $row_rstDocOwnerList['doc_pk']; ?>" 
										id="docVisible<?php echo $row_rstDocOwnerList['doc_pk']; ?>" 
										type="checkbox" 
										value="<?php echo $row_rstDocOwnerList['doc_pk']; ?>" 
										<?php if ($row_rstDocOwnerList['docVisible'] == 1) echo 'checked="checked"'; ?> 
									/>
						</td>
					</tr>
				<?php } while ($row_rstDocOwnerList = mysql_fetch_assoc($rstDocOwnerList)); ?>

Open in new window

0
 
LVL 5

Expert Comment

by:prokvk
ID: 24282867
Let me help understand your problem a lil bit better pls .. You want to know how to loop through your inputs with different IDs via jQuery right ?? Can you pls formulate  the whole desired funcitonality ? :)
0
 
LVL 12

Expert Comment

by:alien109
ID: 24282893
$(":checkbox", ".revDocListVisibleVisible").click(function() {
// update stuff here.
});

This will select only the checkboxes in cells with the class of "revDocListVisibleVisible". Then within your click handler function, you can get the checkbox's id, value and whatever attributes by using $(this).attr("id"), $(this).attr("value")... etc.

Is that what you were looking for?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kcalder
ID: 24282973
Thanks for getting onto my question. In short I simply need to update the database in relation to the value of the checkboxes associated with each item in the table. I have been investigating jQuery and Ajax and like the idea of the asynchronous aspect. The problem here I think is that I am looping through the rows to produce a table using a repeat region and have to tag each checkbox with a unique id if I am to pick it up and pass it back to the database.
0
 
LVL 12

Expert Comment

by:alien109
ID: 24283083
you can do that with the code i posted above. here's the js itegrated in, passing id and value to the server side script

$(function()
{
      $(":checkbox", ".revDocListVisibleVisible").click(function()
      {
            var cbValue = $(this).attr('checked') ? 1 : 0;
            var cbId = $(this).attr('id');      
            $.post('setVisibility.php', {id:cbId, value:cbValue}, processData);
            function processData(visValue)
            {
                  if (visValue=='1')
                  {
                        alert("The document is now visible on the intranet");
                  }
                  else
                  {
                        alert("The document is now not visible on the intranet");
                  }
            }
      });
});
0
 
LVL 12

Accepted Solution

by:
alien109 earned 800 total points
ID: 24283134
and for your callback from the post function - you might want to just use a closure with an anonymous function

$(function()
{
      $(":checkbox", ".revDocListVisibleVisible").click(function()
      {
            var cbValue = $(this).attr('checked') ? 1 : 0;
            var cbId = $(this).attr('id');      
            $.post('setVisibility.php', {id:id, value:value}, function()
            {
                  if (cbValue=='1')
                  {
                        alert("The document is now visible on the intranet");
                  }
                  else
                  {
                        alert("The document is now not visible on the intranet");
                  }
            });
      });
});
0
 

Author Comment

by:kcalder
ID: 24283411
OK thanks, the routine you put forward isnt quite working according to plan. It's not producing an update in the database which is obvious because if a checkbox is clicked the alert always says that the document is "now not available". What's going wrong?
0
 
LVL 12

Assisted Solution

by:alien109
alien109 earned 800 total points
ID: 24283453
I noticed in my second example i accidentally used the wrong keys (sorry).

    $.post('setVisibility.php', {id:id, value:value}, function()

should be:

    $.post('setVisibility.php', {id:cbId, value:cbValue}, function()
0
 

Author Comment

by:kcalder
ID: 24283562
Well that's sorted out the alert but it's still not actually doing anything with the database
0
 
LVL 12

Expert Comment

by:alien109
ID: 24283824
what's does setVisibility.php look like? Is it getting the correct params from the post request?
0
 

Author Comment

by:kcalder
ID: 24283984
It's listed in the code snippet with the original question but I've included in below
<?php
// setVisibility.php - routine to change the visibility of the document
@ $db = new mysqli('svr', 'usr', 'pwd', 'dbs');
if (!$db) die ('Could not connect to the database: '.mysqli_error($db));
// insert the new visibility value
$query1 = sprintf("UPDATE document SET docVisible = %s WHERE docOwnerPK = %s", $cbValue, $_SESSION['MM_Username']);
// Check the new visibility value
$query2 = sprintf("SELECT docVisible FROM document WHERE docOwnerPK = %s", $_SESSION['MM_Username']);
$result2 = $db->query($query2);
$row2 = $result2->fetch_assoc();
$visValue = $row2['docVisible'];
$result2->free();

Open in new window

0
 

Author Comment

by:kcalder
ID: 24286719
I have gone through the code again and adjusted some values to try and ensure that the correct variables are being passed through the routine. However, this is still not interacting with the database. Aarrgggh.
<!-- adjused html/php with correct values for 'id' and 'value'. The recordset rstDocOwnerList simply lists all of the documents in the ownership of the person logged into this session -->
<input type="checkbox" 
name="<?php echo $row_rstDocOwnerList['doc_pk']; ?>" 
id="<?php echo $row_rstDocOwnerList['doc_pk']; ?>" 
value="<?php echo $row_rstDocOwnerList['docVisible']; ?>" 
<?php if ($row_rstDocOwnerList['docVisible'] == 1) echo checked="checked"'; ?> 
/>
 
<!-- adjusted jQuery with visValue being the response from the query in setVisible -->
$(document).ready(function() 
{
    $(function()
    {
        $(":checkbox", ".revDocListVisibleVisible").click(function()
        {
            var cbId = $(this).attr('id');
	    var cbValue = $(this).attr('checked') ? 1 : 0;  
            $.post('setVisibility.php', {id:cbId, value:cbValue}, function(visValue)
            {
                if (visValue=='1')
                {
                    alert("The document is now visible on the intranet");
                }
                else
                {
                    alert("The document is now not visible on the intranet");
                }
            }); // end function(visValue)
      	}); // end click(function)
    }); // end function()
}); // end ready
 
<!-- adjusted query strings in setVisibility.php -->
$query1 = sprintf("UPDATE document SET docVisible = %s WHERE doc_pk = %s", $_POST['cbValue'], $_POST['cbld']);
// Check the new visibility value
$query2 = sprintf("SELECT docVisible FROM document WHERE doc_pk = %s", $_POST['cbld']);
$result2 = $db->query($query2);
$row2 = $result2->fetch_assoc();
$visValue = $row2['docVisible'];
$result2->free();

Open in new window

0
 
LVL 12

Expert Comment

by:alien109
ID: 24287065
the two values being sent to your php script are in the post parameters with the keys id and value. in your php script, you are looking for cbValue. To make the javascript integrate correctly change

$.post('setVisibility.php', {id:cbId, value:cbValue}

to

$.post('setVisibility.php', {id:cbId, cbValue:cbValue}

In the event handler function, you've changed the name of the variable from cbValue back to visValue. This variable needs to be the same name as the cbValue variable that is in javascript, not PHP.  In other words, the variable $visValue in PHP is not passed to the javascript function. it needs to be the variable you set here:

var cbValue = $(this).attr('checked') ? 1 : 0;

In your php script, you have the following line:
$query1 = sprintf("UPDATE document SET docVisible = %s WHERE docOwnerPK = %s", $cbValue, $_SESSION['MM_Username']);

Where is $cbValue being set? If you are intending this to be the value that is passed from the javascript ajax call, you'll need to grab the value from the post params: $_POST["cbValue"]. That is assuming that you have changed the javascript to pass "cbValue", as the key, rather than "value" in my original example.

Once you are sure all of your variable names are correct all the way through, if you are still having problems, try debugging your database call and make sure that 1 - the sql statement is correct and not throwing an error and 2 - that the values being passed into the statement are correct.

You mentioned that you need the id of the checkbox passed to the php script, but i don't see anywhere in the sql that you are using that information to update the record.
0
 

Author Comment

by:kcalder
ID: 24287747
If you check the revised code I uploaded you will see that I have picked up $_POST['cbld'] and used it in the queryfor the document primary key identifier, and I have picked up $_POST['cbValue'] and used it for the checkbox value. However, I have changed $visValue in setVisibility,php to $cbValue.

However, in line with your suggestion I have amended the jQuery line
$.post('setVisibility.php', {id:cbId, value:cbValue}, function(visValue) back to ,function(). I don't quite understand why value:cbValue should change to cbValue:cbValue however. I thought that 'value' in this content is one of the html parameters, in the same way that the element's id is communicated by id:cbld. Is this not the case?

I have also amended $visValue = $row2['docVisible']; so that it reads $cbValue = $row2['docVisible'];

At present, it seems to me that cbValue is being read straight from the jQuery expression if (cbValue=='1') rather than by querying the database. This is borne out because although I am getting the expected alert window message when clicking/re-clicking on the checkbox, the database is not updating.

I must say that this is all quite frustrating! I thought that using jQuery for such a relatively simple operation would be straightforward.
0
 

Author Comment

by:kcalder
ID: 24290781
What of the callback? In the code below I have set processData as a callback function with 'data' as the variable to take the server response. why does this not work? If I express 'data==1' as 'cbValue=1' all the code will do is to take the value of the checkbox once it is clicked without any reference to the server, and this doesn't provide me with any kind of check that the database has been updated with the new value. I want the value from $query2 in setVisibility.php which is the new value that has been updated by $query1.

How does the server provide a response to the callback function?
$(document).ready(function() 
{
    $(":checkbox", ".revDocListVisibleVisible").click(function()
    {
    	var cbid = $(this).attr('id');
	var cbValue = $(this).attr('value') ? 1 : 0;  
        $.post('setVisibility.php', {id: 'cbid', value: 'cbValue'}, processData);
	function processData(data)
	{
       	    if (data==1)
            {
           	alert("The document is now visible on the intranet");
            }
            else
	    {
            	alert("The document is now not visible on the intranet");
            }
	}); // end processData
    }); // end click
}); // end ready

Open in new window

0
 
LVL 12

Expert Comment

by:alien109
ID: 24295274
>>I don't quite understand why value:cbValue should change to cbValue:cbValue

the values passed in the post are expressed and key value pairs (key:value, key:value,...) Since you were looking for a parameter named cbValue in your php script, you'll need to pass it with the key cbValue, and not "value" as I had originally posted.

so, in your latest post, the line:
    $.post('setVisibility.php', {id: 'cbid', value: 'cbValue'}

will actually be sending a parameter called "id", with the string value of "cbid" and a parameter called "value", with the string value of "cbValue". I'm almost positive that this is NOT what you want.

If you want to pass a value back from the server, you'll need to send something back in the response. You can do this any number of ways. I personally am a big fan of json, so I would do something like the following:

If you change (keeping in mind that the code below still passes the wrong key/value pairs!) to
    $.post('setVisibility.php', {id: 'cbid', value: 'cbValue'}, processData, 'json');

Within the scope of the processData function your data argument will contain an object which contains the data passed back from the server as a javascript object.

If you are using PHP5 - you can simply encode your response object as such:
    echo json_encode(array("docVisible"=>$visValue));

Then in your processData function, you can access the docVisible property as data.docVisible

You don't need to use json however, and could simply pass back a string, however, I like stricter data typing and the ability to scale easier if later you decide to pass back more than a single value.
0
 

Author Comment

by:kcalder
ID: 24302184
OK thanks. I do understand the key value pairs relationship, I forgot to say that I had amended the php variable to expect $_POST['value'] as opposed to $_POST['cbValue'].

It looks like passing something back from the server is done via 'echo'ing it, is that right?

Setting json aside for a moment and sticking with a simple string return, I have ended up with the jQuery and php code as below but it's still not working right. Interestingly, if I check or un-check a box I get the "not visible" but there's nothing happening in the database.
// jQuery
$(document).ready(function() 
{
  $(":checkbox",".revDocListVisibleVisible").click(function()
  {
    var cbid = $(this).attr('id');
    var cbValue = $(this).attr('value') ? 1 : 0;  
    $.post('setVisibility.php',{id: 'cbid', value: 'cbValue'},processData);
    function processData(visValue)
    {
      if (visValue==1)
      {
        alert("The document is now visible on the intranet");
      }
      else
      {
        alert("The document is now not visible on the intranet");
      }
    }
  }); // end click
}); // end ready
 
// setVisibility.php - jQuery/php routine to change the visibility of the document
@ $db = new mysqli('server', 'user', 'password', 'database');
if (!$db) die ('Could not connect to the database: '.mysqli_error($db));
// insert the new visibility value
$query1 = sprintf("UPDATE document SET docVisible = %s WHERE doc_pk = %s", $_POST['value'], $_POST['id']);
// Check the new visibility value
$query2 = sprintf("SELECT docVisible FROM document WHERE doc_pk = %s", $_POST['id']);
$result2 = $db->query($query2);
$row2 = $result2->fetch_assoc();
$visValue = $row2['docVisible'];
echo $visValue;
$result2->free();

Open in new window

0
 
LVL 12

Expert Comment

by:alien109
ID: 24303536
If you have verified that the correct values are being sent to the server, and the correct values are being returned. Then I'd say the problem is either your SQL statement, or some other database issue.

again, your script above will not be sending the correct values. it is sending the string cbId and the string cbValue, rather than the actual values OF those variables. Remove the single quotes and this should fix that problem.

It's very possible that the reason you are not seeing a database update, is that you are trying to set values in the database with the incorrect datatype. I'm guessing that the database is expecting INTs or some numeric type, and you are passing strings.
0
 

Author Comment

by:kcalder
ID: 24307347
I have removed the quotation marks from the jQuery values.

Yes, the database is expecting INT values so I have amended the code as follows:

$theID = (int)$_POST['id'];
$theValue = (int)$_POST['value'];
$query1 = sprintf("UPDATE document SET docVisible = %s WHERE doc_pk = %s", $theValue, $theID);
// Check the new visibility value
$query2 = sprintf("SELECT docVisible FROM document WHERE doc_pk = %s", $theID);

but there is still nothing happening in the database. I am struggling to see where the problem lies.
0
 
LVL 12

Expert Comment

by:alien109
ID: 24307461
did you verify, through some sort of debugging, that $theID and $theValue have the correct values?
0
 

Author Comment

by:kcalder
ID: 24308209
I am not convinced that there is anything wrong with the PHP file. I commented out everything but the actual database connection and the live behaviour is still the same, which suggests that the problem lies with the jQuery. As I wrote previously, all I am getting is the "not visible" alert whether I click on a checked or unchecked box.
0
 

Author Comment

by:kcalder
ID: 24396328
OK, I have the jQuery working now, thanks. My problem is that I am getting no response from the php file, something I have picked up in another question you have been helping me with. Again, thanks for persevering!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month15 days, 19 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question