Select one or multiple records and then delete them

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Select one or multiple records and then delete them

Post by seriousdamage »

Hi,
I have a little database, I can search for records and display them in an html table,
I would like to have a check box of every record returned by the search and a button that onclick will delete the selected records from the database.

I am far away from having any idea on where to start on this.

Below is my result script:

Code: Select all

$host = "localhost"; 
$user = "****"; 
$pass = "*****"; 
$dbname = "contacts"; 
 
$connection = mysql_connect($host,$user,$pass) or die (mysql_errno().": ".mysql_error()."<BR>"); 
mysql_select_db($dbname); 
 
 
$search = mysql_real_escape_string($_POST['search']);
$result = mysql_query("SELECT * FROM contacts where first_name LIKE '%{$search}%' OR last_name LIKE '%{$search}%' OR company_name LIKE '%{$search}%' OR website LIKE '%{$search}%' OR street LIKE '%{$search}%' OR city LIKE '%{$search}%' OR zip LIKE '%{$search}%' OR country LIKE '%{$search}%' OR mail1 LIKE '%{$search}%' OR mail2 LIKE '%{$search}%' OR phone LIKE '%{$search}%' OR mobile LIKE '%{$search}%' OR card LIKE '%{$search}%' OR birthday LIKE '%{$search}%'") or die (mysql_errno().": ".mysql_error()."<BR>");
 
 
echo "<table border=0 width=1400>
<tr bgcolor=#87ceeb font face=tahoma>
<td width=100><b><font face=tahoma>First Name</td><td width=100><b><font face=tahoma>Last Name</td>
<td width=100><b><font face=tahoma>Company Name</td><td width=100><b><font face=tahoma>Website</td>
<td width=100><b><font face=tahoma>Street + Number</td><td width=100><b><font face=tahoma>City</td>
<td width=100><b><font face=tahoma>Post Code</td><td width=100><b><font face=tahoma>Country</td>
<td width=100><b><font face=tahoma>E-Mail 1</td><td width=100><b><font face=tahoma>E-Mail 2</td>
<td width=100><b><font face=tahoma>Phone</td><td width=100><b><font face=tahoma>Mobile</td>
<td width=100><b><font face=tahoma>Christmas Card</td><td width=100><b><font face=tahoma>Birthday</b></td>
 
 
</tr>";
 
 
while($row = mysql_fetch_array($result)) {  
 
echo "<tr bgcolor=#4682b4>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['first_name'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['last_name'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['company_name'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['website'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['street'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['city'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['zip'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['country'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['mail1'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['mail2'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['phone'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['mobile'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['card'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['birthday'] . "</font></td>";
 
  echo "</tr>";
  }
echo "</table>";
?>
User avatar
jimthunderbird
Forum Contributor
Posts: 147
Joined: Tue Jul 04, 2006 3:59 am
Location: San Francisco, CA

Re: Select one or multiple records and then delete them

Post by jimthunderbird »

Here's a dummy example:

Code: Select all

 
  <? for($k=0;$k<count($rows);$k++){
     <div><input type="checkbox" name="myselection[]" value="<?=$rows[$k]->id?>"><?=$rows[$k]->name?></input></div>
  <?php } ?
 
Then you capture the value of "myselection" in $_POST['myselection']

Code: Select all

 
$selected_ids = $_POST['myselection'];
 
//preventing bad data
for($k=0;$K<count($selected_ids);$k++){
   $selected_ids[$k] = (int)$selected_ids[$k];
}
 
$selected_ids_str = @implode(",",$selected_ids);
 
Then do a sql: "delete from [your table name] where id in ({$selected_ids_str})";
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Re: Select one or multiple records and then delete them

Post by seriousdamage »

I have tried to apply this method but it does not work,
and I can't find the problem.

Code: Select all

 
while($row = mysql_fetch_array($result)) {  
 
echo "<tr bgcolor=#4682b4>";
  echo "<td><input type=\"checkbox\" name=\"delete[]\" value=\"$row[first_name]\" /></td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['first_name'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['last_name'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['company_name'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['website'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['street'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['city'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['zip'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['country'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['mail1'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['mail2'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['phone'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['mobile'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['card'] . "</td>";
  echo "<td><font color=#FFFFFF font face=Tahoma>" . $row['birthday'] . "</font></td>";
 
  echo "</tr>";
  }
echo "</table>";
 
if (count($_POST['delete']) > 0) {
  foreach ($_POST['delete'] as $val) {
    $sql = "DELETE FROM contacts WHERE "'first_name'" = '$val'";
    mysql_query($sql);
 
echo "<form name="Form" method="post">";
echo "<input type="submit" name="delete" value="Delete">";
echo "</form>";
  }
 
}
User avatar
jimthunderbird
Forum Contributor
Posts: 147
Joined: Tue Jul 04, 2006 3:59 am
Location: San Francisco, CA

Re: Select one or multiple records and then delete them

Post by jimthunderbird »

Some suggestions:

1. Change

Code: Select all

