[SOLVED] Checkboxes to delete rows in members table...

[amsgwp]

I cannot get the delete function to work on the following snippet.

 

Can anyone take a quick look?

 

<form name="search" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
Seach for: <input type="text" name="find" />
<input type="submit" name="search" id="search" value="Search" />
</form>
<?php
include("connect.php");
if(isset($_POST['search']))
{
print "<form action=\"search_members.php\" id=\"form1\" name=\"form1\" method=\"post\">\n";
$query = "select * from members
where first_name like '%$_POST[find]%'
or last_name like '%$_POST[find]%';";
$result = mssql_query($query,$link) or die("Unable to select: ".mssql_get_last_message());
print "<div id=\"myvar\">$query</div>\n";
print "<a onclick=\"switchMenu('myvar');\" title=\"Show SQL\">Show SQL</a>\n";
print "<table border=\"1\">\n";
echo"<tr><th>Member ID</th><th>First Name</th><th>Last Name</th><th>Delete</th></tr>";
while($row = mssql_fetch_array($result)) {
echo "<tr><td>";
echo $row['Member_ID'];
echo "</td>";
echo "<td>";
echo $row['First_Name'];
echo "</td>";
echo "<td>";
echo $row['Last_Name'];
echo "</td>";
echo "<td>";
?>

<input type="checkbox" name="checkbox[]" id="checkbox" value="<?php echo $row['Member_ID']?>" />
<?php
$checkbox = serialize($_POST['checkbox']);  
echo "</td>";
echo "</tr>";
//echo "</table>";

$checkbox = unserialize($checkbox);
print_r($checkbox);
}


	echo "</table>";
	echo "<input type=\"submit\" name=\"delete\" value=\"Delete\" id=\"delete\">";
	echo "</form>";
}
if(isset($_POST['delete']))
$checkbox = $_POST['checkbox'];

{for($i=0;$i<sizeof($checkbox);$i++){
$del_id = $checkbox[$i];
//debugging

$sql = "DELETE FROM Members WHERE Member_ID ='$del_id'";
$result = mssql_query($sql) or die("Error while performing the following query: $sql<br />. MySQL said: " . mysql_error());}
}
mssql_close();
?>

 

the delete part is only the very bottom half of the code

 

if(isset($_POST['delete']))
$checkbox = $_POST['checkbox'];

{for($i=0;$i<sizeof($checkbox);$i++){
$del_id = $checkbox[$i];
//debugging

$sql = "DELETE FROM Members WHERE Member_ID ='$del_id'";
$result = mssql_query($sql) or die("Error while performing the following query: $sql<br />. MySQL said: " . mysql_error());}
}
mssql_close();
?>

[dptr1988]

Is that line where you 'print_r($checkbox)' just for debugging?

 

Why are you serializeing and unserializing the $checkbox var?

 

What do you get with 'print_r($_POST);' after the form has been submitted? Is the post data the way you were expecting it?

[DarkWater]

What error does it give?

[amsgwp]

Well It doesn't give an error at all.  It just doesn't delete anything from the actual database.

 

I'm trying to print_r right now. (I'll post a response in a minute)

 

Also, I have no idea why I was serializing.  I got this snippet of code from somewhere online and thats how it was setup.  Can I safely remove this?

[amsgwp]

ok, after getting the raw data it looked like it was sending the correct id, so I did some changing around and here is my new delete function

 

if(isset($_POST['delete'])) {
foreach($_POST['checkbox'] as $id) 


$sql = "DELETE FROM Members WHERE Member_ID ='$id'";
$result = mssql_query($sql);
}

 

it deletes if I only have one checkbox selected.  So i what can I do to make it delete multiple id's?

[dptr1988]

Are you wanting to delete multiple ID's in one SQL query or multiple ID's in one page request?

[amsgwp]

In one SQL query, can I not do DELETE FROM members where id=1,2,3,4,5,6

 

i did a print $id;

 

just to see how the id's were being formated when multiple were selected.  It was just ID1ID2 without a space or comma.  How can I add commas between the id's(assuming that would work).

[amsgwp]

to see what i'm doing check out

 

http://www.utipu.com/app/invited/id/77c7a302a4f447659555fdd00a9f7ec5

 

[dptr1988]

