Help to update MySQL rows with Query

[ICEcoffee]

Hi all

I have imported a table from an ODBC database into MySQL, but all the data in all the columns are surrounded by quotation marks.

Can anyone tell me how to write a query I can execute, to update all fields, to delete said quotation marks.


Thanks for any help, it will save me hours of work.

[Orio]

From what I understood, everything has to question marks around it, one before and one after (IE ?something?). If thats wrong, this script wont work.

[hr]
[code=php:0]function number_array($arr){ //changes keying from words to numbers
$i=0;
foreach ($arr as $value){
$arr2[$i]=$value;
$i++;
};
return $arr2;
};

//connect to db here

$tables=array(...); //enter all tables name this way: array("table1","table2",...)

foreach($tables as $val){ //loop thru all of the tables

$query="SELECT * FROM $val";
$result=mysql_query($query); //Select everthing

while($arr_results=mysql_fetch_array($result)){ //loop for every row in $val
$values=count($arr_results); //find out how many cols are in $val
$arr_cols=array_keys($arr_results); //get all the names of cols
$arr_results_numbered=number_array($arr_results); //turn array to numbered array
$i=0;
while($i<$values){ //loop every row to all of its values
$current_cols=$arr_rows[$i]; //the name of the col we are updating
$original_val=$arr_results_numbered[$i]; //for the update query save original value
$arr_results_numbered[$i]=substr($arr_results_numbered[$i],1); //remove the first char
$arr_results_numbered[$i]=substr($arr_results_numbered[$i],0,-1); //remove the last char

$query2="UPDATE ".$val." SET ".$current_row."=".$arr_results_numbered[$i]." WHERE ".$current_row."=".$original_val;
mysql_query($query); //update
$i++;
};
};
};[/code]
[hr]


Took me some time, but I think it should work :)
Loops thru all of the tables, then in all of thier rows them thru all of thier fields. And updates :)

Orio.

[ICEcoffee]

Hi Orio

Thanks for that, it is only one table in fact AND yes, all the data in the fields are surrounded by quotation marks, ie "this is data".

I will try and adapt what you have kindly written.

Thanks