Multiple rows deleting problem

Question

peter20 0 Light Poster

12 Years Ago

Hi, I am trying to delete multiple rows with select buttons.
But my code does not work.
Could you help me ?

$user_query= "SELECT * FROM user";
$user_result = mysql_query($user_query);
$count=mysql_num_rows($user_result);
          
echo "<form name='form1' method='post' action=''>";
echo "<table>";
echo "<tr>";
echo "<th scope='col'>#</th>";
echo "<th scope='col'>username</th>";
echo "<th scope='col'>Email</th>";
echo "<th scope='col'>tel</th>";
echo "<th scope='col'>name</th>";
echo "<th scope='col'>surname</th>";
echo "<th scope='col'>type_of_user</th>";
echo "</tr>";
		  
while($row = mysql_fetch_array($user_result)) 
{
	echo "<tr>";
			  
	echo '<td width="20"><input name="checkbox[]" type="checkbox" username="checkbox[]" value="'.$row['username'].'"></td>';
	echo '<td><b>'.$row['username'].'</b></td>';
	echo '<td><a href="mailto:'.$row['e_mail'].'" title="Αποστολή Email">'.$row['e_mail'].'</a></td>';
	echo '<td>'.$row['phone_number'].'</td>';
        echo '<td>'.$row['name'].'</td>';
	echo '<td>'.$row['surname'].'</td>';
	
        if($row['account_type']=='admin')
		echo '<td><img src="images/admin.png" width="40" height="45" title="admin"/></td>';
	elseif($row['sex']=='man')
		echo '<td><img src="images/user.png" width="40" height="45" title="user" /></td>';
	elseif($row['sex']=='woman')
		 echo '<td><img src="images/woman.png" width="40" height="45" title="user" /></td>';	  
           echo '</tr>';
			
	 }
	 echo '<tr><td><input name="delete" type="submit" id="delete" value="Delete"></td></tr>';
		echo "</table>";
		echo "</form>";
		
		
      
        if($delete){
		for($i=0;$i<$count;$i++){    // I know that here is the problem 
                                             // because I don;t have id
                                             // but how can I correct it ?
			$del_username = $checkbox[$i];
			$sql = "DELETE FROM user WHERE username='$del_username'";
			$result = mysql_query($sql);
			}

		   
		// if successful redirect
		if($result){
		  echo "<meta http-equiv=\"refresh\" content=\"0;URL=ShowUsers.php\">";
		}
	}

php

Edited 12 Years Ago by peter20 because: n/a

2 Contributors
8 Replies
139 Views
8 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by klemme

klemme 3 Posting Whiz in Training

12 Years Ago

Looks like you dont check if the form has been submitted:

if (isset($_POST['delete']))
{
    for($i=0;$i<$count;$i++)
     {
	$del_username = $checkbox[$i];
	$sql = "DELETE FROM user WHERE username='$del_username'";
	$result = mysql_query($sql);
     }
 
 
      // if successful redirect
	if($result)
      {
	header("location: ShowUsers.php");
      }
}

I havent looked very intensive at your code, but it looked like you had a variable $delete (line 44, where did that come from and what information does it have?), shouldnt that be: $_POST, ?

EDIT:

Maybe you should consider using id as value in your form, so the row deleted is unique - if you have set your id in the DB to Primary Key Auto Increment.

<input name="delete_post" type="hidden" value="<?php echo $id; ?>">
And then:
$id = mysqli_real_escape_string($connection, $_POST['id']);
// DELETE QUERY:
$sql = "DELETE FROM user WHERE id='$id'";
//....ETC

Klemme

Edited 12 Years Ago by klemme because: n/a

klemme 3 Posting Whiz in Training

12 Years Ago

No apologizes needed, thats what the forum is for!

I guess you can put your hidden field there, I just allways put them just before the submit button, so your form would look like this:
First I would check if the form has ben submitted, and run the delete query at the top of the page, but before you do your num rows count:

- Note i am using mysqli, so adjust your sql if you have to.

if (isset($_POST['delete']))
{
   for($i=0;$i<$count;$i++)
   {
    // Clean id and username from the form when it has been submitted:
   $id = mysqli_real_escape_string($connection, $_POST['delete_id']);
   $username = mysqli_real_escape_string($connection, $_POST['username']);

   $sql = mysqli_query($connection, "DELETE FROM user WHERE id = '".$id."'");
   }

   if ($sql)
   {
     header("location: ShowUser.php");
     exit();
   }
     else echo '<p>Something went wrong executing the query, try again and fix the code...</p>';
     // Something like that..
}

// The form, information:

