Mass TRIM possibe?

Square1

Hi all:

I have a lot of old records in MySQL that need trimming. A lot of different tables and a lot of different columns.

Right now I am using

$queryUpdate = "UPDATE Events_copy SET promoter = TRIM(promoter)";	mysqli_query($link, $queryUpdate);

For each individual column in each individual table. Is there a faster way to do this? I didn't see anything in phpMyAdmin. is there any way you can trim every column in a table at once?

Thanks!

NogDog

I don't know of any automatic way, but you can do more than one column at a time.

UPDATE `table` SET
  `col1` = TRIM(`col1`),
  `col2` = TRIM(`col2`),
  `col3` = TRIM(`col3`)

NogDog

PS: If you're talking about a lot of tables and columns (for some undefined value of "a lot"), you could write a script to get all the tables from the DB, then for each table get all the text/char type fields, and build and execute a query for each table.

Derokorian

Try like this:

$select = "SELECT table_name as tbl, column_name as col
	FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_SCHEMA = 'derokor' AND 
	(
		DATA_TYPE LIKE '%char' OR 
		DATA_TYPE LIKE '%text' OR
		DATA_TYPE LIKE '%blob'
	)";
$result = mysqli_query($link,$select);
while( $row = mysqli_fetch_assoc($result) ) {
   mysqli_query($link,"UPDATE $row[tbl] SET $row[col] = TRIM($row[col])");
}

In my query 'derokor' is the name of the database I was testing on, you would need to change this to your database name.

Square1

Thank you. I will give this a try