DELETING any record in any table in the database if feildx=123

Question

is there a mysql statemnet that goes through every table in a data base and says if applicationid =123 or schoolid=123 or familyid = 123 DELETE THE WHOLE record? i need to write a php script that will do this.

Answer 1

SELECT TABLE_NAME, GROUP_CONCAT(DISTINCT COLUMN_NAME SEPARATOR ',') AS columns 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'yourdatabasename' 
AND COLUMN_NAME IN ('applicationid', 'schoolid', 'familyid')
GROUP BY TABLE_NAME

The result will be an array of each table, and the columns that it has (only from the set of `applicationid, schoolid, familyid) as a comma separated field.

foreach ($results as $result) {
    $cols = explode(',', $result['columns']);
    foreach ($cols as &$col) {
        $col .= ' = 123';
    }
    $sql = 'DELETE FROM '. $result['TABLE_NAME'].
        ' WHERE ' . implode(' OR ', $cols);
}

That'll generate a query for each table like:

DELETE FROM table1 WHERE applicationid = 123 OR schoolid = 123

And it will only include the fields within the tables...

Answer 2

THere's no such single statement. You could fetch a list of table names from information_schema database, and then use stored procedure or external script to loop through it and delete these rows.

Here's reference about information_schema tables. http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

Answer 3

First use SHOW TABLES to get list of tables then loop through tables list and use DELETE FROM tablename WHERE field=?

IMPORTANT: You should have privileges to use SHOW TABLES

Answer 4

how is

$showtablequery = "
SHOW TABLES
FROM
[database]
";

$x='0'; $showtablequery_result = mysql_query($showtablequery); while($r = mysql_fetch_array($showtablequery_result)) { $query="DELETE FROM $r[$x] WHERE applicationid = 123 OR schoolid = 123 OR familyid = 123"; $x++ }