How to update all databases?

[antdk]

Hi, i have +600 databases in my phpmyadmin that runs +600 sites.

Now i want to update ALL fields that contains "xxxxx" with "yyyyy" - how can this be achieved?

[mac_gyver]

there are no wild-card/ALL database/table/column commands.

 

you would need to use a SHOW DATABASES query to get a list of all the databases on the server. you could then loop through that list and use each database name to get the list of tables in each database using a SHOW TABLES query. then use a SHOW COLUMNS query to get a list of all columns within each table within each database.

[Barand]

It's times like this you are glad you normalized your data so a string literal value only occurred in one record in one table.

 

If you do take the multi-looping approach given to you by Mac_Gyver, I would recommend you first do a select and display the values and their locations prior to doing a replace just in case it finds some you do not want to change.

[antdk]

Hi,

 

Thanks for the replies.

 

Well the thing is that it is all admin passwords i want to replace with a new one due to another company is taking over the support etc of the sites.

 

So its pretty basic update table tablename, and then SET 'pass' = 'XXXXX' WHERE 'pass' = 'YYYYY' - but if i have to access each database at a time, i might almost be as quick to login to the individual site and reset it to something else.

 

Anyone have somekind of link of an example of how to do this? And im no big scripting guru xD

[mac_gyver]

it sounds like you know the table name and column name.

 

you can use php to dynamically provide the database name in the update query -

$database = .... value gotten by looping over the result from the SHOW DATABASES query

$query = "UPDATE `$database`.table_name SET pass = 'XXXXX' WHERE pass = 'YYYYY'";

[Barand]

This may help. You will need to edit the include file "dbconnect.php" to set host, user,password and default db then run "ChangePwd.php".

 

The other php files are for building the menus of table and col names dynamically.

 

When you select the column to be updated it will display a preview of the record to be updated if the password field is matched. Note that this assumes you are storing your passwords as plain text. If not you will need to add your hashing code to the update bit at the top of the ChangePwd.php file and also the search code in "ChangePwd_ajax_p.php"

 

 

changePwd.php

changePwd_ajax_c.php

changePwd_ajax_p.php

changePwd_ajax_t.php

dbconnect.php

[jpopuk]

Thank you for reply.

 

Did not realise creating a cascading dropdown could be so complex. - I thought the tricky part would be getting data from database in the form but that was straight forward (as I mentioned above this was already done).

 

Is there not a simplified: if option 1 selected echo option 1 values in second dropdown?

 

Cheers

[Barand]

 

Is there not a simplified: if option 1 selected echo option 1 values in second dropdown?

 

 

 

'Fraid not. The selection is done on the client after php has finished running on the server (which could be on a different continent). So it has to be done on the client using javascript or by communicating with the server via ajax

 

Other way is to show form with first menu only. User makes selection and submits form. Form is then displayed again with second menu as well. But this requires rebuilding the page each time.

Edited May 13, 2014 by Barand