Jump to content


Photo

Help to update MySQL rows with Query


  • Please log in to reply
2 replies to this topic

#1 ICEcoffee

ICEcoffee
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 30 June 2006 - 09:00 AM

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.

#2 Orio

Orio
  • Staff Alumni
  • Advanced Member
  • 2,491 posts

Posted 30 June 2006 - 09:38 AM

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.

 
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++;
};
};
};
 


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.
Think you're smarty?

(Gone until 20 to November)

#3 ICEcoffee

ICEcoffee
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 30 June 2006 - 10:17 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users