Try this

DELETE FROM members WHERE id IN (1,2,3,4,5,6)

[DarkWater]

if(isset($_POST['delete'])) {

$chkbx = $_POST['checkbox'];

$sql = "DELETE FROM Members WHERE Member_ID =";

for($i=0;$i<sizeof($checkbox);$i++){

$del_id = $checkbox[$i];

        if ($i=0) {

        $sql .= "$del_id";

        }

      else {

        $sql .= "OR Member_ID = $del_id";

      }

}

$result = mssql_query($sql);

}

 

Try that out.  It SHOULD work as is.  if not, I might need to tweak it.

[ToonMariner]

dptr had it....

 

$qry = "DELETE FROM `Members` WHERE `Member_ID` IN (" . implode(',', $_POST['checkbox']) . ";

$qry = mysql_query($qry);

 

 

 

[amsgwp]

thanks!

 

it didn't like the

 

dot part for combing the sql statement with the id's

 

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near '='. (severity 15) in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\search_members.php on line 106

 

Warning: mssql_query() [function.mssql-query]: Query failed in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\search_members.php on line 106

[dptr1988]

Or even better:

<?php
if (isset($_POST['delete'])) 
{
  $sql = "DELETE FROM Members WHERE Member_ID IN (" . implode(', ', $_POST['checkbox']) . ")";
  $result = mssql_query($sql);
}
?>

[DarkWater]

thanks!

 

it didn't like the

 

dot part for combing the sql statement with the id's

 

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near '='. (severity 15) in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\search_members.php on line 106

 

Warning: mssql_query() [function.mssql-query]: Query failed in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\search_members.php on line 106

 

It's .=, not =.

[amsgwp]

One more question,

 

What if I wanted to update a field in all the rows the checkboxes that are checked.

 

Say I have a table that has a column called Status and it can either be Active or Inactive.  How can I change this one field for all the boxes that are checked?

 

So all the checked ID's change to inactive.

[amsgwp]

update members set status = 'Inactive' where member_id = $[chkbox]

 

the only problem is I need to loop this code for each check box that is checked, I can't find a way using SQL to have multiple member id's in an update.

[DarkWater]

Almost the same thing as last time:

<?php
$sql = "UPDATE Members SET active = 0 WHERE Member_ID =";
for($i=0;$i<sizeof($checkbox);$i++){
   $user_id_update = $checkbox[$i];
        if ($i=0) {
         $sql .= "$user_id_update";
        }
       else {
         $sql .= "OR Member_ID = $user_id_update";
       }
}
$result = mssql_query($sql);
?>

 

And $checkbox = $_POST['checkboxname'], as assigned previously.

[amsgwp]

message: Incorrect syntax near '='.

 

I never got your code to work the first time, I tried someone else's.  I knew for this to work I would have to get your code to work.

 

 

if(isset($_POST['delete'])) {
$chkbx = $_POST['checkbox'];
$sql = "update members set status = 'Inactive' where member_id = ";
for($i=0;$i<sizeof($checkbox);$i++){
   $del_id = $checkbox[$i];
        if ($i=0) {
         $sql . "$del_id";
        }
       else {
         $sql . " OR Member_ID = $del_id";
       }
}
$result = mssql_query($sql);
}

 

I obviously can't figure out how to properly concatenate the strings

[DarkWater]

<?php
$sql = "UPDATE Members SET status = 'Inactive' WHERE Member_ID =";
for($i=0;$i<sizeof($chkbx);$i++){
   $user_id_update = $chkbx[$i];
        if ($i=0) {
         $sql .= "$user_id_update";
        }
       else {
         $sql .= "OR Member_ID = $user_id_update";
       }
}
$result = mssql_query($sql);
?>

 

Should work just like that, except you know, don't use $_POST['delete'].  Make a new submit button for status change. =P  Copy that EXACTLY.  It's .=, not =.

 

[amsgwp]

I'm having no such luck with this

 

the code seems fine, but now my code is getting into a infinite loop and timing out.

 

do you see anything wrong? It was throwing up a error at the last = where $result = mssql_query($sql)

 

