CODEWITHSUNDEEP
mysqlsql
Arman
Arman

Reputation: 73

How to check affected rows before an sql query running?

Consider a table with some rows. by php, I make a SQL query such as "DELETE * From my_table". How can I recognize affected rows before running the sql query. On the other words, I want to have the list of affected rows after an sql query and before of its running.

All in all, I want to check somethings before running SQL irreversible queries.

best respects.

Upvotes: 1

Views: 5324

Answers (5)

shenyan
shenyan

Reputation: 428

Add the MySQL EXPLAIN keyword before your SQL statement.

Upvotes: 2

Djordje
Djordje

Reputation: 186

You can use SELECT and then mysqli_affected_rows to get the number of rows about to get deleted. If you want data from the rows as well then you fetch it with SELECT.

Upvotes: 1

Barmar
Barmar

Reputation: 781741

You can perform a SELECT query with the same JOIN and WHERE criteria as the DELETE would use. This will return the rows that would have been deleted.

If you want perfect safety, you need to use transactions in all the applications that access the tables. The process that's doing the DELETE should perform the SELECT in the same transaction. Otherwise, another process could add rows to the table between the SELECT and DELETE, and they might be deleted if they meet the criteria.

If you're just trying to verify the logic of your DELETE query with an eyeball examination of the results, this may be overkill.

Upvotes: 4

PlantTheIdea
PlantTheIdea

Reputation: 16369

1) Create a function that analyzes the rows:

function checkRows($table){
    $query = 'SELECT * FROM ' . $table;

    // execute SELECT query
    // get the result
    // using for loop, set $delete boolean based on whether to delete or not

    return $delete;
}

2) Create a function that deletes the stuff:

function deleteFromTable($table){
    $query = 'DELETE FROM ' . $table;

    // execute DELETE query
}

3) Create conditional calls:

$table = 'SomeTable';
$validDelete = checkRows($table);

if($validDelete){
    deleteFromTable($table);
}

This will allow you to perform validation of records prior to deletion.

Upvotes: 0

JNevill
JNevill

Reputation: 50200

Suppose you have a DELETE query like:

DELETE FROM my_table WHERE foo='bar'

To see how many records will be deleted you could first run:

SELECT count(*) from my_table where foo='bar'

The result of that query will be the count of records that meet the same condition.

Upvotes: 2

Related Questions