mysqli_affected_rows() - using a SQL DELETE Statement

[truegilly]

Hi, wondering if anyone can help...

I have made a form that a user can input the ArtefactID and EmployeeId to delete a row in my database.

To make the page slightly more robust I want it reject a query that contains invalid data. The problem is in MYSQL it displays the same message regardless of weather a row was deleted or weather a row was not, due to invalid data.

[b]A valid query[/b]
[color=orange]DELETE FROM Artefact WHERE abArtifact = 10 AND dbEmployeeId = 'e2'; [/color]

[b]MySQL outputs[/b]
[color=orange]Query OK, 0 rows affected[/color]

[b]A invalid query[/b]
[color=orange]DELETE FROM Artefact WHERE abArtifact = 5854 AND dbEmployeeId = 'z9'; [/color]

[b]MySQL outputs[/b]
[color=orange]Query OK, 0 rows affected[/color].

In my PHP Code im using a switch statement...

[color=orange]switch(mysqli_affected_rows($statement))
{
case 0: // success
$useCaseComplete = true;
$useCaseOutcomeMessage = $_POST['frmfileId'] . " File Deleted";
break;

case 1: // duplicate primary key
$invalidOwnerIdMessage = "No artifact found.";
break;

default: // something else failed - just give up
$useCaseComplete = true;
$useCaseOutcomeMessage = mysqli_stmt_errno($statement);
break;

}[/color]

The problem is it will always receive 0 rows regardless of the query.

Can any one suggest a solution ??

Many thanks

Jonathan  ;)

Here is the SQL of my table

CREATE TABLE artefact
(
  dbArtefactId          INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  dbEmployeeId          VARCHAR(10) NOT NULL,    -- such as 'e1' (foreign key of the owner of this artefact)
  dbArtefactFileName    VARCHAR(255) NOT NULL,  -- such as 'asterisk.wav'. This could be the name of the file in the file store.
  dbArtefactFileType    VARCHAR(50),            -- MIME type such as audio/wav
  dbArtefactFileSize   INTEGER,                -- in bytes such as 44136
  dbArtefactDescription VARCHAR(4000),          -- free text comments, use how you wish
  dbArtefactFileContent  MEDIUMBLOB,            -- the actual media artefact (if the file is held in the filestore, this will be empty)
  UNIQUE employee_file_name (dbEmployeeId, dbArtefactFileName), -- owner cannot have two files with the same name
  INDEX file_name_index (dbEmployeeId, dbArtefactFileName),    -- speeds up retrieval of an owners files by filename
  INDEX file_type_index (dbEmployeeId, dbArtefactFileType),    -- speeds up retrieval of an owners files by filetype
  CONSTRAINT artefact_employee_FK
  FOREIGN KEY (dbEmployeeId)
    REFERENCES employee(dbEmployeeId) -- an artefact must reference the employee that 'owns' it
) ;

[joquius]

lets say someone inputs "delete.php?delete=10"
[code]
$query = mysql_query ("SELECT `id` FROM `table` WHERE `id` = '".mysql_real_escape_string ($_GET['id'])."'");
if (mysql_num_rows ($query) == 0) {
$error = "Blah";
} elseif (mysql_num_rows ($query) > 1) {
$error = "Bleh";
} else {
delete it
}
[/code]

[trq]

can't you just check to see wheather or not your query failed? eg;

[code=php:0]
if (mysql_query($sql)) {
  // success.
} else {
  // failed.
}
[/code]

[trq]

joquius, mysql_num_rows only works on SELECT statements.

[truegilly]

thanks for a quick replay

Im using POST, do i replace the instances of GET in your code ?

jonathan.

[joquius]

Mine was a SELECT query (I added a query)

[trq]

[quote]Mine was a SELECT query (I added a query)[/quote]

Did you read the question?

[joquius]

Did you read my answer?
Just check whether the line exists, doesn't, or more than one exist, then delete only if it exists once.

I just wrote 'delete' and not the whole query which I felt would be redundant.

[truegilly]

Hi thorpe,

thanks for a swift reply

I dont think i can because even if the query contains garbage data it still reports....

Query OK, 0 rows affected.

truegilly  ;)

[joquius]

Just check before whether the query is going to have an affect rather than checking after. Query the DB, check the results, and act accordingly, deleting when the conditions you approve of exist.