$sql = "DELETE FROM contacts WHERE "'first_name'" = '$val'";
to

Code: Select all

$sql = "DELETE FROM contacts WHERE first_name = '$val'";
2. Better use contact's id instead of first_name, since two contacts might have the same first name, then you will delete both.
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Re: Select one or multiple records and then delete them

Post by seriousdamage »

still not working, I get the following message:

Parse error: parse error, unexpected T_STRING, expecting ',' or ';' in /usr/local/psa/home/vhosts/giustinianinicola.com/httpdocs/contacts/search.php on line 30
r3gan
Forum Newbie
Posts: 4
Joined: Thu Jan 17, 2008 12:48 pm

Re: Select one or multiple records and then delete them

Post by r3gan »

You might have a parsing problem with your strings... in your latest code example posts, around line 30 you have this:

Code: Select all

 
echo "<form name="Form" method="post">";
echo "<input type="submit" name="delete" value="Delete">";
echo "</form>";
which might not parse right b/c of the quotes... make sure you properly escape quotes in your strings, like:

Code: Select all

 
echo "<form name=\"Form\" method=\"post\">";
echo "<input type=\"submit\" name=\"delete\" value=\"Delete\">";
echo "</form>";
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Re: Select one or multiple records and then delete them

Post by seriousdamage »

OK, that surely did something right,
now, the previous error is gone, and the delete button has appeared,
but when I select a record and click on delete , first the page refreshes and shows all record in the database and at the bottom I get the error :

Warning: Invalid argument supplied for foreach() in /usr/local/psa/home/vhosts/giustinianinicola.com/httpdocs/contacts/search.php on line 53
which would be line 26 in the example above.:
foreach ($_POST['delete'] as $val) {
r3gan
Forum Newbie
Posts: 4
Joined: Thu Jan 17, 2008 12:48 pm

Re: Select one or multiple records and then delete them

Post by r3gan »

Well from your example earlier, you have:

Code: Select all

 
echo "<form name=\"Form\" method=\"post\">";
echo "<input type=\"submit\" name=\"delete\" value=\"Delete\">";
echo "</form>"
which looks to me like the the POST variable named "delete" is not an array of things, but rather a single string with a value as "Delete". Your foreach() statement would need to reference the array of checkbox names/variables you used.
seriousdamage
Forum Commoner
Posts: 30
Joined: Sat Nov 27, 2004 10:18 am

Re: Select one or multiple records and then delete them

Post by seriousdamage »

Hi, I must really apologize, but you just spoke a language that I have no idea what it means.
I keep looking at the code but I just don't get it.
I am sorry.
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Select one or multiple records and then delete them

Post by Jade »

Hey there.

Here's some code I use for an inbox for members. It goes through and deletes each of the selected mail messages that the user has checked after they hit the delete button.

Code: Select all

 
function deleteMail($_POST)
    {
        
        foreach($_POST as $key=>$value)
            if (is_numeric($value))
            {
                //delete it from the database
                mysql_query("DELETE FROM mail WHERE id='$value' WHERE toid='$this->id'")
                or die ('cannot delete this piece of mail');
                
                //delete it from their object
                $this->inbox = deleteObjectFromArray($this->inbox, $value);
                $this->newmail = 1;
            }
        
        return successmsg("message(s) deleted.");
    }
 
Here's the code on the mail.php page where it displays the mail and the delete checkboxes:

Code: Select all

 
echo "<table cellpadding=\"5\" cellspacing=\"0\" width=\"100%\">
              <tr>
                <td>From</td><td>Subject</td><td>Sent On</td><td></td>
              </tr>";
              
        foreach($this->inbox as $mail)
            echo "<tr>
                  <td><a href=\"vaccount.php?id=" . $mail->fromid . "\">" . toName($mail->fromid) . "</a></td>
                  <td><a href=\"mail.php?id=" . $mail->id . "\">" . $mail->subject . "</a></td>
                  <td>" . date("F j, Y g:ia", $mail->ndate) . "</td>
                  <td align=\"center\"><input type=\"checkbox\" name=\"id$mail->id\" value=\"" . $mail->id . "\" /></td>
                  </tr>";           
        
        if (sizeof($this->inbox))
            echo "<tr>
                    <td colspan=\"3\"></td>
                    <td align=\"center\">
                        <br />
                        <input type=\"submit\" name=\"delete\" value=\"Delete\" class=\"submitinput\" />
                    </td>
                  </tr>";
        else
            echo "<tr><td colspan=\"4\" align=\"center\">None</td></tr>";
            
            echo "</table><br /><br />";
 
Of course this uses objects but it follows the same idea. The user clicks on the checkboxes of the message they want to be deleted. Each checkbox has a value that's set to the ID of the message in the database. When the delete function is called it goes through each of the numeric values that were submitted and deletes them from the database for that particular member.

Note that if you used this on a form that submits any other kind of numeric values it would try to delete messages with and ID of that value and you could potentially delete fields you don't want to.
Post Reply