Insert Deleted record to a new table

[mulaus]

hi..i'm a php/mysql newbie

i would like my script to be when i delete a record i want all the record to be put into a new table called bin

i tried this but it didn't work



<?php
include "connect.php";
$result = mysql_query("SELECT * FROM person");

echo "<form action='delete.php' method='post'>";



echo "<table border='1'>
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
</tr>";


while($row = mysql_fetch_array($result))
{
$id=$row['id']; echo "<tr>";
echo "<td>" . $row['id'] . " <input type='radio' name='id' value='$id'></td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "</tr>";
}
echo "</table>
<br /><input type='submit' value='Delete'/>


</form>";

include "menu.php";


mysql_close($con);

?>


delete.php


<?php
include "connect.php";

$id=$_POST[id];
//$id = intval($_POST['id']);


mysql_query("DELETE FROM person WHERE id=$id");
mysql_query("INSERT INTO bin (id, firstname, lastname, age)
VALUES
('$_POST[id]','$_POST[firstname]','$_POST[lastname]','$_POST[age]') WHERE id=$id");

printf("Records deleted: %d\n", mysql_affected_rows());

include "menu.php";
mysql_close($con);
?>





--
-- Table structure for table `bin`
--

CREATE TABLE IF NOT EXISTS `bin` (
`bin_id` int(3) NOT NULL auto_increment,
`id` int(3) NOT NULL,
`firstname` varchar(15) collate latin1_general_ci NOT NULL,
`lastname` varchar(15) collate latin1_general_ci NOT NULL,
`age` int(3) NOT NULL,
PRIMARY KEY (`bin_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Table structure for table `person`
--

CREATE TABLE IF NOT EXISTS `person` (
`id` int(3) NOT NULL auto_increment,
`firstname` varchar(15) collate latin1_general_ci NOT NULL,
`lastname` varchar(15) collate latin1_general_ci NOT NULL,
`age` int(3) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


help ...thank you in advance :):)

[JShor]

This is a complex script, there are easier ways to do what you're trying to accomplish. Also, the reason it's not inserting into 1`bin` is bc you're deleting the record BEFORE you get a chance to extract it's data. As well, you're not posting the other fields, you're just calling them. You didn't get the mysql_fetch_array for the row[s] you're deleting.

Then, you can't have WHERE="id" in an insertion query, bc you're inserting data, not calling it.

Also, you can convert the radio button you have into a checkbox so you can delete more than one record at a time.

Here's the code I revised for you:


<?php
include "connect.php";
$result = mysql_query("SELECT * FROM person");

echo '<form action="delete.php" method="post">';

echo '<table border="1">
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
</tr>';


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

echo "<tr>";
echo '<td>' . $row['id'] . ' <input type="checkbox" name="id[]" value="'.$id.'"></td>';
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "</tr>";
}

echo '</table>
<br />
<input type="submit" value="Delete" />
</form>';

include "menu.php";


mysql_close($con);

?>


delete.php



<?php

include "connect.php";

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

$qq = mysql_query("SELECT * FROM person WHERE id='$id'") or die(mysql_error());

$row = mysql_fetch_array( $qq ) or die(mysql_error());

mysql_query("INSERT INTO bin (firstname, lastname, age)
VALUES
('$_row[firstname]','$_row[lastname]','$_row[age]') ") or die(mysql_error());

mysql_query("DELETE FROM person WHERE id=$id") or die(mysql_error());

}
} else {
echo "You must select at least one rows to delete";
}

printf("Records deleted: %d\n", mysql_affected_rows());

include "menu.php";
mysql_close($con);
?>


HTH:)

[mulaus]

thanks Jshor

but when i choose a record and hit delete...i received a blank page

[JShor]

Try revising delete.php to this:


<?php

include "connect.php";

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

$qq = mysql_query("SELECT * FROM person WHERE id='$id'") or die(mysql_error());

$row = mysql_fetch_array( $qq ) or die(mysql_error());

mysql_query("INSERT INTO bin (firstname, lastname, age)
VALUES
('$_row[firstname]','$_row[lastname]','$_row[age]') ") or die(mysql_error());

mysql_query("DELETE FROM person WHERE id=$id") or die(mysql_error());

}

printf("Records deleted: %d\n", mysql_affected_rows());

} else {
echo "You must select at least one rows to delete";
}

include "menu.php";
mysql_close($con);

?>

[mulaus]

same blank page

is my table structure for bin ok ?



--
-- Table structure for table `bin`
--

CREATE TABLE IF NOT EXISTS `bin` (
`bin_id` int(3) NOT NULL auto_increment,
`id` int(3) NOT NULL,
`firstname` varchar(15) collate latin1_general_ci NOT NULL,
`lastname` varchar(15) collate latin1_general_ci NOT NULL,
`age` int(3) NOT NULL,
PRIMARY KEY (`bin_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

[JShor]

If it was a mysql problem, it'd return mysql_error();. I have one more solution tho, why don't you try conforming the delete functions in the same page? It's a better solution anyway imo.

Here's the code:


<?php

include "connect.php";

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

$qq = mysql_query("SELECT * FROM person WHERE id='$id'") or die(mysql_error());

$row = mysql_fetch_array( $qq ) or die(mysql_error());

mysql_query("INSERT INTO bin (firstname, lastname, age)
VALUES
('$_row[firstname]','$_row[lastname]','$_row[age]') ") or die(mysql_error());

mysql_query("DELETE FROM person WHERE id=$id") or die(mysql_error());

printf("Records deleted: %d\n", mysql_affected_rows());

}
} else {
echo "You must select at least one rows to delete";
}

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

echo '<form action="'.$_SERVER['PHP_SELF'].'" method="post">';

echo '<table border="1">
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
</tr>';


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

echo "<tr>";
echo '<td>' . $row['id'] . ' <input type="checkbox" name="id[]" value="'.$id.'"></td>';
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "</tr>";
}

echo '</table>
<br />
<input type="submit" value="Delete" />
</form>';

include "menu.php";


mysql_close($con);

?>


HTH:)