// I have moved all the table stuff to make it more clear:
// And only displaying the form fields as i think you are after?
// So you have to put it back to the table layout that fits your headings etc.
echo '<form name="form1" method="post" action="">';
while($row = mysql_fetch_array($user_result)) 
{
 echo '<input name="username" type="checkbox" value="'.$row['username'].'">';
 echo $row['username'];
 echo '<a href="mailto:'.$row['e_mail'].'" title="send Email">'.$row['e_mail'].'</a>';
 echo $row['phone_number'];
 echo $row['name'];
 echo $row['surname'];
 if($row['account_type'] == 'admin')
 {
   echo '<img src="images/admin.png" width="40" height="45" title="admin"/>';
 }
 if($row['sex'] == 'man')
 {
   echo '<img src="images/user.png" width="40" height="45" title="user" />';
 }
 if($row['sex'] == 'woman')
 {
   echo '<img src="images/woman.png" width="40" height="45" title="user" />';
 }	  
// Pass the id in the hidden field here, just before your </form> tag and inside your while loop:
echo '<input name="delete_id" type="hidden" value="'.$id.'">';
}
echo '<input name="delete" type="submit" id="delete" value="Delete">';

echo '</form>';

Try and test it out :-)

You can benefit from mysql_error();

So if it doesnt work, and whenever you have some problems include this in your testing invorenment:

$sql = mysqli_query($connection, "DELETE FROM user WHERE id = '".$id."'") or die(mysqli_error($connection));

EDIT:
TEST YOUR QOUTES AROUND THE FORM ELEMENTS, I HAVE CHANGED THEM A BIT, REPLACING ' WITH "

Edited 12 Years Ago by klemme because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

peter20 0 Light Poster · Answer 1 · 2012-01-23T17:35:22+00:00

Looks like you dont check if the form has been submitted:
if (isset($_POST['delete']))
{
    for($i=0;$i<$count;$i++)
     {
	$del_username = $checkbox[$i];
	$sql = "DELETE FROM user WHERE username='$del_username'";
	$result = mysql_query($sql);
     }
 
 
      // if successful redirect
	if($result)
      {
	header("location: ShowUsers.php");
      }
}
I havent looked very intensive at your code, but it looked like you had a variable $delete (line 44, where did that come from and what information does it have?), shouldnt that be: $_POST, ?
EDIT:
Maybe you should consider using id as value in your form, so the row deleted is unique - if you have set your id in the DB to Primary Key Auto Increment.
<input name="delete_post" type="hidden" value="<?php echo $id; ?>">
And then:
$id = mysqli_real_escape_string($connection, $_POST['id']);
// DELETE QUERY:
$sql = "DELETE FROM user WHERE id='$id'";
//....ETC
Klemme

Thanks a lot Klemme , but could you sent me the code because I did not understand where I have to paste it.
Something like that ?

$user_query= "SELECT * FROM user";
  $user_result = mysql_query($user_query);
  $count=mysql_num_rows($user_result);
          
  echo "<form name='form1' method='post' action=''>";
  echo "<table>";
  echo "<tr>";
  echo "<th scope='col'>#</th>";
  echo "<th scope='col'>username</th>";
  echo "<th scope='col'>Email</th>";
  echo "<th scope='col'>tel</th>";
  echo "<th scope='col'>name</th>";
  echo "<th scope='col'>surname</th>";
  echo "<th scope='col'>type</th>";
  echo "</tr>";
		  
  while($row = mysql_fetch_array($user_result)) 
  {
       echo "<tr>";
       echo '<input name="delete_post" type="hidden" value="'.$id.'">';
       $id = mysqli_real_escape_string($db_x, $_POST['id']);
       echo '<td><input name="checkbox[]" type="checkbox" username="checkbox[]" value="'.$row['username'].'"></td>';
       echo '<td><b>'.$row['username'].'</b></td>';
       echo '<td><a href="mailto:'.$row['e_mail'].'" title="send Email">'.$row['e_mail'].'</a></td>';
       echo '<td>'.$row['phone_number'].'</td>';
       echo '<td>'.$row['name'].'</td>';
       echo '<td>'.$row['surname'].'</td>';
       if($row['account_type']=='admin')
	    echo '<td><img src="images/admin.png" width="40" height="45" title="admin"/></td>';
	elseif($row['sex']=='man')
	     echo '<td><img src="images/user.png" width="40" height="45" title="user" /></td>';
	elseif($row['sex']=='woman')
	     echo '<td><img src="images/woman.png" width="40" height="45" title="user" /></td>';	  
         echo '</tr>';
	 }
	 echo '<tr><td colspan="7"><input name="delete" type="submit" id="delete" value="Delete"></td></tr>';
        echo "</table>";
        echo "</form>";
		
		
		
           if (isset($_POST['delete']))
    	  {
   			 for($i=0;$i<$count;$i++)
    		{
   				 $del_username = $checkbox[$i];
    			$sql = "DELETE FROM user WHERE username='$del_username'";
    			$result = mysql_query($sql);
    		}
     
     
    		// if successful redirect
    		if($result)
    		{
   				 header("location: ShowUsers.php");
    		}
    	}

