PHP Edit/Delete multiple rows in mysql

Question

Good evening, I'm trying to make a single php page wich can edit/delete multiple rows in mysql:

<html>  
<head>  
<title>Update/Delete Test Page</title>  
</head>  
<body> 

<?  
include 'connect.php';

if(isset($_POST['edit'])) // from button name="delete"
{
    $title = $_POST['title'];
    $description = $_POST['description'];
    if(!empty($_POST['checkbox'])) {
    foreach($_POST['checkbox'] as $check) {
        $ed = $check;
        $sql = "UPDATE events SET title = '$title', description ='$description' WHERE id = $ed";  
    }
    }

if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
echo "1 record added";

mysql_close($con);
}
    ?>

<?php
include ("connect.php");

if(isset($_POST['delete'])) // from button name="delete"
{
    if(!empty($_POST['checkbox'])) {
    foreach($_POST['checkbox'] as $check) {
        $del = $check;

        $sql = "DELETE from events where id = $del";
        $result = $mysqli->query($sql) or die(mysqli_error($mysqli));
}
}
?>  

<?php
include 'connect.php';
$query = 'SELECT id, title, description FROM events WHERE evdate = "1/9/2013" order by title asc';
$result2 = $mysqli->query($query) or die(mysqli_error($mysqli));

echo '<br><br><br>';
echo '<b><div align="center"> "1/9/2013"</div></b>';

if ($result2) {

  // create a new form and then put the results
  // into a table.
  echo "<form method='post' action=".$_SERVER['PHP_SELF'].">"; 
  echo "<table cellspacing='0' cellpadding='3'>
    <th align='left'>Interval orar</th>
    <th align='left'>Eveniment</th>
    <th align='left'></th>
    ";

  while ($row = $result2->fetch_object()) {

    $title  = $row->title;
    $description = $row->description;
    $id     = $row->id;

    //put each record into a new table row with a checkbox
echo "
    <tr>
        <td align='left'><input type='text' name='title' size='20'  value='$title'></td>
        <td align='left'><input type='text' name='description' size='50'  value='$description'></td>
        <td align='left'><input type='checkbox' name='checkbox[]' id='checkbox[]'  value=$id />
     </tr>

     "; 
}

// when the loop is complete, close off the list.
echo "</table>
<p>
<input id='edit' type='submit' class='button' name='edit' value='Edit'/> 
<input id='delete' type='submit' class='button' name='delete' value='Delete'/>
</p>
</form>";
}   
?>
</body>  
</html>

Connect.php looks like this:

<?php
$db_host = "localhost";
$db_username = "calendar";
$db_pass = "calendar";
$db_name = "ecalendar";
$con = mysql_connect ("$db_host", "$db_username", "$db_pass") or die ("could not connect to     mysql database");
mysql_select_db("$db_name") or die ("no database");

$mysqli = new MySQLi($db_host, $db_username, $db_pass, $db_name) or die(mysqli_error());
?>

The delete works fine, but the edit doesn't do anything...

Table looks like this:

Start/End Time         Event     (Checkbox is here)
08:00-10:00            test1               X
10:00-12:00            test2               X

When I try to edit test2 to test2xx I get this in Firebug POST :

title=08%3A00-10%3A00&description=test1&title=10%3A00-12%3A00&description=test2xx&checkbox%5B%5D=53&edit=Edit

If I delete I get this (and it works)

title=08%3A00-10%3A00&description=test1&title=10%3A00-12%3A00&description=test2&checkbox%5B%5D=53&delete=Delete

Edit works but only for the last row (test2), if I try to edit the row above (test1), it insted updates it with the values of the last row (test2)

why you using a for loop for check box array? try to use a foreach loop eg: foreach($_POST['checkbox'] as $rows){ } — Arun
– Arun, Commented Jan 7, 2013 at 17:15

Vimalnath · Accepted Answer · 2013-01-07 17:31:51Z

3

Guess you need del_id not id

$sql = "UPDATE events SET title = '$title', description ='$description' WHERE id = $del_id ";

edited Jan 7, 2013 at 17:31

answered Jan 7, 2013 at 17:12

Vimalnath

6,4732 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Arun Over a year ago

Yes it seems the problem is because of this!

Arun Over a year ago

do you get the $_POST['checkbox'] correctly ? or you got any error?

Pascan Sorin Over a year ago

Finaly it gave me some errors. I seem to be missing 2 }} for for($i=0;$i<$countCheck;$i++) and if(isset($_POST['edit']))

Pascan Sorin Over a year ago

Sorry, nevermind, I forgot to check the box. It works now. Thank you all for your help

echo_Me · Accepted Answer · 2013-01-07 18:24:46Z

