Jump to content

Super basic, deleting rows with checkbox


downah

Recommended Posts

Hi guys,

 

I am trying out this code but it does not work, any idea what I am doing wrong?

 

<?php

include 'connect.php';

$tbl_name="users";

 

$sql="SELECT * FROM $tbl_name";

$result=mysql_query($sql);

 

$count=mysql_num_rows($result);

 

?>

<table width="400" border="0" cellspacing="1" cellpadding="0">

<tr>

<td><form name="form1" method="post" action="">

<table width="400" border="0" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC">

<tr>

<td bgcolor="#FFFFFF"> </td>

<td colspan="4" bgcolor="#FFFFFF"><strong>Delete multiple rows in mysql</strong> </td>

</tr>

<tr>

<td align="center" bgcolor="#FFFFFF">#</td>

<td align="center" bgcolor="#FFFFFF"><strong>Id</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>Firstname</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>Lastname</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>Location</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>Email</strong></td>

<td align="center" bgcolor="#FFFFFF"><strong>Website</strong></td>

</tr>

<?php

while($rows=mysql_fetch_array($result)){

?>

<tr>

<td align="center" bgcolor="#FFFFFF"><input name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $rows['id']; ?>"></td>

<td bgcolor="#FFFFFF"><? echo $rows['userID']; ?></td>

<td bgcolor="#FFFFFF"><? echo $rows['firstname']; ?></td>

<td bgcolor="#FFFFFF"><? echo $rows['lastname']; ?></td>

<td bgcolor="#FFFFFF"><? echo $rows['location']; ?></td>

<td bgcolor="#FFFFFF"><? echo $rows['email']; ?></td>

<td bgcolor="#FFFFFF"><? echo $rows['website']; ?></td>

</tr>

<?php

}

?>

<tr>

<td colspan="5" align="center" bgcolor="#FFFFFF"><input name="delete" type="submit" id="delete" value="Delete"></td>

</tr>

<?

 

if($delete){

for($i=0;$i<$count;$i++){

$del_id = $checkbox[$i];

$sql = "DELETE FROM $tbl_name WHERE id='$del_id'";

$result = mysql_query($sql);

}

 

 

if($result){

echo "<meta http-equiv=\"refresh\" content=\"0;URL=showusers2.php\">";

}

}

mysql_close();

?>

</table>

</form>

</td>

</tr>

</table>

Link to comment
Share on other sites

Hi, sorry I am new here and also very new to PHP & MySQL. this is another script that is trying to do the same thing (deleting rows with checkbox)

 

 

<?php
include "connect.php";

$result = mysql_query("SELECT * FROM users");

if(isset($_POST['delete'])){
   if(is_array($_POST['id'])){
     echo '<pre>';
     print_r($_POST);
     echo '<pre>';
     foreach($_POST['id'] as $id){
     $query = "DELETE FROM users WHERE id=".$id;
     mysql_query($query)or die(mysql_error()); 
     echo "User Deleted";
     
     }

} 
}
?>
<form action="<?php $_SERVER['PHP_SELF'] ?>" method="POST">  
<?
echo "<table border='0'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Location</th>
<th>Email</th>
<th>Website</th>
<th>userID</th>


</tr>";

while($row = mysql_fetch_array($result))
  {

  echo "<tr>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['lastname'] . "</td>";
  echo "<td>" . $row['location'] . "</td>";
  echo "<td>" . $row['email'] . "</td>";
  echo "<td>" . $row['website'] . "</td>";
  echo "<td>" . $row['userID'] . "</td>";
  ?> <td><input type="checkbox" name="id[]" value="<?php $row['id'] ?>" /></td> <?
  echo "</tr>";
  }
echo "</table>";
  echo "<input type='submit' value='Delete Users' name='delete' />";




mysql_close($con);
?> 

 

this gives me this error:

 

Array

(

    [id] => Array

        (

            [0] =>

        )

 

    [delete] => Delete Users

)

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

And the first script does not seem to do anything at all, no error or anything.

Link to comment
Share on other sites

Ah I see I am not sure I have been doing some research about this by searching in google "deleting rows with checkbox php mysql" and been trying multiple different bits trying to incorporate it in my pathetic code, either way how can I get this to work? Thanks a lot

Link to comment
Share on other sites

I think I might know what the problem is. You first run a query to get all the current records. Next you run the operations to delete the selected records. Then you take the results from the first query to display o the page. So, if you did delete any records you wouldn't see it in the output because you retireved those records before you did the deleted. If you were to refresh the page you would see them gone.

 

But, assuming that is not the problem, I'll provide some suggestions. It looks like you have the basic idea and may just be missing something. You have some debugging code in there so that should point you in the right direction (yet you failed to tell us what errors, if any, you were getting so we would know as well).

 

Anyway, I see a couple things that I think should be changed.

 

1. You check to see if the delete button was checked and then test to see if the 'id' value is an array. Why not just do a single check to see if the 'id' value was passed and is an array > 0 elements?

 

2. You do not need to run an individual query for each selected record. Instead run ONE query to delete all the selected records using the IN() operator

 

3. I would really suggest adding some code formatting to your HTML output. As it is there are no line breaks. If the problem is in the creation of the form, it is going to be difficult to easily spot it.

 

4. Your code does not have a good flow. For example, you run the query to get the records to display then you run the code to delete the selected records, then you use the results from the first query. Keep the operations that go together with each other. Also, figure out the logic flow before you write it.

 

5. Don't use '*' for your select queries unless you really need every field. It is inefficient. Plus, you may be referencing the wrong field for the id value which could also be the problem. If you listed the fields out in the query and one did not exist you would have received an error.

 

6. I don't see a closing form tag.

 

<?php
include "connect.php";

//Check if records were submitted for deletion
if(isset($_POST['id']) && count($_POST['id']))
{
    //Ensure values are ints
    $deleteIDs = implode(', ', array_map('intval', $_POST['id']));
    $query = "DELETE FROM users WHERE id IN ({$deleteIDs})";
    //Debug line
    echo "Delete Query: $query<br>\n";
    mysql_query($query) or die(mysql_error());
}

//Get any existing records to create form
$query = "SELECT `id`, `firstname`, `lastname`, `location`, `email`, `website`, `userID`
          FROM users
          ORDER BY `lastname`, `firstname`"
$result = mysql_query($query) or die(mysql_error());;

//Process results into HTML output and store in variable
$userFormList = '';
while($row = mysql_fetch_array($result))
{
    $userFormList .= "  <tr>\n";
    $userFormList .= "    <td>{$row['firstname']}</td>\n";
    $userFormList .= "    <td>{$row['lastname']}</td>\n";
    $userFormList .= "    <td>{$row['location']}</td>\n";
    $userFormList .= "    <td>{$row['email']}</td>\n";
    $userFormList .= "    <td>{$row['website']}</td>\n";
    $userFormList .= "    <td>{$row['userID']}</td>\n";
    $userFormList .= "    <td><input type='checkbox' name='id[]' value='{$row['id']}' /></td>\n";
    $userFormList .= "  </tr>";
}

mysql_close($con);

?>
<html>
<body>

<form action="" method="POST">  
<table border='0'>
  <tr>
    <th>Firstname</th>
    <th>Lastname</th>
    <th>Location</th>
    <th>Email</th>
    <th>Website</th>
    <th>userID</th>
  </tr>
  <?php echo $userFormList; ?>
</table>
<input type='submit' value='Delete Users' name='delete' />

</form>

</body>
</html>

Link to comment
Share on other sites

Thanks Psycho, I am a beginner to php and mysql but for me it seems like just getting stuck in is the best to learn, but I really appreciate your advice and read it atleast 4 times over and looking some stuff up as I wasn't sure of some things but thanks again, with the code you gave

 

<?php
include "connect.php";

//Check if records were submitted for deletion
if(isset($_POST['id']) && count($_POST['id']))
{
    //Ensure values are ints
    $deleteIDs = implode(', ', array_map('intval', $_POST['id']));
    $query = "DELETE FROM users WHERE id IN ({$deleteIDs})";
    //Debug line
    echo "Delete Query: $query<br>\n";
    mysql_query($query) or die(mysql_error());
}


