Deleting records - checking to make sure they exist

[tom11011]

Hi, I have a question about deleting records. In my form, the record will be deleted based on the Asset number entered into a form. In my snippet of code below, I check to make sure the entry is not blank. That works. How can I check to make sure the asset even exists? Currently, as long as something is put in the form, it will say "Record Deleted" even if there is no such record. If the record really does exist, it works ok.

Thanks in advance.


<?php

if (isset($_POST['delete'])):
// A new entry has been entered
// using the form below
$delasset = $_POST['delasset'];
$sqldel = "DELETE FROM Inventory WHERE
Asset='$delasset'";

if(empty($delasset)){
$blanks++;
}
if ($blanks > 0){
echo('<p><table><tr><td bgcolor=red><font face=verdana size=-4>Error deleting record: ' .
mysql_error() . '</font></td></tr></table></p>');
echo "&nbsp;<font face=verdana size=-4>* Asset cannot be blank *</font>";
}else{

if (mysql_query($sqldel)) {
echo('<p><table><tr><td bgcolor=#ccffcc><font face=verdana size=-4>* Record Deleted *</font></td></tr></table></p>');
} else {
echo('<p><font face=verdana size=-4>Error deleting record: ' .
mysql_error() . '</font></p>');
}
}

?>

 

[sleipnir214]

Check out mysql_affected_rows()



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[sleipnir214]

Actually, mysql_affected_rows() will tell you how many rows were deleted after the DELETE query is executed.

If you're looking to see, ahead of time, how many rows match the asset number entered by the user, you're going to have to execute a SELECT query.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[tom11011]

Hi, thanks for responding, I'm not sure if I asked the right question, you misunderstood, or I am misunderstanding what you are saying.

Basically, my delete script works just fine. But if I put in a value that doesn't exist, it reports that it sucessfully deleted the record and does nothing to the database. I wish it to check to see if it exists, and if it does not, report that the record does not exist and try again.

thanks again.

 

[sleipnir214]

See my second post.

If you want to see whether a value exists in a database, you'll have to perform some kind of SELECT query. It's the only way to get records or information about records out of a database.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[spookie]

As sleipnir says, make use of mysql_affected_rows() after you perform the delete operation.

Even if the query does not affect data in the table (because there is no matching row), it will return true provided it is syntactically correct.

so you can have something like

if ( mysql_affected_rows() > 0 ) {
     echo "Record deleted successfully" ;
}
else {
     echo "Record to be deleted does not exist" ;
}

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[ingresman]

call mysql_affected_rows(), if the answer is 0 you disn't delete anything

 

[tom11011]

Here is my updated code, but it's not working. Just get a blank html page. Thanks for your help.

<?php

if (isset($_POST['delete'])):
// A new entry has been entered
// using the form below
$delasset = $_POST['delasset'];
$sqldel = "DELETE FROM Inventory WHERE
Asset='$delasset'";

if(empty($delasset)){
$blanks++;
}
if ($blanks > 0){
echo('<p><table><tr><td bgcolor=red><font face=verdana size=-4>Error deleting record: ' .
mysql_error() . '</font></td></tr></table></p>');
echo "&nbsp;<font face=verdana size=-4>* Asset cannot be blank *</font>";
}else{

if ( mysql_affected_rows() > 0 ) {
echo "Record does exist" ;
}
else {
echo "Record to be deleted does not exist" ;
}
}else{

if (mysql_query($sqldel)) {
echo('<p><table><tr><td bgcolor=#ccffcc><font face=verdana size=-4>* Record Deleted *</font></td></tr></table></p>');
} else {
echo('<p><font face=verdana size=-4>Error deleting record: ' .
mysql_error() . '</font></p>');
}
}

?>

 

[sleipnir214]

As the PHP online manual entry for the funstion states, mysql_affected_rows() reports the number of changes to the database made by the previous UPDATE, INSERT or DELETE operation. It can only tell you how many rows were previously deleted -- it cannot tell you how many rows would be deleted.

If you need to know ahead of time how many rows will be deleted by a DELETE query, you must first issue a SELECT query which uses the same WHERE clause as the DELETE query will.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[tom11011]

ok, I tried, but no luck. I'm still a newbie here but here is my latest effort that does not load the page. Thanks again.

<?php

if (isset($_POST['delete'])):
// A new entry has been entered
// using the form below
$delasset = $_POST['delasset'];
$sqlsel = "SELECT FROM Inventory WHERE
Asset='$delasset'";
$sqldel = "DELETE FROM Inventory WHERE
Asset='$delasset'";

if(empty($delasset)){
$blanks++;
}
if ($blanks > 0){
echo('<p><table><tr><td bgcolor=red><font face=verdana size=-4>Error deleting record: ' .
mysql_error() . '</font></td></tr></table></p>');
echo "&nbsp;<font face=verdana size=-4>* Asset cannot be blank *</font>";
}else{

if (mysql_query($sqlsel)) {
echo('<p><table><tr><td bgcolor=#ccffcc><font face=verdana size=-4>* Record Exists *</font></td></tr></table></p>');
}
echo('<p><font face=verdana size=-4>Error - No such record: ' .
mysql_error() . '</font></p>');
}else{

if (mysql_query($sqldel)) {
echo('<p><table><tr><td bgcolor=#ccffcc><font face=verdana size=-4>* Record Deleted *</font></td></tr></table></p>');
}
echo('<p><font face=verdana size=-4>Error deleting record: ' .
mysql_error() . '</font></p>');
}

?>

 

[sleipnir214]

At first blush, I notice your code has a couple of problems.

First is that your SELECT query is flawed. I recommend that you read up on SELECT queries in the MySQL online manual.

Second is that with a SELECT query, running mysql_query() is only step one of the process. You also need to fetch the returned information. There is example code in the PHP online manual page for mysql_fetch_array()



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[tom11011]

Hi again, I added this:

$sqlsel = "SELECT Asset FROM Inventory WHERE
Asset='$delasset'";

However, I don't know what to do with mysql_fetch_array() after reading your link. Could you help me out by editting my script?

PS- this is not for a work venture, this is for personal use. Basically, I am teaching myself to add and delete records, which works, but I want to learn to add the error checking.

thanks again.

 

[sleipnir214]

There is excellent example code in the PHP online manual. I have provided the links.



Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[DRJ478]

Suggestion:

$sqlsel = "SELECT count(*) as num FROM Inventory WHERE Asset='$delasset'";
# execute query and catch error
$result = mysql_query($sqlsel) OR die (mysql_error());
# read resulting row that holds the count
$row = mysql_fetch_assoc($result);
# evaluate num
if ($row['num']>0){
  # there's something to be deleted....
etc.