MySQL –– mass editing
written by: admin
Date Written: 3/11/10
Last Updated: 3/12/10
This program is a bit dangerous, so make sure you have the appropriate security settings in place so that only users who have the proper admin privileges can run this program. You can set up security settings using htaccess, sessions, and/or cookies. Your choice. Also be sure to
BACK UP YOUR DATABASE!! I backed up my database just before running this program and I found that even though I took many precautions and tested the program out a few times beforehand I still ruined my database and had to restore it.
This little script will insert the word "hi" at the beginning of "col1" in "table" in every row.
<?php
include 'db.php';
$query = "SELECT * FROM table order by ID";
$result = mysql_query($query,$conn) or die ("Couldn't execute query.");
while ($add_info = mysql_fetch_array($result)){
$ID = $add_info['ID'];
$col1 = $add_info['col1'];
$col1 = substr_replace($col1,'hi',0,0);
$col1 = mysql_real_escape_string($col1);
$query1 = "update misc set col1='$col1' where ID='$ID'";
mysql_query($query1) or die ("Couldn't execute query.");
}
?>
If you have
magic_quotes_gpc = on then you will want to add
$col1=stripslashes($col1); right before
$col1 = mysql_real_escape_string($col1); It is a good idea to turn off magic_quotes_gpc as the setting has deprecated.
This script is handy for mass editing of all the rows in a table.
TAGS: mysql