//Get any existing records to create form
$query = "SELECT `id`, `firstname`, `lastname`, `location`, `email`, `website`, `userID`
          FROM users
          ORDER BY `lastname`, `firstname`";
          
$result = mysql_query($query) or die ( mysql_error () );

//Process results into HTML output and store in variable
$userFormList = '';
while($row = mysql_fetch_array($result))
{
    $userFormList .= "  <tr>\n";
    $userFormList .= "    <td>{$row['firstname']}</td>\n";
    $userFormList .= "    <td>{$row['lastname']}</td>\n";
    $userFormList .= "    <td>{$row['location']}</td>\n";
    $userFormList .= "    <td>{$row['email']}</td>\n";
    $userFormList .= "    <td>{$row['website']}</td>\n";
    $userFormList .= "    <td>{$row['userID']}</td>\n";
    $userFormList .= "    <td><input type='checkbox' name='id[]' value='{$row['id']}' /></td>\n";
    $userFormList .= "  </tr>";
}

mysql_close($con);

?>
<html>
<body>

<form action="" method="POST">  
<table border='0'>
  <tr>
    <th>Firstname</th>
    <th>Lastname</th>
    <th>Location</th>
    <th>Email</th>
    <th>Website</th>
    <th>userID</th>
  </tr>
  <?php echo $userFormList; ?>
</table>
<input type='submit' value='Delete Users' name='delete' />

</form>

</body>
</html>

 

I get an error saying:

 

 

Parse error: syntax error, unexpected T_VARIABLE in /Applications/XAMPP/xamppfiles/htdocs/php/showusers2.php on line 21

 

