Updating database with ajax on checkbox click

Hi all,

having successfully created a dependent dropdownlist, coding with ajax/jquery under Yii last week, I’m now trying to do the same with a checkbox, but for the moment I’m not as much successful and some help would be greatly appreciated !

[b]

  • What I’m trying to do :

[/b]

When I check/uncheck the checkbox, I would like the corresponding boolean database field to update and the checkbox to change its display state.

[b]

  • My code :

[/b]

[indent]

  • View : index.php

[/indent]


<div id="submenu">

    <?php echo CHtml::link('Créer un utilisateur', array('users/create') ); ?>

</div>

<?php echo CHtml::form('', 'post', array('class'=>'formulaire') ); ?>

<div class="tickets-grid-header">

</div>

<table class="default-grid">

    <thead>

        <th><a href="#">Nom</a></th>

        <th><a href="#">Email</a></th>

        <th><a href="#">Rôle</a></th>

        <th><a href="#">Actif</a></th>

    </thead>

<?php if( !empty($users) && is_array( $users ) ): ?>

    <?php foreach( $users as $u ): ?>

        <tr>

            <td>

                <?php echo CHtml::link($u['name'] . ' '. $u['lastname'], array('users/update', 'user_id' => $u['user_id'] ) ); ?>

            </td>

            <td>

                <?php echo $u['email']; ?>

            </td>

            <td>

                <?php echo $u['itemname']; ?>

            </td>

            <td>

                <?php echo CHtml::checkBox('UserEst_valide', $u['est_valide'],

				array(

				'ajax' => array(

				'type'=>'POST',

				'url'=>CController::createUrl('users/updateUserEst_valide')

				))); ?>

            </td>

        </tr>

    <?php endforeach; ?>

<?php endif; ?>

</table>

</form>

<div class="tickets-grid-footer"></div>

[indent]

  • Controller : UsersController.php

[/indent]


<?php

class UsersController extends CRootController {

	

	public function actionUpdateUserEst_valide()

	{

		$query = "UPDATE users SET est_valide=:est_valide WHERE user_id=:user_id";

		$command = Yii::app()->db->createCommand($query);

		$command->bindValue(':est_valide', $_POST['User']['est_valide'], PDO::PARAM_INT );

		$command->bindValue(':user_id', $u['user_id'], PDO::PARAM_INT );

		$command->execute();

	}

[b]

  • What the issue is :

[/b]

[indent]1- the database doesn’t update[/indent]

[indent]2- the checkbox remains in its original display state (checked or unchecked) when I click on it[/indent]

So thanks in advance to anyone who could help me !

PS : I’m a noob in ajax/jquery/Yii and I got the original code (without my ajax implementation) from an existing project.

The correct approach would be:




<?php echo CHtml::checkBox('UserEst_valide', $u['est_valide'],

                                array(

                                'ajax' => array(

                                'type'=>'POST',

                                'url'=>$this->createUrl('updateUserEst_valide'),

                                'data'=>"js:function(){

                                   var est_valide=$('#UserEst_valide').is(':checked')?0:1;

                                   return 'User[est_valide]='+est_valide;

                                }",

                                ))); ?>



But this ain’t going to work for you since you are not using cactive form.

In order to make this work as you want, you need to add additional javascript, like:




// checkbox.

<?php echo CHtml::checkBox('UserEst_valide', $u['est_valide']); ?>


// javascript code, somewhere in your view.

$('form.formulaire').on("change","input#UserEst_valide",function(){

    var est_valide=$(this).is(':checked')?0:1;

    var formData = 'User[est_valide]='+est_valide;

    $.post('<?php echo $this->createUrl('updateUserEst_valide');?>',formData);

});



Not tested as you can assume, but should work.

(P.S: you controller action shouldn’t be changed)

Thank you twisted1919 for studying my case…

You’re right I’m not using cactive form because the former developper on this project didn’t develop a model for the user, but coded sql requests directly in the controller.

So you’re saying that puting ajax directly in the options of the checkbox won’t work because of that ? But why did it work with a dropdownlist (see Need help creating a dependant dropdown) ?

Anyway, after implementing your javascript code, here is what firebug states : $("form.formulaire").on is not a function

I’ve just changed the view code like this :


