SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multi Update Via Checking Box

    Hello there,
    I'm looking forward for a way on updating multi rows in my database.
    I have a table with many fields and 4 of them are ENUM fields with values YES or NO (IsActive, Index, Hot, New). If the value of "IsActive=YES' the row is active (Index=is showin in index; hot=is showing a hot image; new=is showing a new image). When i list all the rows of that db, for those field i have made a checkbox that is checked if the value is YES and unchecked if the value is NO. What i need to do now, is a way to modify multi rows of db only the field affected by checking or unchecking option and pressing a submit button.
    When i press the submit button, will be updated only rows that has any changes on it and other rows will remain intact.

    I have attached an print screen of my page to gett a better idea on what i want to do.

    Hope that someone will help me on this!

    Thank you in advance!
    Attached Images Attached Images

  2. #2
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you use IN in your SQL/mySql Where statement you are able to update multiple values. I.E.
    Code:
      UPDATE
      yourTable
      SET yourField = yourValue
      WHERE yourField IN ( 1, 3, 5, 8, 9 )
    Last edited by Mittineague; Dec 11, 2010 at 19:31. Reason: fixing bbcode tag

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by donboe View Post
    When you use IN in your SQL/mySql Where statement you are able to update multiple values. I.E.
    Code:
      UPDATE
      yourTable
      SET yourField = yourValue
      WHERE yourField IN ( 1, 3, 5, 8, 9 )
    But, since i'll have 4 fields for update which of them should be on
    Code:
    ... SET 
    yourField = yourValue
      WHERE ...
    In place of your number what should be? Field names or values?

    thank you for your help and sorry for my stupid questions

  4. #4
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first thing you need to do is make sure all the checkboxes for each ID in your form have the same name. Then each ID will send an array of checked checkboxes.

    The name should include the ID value to make it easier in the php script to see which ID the checkbox values belong to.

    Your php script will then receive an array of checked checkbox values for each ID.

    You then loop thorough each set of checkboxes for each ID and build your update query based on the values of the checkboxes for that ID. The value of each checkbox will tell you which columns you need to include in your update query.

    After you build an update query for each ID, then run it to update the selected columns for that ID.

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kalon View Post
    The first thing you need to do is make sure all the checkboxes for each ID in your form have the same name. Then each ID will send an array of checked checkboxes.

    The name should include the ID value to make it easier in the php script to see which ID the checkbox values belong to.

    Your php script will then receive an array of checked checkbox values for each ID.

    You then loop thorough each set of checkboxes for each ID and build your update query based on the values of the checkboxes for that ID. The value of each checkbox will tell you which columns you need to include in your update query.

    After you build an update query for each ID, then run it to update the selected columns for that ID.
    Thank you for your help, but, since i'm new in php i don't know how to do that.
    Field Names are:
    Code:
    PakIndex
    PakHot
    PakNew
    PakAktiv
    And the html list looks like this:
    HTML Code:
    <span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="PakAktiv" value="2" /></center></span>
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakAktiv" value="2" /></center></span>
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakAktiv" value="2" checked="checked" /></center></span>
    <span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="PakAktiv" value="2" checked="checked" /></center></span>
    Where value=ID of the row. In this case, the row have ID 2

    I have changed the type of field for this fields to TINYINT with values 1 and 0 from ENUM.
    The tablename is paketa.
    Now, having all this details, can you help me to build that query and function, PLEASE?

    Thank you in advance!

  6. #6
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    to give checkboxes the same name, you do it like this

    Code:
     
    <input type="checkbox" name="chkBox_1[]" value="val_1" />
    <input type="checkbox" name="chkBox_1[]" value="val_2" />
    <input type="checkbox" name="chkBox_1[]" value="val_3" />
    <input type="checkbox" name="chkBox_1[]" value="val_4" />
    and then each checkbox that is checked will have its value sent to the server side script.

    assuming you used method="post" in your form then $_POST['chkBox_1'] in your php script will contain the checked values.

    these general coding and debugging principles might help you build your code.

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kalon View Post
    to give checkboxes the same name, you do it like this

    Code:
     
    <input type="checkbox" name="chkBox_1[]" value="val_1" />
    <input type="checkbox" name="chkBox_1[]" value="val_2" />
    <input type="checkbox" name="chkBox_1[]" value="val_3" />
    <input type="checkbox" name="chkBox_1[]" value="val_4" />
    and then each checkbox that is checked will have its value sent to the server side script.

    assuming you used method="post" in your form then $_POST['chkBox_1'] in your php script will contain the checked values.
    Yes, i'm using Post and my php side script looks like this:
    PHP Code:
    $checkedaktiv = ($row['PakAktiv']==1) ? 'checked="checked"' '';
                
    $checkednew = ($row['PakNew']==1) ? 'checked="checked"' '';
                
    $checkedhot = ($row['PakHot']==1) ? 'checked="checked"' '';
                
    $checkedindex = ($row['PakIndex']==1) ? 'checked="checked"' '';

    <
    span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="PakAktiv" value="'.$row['PakID'].'" '.$checkedindex.'/></center></span>
                            <
    span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakAktiv" value="'.$row['PakID'].'" '.$checkedhot.'/></center></span>
                            <
    span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakAktiv" value="'.$row['PakID'].'" '.$checkednew.'/></center></span>
                            <
    span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="PakAktiv" value="'.$row['PakID'].'" '.$checkedaktiv.'/></center></span
    You said that i should use name=chkBox_1[] for each one, but those 4 field are 4 diferent field and have their diferent field on db too.
    Also, value="val_1" where val_1 should be what?

    Thank you very much for helping

  8. #8
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the names and values I used are just for demo purposes.

    you're supposed to substitute your own names and values in your code to suit your application

  9. #9
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kalon View Post
    the names and values I used are just for demo purposes.

    you're supposed to substitute your own names and values in your code to suit your application
    Ok, my php looks like this now:
    PHP Code:
    <span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="PakIndex_'.$row['PakID'].'[]" value="'.$row['PakIndex'].'" '.$checkedindex.'/></center></span>

    <
    span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakHot_'.$row['PakID'].'[]" value="'.$row['PakHot'].'" '.$checkedhot.'/></center></span>

    <
    span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakNew_'.$row['PakID'].'[]" value="'.$row['PakNew'].'" '.$checkednew.'/></center></span>

    <
    span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="PakAktiv_'.$row['PakID'].'[]" value="'.$row['PakAktiv'].'" '.$checkedaktiv.'/></center></span
    ...where name=FielName_RowID[] and Value=FielRecord, 0 or 1. Where the value is 0 the checkbox is blank, and at value = 1 the checkbox is ticked.
    Hope that since here i'm doing well. Now, how to build the update query? can you help me on this too, please?

    Thank you!

  10. #10
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you might be jumping the gun a little here.

    did you read the coding and debugging link I posted earlier?

    in it I suggest the first thing you need to do in your php script is make sure the form data is coming across correctly. if it isn't, there is no point in coding anything else until the form data comes across correctly.

    now that you have changed the names in your form, have you checked the values of the checkbox are being received correctly in the php script?

  11. #11
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kalon View Post
    you might be jumping the gun a little here.

    did you read the coding and debugging link I posted earlier?

    in it I suggest the first thing you need to do in your php script is make sure the form data is coming across correctly. if it isn't, there is no point in coding anything else until the form data comes across correctly.

    now that you have changed the names in your form, have you checked the values of the checkbox are being received correctly in the php script?
    Yep, i have checked and they are working correctly. In the first post i had a mistake in coding since i give the same name to all four fields and that wasn't correctly since every field has it's own name and it's own field in db.

    the Html looks like this now:
    HTML Code:
    <span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="PakIndex_4[]" value="1" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakHot_4[]" value="1" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakNew_4[]" value="1" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="PakAktiv_4[]" value="0" /></center></span>
    
    <br />
    
    <span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="PakIndex_3[]" value="1" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakHot_3[]" value="1" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakNew_3[]" value="1" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="PakAktiv_3[]" value="0" /></center></span>
    
    <br />
    
    <span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="PakIndex_1[]" value="0" /></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakHot_1[]" value="0" /></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="PakNew_1[]" value="0" /></center></span>
    
    <span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="PakAktiv_1[]" value="0" /></center></span>

  12. #12
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the naming system you used will probably still be ok but imho will make your php code trickier.

    I would have done it this way.

    Going back to your image of checkboxes in post 1, I would have given the 4 checkboxes for each ID the same name and the value of each checkbox for an id set to the column name it represents in the database. This way, the array of checked check boxes for each id will contain the column names you want to update.

    So for ID = 1, I would do this

    Code:
     
    <input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakIndex" checked="checked"/>
    <input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakHot" checked="checked"/>
    <input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakNew" checked="checked"/>
    <input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakAktiv" />
    and then similarly for each 4 checkboxes for the other ID's

    Then you can loop through each array of checkboxes to see which column names were checked and you can then build your update query with those column names.

  13. #13
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kalon View Post
    the naming system you used will probably still be ok but imho will make your php code trickier.

    I would have done it this way.

    Going back to your image of checkboxes in post 1, I would have given the 4 checkboxes for each ID the same name and the value of each checkbox for an id set to the column name it represents in the database. This way, the array of checked check boxes for each id will contain the column names you want to update.

    So for ID = 1, I would do this

    Code:
     
    <input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakIndex" checked="checked"/>
    <input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakHot" checked="checked"/>
    <input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakNew" checked="checked"/>
    <input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakAktiv" />
    and then similarly for each 4 checkboxes for the other ID's

    Then you can loop through each array of checkboxes to see which column names were checked and you can then build your update query with those column names.
    This is the problem...that i don't have a clue how to do it since i'm new on php.

    My Php script looks like this now:
    PHP Code:
    <span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="chk_id_'.$row['PakID'].'[]" value="PakIndex" '.$checkedindex.'/></center></span>

    <
    span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="chk_id_'.$row['PakID'].'[]" value="PakHot" '.$checkedhot.'/></center></span>

    <
    span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="chk_id_'.$row['PakID'].'[]" value="PakNew" '.$checkednew.'/></center></span>

    <
    span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="chk_id_'.$row['PakID'].'[]" value="PakAktiv" '.$checkedaktiv.'/></center></span

    and html code looks like his:
    HTML Code:
    <span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="chk_id_4[]" value="PakIndex" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="chk_id_4[]" value="PakHot" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="chk_id_4[]" value="PakNew" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="chk_id_4[]" value="PakAktiv" /></center></span>
    
    
    <br/>
    
    <span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="chk_id_3[]" value="PakIndex" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="chk_id_3[]" value="PakHot" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="chk_id_3[]" value="PakNew" checked="checked"/></center></span>
    
    <span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="chk_id_3[]" value="PakAktiv" /></center></span>
    
    
    <br />
    
    <span class="result_column" style="width: 7%;"><center><input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakIndex" /></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakHot" /></center></span>
    
    <span class="result_column" style="width: 6%;"><center><input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakNew" /></center></span>
    
    <span class="result_column" style="width: 8%;"><center><input type="checkbox" style="checkbox" name="chk_id_1[]" value="PakAktiv" /></center></span>
    Thank you!

  14. #14
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kleidi View Post
    This is the problem...that i don't have a clue how to do it since i'm new on php.
    ok then it looks like you're probably a little ahead of yourself taking on a project like this which requires you to know at least the basics of php and sql.

    perhaps, you would better off at this stage to first get a good book on php and sql and work through that learning the basics. Basically learn to walk with php before you start to run with it.

    short of actually writing the code for you there probably isn't much more I can do at this stage.

    so out of curiosity, why are you taking on a project of this size rather than a smaller project since you are new to php?

  15. #15
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kalon View Post
    ok then it looks like you're probably a little ahead of yourself taking on a project like this which requires you to know at least the basics of php and sql.

    perhaps, you would better off at this stage to first get a good book on php and sql and work through that learning the basics. Basically learn to walk with php before you start to run with it.

    short of actually writing the code for you there probably isn't much more I can do at this stage.

    so out of curiosity, why are you taking on a project of this size rather than a smaller project since you are new to php?
    I'm learning and reading tutorials but, for this type of process does not exists many tutorials. This is not a big project but a little one and a basic one. To work with check-boxes is the most hard work/process on my project. I was and I'm just learning and i don't like theory :P ... I'm learning evrything with examples and practice.
    In this case, if you can write the code, i'll be able to understand and continue it.
    Anyway, thank you for your help. It was very helpful for me.
    Thanks!

  16. #16
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Kleidi View Post
    In this case, if you can write the code, i'll be able to understand and continue it.
    Anyway, thank you for your help. It was very helpful for me.
    Thanks!
    It'll take less time for me to help you fix the code you come up with from the tutorials you are doing than to put the code together for you and simply post it.

    since you like learning from examples and practice, why not have a go at writing the code yourself and if you get stuck post your code and we can try to help.

    the concepts I posted earlier in the coding and debugging link might help in your general work flow.

  17. #17
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,139
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I still think SQL/mySql should do the work here. Kalon is absolutely right all select boxes should have the same name. (lets call them status) Since the rows you want to update are already in the database (otherwise you would not talk about an update) you can use a hidden holding the ID (id) for each row. Your update query would than look like:
    Code MySQL:
      UPDATE 
        your_table
      SET 
        status = 1
      WHERE 
        id IN ( #Form.id# )

    This normally works for me


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •