RTRIM function not working

[eevan79]

Ok. I solved .php part on this topic:

http://www.phpfreaks.com/forums/index.php/topic,287631.0.html

 

but now cant make this sql code to work:

$sql = "UPDATE `gameplayers` SET `name` = RTRIM(`name`)";

 

Problem is to remove empty spaces at end of name and this script above is not working. DATATYPE is varchar(15)

[sader]

try with phpmyadmin

[eevan79]

I have tried.

 

0 row(s) affected. ( Query took 0.0030 sec )

 

I already posed this answer about this on php help forum (dont want double posts)

[sader]

can it be that u dont even have names with empty spaces at the end?

[eevan79]

No. Here is image:

 

Maybe its cause of datatype is VARCHAR? I have read something about that...

[sader]

hmm I am a bit afraid that this will screw up your data maybe you cold cretae some temporary field tmp_name just in case

since I coulnd find alternative with mysql functions I can suggest do it with php

$qr = mysql_query("SELECT * FROM `gameplayers`");
while($row = mysql_fetch_assoc($qr))
{
  $id = $row['id'];
//I asume that valid names are only from A-Z0-9 _ . - chars
  $new_name = preg_replace('/^([a-z0-9_-\.]+)/im','\\1', $row['name']);

  //if didint create new tmp field use this
  //mysql_query("UPDATE `gameplayers` SET `name`='$new_name' WHERE `id`=$id LIMIT 1");
  //or if u cretaed just in case
  mysql_query("UPDATE `gameplayers` SET `tmp_name`='$new_name' WHERE `id`=$id LIMIT 1");
}

[eevan79]

Ok, I will test this.

 

//I asume that valid names are only from A-Z0-9 _ . - chars

 

Is this include characters as "@", "$" (like on screenshot above) ???

[sader]

this includes  '/^([a-z0-9-_\\.@\\$]+)/im'

[gizmola]

You have a character encoding problem.  Those are not space characters as can be seen from your own screenshot.  Look at the 2nd place you display the name, and notice the FF/FD boxes.  This leads to all sorts of questions like: what is the charset of the page that is rendered (UTF-8?) and does this match the character set used in the mysql table?

[eevan79]

You have a character encoding problem.  Those are not space characters as can be seen from your own screenshot.  Look at the 2nd place you display the name, and notice the FF/FD boxes.  This leads to all sorts of questions like: what is the charset of the page that is rendered (UTF-8?) and does this match the character set used in the mysql table?

Exactly. That was my problem. When I changed charset to ISO-8859-1, I get this character: ®

 

And I used this code:

$sql = "UPDATE `gameplayers` SET `name` = REPLACE(`name`, '®', '')";
$result = mysql_query($sql); 

And Its working (removed FF/FD), but now I have really empty space.

This makes 2 different users.

First (HTML link created for that user) - with space:

?p=user&u=l@dy%20ev@%A0%A0%A0&s=datetime

Second (w/o empty space):

?p=user&u=L@dy Ev@&s=datetime

[gizmola]

Well, this is for you to decide. However, it seems that your issue is not with the data but rather with your mismatched database character set to the one you are claiming to use when you render your pages.  If the mysql charset is 8859-1 then you should emit that as the charset for your page, rather than claiming to use utf-8 which in fact you aren't using.

 

Once you do that, the characters that are in there -- which are valid 8859-1 characters, will display fine, and you'll not have an issue.

 

Otherwise, what you are doing is basically making sure that those users you are changing are not going to be logging in again, because you destroyed the username they had been using.

 

 

[eevan79]

This how it works. I used some program (written in c++) for update mysql and it seems that program makes this "bug", cause users didnt write this character.

 

I set charset for pages to 8859-1, but in mysql collation is: utf_general_ci

I'm not sure how to remove this "empty space" or strange characters (or I need to change some code in program, but thats different story)?

 

Thanks for reply.

[gizmola]

Collation and character set are 2 different things.  Can you verify for the user table what the character set is.  Please do this from mysql or phpmyadmin sql window:

 

SHOW CREATE TABLE tablename;

 

tablename should be your user table name, whatever that is.  Then paste the contents in a reply.

[eevan79]

Here it is:

CREATE TABLE `gameplayers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`botid` int(11) NOT NULL,
`gameid` int(11) NOT NULL,
`name` varchar(15) NOT NULL,
`ip` varchar(15) NOT NULL,
`spoofed` int(11) NOT NULL,
`reserved` int(11) NOT NULL,
`loadingtime` int(11) NOT NULL,
`left` int(11) NOT NULL,
`leftreason` varchar(100) NOT NULL,
`team` int(11) NOT NULL,
`colour` int(11) NOT NULL,
`spoofedrealm` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `gameid` (`gameid`),
KEY `gameid_2` (`gameid`),
KEY `colour` (`colour`),
KEY `name` (`name`),
KEY `gameid_3` (`gameid`),
KEY `colour_2` (`colour`),
KEY `name_2` (`name`),
KEY `gameid_4` (`gameid`),
KEY `colour_3` (`colour`),
KEY `name_3` (`name`),
KEY `gameid_5` (`gameid`),
KEY `colour_4` (`colour`),
KEY `name_4` (`name`)
) ENGINE=MyISAM AUTO_INCREMENT=12363 DEFAULT CHARSET=latin1

[gizmola]

Ok, so as I stated, your table is "DEFAULT CHARSET=latin1".  For all intents and purposes, latin1 = 8859-1.

 

I question that your page is actually setting the charset to be 8859-1.  You should probably double check your assumptions.

 

This page might be helpful, in case anything is unclear.  http://en.wikipedia.org/wiki/ISO/IEC_8859-1#ISO-8859-1

 

 

[eevan79]

Ok, I have read this page on wiki and still can figure how to fix this.

 

Here is image with listing (I have selected second row where is "empty" char in nickname)

First and third nickname in gamelist is fine.

 

But it seems that I cant fix it for latin1 charcode (I must look deeper into program code that update data).

[gizmola]

Using some sort of header checking tool like Firebug, open up your page and look at the headers.

 

For latin-1 it should be:

 

Content-Type: text/html; charset=ISO-8859-1.

 

If you want to PM me the url (assuming you don't want to post it) of your site, I can take a look as well.

 

Summary:  check the code that emits the http headers for your site.  If this is not being set in the header, then I'd expect that the pages would have a header section like:

 

 

Something tells me that what you actually have is charset=utf-8