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.