<td>

                <?php echo CHtml::checkBox('UserEst_valide', $u['est_valide']/*,

				array(

				'ajax' => array(

				'type'=>'POST',

				'url'=>$this->createUrl('updateUserEst_valide'),

				'data'=>"js:function(){

				   var est_valide=$('#UserEst_valide').is(':checked')?0:1;

				   return 'User[est_valide]='+est_valide;

				}",

				))*/); ?>

            </td>

        </tr>

    <?php endforeach; ?>

<?php endif; ?>

</table>

</form>

<div class="tickets-grid-footer"></div>


<script type="text/javascript">


// javascript code, somewhere in your view.

$('form.formulaire').on("change","input#UserEst_valide",function(){

    var est_valide=$(this).is(':checked')?0:1;

    var formData = 'User[est_valide]='+est_valide;

    $.post('<?php echo $this->createUrl('updateUserEst_valide');?>',formData);

});


</script>

I didn’t touch the actionUpdateUserEst_valide function in the controller code.

Now I’m gonna try to find some tuto to understand your code.

Ah, .on() is available in jquery >= 1.7 and it is a replacement for live()/delegate().

I assumed you where using the latest yii version and also you wrap your jquery code in




$(function(){

   // your code here, please note this is basic jquery stuff.

});


// so your code should be: (if you don't use latest jquery version, replace on() with delegate() )

<script type="text/javascript">

$(function(){


// javascript code, somewhere in your view.

$('form.formulaire').on("change","input#UserEst_valide",function(){

    var est_valide=$(this).is(':checked')?0:1;

    var formData = 'User[est_valide]='+est_valide;

    $.post('<?php echo $this->createUrl('updateUserEst_valide');?>',formData);

});




});

</script>



Yes, i assumed this ain’t going to work since there is no trigger for your action(I.E: how is the ajax request will be triggered, by who?)

Maybe on your dropdown you would have bound the event to "onclick" and in this case you could do the same and bind it "onchange"?

Thanks again twisted1919…

My bad I hadn’t precise… : the project is developped under Yii 1.1.5 and the jquery used is 1.4.2 version since I changed it from 1.3.2 to make my dependent dropdownlist to work.

Here are the different changes I tried since, and the errors raised :

[list=1]

[*]Change 1 : jquery from 1.4.2 to 1.7.1 and wrapping the previous code with $(function(){}); in the view

  • In the CRootController.php file :

Yii::app()->clientScript->scriptMap = array(

            'jquery.js' => 'http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js',//I just replaced 1.4.2 with 1.7.1

            'jquery-ui.js' => 'http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/jquery-ui.min.js',//I didn't touch this line

        );


        Yii::app()->clientScript->registerScriptFile('http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js' ,CClientScript::POS_HEAD);//I just replaced 1.4.2 with 1.7.1

        Yii::app()->clientScript->registerScriptFile('http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/jquery-ui.min.js', CClientScript::POS_END);//I didn't touch this line

  • In the view

<script type="text/javascript">

$(function(){

$('form.formulaire').on("change","input#UserEst_valide",function(){

    var est_valide=$(this).is(':checked')?0:1;

    var formData = 'User[est_valide]='+est_valide;

    $.post('<?php echo $this->createUrl('updateUserEst_valide');?>',formData);

});

});

</script>

  • Error : no error raised. The display state of the checkbox is well changed when I click on it, but the database doesn’t update, yet an ajax request is posted.

[*]Change 2 : replacing on() with delegate() and wrapping the previous code with $(function(){}); in the view, keeping the 1.4.2 jquery version

  • In the view

<script type="text/javascript">

$(function(){

$('form.formulaire').delegate("change","input#UserEst_valide",function(){

    var est_valide=$(this).is(':checked')?0:1;

    var formData = 'User[est_valide]='+est_valide;

    $.post('<?php echo $this->createUrl('updateUserEst_valide');?>',formData);

});

});

</script>

  • Error : no error raised. The display state of the checkbox is well changed when I click on it, but the database doesn’t update. No ajax request seems to be posted.

[/list]

Edit : it looks like the function actionUpdateUserEst_valide() is never executed. Is there a way to test the value of $_POST[‘User’][‘est_valide’] ?

This is what i wanted to see :)

If the ajax request is sent, let’s also see what post params this sends along.

Using firebug/developer tools on chrome, when you watch your ajax request, see the url where it goes and also by clicking in console on the request link you will get a bunch of details about the request.

L.E:

I attached a screenshot from chrome for you to see what data we need, please take a look.

In Form Data tab you should find the data that is sent by your ajax request.

2641