0

1-look your value value=$id in your chekbob

change it to value= '$id'

2-and also , the delete query is with mysqli but the update query is with mysql ??

3- u dont have to include connect.php 3 times

4- also what previous answer about the variable $del_id.

5- u are missing closing tag td

6- u are missing closing }

7- try this

<html>  
 <head>  
 <title>Update/Delete Test Page</title>  
 </head>  
 <body> 

 <?php  
 include 'connect.php';

 if(isset($_POST['edit'])) // from button name="edit"
 {
 $checkbox = $_POST['checkbox']; //from name="checkbox[]"
 $countCheck = count($_POST['checkbox']);

 for($i=0;$i<$countCheck;$i++)
 {
    $del_id  = $checkbox[$i];

  $sql3 = "UPDATE events SET title = '$title', description ='$description' WHERE id = '$del_id' ";  
  $result3 = $mysqli->query($sql3) or die(mysqli_error($mysqli));
 if (!$result3)
 {
 die(mysqli_error($mysqli)) ;
 }
 echo "1 record added";

 }
 }



  if(isset($_POST['delete'])) // from button name="delete"
 {
 $checkbox = $_POST['checkbox']; //from name="checkbox[]"
 $countCheck = count($_POST['checkbox']);

 for($i=0;$i<$countCheck;$i++)
 {
    $del_id  = $checkbox[$i];

    $sql = "DELETE from events where id = '$del_id' ";
    $result = $mysqli->query($sql) or die(mysqli_error($mysqli));

  }
  }


  $query = 'SELECT id, title, description FROM events WHERE evdate = "1/9/2013" order by title asc';
  $result2 = $mysqli->query($query) or die(mysqli_error($mysqli));

  echo '<br><br><br>';
  echo '<b><div align="center"> "1/9/2013"</div></b>';

 if ($result2) {

 // create a new form and then put the results
 // into a table.
 echo "<form method='post' action=".$_SERVER['PHP_SELF'].">"; 
 echo "<table cellspacing='0' cellpadding='3'>
 <th align='left'>Interval orar</th>
 <th align='left'>Eveniment</th>
 <th align='left'></th>
 ";

 while ($row = $result2->fetch_object()) {

 $title  = $row->title;
 $description = $row->description;
 $id     = $row->id;

  //put each record into a new table row with a checkbox
 echo "
 <tr>
    <td align='left'><input type='text' name='title' size='20'  value='$title'></td>
    <td align='left'><input type='text' name='description' size='50'    value='$description'></td>
    <td align='left'><input type='checkbox' name='checkbox[]' id='checkbox[]'  value='$id' /></td>
  </tr>

 "; 
 }

 // when the loop is complete, close off the list.
 echo "</table>
 <p>
 <input id='edit' type='submit' class='button' name='edit' value='Edit'/> 
  <input id='delete' type='submit' class='button' name='delete' value='Delete'/>
  </p>
 </form>";
 }   
 ?>
 </body>  
 </html>

edited Jan 7, 2013 at 18:24

answered Jan 7, 2013 at 17:40

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

17 Comments

echo_Me Over a year ago

is not helpful my answer ?

Pascan Sorin Over a year ago

missed closing } for if(isset($_POST['edit'])). If I try to edit it gives me Undefined variable: title in test.php on line 19 and Undefined variable: description in test.php on line 19, and it leaves the boxes empty

Pascan Sorin Over a year ago

Undefined variable: result2 in test.php on line 9

Pascan Sorin Over a year ago

I did the same thing (moved that on top). It does the same thing, it copies the last rows values to the one above

echo_Me Over a year ago

what same thing i dont understand what u want exactly , must go out now and back in one hour. u can explain me what u want . and i will see it

|

Shashi · Accepted Answer · 2013-01-07 18:31:22Z

0

id=$del_id under for loop,double check your SQL statements and variables bound to them.

answered Jan 7, 2013 at 18:31

Shashi

4746 silver badges21 bronze badges

1 Comment

Pascan Sorin Over a year ago

I corrected everything and it works but only if I edit the last row (test2). If I try to edit one of the rows above (test1) they are updated with the values of the last line (test2).

Pascan Sorin · Accepted Answer · 2013-01-18 13:48:18Z

-1

gave up on the code above and went with this example from

http://bohemiawebsites.com/PHP-MYSQL-Update-Multiple-Rows.html

Everything works if anybody needs it...

edited Jan 18, 2013 at 13:48

answered Jan 9, 2013 at 11:29

Pascan Sorin

412 silver badges10 bronze badges

Collectives™ on Stack Overflow

PHP Edit/Delete multiple rows in mysql

4 Answers 4

4 Comments

17 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

17 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related