I searched around and it seems to mean there is most likely a { ( etc missing and I found a ; was missing and a ; was added somewhere else by accident it seems, either way I got rid of that and now I get an error saying

 

" Unknown column 'id' in 'field list' "

 

Is this by any chance talking about a primary key? I have read about this and feel like I made my table wrong, I have a unique auto increment in there called userID and not an id.

 

Appreciated

Link to comment
Share on other sites

I don't normally test code - especially when it requires me to have a database which isn't provided. So it is not uncommon for there to be syntax errors.

 

However THIS is your problem

" Unknown column 'id' in 'field list' "

 

Is this by any chance talking about a primary key? I have read about this and feel like I made my table wrong, I have a unique auto increment in there called userID and not an id.

 

I was going to ask you about that - I was wondering if 'id' and 'userID' were supposed to be the same. In fact, that is EXACTLY why I said you should NOT use '*' in your select statement and instead should list out the fields. In the code to create the form you were displaying $row['userID'] but you were then using $row['id'] to populate the value of the checkbox fields. Since there was no field with that index in the result set the values were getting set as empty strings. So, when you checked the checkboxes you were only passing those empty strings - not the IDs you wanted to delete!

 

You can use whatever name you want for your id field and it should be an auto-increment int field. you just need to make sure you are USING the values. I've revised the code to use the correct field name - but I'm not getting the syntax error.

 

<?php
include "connect.php";

//Check if records were submitted for deletion
if(isset($_POST['id']) && count($_POST['id']))
{
    //Ensure values are ints
    $deleteIDs = implode(', ', array_map('intval', $_POST['id']));
    $query = "DELETE FROM users WHERE id IN ({$deleteIDs})";
    //Debug line
    echo "Delete Query: $query<br>\n";
    mysql_query($query) or die(mysql_error());
}


//Get any existing records to create form
$query = "SELECT `firstname`, `lastname`, `location`, `email`, `website`, `userID`
          FROM users
          ORDER BY `lastname`, `firstname`";
          
$result = mysql_query($query) or die ( mysql_error () );

//Process results into HTML output and store in variable
$userFormList = '';
while($row = mysql_fetch_array($result))
{
    $userFormList .= "  <tr>\n";
    $userFormList .= "    <td>{$row['firstname']}</td>\n";
    $userFormList .= "    <td>{$row['lastname']}</td>\n";
    $userFormList .= "    <td>{$row['location']}</td>\n";
    $userFormList .= "    <td>{$row['email']}</td>\n";
    $userFormList .= "    <td>{$row['website']}</td>\n";
    $userFormList .= "    <td>{$row['userID']}</td>\n";
    $userFormList .= "    <td><input type='checkbox' name='id[]' value='{$row['`userID`']}' /></td>\n";
    $userFormList .= "  </tr>";
}

mysql_close($con);

?>
<html>
<body>

<form action="" method="POST">  
<table border='0'>
  <tr>
    <th>Firstname</th>
    <th>Lastname</th>
    <th>Location</th>
    <th>Email</th>
    <th>Website</th>
    <th>userID</th>
  </tr>
  <?php echo $userFormList; ?>
</table>
<input type='submit' value='Delete Users' name='delete' />

</form>

</body>
</html>

Link to comment
Share on other sites

I've now changed the table name

 

users is now called user

 

userID is now called id (also primary key)

 

added in extra fields - all shows up but when I try to delete it gives this error:

 

Delete Query: DELETE FROM user WHERE id IN (0)

 

<?php
include "connect.php";

//Check if records were submitted for deletion
if(isset($_POST['id']) && count($_POST['id']))
{
    //Ensure values are ints
    $deleteIDs = implode(', ', array_map('intval', $_POST['id']));
    $query = "DELETE FROM user WHERE id IN ({$deleteIDs})";
    //Debug line
    echo "Delete Query: $query<br>\n";
    mysql_query($query) or die(mysql_error());
}

//Get any existing records to create form
$query = "SELECT `id`, `firstname`, `lastname`, `email`, `website`, `password`, `address1`, `address2`, `city`, `county`, `postcode`, `sex`, `dobmonth`, `dobday`, `dobyear`, `customerfeedback`, `wgtffeedback`
          FROM user
          ORDER BY `lastname`, `firstname`";
          
$result = mysql_query($query) or die ( mysql_error () );

//Process results into HTML output and store in variable
$userFormList = '';
while($row = mysql_fetch_array($result))
{
    $userFormList .= "  <tr>\n";
    $userFormList .= "    <td>{$row['id']}</td>\n";
    $userFormList .= "    <td>{$row['firstname']}</td>\n";
    $userFormList .= "    <td>{$row['lastname']}</td>\n";
    $userFormList .= "    <td>{$row['email']}</td>\n";
    $userFormList .= "    <td>{$row['website']}</td>\n";
    $userFormList .= "    <td>{$row['password']}</td>\n";
    $userFormList .= "    <td>{$row['address1']}</td>\n";
    $userFormList .= "    <td>{$row['address2']}</td>\n";
    $userFormList .= "    <td>{$row['city']}</td>\n";
    $userFormList .= "    <td>{$row['county']}</td>\n";
    $userFormList .= "    <td>{$row['postcode']}</td>\n";
    $userFormList .= "    <td>{$row['sex']}</td>\n";
    $userFormList .= "    <td>{$row['dobmonth']}</td>\n";
    $userFormList .= "    <td>{$row['dobday']}</td>\n";
    $userFormList .= "    <td>{$row['dobyear']}</td>\n";
    $userFormList .= "    <td>{$row['customerfeedback']}</td>\n";
    $userFormList .= "    <td>{$row['wgtffeedback']}</td>\n";
    $userFormList .= "    <td><input type='checkbox' name='id[]' value='{$row['`id`']}' /></td>\n";
    $userFormList .= "  </tr>";
}

mysql_close($con);

?>
<html>
<body>

<form action="" method="POST">  
<table border='0'>
  <tr>
    <th>id</th><br>
    <th>First Name</th><br>
    <th>Last Name</th><br>
    <th>Email</th><br>
    <th>Website</th><br>
    <th>Password</th><br>
    <th>Address Line 1</th><br>
    <th>Address Line 2</th><br>
    <th>city</th><br>
    <th>county</th><br>
    <th>postcode</th><br>
    <th>sex</th><br>
    <th>Date of Birth (month)</th><br>
    <th>Date of Birth (day)</th><br>
    <th>Date of Birth (year)e</th><br>
    <th>Customer Feedback</th><br>
    <th>WGTF Feedback</th><br>
    
  </tr>
  <?php echo $userFormList; ?>
</table>
<input type='submit' value='Delete Users' name='delete' />

</form>

</body>
</html>

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.