request-details.PNG

Yes that’s what I’ve been looking at during all afternoon yesterday…without finding the light :( :lol:

So here is what I get :

Request URL:http://localhost/straw/index.php?r=users/updateUserEst_valide

Request Method:POST

Status Code:200 OK

Request Headers

Accept:/

Accept-Charset:ISO-8859-1,utf-8;q=0.7,*;q=0.3

Accept-Encoding:gzip,deflate,sdch

Accept-Language:fr-FR,fr;q=0.8,en-US;q=0.6,en;q=0.4

Connection:keep-alive

Content-Length:18

Content-Type:application/x-www-form-urlencoded

Cookie:983ad47b31b307702db909b82b51d0ef=e893355902b4ea606ee65823d2cd7dd4c3437548a%3A4%3A%7Bi%3A0%3Bs%3A2%3A%2212%22%3Bi%3A1%3Bs%3A7%3A%22JuAdmin%22%3Bi%3A2%3Bi%3A2592000%3Bi%3A3%3Ba%3A0%3A%7B%7D%7D; PHPSESSID=9loasbrc256n57h9skhdk9o9c4

Host:localhost

Origin:http://localhost

Referer:http://localhost/straw/index.php?r=users/index

User-Agent:Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11

X-Requested-With:XMLHttpRequest

Query String Parameters

r:users/updateUserEst_valide

Form Data

User[est_valide]:0

Response Headers

Cache-Control:no-store, no-cache, must-revalidate, post-check=0, pre-check=0

Connection:Keep-Alive

Content-Length:0

Content-Type:text/html; charset=UTF-8

Date:Wed, 07 Mar 2012 08:24:52 GMT

Expires:Thu, 19 Nov 1981 08:52:00 GMT

Keep-Alive:timeout=5, max=100

Pragma:no-cache

Server:Apache/2.2.21 (Win32) PHP/5.3.8

X-Powered-By:PHP/5.3.8

According to me, the url where it goes is correct, isn’t it ?

So the data sent is User[est_valide]:0. Knowing that to send this request, I clicked on a previously unchecked checkbox, shouldn’t it be the contrary ?

When I click on an already checked checkbox, the data sent is 1.

So it seems that the data sent is the same as the already recorded one in the database…

Seeing that yesterday, I tried to change my sql request in the controller, writing


$command->bindValue(':est_valide', !$_POST['User']['est_valide'], PDO::PARAM_INT );

with a ! before $_POST but with no effect. I wonder if $_POST[‘User’][‘est_valide’] is correct…

Otherwise, is there no risk that by changing the jquery version like I did, some previous features in the project won’t work anymore ? (I also changed the jqueryui version to the 1.8.18 one).

Then just change:




var est_valide=$(this).is(':checked')?0:1;


// to


var est_valide=$(this).is(':checked')?1:0;



and should do the trick without altering your controller action.

if you see “User[est_valide]:0” then $_POST[‘User’][‘est_valide’] should be 0 in your controller, but why not do a var_dump there and see the result? [ exit(var_dump($_POST)) ]

However, if your est_valide var is one time 0 then the next time is 1 it should change in the database, no matter what. So, who is $u in your actionUpdateUserEst_valide method? i don’t see it instantiated anywhere, do you have error reporting set to E_ALL so that you can see all the errors and warnings?

Updating jquery shouldn’t break any of your existing functionality.

Thank you for answering again twisted1919…

Yes I’ve tried this already but it didn’t change anything.

Sorry I don’t know where to put var_dump. Whether I put var_dump($_POST); or var_dump($_POST[‘User’][‘est_valide’]); in the actionUpdateUserEst_valide function or in the view, nothing shows.

Ok, the problem comes from there :

if I modify the function to WHERE user_id=6, the db updates. About $u, I took it from the view where is coded foreach( $users as $u ) to retrieve the current user id. Obviously I did wrong. But now I wonder what the syntax should be to get the current user id…

Yes I have :

; error_reporting

; Default Value: E_ALL & ~E_NOTICE

; Development Value: E_ALL | E_STRICT

; Production Value: E_ALL & ~E_DEPRECATED

You can retrieve the current user id with Yii::app()->user->getId(); but only if your auth implementation is following the Yii standards.

You should look at the login controller/methods and see how the user_id is stored in case your auth mechanism is something custom.

Other than that, if you fix this "get the user id" issue, everything works good right ?

L.E:

Also, if you need the user id for each user from the foreach loop, you can send it along with the est_valide variable, something like:




<?php foreach( $users as $u ): ?>

        <tr>

            <td>

                <?php echo CHtml::link($u['name'] . ' '. $u['lastname'], array('users/update', 'user_id' => $u['user_id'] ) ); ?>

            </td>

            <td>

                <?php echo $u['email']; ?>

            </td>

            <td>

                <?php echo $u['itemname']; ?>

            </td>

            <td>

                <?php echo CHtml::checkBox('UserEst_valide', $u['est_valide'], array("data-user-id"=>$u['user_id'])); ?>

            </td>

        </tr>

    <?php endforeach; ?>




// js 

<script type="text/javascript">

$(function(){

$('form.formulaire').on("change","input#UserEst_valide",function(){

    var est_valide=$(this).is(':checked')?0:1;

    var user_id = $(this).data("user-id");// or $(this).attr("data-user-id");

    var formData = 'User[est_valide]='+est_valide+'&User[user_id]='+user_id;

    $.post('<?php echo $this->createUrl('updateUserEst_valide');?>',formData);

});

});

</script>




// controller action

public function actionUpdateUserEst_valide()

        {

                $query = "UPDATE users SET est_valide=:est_valide WHERE user_id=:user_id";

                $command = Yii::app()->db->createCommand($query);

                $command->bindValue(':est_valide', (int)$_POST['User']['est_valide'], PDO::PARAM_INT );

                $command->bindValue(':user_id', (int)$_POST['User']['user_id'], PDO::PARAM_INT );

                $command->execute();

        }



Yes, other than the user id, everything works good.

Actually the user I want to get the id from is not the current logged in user, it is the user whom I want to change the status est_valide, for who the name and email and role are on the same row as the checkbox in the datagrid on the view… am I clear ?

Edit : sorry I hadn’t seen your edit yet. Gonna try what you last posted and come back…

see my last edit please(in #10), i assumed that you need the user id from the foreach loop.

Yes twisted1919, that’s it !! Finally it works all good ! :)

The thing is I’d like to understand how the syntax works… do you know a website or a tuto I could read to better understand ?

I specially wonder how it goes from array(“data-user-id”=>$u[‘user_id’]) to $(this).data(“user-id”)

Otherwise, as a last improvement, if I want to raise a confirmation box on the checkbox click, can you tell me where to put the confirm code, if not too complicated ?

for the confirm issue:




<script type="text/javascript">

$(function(){

$('form.formulaire').on("change","input#UserEst_valide",function(){

    if(!confirm("Are you sure ?")){

       return false;

    }

    var est_valide=$(this).is(':checked')?0:1;

    var user_id = $(this).data("user-id");// or $(this).attr("data-user-id");

    var formData = 'User[est_valide]='+est_valide+'&User[user_id]='+user_id;

    $.post('<?php echo $this->createUrl('updateUserEst_valide');?>',formData);

});

});

</script>



For the "data" attribute accessing, this is jquery stuff. any html tag produced by CHtml class has the htmlOptions param, which allows you to pass additional data to the element.

Adding to data-* means that you can access it from jquery with $(elem).data(*).

Anyway, jquery website is the way where you can find real documentation about this :)

Ok twisted1919, what I finally must say is a big big thank you !!

I would have some more questions to better understand, but I think you’ve already spent enough time helping me. Now I need to find some time to read by myself on jquery…

Again, thank you very much twisted1919, your help was greatly appreciated !! :)

no problem, glad i could help.

Learning jquery will be a huge plus for you because Yii is using it and it will make your work with the framework(and not only) way easier :) so just go for it and don’t look back :)

hi firends it as simple as that…plz comment me if u like this…i m new to yii




<?php 


 echo CHtml::checkbox('my_checkbox', false, array(

                              

							    'value'=>'on',

                                'ajax' => array(

                                'type'=>'POST',

                                'url'=>'index.php?r=realestate/default/checkboxUpdate',

                                'data'=>"shafi"

                            //    {

                                //   var est_valide=$('#UserEst_valide').is(':checked')?0:1;

                                 //  return 'User[est_valide]='+est_valide;

                                //}",

 //array(

  //'submit'=>'index.php?r=realestate/default/checkboxUpdate',

  //'params'=> array('my_checkbox'=>'1')

//)

))); ?>

// in the controller


public function actionCheckboxUpdate() {

 

     echo "wow success";

}