[mulaus]

blank page :-(

[JShor]

I have no idea why it would be a blank page. Do you have a link or a sample of it?

The only explanation I can think of is that you disabled errors in php.ini and there's an error that's not being caught and the page isn't processing.

Also, when you first see the page is it blank? Or is it AFTER submitting the form? That'll tell us a lot.

[mulaus]

i received blank page after submitting the form

i did this in localhost

i did not touch any of php.ini settings

[JShor]

What's the page URL? There could be so many different things wrong, it's hard to diagnose w/o seeing the page.

[mulaus]

as i said i did this in localhost

i manage to convert my radio button delete form to check boxes..how and where do i put insert query in delete.php ?




<?php
include "connect.php";
$result = mysql_query("SELECT * FROM person");

echo '<form action="delete.php" method="post">';

echo '<table border="1">
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
</tr>';


$count = 1;

while($row = mysql_fetch_array($result))
{
$id=$row['id']; echo "<tr>";
echo "<td>";
echo $count++;

echo "<input type='checkbox' name='id[]' value='$id'></td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "</tr>";
}

echo '</table>
<br />
<input type="submit" value="Delete" />
</form>';

include "menu.php";


mysql_close($con);

?>


delete.php


<?php

include "connect.php";

$id=$_POST[id];

for($i=0;$i<count($_POST['id']);$i++)

mysql_query("DELETE FROM person WHERE id={$id[$i]}");

echo "$i records deleted";

include "menu.php";
mysql_close($con);
?>

[forum_amnesiac]

As I see it your problem is that you are not passing the value $id as a POST variable to delete.php.

Try changing this :


echo "<input type='checkbox' name='id[]' value='$id'></td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "</tr>";

to this :


echo "<input type='checkbox' name='id[]' value='$id'></td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "<td input type='hidden' name='id' value='".$row[id]."'></td>";
echo "</tr>";

Or change the checkbox line to this:


echo "<input type='checkbox' name='id[]' value='".$id."'></td>";

The input type="hidden" is a useful way of passing variables to PHP that you don't want to be visible on the input screen

[mulaus]

thanks..

i did it like this and it worked

delete.php


<?php

include "connect.php";

$id=$_POST[id];

for($i=0;$i<count($_POST['id']);$i++)
{
$result = mysql_query("SELECT * FROM person WHERE id={$id[$i]}") or die(mysql_error());

$row = mysql_fetch_array( $result ) or die(mysql_error());

mysql_query("INSERT INTO bin (id, firstname, lastname, age)
VALUES
(". $row['id'] . ",". $row['firstname'] . ",". $row['lastname'] . ",". $row['age'] . ")");


mysql_query("DELETE FROM person WHERE id={$id[$i]}");
}
echo "$i records deleted";

include "menu.php";
mysql_close($con);
?>


SOLVED..

[JShor]

Of course! I can't believe I didn't see that. I guess some problems are simpler than they look. :)

[mulaus]

can someone help me to check this...what did i do wrong

i add the ability to restore back the data in the 'recycle bin' (restore.php)

last time i didn't input/check it properly

the problem if i input first name,last name as text it is not inserted to the bin table...

but if i put first name,last name as numbers it is ok it is inserted to the bin table and i can restore it back to person table

and how do i put add new ability to delete in restore.php...

newbie here

please help...thanks



Person
http://img690.imageshack.us/i/person.jpg/

Bin
http://img215.imageshack.us/i/bint.jpg/

mylist.php


<?php
include "connect.php";
$result = mysql_query("SELECT * FROM person");

echo '<form action="mydelete.php" method="post">';

echo '<table border="1">
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
</tr>';


$count = 1;

while($row = mysql_fetch_array($result))
{
$id=$row['id']; echo "<tr>";
echo "<td>";
echo $count++;

echo "<input type='checkbox' name='id[]' value='$id'></td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "</tr>";
}

echo '</table>
<br />
<input type="submit" value="Delete" />
</form>';

include "menu.php";


mysql_close($con);

?>


mydelete.php


<?php

include "connect.php";

$id=$_POST[id];

for($i=0;$i<count($_POST['id']);$i++)
{
$result = mysql_query("SELECT * FROM person WHERE id={$id[$i]}") or die(mysql_error());

$row = mysql_fetch_array( $result ) or die(mysql_error());

mysql_query("INSERT INTO bin (id, firstname, lastname, age)
VALUES
(". $row['id'] . ",". $row['firstname'] . ",". $row['lastname'] . ",". $row['age'] . ")");


mysql_query("DELETE FROM person WHERE id={$id[$i]}");
}
echo "$i records deleted";

include "menu.php";
mysql_close($con);
?>


restore.php




<?php
include "connect.php";
$result = mysql_query("SELECT * FROM bin");

echo '<form action="restore-success.php" method="post">';

echo "<H4>Deleted Items</H4>";

echo "Select To Restore";

echo '<table border="1">
<tr>
<th>No</th>
<th>Deleted ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
</tr>';

$count = 1;

while($row = mysql_fetch_array($result))
{
$bin_id=$row['bin_id'];

echo "<tr>";
echo "<td>";
echo $count++;

echo "<input type='checkbox' name='bin_id[]' value='$bin_id'></td>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['lastname'] . "</td>";
echo "<td>" . $row['age'] . "</td>";
echo "</tr>";
}

echo '</table>
<br />
<input type="submit" value="Restore" />
</form>';

include "menu.php";


mysql_close($con);

?>


restore-success.php


<?php

include "connect.php";

$bin_id=$_POST[bin_id];

for($i=0;$i<count($_POST['bin_id']);$i++)
{
$result = mysql_query("SELECT * FROM bin WHERE bin_id={$bin_id[$i]}") or die(mysql_error());

$row = mysql_fetch_array( $result ) or die(mysql_error());

mysql_query("INSERT INTO person (id, firstname, lastname, age)
VALUES
(". $row['id'] . ",". $row['firstname'] . ",". $row['lastname'] . ",". $row['age'] . ")");

mysql_query("DELETE FROM bin WHERE bin_id={$bin_id[$i]}");
}
echo "$i records restored";

include "menu.php";
mysql_close($con);
?>

[mulaus]

also tried below code but not working....this code is working perfectly if i input numbers for all fields i can delete and restore - but as text its failed to be inserted to bin table



<?php

include "connect.php";



$id_array=$_POST[id];
foreach($id_array as $id)
{
$result = mysql_query("SELECT * FROM person WHERE id=$id") or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$id=$row['id'];
$firstname=$row['firstname'];
$lastname=$row['lastname'];
$age=$row['age'];
}
echo $firstname;


mysql_query("INSERT INTO bin (id, firstname, lastname, age)
VALUES ($id,$firstname,$lastname,$age)");

mysql_query("DELETE FROM person WHERE id=$id");

echo "Record deleted and inserted to recycle bin";

}

mysql_close($con);
?>