Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder student101's Avatar
    Join Date
    Nov 2007
    Posts
    634
    Thanks
    80
    Thanked 15 Times in 15 Posts

    Post Update multiple rows in MySQL with checkboxes

    A useful method to select all checkboxes and the ability to update MySQL records with PHP.
    Code:
    CREATE DATABASE `test`;
    USE `test`;
     
    CREATE TABLE `test_mysql` (
    `id` int(4) NOT NULL auto_increment,
    `name` varchar(65) NOT NULL default '',
    `lastname` varchar(65) NOT NULL default '',
    `email` varchar(65) NOT NULL default '',
    `status` varchar(1) NOT NULL default '',
    PRIMARY KEY (`id`)
    ) TYPE=MyISAM AUTO_INCREMENT=7 ;
     
    --
    -- Dumping data for table `test_mysql`
    --
     
    INSERT INTO `test_mysql` VALUES (1, 'Billly', 'Bob', 'bb@example.com','Y');
    INSERT INTO `test_mysql` VALUES (2, 'James', 'Bell', 'jb@example.com','Y');
    INSERT INTO `test_mysql` VALUES (3, 'Mark', 'Jackson', 'mj@example.com','N');
    INSERT INTO `test_mysql` VALUES (4, 'Linda', 'Travorse', 'lt@example.com','Y');
    INSERT INTO `test_mysql` VALUES (5, 'Joy', 'Ford', 'jf@example.com','Y');
    INSERT INTO `test_mysql` VALUES (6, 'Sidney', 'Gib',  'sgib@example.com','N');
    You can name this page anything you like, multiple_update.php
    PHP Code:
    <?php
    $host
    ="localhost"// Host name 
    $username="username"// Mysql username 
    $password="password"// Mysql password 
    $db_name="test"// Database name 
    $tbl_name="test_mysql"// Table name 
     
    // Connect to server and select databse.
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");
     
    if(isset(
    $_POST['checkbox'])){$checkbox $_POST['checkbox'];
    if(isset(
    $_POST['activate'])?$activate $_POST["activate"]:$deactivate $_POST["deactivate"])
     
    $id "('" implode"','"$checkbox ) . "');" ;
    $sql="UPDATE test_mysql SET status = '".(isset($activate)?'Y':'N')."' WHERE id IN $id" ;
    $result mysql_query($sql) or die(mysql_error());
    }
     
    $sql="SELECT * FROM $tbl_name";
    $result=mysql_query($sql);
     
    $count=mysql_num_rows($result);
    ?>
     
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
     
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Update multiple rows in mysql with checkbox</title>
     
    <script type="text/javascript">
    <!--
    function un_check(){
    for (var i = 0; i < document.frmactive.elements.length; i++) {
    var e = document.frmactive.elements[i];
    if ((e.name != 'allbox') && (e.type == 'checkbox')) {
    e.checked = document.frmactive.allbox.checked;
    }
    }
    }
    //-->
    </script>
     
    </head>
    <body>
     
    <table width="400" border="0" cellspacing="1" cellpadding="0">
    <tr>
    <td><form name="frmactive" method="post" action="">
    <table width="400" border="0" cellpadding="3" cellspacing="1">
    <tr>
      <td colspan="5"><input name="activate" type="submit" id="activate" value="Activate" />
      <input name="deactivate" type="submit" id="deactivate" value="Deactivate" /></td>
      </tr>
    <tr>
    <td>&nbsp;</td>
    <td colspan="4"><strong>Update multiple rows in mysql with checkbox</strong> </td>
    </tr>
    <tr>
    <td align="center"><input type="checkbox" name="allbox" onclick="un_check(this);" title="Select or Deselct ALL" style="background-color:#ccc;"/></td>
    <td align="center"><strong>Id</strong></td>
    <td align="center"><strong>Firstname</strong></td>
    <td align="center"><strong>Lastname</strong></td>
    <td align="center"><strong>Status</strong></td>
    </tr>
    <?php
    while($rows=mysql_fetch_array($result)){
    ?>
    <tr>
    <td align="center"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
    <td><? echo $rows['id']; ?></td>
    <td><? echo $rows['name']; ?></td>
    <td><? echo $rows['lastname']; ?></td>
    <td><? echo $rows['status']; ?></td>
    </tr>
    <?php
    }
    ?>
    <tr>
    <td colspan="5" align="center">&nbsp;</td>
    </tr>
    </table>
    </form>
    </td>
    </tr>
    </table>
     
    </body>
    </html>
    register_globals = Off
    Thanks for your support!
    Update MySQL with checkboxes | Tell A Friend | Delete MySQL with checkboxes

    Give thanks & resolve when done :thumbsup:

  • #2
    New Coder
    Join Date
    Jul 2010
    Location
    AlMadinah
    Posts
    11
    Thanks
    18
    Thanked 0 Times in 0 Posts
    thank you very much ..

    it is very useful code ..

    I will use it in my work..

  • #3
    New Coder
    Join Date
    Jun 2010
    Posts
    68
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Hi,

    I was wondering, is it possible to edit this code so I can use a dropdown menu and one submit button, and to have "Edit" and "Delete" in this dropdown menu, so I can use it to not only update multiple rows, but also to delete multiple rows?

    And can I also use it to change multiple items in the database, not only the status but also another field at the same time?

    I can use the code below to delete, but then the update does not work anymore. Can someone help?

    Code:
    if(isset($_POST['checkbox'])){$checkbox = $_POST['checkbox'];
    if(isset($_POST['activate'])?$activate = $_POST["activate"]:$deactivate = $_POST["deactivate"])
    
    $id = "('" . implode( "','", $checkbox ) . "');" ;
    $sql="DELETE FROM test_mysql WHERE id IN $id" ;
    $result = mysql_query($sql) or die(mysql_error());
    }

  • #4
    Regular Coder student101's Avatar
    Join Date
    Nov 2007
    Posts
    634
    Thanks
    80
    Thanked 15 Times in 15 Posts

    Smile

    There may be better ways to write it but should work all the same.

    Pseudo-Code, just an idea...
    PHP Code:
    if (isset(delete)) {
    delete statement...
    }else{
    Update statement...

    Thanks for your support!
    Update MySQL with checkboxes | Tell A Friend | Delete MySQL with checkboxes

    Give thanks & resolve when done :thumbsup:

  • #5
    New Coder
    Join Date
    Jun 2010
    Posts
    68
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by student101 View Post
    There may be better ways to write it but should work all the same.

    Pseudo-Code, just an idea...
    PHP Code:
    if (isset(delete)) {
    delete statement...
    }else{
    Update statement...

    Thanks, I was indeed using the same thing ("if isset" etc) but I did not use the "else" statement, so maybe I can get it to work now

  • #6
    Regular Coder sitNsmile's Avatar
    Join Date
    Dec 2009
    Location
    Charlotte, NC
    Posts
    358
    Thanks
    19
    Thanked 2 Times in 2 Posts
    Thank you. I've actually been thinking of a good way of doing so. I will test out your code.

  • #7
    New Coder
    Join Date
    Jun 2010
    Posts
    68
    Thanks
    9
    Thanked 0 Times in 0 Posts
    I cannot get this fixed, could someone make a working example?

  • #8
    Regular Coder student101's Avatar
    Join Date
    Nov 2007
    Posts
    634
    Thanks
    80
    Thanked 15 Times in 15 Posts

    Smile

    Quote Originally Posted by badHabitZ View Post
    I cannot get this fixed, could someone make a working example?
    This forum is JUST for posting a PHP snippet. Questions go in the parent forum.
    Thanks for your support!
    Update MySQL with checkboxes | Tell A Friend | Delete MySQL with checkboxes

    Give thanks & resolve when done :thumbsup:

  • #9
    Regular Coder student101's Avatar
    Join Date
    Nov 2007
    Posts
    634
    Thanks
    80
    Thanked 15 Times in 15 Posts

    Thumbs up DELETE multiple rows in MySQL with checkboxes

    DELETE multiple rows in MySQL with checkboxes

    Had many questions about this one, finally coded a dirty DELETE script, please play as needed, improvements welcome!

    Based on: Update multiple rows in MySQL with checkboxes

    PHP Code:
    <?php 
    $host
    ="localhost"// Host name 
    $username="username"// Mysql username 
    $password="password"// Mysql password 
    $db_name="test"// Database name 
    $tbl_name="test_mysql"// Table name
     
    // Connect to server and select databse.
    mysql_connect("$host""$username""$password")or die("cannot connect"); 
    mysql_select_db("$db_name")or die("cannot select DB");
     
    if(isset(
    $_POST['checkbox'])){$checkbox $_POST['checkbox'];
    if(isset(
    $_POST['activate'])?$activate $_POST["activate"]:$deactivate $_POST["deactivate"])
     
    $id "('" implode"','"$checkbox ) . "');" ;
    $sql="UPDATE test_mysql SET status = '".(isset($activate)?'Y':'N')."' WHERE id IN $id" ;
    $result mysql_query($sql) or die(mysql_error());
    }
     
     
    // * Delete multiple via checkboxes * START *
    if(isset($_POST['delete'])) { 
    foreach(
    $_POST['delete'] as $value) { 
    $sql_query mysql_query("DELETE FROM $tbl_name WHERE id = $value"); 

    }
    // * Delete multiple via checkboxes * END *
     
     
    $sql="SELECT * FROM $tbl_name";
    $result=mysql_query($sql);
     
    $count=mysql_num_rows($result);
    ?>
     
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
     
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>DELETE multiple rows in mysql with checkbox</title>
    </head>
    <body>
     
    <table width="400" border="0" cellspacing="1" cellpadding="0">
    <tr>
    <td><form name="frmactive" method="post" action="">
    <table width="400" border="0" cellpadding="3" cellspacing="1">
    <tr>
    <td colspan="6"><input name="activate" type="submit" id="activate" value="Activate" />
    <input name="deactivate" type="submit" id="deactivate" value="Deactivate" />
    <input name="delete" type="submit" id="delete" value="Delete" /></td>
    </tr>
    <tr>
    <td>&nbsp;</td>
    <td colspan="5"><strong>Update multiple rows in mysql with checkbox</strong> </td>
    </tr>
    <tr>
    <td align="center">&nbsp;</td>
    <td align="center"><strong>Delete</strong></td>
    <td align="center"><strong>Id</strong></td>
    <td align="center"><strong>Firstname</strong></td>
    <td align="center"><strong>Lastname</strong></td>
    <td align="center"><strong>Status</strong></td>
    </tr>
    <?php
    while($rows=mysql_fetch_array($result)){
    ?>
    <tr>
    <td align="center"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>
    <td bgcolor="#FF0000" align="center"><input type="checkbox" name="delete[]" id="delete[]" value="<? echo $rows['id']; ?>" /></td>
    <td><? echo $rows['id']; ?></td>
    <td><? echo $rows['name']; ?></td>
    <td><? echo $rows['lastname']; ?></td>
    <td><? echo $rows['status']; ?></td>
    </tr>
    <?php
    }
    ?>
    <tr>
    <td colspan="6" align="center">&nbsp;</td>
    </tr>
    </table>
    </form>
    </td>
    </tr>
    </table>
    </body>
    </html>
    Enjoy!
    Last edited by student101; 09-19-2010 at 09:50 PM. Reason: Based on: Update multiple rows in MySQL with checkboxes
    Thanks for your support!
    Update MySQL with checkboxes | Tell A Friend | Delete MySQL with checkboxes

    Give thanks & resolve when done :thumbsup:


  •  

    Posting Permissions

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