Sql delete from row by clicking a button

[jdock1]

Im displaying the results of a database by using a while loop... so Im trying to figure out how to delete some entries from the datbase. What I wanted to do is add a button in each result that will have the value "delete"... but I cant figure out how to do this. Im kinda new to mysql. I just want it so when I click the button the result will get deleted from the database.

 

I did some research on this but I cant really grasp the examples.

 

Can anybody help me out with this one?

 

Thanks!

[Pikachu2000]

If it's likely that you'll want to delete multiple records at a time, you'd be better off to add a checkbox when you echo each record, then you can loop through the checkbox array and delete the associated records with one click (and a confirmation dialog, if you want one).

[jdock1]

If it's likely that you'll want to delete multiple records at a time, you'd be better off to add a checkbox when you echo each record, then you can loop through the checkbox array and delete the associated records with one click (and a confirmation dialog, if you want one).

I was thinking of that, the only problem is Idk where to start with that... Its frustrating I cant think of anyway to do it at all. Can you give a few code examples?

[jdock1]

Anybody put me in the right direction?

[Pikachu2000]

Take a look a these scripts and you should get a pretty good idea of what you need to do. They're pretty basic and could be organized a little better, but do include some validation and error checking. I briefly tested them locally, and they seem to do as intended, but there are no warranties, either express or implied

 

test.php

<?php
// **** include your own database credentials ****
$dbc = mysqli_connect('localhost', 'root', 'root', 'test') or die( 'Database unavailable' );
// for this example, retrieve only the newest 30 records
$query = "SELECT `pk_id`, `name`, `email`, `department` FROM `table` LIMIT 30";
echo '<table>';
if( $result = mysqli_query($dbc, $query) or die(mysqli_error($dbc)) ) {
echo '<form action="delete.php" method="post">';
while( $array = mysqli_fetch_assoc($result) ) { // echo the results into a table with each checkbox's value as that of the pk_id field.
	echo "
	<tr>
	<td>{$array['name']}</td>
	<td>{$array['email']}</td>
	<td>{$array['department']}</td>
	<td><input type=\"checkbox\" name=\"pk_id[]\" value=\"{$array['pk_id']}\" /> (Delete)</td>
	</tr>
	";
}
echo '
<tr>
<td colspan="4"><input type="hidden" name="submitted" value="true" /><input type="submit" name="submit" value="Submit" /></td>
</tr>
</form>
</table>
';
} else {
echo '
<tr>
<td>Database error.</td>
</tr>
</table>
';
}
?>

 

delete.php

<?php
// delete.php
// **** include your own database credentials ****
$dbc = mysqli_connect('localhost', 'root', 'root', 'test') or die( 'Connect: ' .mysqli_connect_error() );
if( !isset($_POST['submitted']) || $_POST['submitted'] != 'true' ) {
header('Location: test.php'); // If form hasn't been submitted, no reason to access this script, so redirect.
exit();
} else {
if( $_POST['confirmed'] != 'yes' ) { // if form received from test.php, list the candidates for deletion, and present confirmation checkbox
	$errors = array();
	$delete = array();
	foreach( $_POST['pk_id'] as $key => $val ) { // since pk_id is expected to be a whole number, validate it as such and cast as integer
		if( ctype_digit($val) ) {
			$delete[$key] = (int) $val;
		} else { // if any of the values are not a whole number, exit with
			exit('One or more keys are invalid. Cannot proceed.');
		}
	}
	$query = "SELECT `pk_id`, `name`, `email`, `department` FROM `table` WHERE `pk_id` IN( " . implode( ',', $delete) . " ) ORDER BY `pk_id` DESC LIMIT 30";
	if( $result = mysqli_query($dbc, $query) ) {
		echo '<table><form action="" method="post">';
		while( $array = mysqli_fetch_assoc($result) ) {
			echo "
		<tr>
		<td>{$array['name']}</td>
		<td>{$array['email']}</td>
		<td>{$array['department']}<input type=\"hidden\" name=\"pk_id[]\" value=\"{$array['pk_id']}\" /></td>
		</tr>";
		}
		echo '<tr><td colspan="3"><input type="hidden" name="submitted" value="true" />';
		echo '<input type="checkbox" name="confirmed" value="yes" /> Confirm permanent deletion of the above records.<br />';
		echo '<input type="submit" name="submit" value="Delete Now" /></td></tr>';
		echo "</form></table>";
	} else {
		trigger_error( 'Query failed: ' . mysqli_error($dbc) );
	}
} else { // if confirmed and resubmitted, delete indicated records, revalidate data and delete records
	foreach( $_POST['pk_id'] as $key => $val ) {
		if( ctype_digit($val) ) {
			$delete[$key] = (int) $val;
		} else { // if any of the values are not a whole number, exit with
			exit('One or more keys are invalid. Cannot proceed.');
		}
	}
	$query  = "DELETE FROM `table` WHERE `pk_id` IN ( ". implode(',', $delete) . " ) LIMIT " . count($delete);
	$result = mysqli_query($dbc, $query);
	if( mysqli_affected_rows($dbc) == count($delete) ) { // if number of affected rows is same as number of deletion candidates, result OK, redirect to main form again
		header('Location: test.php');
		exit();
	} else { // if one or more records failed to delete, notify user of error.
		echo 'Some records were NOT deleted! <a href="test.php">Return</a> to selection page to review.';
	}
}
}
?>