I apologise but I am novice and I am trying to make my site.

peter20 0 Light Poster · Answer 2 · 2012-01-23T18:49:33+00:00

I changed your code to

if (isset($_POST['delete']))
     {
          for($i=0;$i<$count;$i++) 
          {
              // Clean id and username from the form when it has been submitted:
			  $query = sprintf("DELETE FROM user WHERE id = '".$id."'",
              mysql_real_escape_string($_POST['delete_id']),
              mysql_real_escape_string($_POST['username']));
              $q = mysql($query);
           } 
 
           if ($q)
           {
               header("location: ShowUsers.php");
               exit();
           }
		   
           else echo '<p>Something went wrong executing the query, try again and fix the code...</p>';
          // Something like that..
       }

but It does not work again

klemme 3 Posting Whiz in Training · Answer 3 · 2012-01-23T18:59:58+00:00

What is this for?

$query = sprintf("DELETE FROM user WHERE id = '".$i."'",

That would definately not work - The syntax is wrong, and you have also opened an paranthesis, and not closed it.

Take these steps when you submit the form:
1) Check if the form has been submitted
2) Get the info from the form, and clean the data:
3) Run your delete query:

//1) 
if (isset($_POST['delete']))
{
   for($i=0;$i<$count;$i++)
   {
   // 2) Clean id and username from the form when it has been submitted:
   $id = mysqli_real_escape_string($connection, $_POST['delete_id']);
   $username = mysqli_real_escape_string($connection, $_POST['username']);

   // 3
   $sql = mysqli_query($connection, "DELETE FROM user WHERE id = '".$id."'");
}

Look at the order in the previous post, you have changed them around a bit :-)

Have you tried like this:

if (isset($_POST['delete']))
{
   for($i=0;$i<$count;$i++)
   {
    // Clean id and username from the form when it has been submitted:
   $id = mysqli_real_escape_string($connection, $_POST['delete_id']);
   $username = mysqli_real_escape_string($connection, $_POST['username']);
 
   $sql = mysqli_query($connection, "DELETE FROM user WHERE id = '".$id."'");
   }
 
   if ($sql)
   {
     header("location: ShowUser.php");
     exit();
   }
     else echo '<p>Something went wrong executing the query, try again and fix the code...</p>';
     // Something like that..
}

No need to sprint_f, and put all the queries inside () - Try what i wrote above and see if it works - You can edit your code and run it - and if it doesnt work , try posting your updated code one more time.

EDIT, this is the code you just wrote, i have changed it a bit, try with this:

// CHECK THE ORDER THINGS ARE RUNNING
if (isset($_POST['delete']))
     {
          for($i=0;$i<$count;$i++) 
          {
              // Clean id and username from the form when it has been submitted:
	      
              $id = mysql_real_escape_string($_POST['delete_id']);
              $username = mysql_real_escape_string($_POST['username'])); 
              
              // AND NOW DELETE FROM THE DATABASE
              $query = mysql_query("DELETE FROM user WHERE id = '".$id."'"); // id = to $id - not = $i.             
           } 
 
           if ($query)
           {
               header("location: ShowUsers.php");
               exit();
           }
 
           else echo '<p>Something went wrong executing the query, try again and fix the code...</p>';
          // Something like that..
       }

peter20 0 Light Poster · Answer 4 · 2012-01-23T19:07:03+00:00

peter20 0 Light Poster

12 Years Ago

thanks a lot for yout time!
It works fine

klemme 3 Posting Whiz in Training · Answer 5 · 2012-01-23T19:09:22+00:00

maybe you need to include the connection in your mysql_query?

I use mysqli - With the I extenstion - and the syntax there is:

$sql = mysqli_query($connection, "DELETE FROM user WHERE id = '".$id."'");

// I cant remember if you have to include the db connection in your query too, but i think you have?

$sql = mysql_query("DELETE FROM user WHERE id = '".$id."', $connection");

klemme 3 Posting Whiz in Training · Answer 6 · 2012-01-23T19:10:16+00:00

It works?

Good :-) Mark as solved so no one writes you even more answers..

Klemme