seems like I have an array problem…
this is a script to rename all the fields in all tables to remove white spaces. Such as, changing the field name ‘invoice by’ to ‘invoiceby’
this is a script that I found. First I had the problem with the query function, but now I am on to the bolded line below.
foreach ($columnstemp as $columntemp)
hope someone can help me!
[php]
//just to help prevent the script from timing out
ini_set(‘memory_limit’, ‘100M’);
ini_set(‘default_socket_timeout’,600);
ini_set(‘max_input_time’,600);
ini_set(‘max_execution_time’,600);
//give us something to look at on the screen
echo “
Start Process…
”;//create a database connection
$mysql_hostname = ‘localhost’;
$mysql_user = ‘username’;
$mysql_password = ‘password’;
$mysql_database = ‘databaseName’;
$conn = mysql_connect($mysql_hostname,$mysql_user,$mysql_password);
mysql_select_db($mysql_database, $conn);
//build up the list of tables to process
$sql = “SHOW TABLES”;
$tables = array();
echo ‘
Building audit tables list…
’;//$tablestemp = $db->query($sql,2); this line didn’t work for me so I changed it to the line below and found a query function online
$tablestemp = query($sql,2);
foreach ($tablestemp as $tabletemp)
{
$tables[] = $tabletemp;
}
echo ‘
Audit table list built.
’;//run through each table, build up a column list and then run an update SQL statement against the column
foreach($tables as $table)
{
set_time_limit(80);
$sql = "SHOW COLUMNS FROM $table";
$columns = array();
echo '
Building audit columns list for ’ . $table . ‘…
’;//$columnstemp = $db->query($sql,2); this line didn’t work for me so I changed it to the line below and found a query function online
$columnstemp = query($sql,2);
foreach ($columnstemp as $columntemp)
{
$columns[] = $columntemp;
}
echo ‘
Audit column list for ’ . $table . ’ built.
’;foreach($columns as $column)
{
set_time_limit(80);
//trim and replace in one foul SQL swoop!
$sql = "UPDATE `$table` SET `" . $column['Field'] . "` = TRIM(REPLACE(REPLACE(REPLACE(`" . $column['Field'] ."`,\"'\",\"'\"),'\"','"'),'~','''))";
echo "<p>$sql</p>";
mysql_query($sql);
}
}
mysql_close($conn);
echo “
End Process.
”;?>[/php]and here is the query function that I used:
[php]function query($query) {
if(!$query) return “”;
$result = mysql_query($query) or die("MySQL Error : " . mysql_error() . "\n<br />In Query <code>$query</code>");
if(mysql_num_rows($result) == 1) { //If there is just one result, return it.
$arr = mysql_fetch_assoc($result);
if(count($arr) == 1) { //If there is just one result...
$item = array_values($arr);
return stripslashes($item[0]); // Creates the effect of 'getOne()'
} else {
foreach($arr as $key => $value) $arr[$key] = stripslashes($value);
return $arr; // Makes the 'query()' effect
}
} else {
$arr = array();
$primary_key = false;
while ($all = mysql_fetch_row($result)) {
if(count($all) == 1) array_push($arr,$all[0]);
elseif(count($all) == 2) {
if(!$primary_key) { //Happens only one time
$meta = mysql_fetch_field($result,0); //If the first element is a primary key, make
$primary_key = $meta->primary_key; // the result an associative array.
}
//Make it an Associative Array if there is a primary_key in the given data.
else break;
}
if($primary_key) $arr[$all[0]] = $all[1];
else break;
}
if($arr) {
//Do a stripslashes() on each element
foreach($arr as $key => $value) $arr[$key] = stripslashes($value);
return $arr; // For 'getAll()'
} else { //If nothing matches...
mysql_data_seek($result,0); //Reset the Query.
return $result; // This results in 'getSqlHandle()' effect
}
}
}
[/php]