Super basic, deleting rows with checkbox

[downah]

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>

[batwimp]

Couple things: Please wrap your code in code blocks (the octothorpe in the editor), and please tell us what exactly isn't working in your script. Are you getting an error message? Please be more specific how it's not working.

[downah]

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.

[Pikachu2000]

Where did that code come from? It relies on a certain php.ini file directive being ON. That particular directive has been OFF by default for about the past 10 years.

[downah]

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

[batwimp]

I'm not sure what the overall problem is, but you are missing a:

 

<?

right before your while statement.

[downah]

I don't think I am batwimp

[batwimp]

Oh, you're right. I'm brain dead. Time to go home!

[Pikachu2000]

In the second code block, change the short open <? tag to the standard <?php open tag and see if that makes a difference. Short open tags is another directive in the php.ini file that is now OFF by default . . .

[downah]

No problem batwimp, and thanks pikachu, changed it all but still get the same error, I'd prefer the second block of code to work so thanks for giving some help on that one

[downah]

Otherwise could anyone help me change the second code? I did not thought it would be this tricky to delete with checkbox

[batwimp]

Go through and make sure all of your:

 

<?

 

 

are actually:

 

 

<?php

 

Also, this line:

 

 

<input type="checkbox" name="id[]" value="<?php $row['id'] ?>"

 

 

should be:

 

 

<input type="checkbox" name="id[]" value="<?php echo $row['id'] ?>"

[Psycho]

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>

[downah]

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

[Psycho]

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>

[BigTime]

I think psycho forgot to change it to user ID in the delete directive

 

$query = "DELETE FROM users WHERE id IN ({$deleteIDs})";

 

should be

$query = "DELETE FROM users WHERE Userid IN ({$deleteIDs})";

 

[downah]

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>

[Psycho]

You only use backticks when referencing field in a query. Remove them from this line

$userFormList .= "    <td><input type='checkbox' name='id[]' value='{$row['`id`']}' /></td>\n";

 

$row['`id`'] should be $row['id']

[downah]

Thanks psycho, all working, much appreciated everyone for their help and brains