Skip to content

Instantly share code, notes, and snippets.

@rcarvs
Created January 19, 2019 19:00
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save rcarvs/39bcc48376f8c086946c2b4eacc78dc5 to your computer and use it in GitHub Desktop.
Save rcarvs/39bcc48376f8c086946c2b4eacc78dc5 to your computer and use it in GitHub Desktop.
PHP Script to update all defined word in every column of Mysql database
<?php
/*
@Author: carvs
-----
@HOW TO USE
-----
OBS: Necessary to use mysql user with permission in information_schema database and in database witch you would like to change defined word.
> php ./mysql_all_text_update.php [host] [database] [mysqluser] [mysqlpassword] [oldword] [newword]
Example: php ./mysql_all_text_update.php localhost website root mypass website_logo.jpg new_logo.png
*/
$pdoInfoSchema = new PDO('mysql:host='.$argv[1].';dbname=information_schema',$argv[3],$argv[4]);
$queryGetAllTextColuns = $pdoInfoSchema->prepare("SELECT table_schema,table_name,column_name FROM information_schema.columns
WHERE table_schema = '".$argv[2]."' AND (column_type LIKE 'char(%' OR column_type LIKE 'varchar(%' OR column_type LIKE '%text')");
$pdo = new PDO('mysql:host='.$argv[1].';dbname='.$argv[2],$argv[3],$argv[4]);
if($queryGetAllTextColuns->execute()){
$colunsList = $queryGetAllTextColuns->fetchAll(PDO::FETCH_OBJ);
//var_dump($colunsList);
foreach($colunsList as $colum){
$queryUpdate = $pdo->prepare("UPDATE ".$colum->table_name." SET ".$colum->column_name." = REPLACE(".$colum->column_name.", '".$argv[5]."', '".$argv[6]."') WHERE ".$colum->column_name." LIKE '%".$argv[5]."%'");
if($queryUpdate->execute()){
echo "\n".$queryUpdate->rowCount()." updated row in [".$colum->table_name."].[".$colum->column_name."]";
}
}
}else{
echo "\nThere was an error executing the search of fields of your tables\n";
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment