[RESOLVED] Problem Deleting Customer Accounts

[cash09]

Hi,

I am trying to create a script that will delete customer accounts.

<?php
// This page deletes the customer account.

$problem = FALSE; // Assume no problem.

// Start the PHP page.
$page_title = 'Delete Account';
include ('./includes/header.php');

if (isset($_GET['customer_id'])) { // Make sure there's a customer ID.

$customer_id = $_GET['customer_id'];

require_once ('/mysqli_connect.php'); // Connect to the database.

$query = "DELETE FROM customers WHERE customer_id = '$customer_id'";

$result = mysqli_query ($dbc, $query) or die("Error: ".mysqli_error($dbc));	

if (mysqli_num_rows($result) == 1) { // Good to go

	echo '<div align="center"><font size="+1">The selected customer account has been deleted</font></div>';	

} else { // No record returned from the database.
	$problem = TRUE;
}

mysqli_close($dbc); // Close the database connection.

} else { // No customer ID.
	$problem = TRUE;
}

if ($problem) { // Show an error message.
	$page_title = 'Error';
	echo '<div align="center"><font size="+1">The account could not be deleted</font></div>';

}

// Complete the page.
include ('./includes/footer.php');
?>

At present when I run the script, the error message 'The account could not be deleted' is displayed which i think means the IF statement is failing. I have no idea why tho?

Any help would be greatly appreciated. Thanks

[djjjozsi]

you should use
mysqli->affected_rows
OR
mysqli_affected_rows

to catch the deleted records.

[cash09]

djjjozsi;10949494 wrote:

you should use
mysqli->affected_rows
OR
mysqli_affected_rows

to catch the deleted records.

I've added that in but I still get the same result. I think that the customer_id I'm trying to get from the previous script maybe isn't working?

Heres the code for the script that brings you to the delete account script:

<?php
// This page allows the administrator to browse customer accounts.

// Set the page title and include the PHP header.
$page_title = 'Browse Customer Accounts';
include ('./includes/header.php');

require_once ('/mysqli_connect.php'); // Connect to the database.



$query = "SELECT customer_id, title, CONCAT_WS(' ', first_name, last_name) AS name, address, town, postcode, telephone_no, email_address FROM customers ORDER BY customers.last_name ASC";

// Create the table head.
echo '<table border="0" width="100%" cellspacing="0" cellpadding="8" align="center">
<tr>
<td align="left" width="15%"><b>Title</b></td>
<td align="left" width="15%"><b>Name</b></td>
<td align="left" width="25%"><b>Address</b></td>
<td align="left" width="20%"><b>Town</b></td>
<td align="left" width="30%"><b>Postcode</b></td>
<td align="left" width="20%"><b>Telephone No</b></td>
<td align="left" width="30%"><b>Email Address</b></td>
<td align="left" width="30%"><b></b></td>
</tr>';

// Display all the books, linked to URLs.
$result = mysqli_query ($dbc, $query) or die("Error: ".mysqli_error($dbc));
while ($row = mysqli_fetch_array ($result, MYSQL_ASSOC)) {

// Display each record.
echo "	<tr>
<td align=\"left\">{$row['title']}</a></td>
<td align=\"left\">{$row['name']}</a></td>
<td align=\"left\">{$row['address']}</td>
<td align=\"left\">{$row['town']}</td>
<td align=\"left\">{$row['postcode']}</td>
<td align=\"left\">{$row['telephone_no']}</td>
<td align=\"left\">{$row['email_address']}</td>
<td align=\"left\"><a href=\"delete_account.php?cid={$row['customer_id']}\">Delete Account</td>
</tr>\n";

} // End of while loop.

echo '</table>'; // Close the table.


mysqli_close($dbc); // Close the database connection.
include ('./includes/footer.php');
?>

[cash09]

Okay I've updated my script and i'm now getting this error, even though the account is being deleted:

Warning: mysqli_affected_rows() expects parameter 1 to be mysqli, boolean given in C:\xampplite\htdocs\website\delete_account.php on line 20

Here is the new code:

<?php
// This page deletes the customer account.

$problem = FALSE; // Assume no problem.

// Start the PHP page.
$page_title = 'Delete Account';
include ('./includes/header.php');

if (isset($_GET['cid'])) { // Make sure there's a customer ID.

$customer_id = $_GET['cid'];

require_once ('/mysqli_connect.php'); // Connect to the database.

$query = "DELETE FROM customers WHERE customer_id = '$customer_id'";

$result = mysqli_query ($dbc, $query) or die("Error: ".mysqli_error($dbc));	

if (mysqli_affected_rows($result) == 1) { // Good to go

	echo '<div align="center"><font size="+1">The selected customer account has been deleted</font></div>';	

} else { // No record returned from the database.
	$problem = TRUE;
}

mysqli_close($dbc); // Close the database connection.

} else { // No customer ID.
	$problem = TRUE;
}

if ($problem) { // Show an error message.
	$page_title = 'Error';
	echo '<div align="center"><font size="+1">The account could not be deleted</font></div>';

}