if(isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
echo $checkbox;
$sql = "update members set status = 'Inactive' where member_id =";
for($i=0;$i<sizeof($checkbox);$i++){
  $del_id = $checkbox[$i];
       if ($i=0) {
        $sql .= "$del_id";
       }
      else {
        $sql .= "OR Member_ID = $del_id";
      }
}
$result = mssql_query($sql);
}

ignore that I left the post function at delete, thats really irrelevant right now. 

 

EDIT

I've also just tried this little snippet to debug

 

if(isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
echo $checkbox;

}
[code]

The result after selecting two or more checkboxes is the word "Array" is displayed on the page. Yuck.

[/code]

[DarkWater]

That's because you're echoing $checkbox...

[amsgwp]

Ok well why is this not working then??

 

if(isset($_POST['delete'])) {

$checkbox = $_POST['checkbox'];

$sql = "update members set status = 'Inactive' where member_id =";

for($i=0;$i<sizeof($checkbox);$i++){

  $del_id = $checkbox[$i];

if ($i=0) {

  $sql .= "$del_id";

}

  else {

  $sql .= "OR Member_ID = $del_id";

  }

}

$result = mssql_query($sql);

}

 

 

is that correct? My PHP freaks out and I get a timeout.

 

if you want to watch it choke horribly go to

 

http://fit4life.darktech.org/search_members2.php

[DarkWater]

if(isset($_POST['delete'])) {

$checkbox = $_POST['checkbox'];

$sql = "update members set status = 'Inactive' where member_id =";

for($i=0;$i<sizeof($checkbox);$i++){

  $del_id = $checkbox[$i];

        if ($i=0) {

        $sql .= "$del_id";

        }

      else {

        $sql .= "OR Member_ID = $del_id";

      }

}

$result = mssql_query($sql) or die(mssql_get_last_message());

}

 

Do that and tell me what it says.

 

 

[amsgwp]

I just get the same error, it says

Fatal error: Maximum execution time of 30 seconds exceeded in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\search_members2.php on line 106

 

Line 106 is the closing } right above the $results =

 

 

I just rebooted the MSSQL server and that didn't help. All the other queries run lightning but if that line that starts with for, runs then it's game over.

 

here is the entire page

 

<form name="search" method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
Seach for: <input type="text" name="find" />
<input type="submit" name="add" id="add" value="Search" />
</form>
<?php
include("connect.php");
if(isset($_POST['add']))
{
print "<form action=\"search_members2.php\" id=\"form1\" name=\"form1\" method=\"post\">\n";
$query = "select * from members
where first_name like '%$_POST[find]%'
or last_name like '%$_POST[find]%'
or street like '%$_POST[find]%'
or city like '%$_POST[find]%'
or state like '%$_POST[find]%';";
$result = mssql_query($query,$link) or die("Unable to select: ".mssql_get_last_message());
print "<div id=\"myvar\">$query</div>\n";
print "<a onclick=\"switchMenu('myvar');\" title=\"Show SQL\">Show SQL</a>\n";
print "<table class=\"sample\">\n";
echo"<tr><th>Member ID</th><th>First Name</th><th>Last Name</th><th>Delete</th></tr>";
while($row = mssql_fetch_array($result)) {
echo "<tr><td>";
echo $row['Member_ID'];
echo "</td>";
echo "<td>";
echo $row['First_Name'];
echo "</td>";
echo "<td>";
echo $row['Last_Name'];
echo "</td>";
echo "<td>";
?>

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

echo "</td>";
echo "</tr>";
//echo "</table>";

}


	echo "</table>";
	echo "<input type=\"submit\" name=\"delete\" value=\"Delete\" id=\"delete\">";
	echo "</form>";
}
if(isset($_POST['delete'])) {
$checkbox = $_POST['checkbox'];
$sql = "update members set status = 'Inactive' where member_id =";
for($i=0;$i<sizeof($checkbox);$i++){
  $del_id = $checkbox[$i];
       if ($i=0) {
        $sql .= "$del_id";
       }
      else {
        $sql .= "OR Member_ID = $del_id";
      }
}
$result = mssql_query($sql) or die(mssql_get_last_message());
}
mssql_close();
?>

[ToonMariner]

in this case...

 

$qry = "UPDATE `Members` SET `Status` = 'Inactive' WHERE `Member_ID` IN (" . implode(',', $_POST['checkbox']) . ";