Yii Framework Forum: Updating database with ajax on checkbox click - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Updating database with ajax on checkbox click Rate Topic: -----

#1 User is offline   Yiaan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 01-March 12
  • Location:France

Posted 05 March 2012 - 11:20 AM

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 !

  • What I'm trying to do :

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

  • My code :

  • View : index.php

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


  • Controller : UsersController.php

<?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();
	}


  • What the issue is :

1- the database doesn't update

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


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.
0

#2 User is offline   twisted1919 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 609
  • Joined: 23-October 10
  • Location:Romania

Posted 05 March 2012 - 12:31 PM

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)
0

#3 User is offline   Yiaan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 01-March 12
  • Location:France

Posted 06 March 2012 - 04:00 AM

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.
0

#4 User is offline   twisted1919 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 609
  • Joined: 23-October 10
  • Location:Romania

Posted 06 March 2012 - 06:53 AM

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>


Quote

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 ?


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"?
0

#5 User is offline   Yiaan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 01-March 12
  • Location:France

Posted 06 March 2012 - 09:48 AM

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 :

  • 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.


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

#6 User is offline   twisted1919 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 609
  • Joined: 23-October 10
  • Location:Romania

Posted 06 March 2012 - 04:15 PM

View PostYiaan, on 06 March 2012 - 09:48 AM, said:


[*]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.


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.

Attached File  request-details.PNG (44.01K)
Number of downloads: 39
0

#7 User is offline   Yiaan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 01-March 12
  • Location:France

Posted 07 March 2012 - 03:55 AM

View Posttwisted1919, on 06 March 2012 - 04:15 PM, said:

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.

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 ?

View Posttwisted1919, on 06 March 2012 - 04:15 PM, said:

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

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).
0

#8 User is offline   twisted1919 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 609
  • Joined: 23-October 10
  • Location:Romania

Posted 07 March 2012 - 06:08 AM

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.
0

#9 User is offline   Yiaan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 01-March 12
  • Location:France

Posted 07 March 2012 - 07:47 AM

Thank you for answering again twisted1919...

View Posttwisted1919, on 07 March 2012 - 06:08 AM, said:

Then just change:
var est_valide=$(this).is(':checked')?0:1;
// to
var est_valide=$(this).is(':checked')?1:0;
Yes I've tried this already but it didn't change anything.

Quote

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)) ]
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.

Quote

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,
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...

Quote

do you have error reporting set to E_ALL so that you can see all the errors and warnings?
Yes I have :
; error_reporting
; Default Value: E_ALL & ~E_NOTICE
; Development Value: E_ALL | E_STRICT
; Production Value: E_ALL & ~E_DEPRECATED
0

#10 User is offline   twisted1919 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 609
  • Joined: 23-October 10
  • Location:Romania

Posted 07 March 2012 - 08:24 AM

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();
        }

0

#11 User is offline   Yiaan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 01-March 12
  • Location:France

Posted 07 March 2012 - 08:34 AM

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...
0

#12 User is offline   twisted1919 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 609
  • Joined: 23-October 10
  • Location:Romania

Posted 07 March 2012 - 08:36 AM

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

#13 User is offline   Yiaan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 01-March 12
  • Location:France

Posted 07 March 2012 - 09:17 AM

View Posttwisted1919, on 07 March 2012 - 08:36 AM, said:

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 ?
0

#14 User is offline   twisted1919 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 609
  • Joined: 23-October 10
  • Location:Romania

Posted 07 March 2012 - 09:30 AM

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 :)
0

#15 User is offline   Yiaan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 16
  • Joined: 01-March 12
  • Location:France

Posted 07 March 2012 - 10:12 AM

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 !! :)
0

#16 User is offline   twisted1919 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 609
  • Joined: 23-October 10
  • Location:Romania

Posted 07 March 2012 - 10:47 AM

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 :)
0

#17 User is offline   shafi 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 03-August 12
  • Location:cochin keral india

Posted 15 October 2012 - 03:17 AM

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";
}

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users