How would i go about this ?, pretty complicated.

[jamesxg1]

Hiya Peeps,

 

I need to make a script that will search and entire database (mysql) for a specific thing and delete it.

 

EG. Search DB for: 8398487924, All results with that in the row `DROP`,

 

But i need it to search the whole thing without being un-secure or penetrable,

 

Where would i start ?,

 

Many thanks,

 

James.

[Mark Baker]

Searching for the value within a table column, understood.

Searching for the value within certain specific columns in a table, understood.

 

Searching in every column in every table of the database. Why?

At least, that's what your question appears to be asking.

If that is what you mean, then there's almost certainly something very wrong with what you're trying to do.

[jamesxg1]

Hiya,

 

Wierd one i know but heres the story,

 

I have built a membership system and as usually every member has a unique id.

 

Well i want to remove any columns that have there ID in the `id` column upon there 'Remove Account' submission.

 

Many thanks,

 

James.

[Psycho]

How is that complicated? That is basic database maintenance.

 

Just include the appropriate table names and column names.

mysql_query("DELETE FROM table1 WHERE user_id='{$userID}'");
mysql_query("DELETE FROM table2 WHERE user_id='{$userID}'");
mysql_query("DELETE FROM table3 WHERE user_id='{$userID}'");
mysql_query("DELETE FROM table4 WHERE user_id='{$userID}'"); 

[jamesxg1]

Lol. No complicated in the fact of i was trying to do it in one query a SEARCH if you will.

 

As i was hoping i could build my script even larger and as my database grows with new tables ect i dont have to keep adding it to the script.

 

Many thanks,

 

James.

[jamesxg1]

Anyone have any idea's ?,

 

Many thanks,

 

James.

[Psycho]

Then you would need to concatenate the queries into one string, and run that. I think you would just delimit the queries using a semicolon. If all the records use the same field name for the id then jsut create a loop to go through each table by name and do the delete operation for any record where the field value is the vale you are searching for.

 

Personally, I wouldn't do such a thing. When dealing with databases I think the developer should be explicit in every action that is taken. The developer should state precisely what tables to delete data from and exacty why. Trying to automate this only has the potential to cause problems.

 

This is the perfect scenario for the creation of a class where you could create all the delete operations individually and then chain them as necessary.

[jamesxg1]

Yes this would be made into a class.

 

And ok i will try my best .

 

Could you show me where to start please lol.

 

Many thanks,

 

James.

[Daniel0]

Look up constraints in InnoDB.

[Mark Baker]

Does MySQL support DELETE CASCADE like Oracle?

[Daniel0]

It supports cascading deletes (and updates) using constraints in InnoDB.