// Complete the page.
include ('./includes/footer.php');
?>

[bradgrafelman]

[man]mysqli_affected_rows/man expects a connection resource to be passed (e.g. $dbc) - mysql_query() returns boolean TRUE or FALSE (for success or failure, respectively).

[cash09]

bradgrafelman;10949507 wrote:

[man]mysqli_affected_rows/man expects a connection resource to be passed (e.g. $dbc) - mysql_query() returns boolean TRUE or FALSE (for success or failure, respectively).

$dbc is passed on this line here tho isn't it? And then $result is gettin passed into the if statement?

	$result = mysqli_query ($dbc, $query) or die("Error: ".mysqli_error($dbc));	

if (mysqli_affected_rows($result) == 1) { // Good to go

Or I am getting confused with something else?

[djjjozsi]

but $result is not a link to your connection.

use $dbc...

[cash09]

That gets rid of the error message but I still get the 'The account could not be deleted' error message that I have set which means the IF statement is still failing?

[bradgrafelman]

Can you echo out $query and paste the output here for us to see?

[cash09]

I echo'd the query and this is what was returned:

DELETE FROM customers WHERE customer_id = '5'

This is exactly what I want. The customer_id varies depending on what customer account was chosen on the previous page.

[bradgrafelman]

If you change "DELETE" to "SELECT *", can you execute the query (either via the CLI or in a GUI such as phpMyAdmin) and see the row in the table that you're trying to delete?

[cash09]

Yeah I can... It shows all the fields associated with that customer_id.

[halojoy]

I would not quote $cid
if it is a number.
We quote STRING values.

"DELETE FROM customers WHERE customer_id = $customer_id"

$_GET['cid'] error you did find yourself

I have changed error reportin to display error number like: (1)
Remember now, that once the script has deleted record once
it will report error number (1) every time after.

So, in order to test your script is working alright
you need to INSERT $customer_id again, before run script

This is what I think it should be:

<?php
// This page deletes the customer account.

$problem = FALSE; // Assume no problem.

// Start the PHP page.
$page_title = 'Delete Account';
include ('./includes/header.php');

if (isset($_GET['cid'])) { // Make sure there's a customer ID.

$customer_id = $_GET['cid'];
require_once ('/mysqli_connect.php'); // Connect to the database.
$query = "DELETE FROM customers WHERE customer_id=$customer_id";
$result = mysqli_query ($dbc, $query) or die("Error: ".mysqli_error($dbc));    

if (mysqli_affected_rows($dbc) == 1) { // Good to go
    echo '<div align="center"><font size="+1">The selected customer account has been deleted</font></div>';    
} else { // No record returned from the database.
    $problem = 1;
}

mysqli_close($dbc); // Close the database connection.
} else { // No customer ID.
    $problem = 2;
}

if ($problem) { // Show an error message.
    $page_title = 'Error';
    echo '<div align="center"><font size="+1">The account could not be deleted ('.$problem.')</font></div>';
}

// Complete the page.
include ('./includes/footer.php');
?>

[cash09]

halojoy;10949525 wrote:

I would not quote $cid
if it is a number.
We quote STRING values.

"DELETE FROM customers WHERE customer_id = $customer_id"

$_GET['cid'] error you did find yourself

I have changed error reportin to display error number like: (1)
Remember now, that once the script has deleted record once
it will report error number (1) every time after.

So, in order to test your script is working alright
you need to INSERT $customer_id again, before run script

This is what I think it should be:

<?php
// This page deletes the customer account.

$problem = FALSE; // Assume no problem.

// Start the PHP page.
$page_title = 'Delete Account';
include ('./includes/header.php');

if (isset($_GET['cid'])) { // Make sure there's a customer ID.

$customer_id = $_GET['cid'];
require_once ('/mysqli_connect.php'); // Connect to the database.
$query = "DELETE FROM customers WHERE customer_id=$customer_id";
$result = mysqli_query ($dbc, $query) or die("Error: ".mysqli_error($dbc));    

if (mysqli_affected_rows($dbc) == 1) { // Good to go
    echo '<div align="center"><font size="+1">The selected customer account has been deleted</font></div>';    
} else { // No record returned from the database.
    $problem = 1;
}

mysqli_close($dbc); // Close the database connection.
} else { // No customer ID.
    $problem = 2;
}

if ($problem) { // Show an error message.
    $page_title = 'Error';
    echo '<div align="center"><font size="+1">The account could not be deleted ('.$problem.')</font></div>';
}

// Complete the page.
include ('./includes/footer.php');
?>

This works perfectly... Thank you very very much! 🙂

[bradgrafelman]

Also note that user-supplied data should never be placed directly into a SQL query, else your code is vulnerable to SQL injection attacks (and/or just plain SQL errors). Instead, you must first sanitize it with a function such as [man]mysql_real_escape_string/man (or, for integers, cast the data to an (int) or use the [man]intval/man function).