SQL statement

[APZteam]

Hello!

 

I run and host www.airplanephotzone.com and I recently switched to a new server, and when I did, all of the special characters were lost..umlats, accents..etc. Basically what I need to do is fix my tags.  I have datatables associated with each photo:  I have MySQL - 5.0.45

 

http://www.airplanephotozone.com/search.php?Airport=EDDL  (you can see the umlat over Düsseldorf)

 

What I have already done is gone into the SQL tab for my airplane_apz database and updated all the special characters using the following:

 

UPDATE apz_photos SET airport="Düsseldorf - Rhein-Ruhr (DUS/EDDL)" WHERE airport="D?sseldorf - Rhein-Ruhr (DUS/EDDL)"

 

My tags are setup as the following based on each field in the datatable next to the picture:

 

Lufthansa,Airbus,A320,Düsseldorf - Rhein-Ruhr (DUS/EDDL),Germany,Ben Graden,  ----basically each separated by a comma.

 

My question is does anyone know of an SQL statement like the one above, that could insert the correct: Düsseldorf - Rhein-Ruhr (DUS/EDDL) into the tags without Updating and removing all the rest of them..since they are separated by a comma? I am guessing if I were to do

 

UPDATE apz_photos SET tags="Düsseldorf - Rhein-Ruhr (DUS/EDDL)" WHERE airport="Düsseldorf - Rhein-Ruhr (DUS/EDDL)"  it would overwrite all the other tags in there already.

 

Any ideas?

 

Ben

[cooldude832]

check your collation so it allows the special characters you desire.

[APZteam]

Yea..I've played with the coalition, that was the first thing I did.  The problem is that the characters were just LOST entirely..so, changing the coalition won't bring them back.  I just need to figure out a way to ADD them to the tags field.

 

Ben

[fenway]

You're trying to store multiple values in a single field... it's not fun.

[APZteam]

Yea, no lie.  I might have to change that.  I came up with a function to update it, but can't figure out why it's not working.

 

$dbusername="airplane_apz";
$dbpasswd="apzteam";
$database_name="airplane_apz";

require_once('inc/fe_get.php');
require_once('inc/db.php');

$link = mysql_connect("$dbhost","$dbusername","$dbpasswd"); 
mysql_select_db("$database_name", $link); 

fixthiscrap();

function fixthiscrap(){
$sql="SELECT 
	photoid
	airline,
	airline2,
	nation,
	nations,
	state,
	states,
	photog,
	tags
	FROM apz_photos WHERE tags LIKE '%?%'";
if($result = mysql_query($sql) or die(mysql_error())){
	while($row = mysql_fetch_array($resultado)){
		$newtag = $row['tags'].",".$row['airline'].",".$row['airline2'].",".$row['nation'];
		$newtag.= ",".$row['nations'].",".$row['state'].",".$row['states'].",".$row['photog'];
		$update_sql = "UPDATE tags FROM apz_photos SET tags = '".$newtag."' WHERE photoid='".$row['photoid']."'";
		$result_update = mysql_query($update_sql) or die(mysql_error());
		echo $update_sql."updated";
	}
}else{
	echo "No UPDATE<br>";
}
}

?>

 

Ideas?

 

Ben

[fenway]

Why not use REPLACE?