1

I am trying to save image urls to a MySQL database table

The column field is long enough. The table and database are using UTF-8 CI-general collation (IIRC)

The urls look something like this:

http://example.com/media/images/47142000/jpg/_47142379_005857853-1.jpg

but they get saved like this:

http://example.com/media/images/47142000/jpg...

it would seem that the apprearance of the '_' is causing mySQL problems. Do I need to urlencode the string (or perform some other data munging on the string) before saving it to the database?

tmthydvnprt
  • 10,398
  • 8
  • 52
  • 72
Stick it to THE MAN
  • 5,621
  • 17
  • 77
  • 93

1 Answers1

1

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the following escape sequences.

\0 An ASCII NUL (0x00) character. \' A single quote (“'”) character. \" A double quote (“"”) character. \b A backspace character. \n A newline (linefeed) character. \r A carriage return character. \t A tab character. \Z ASCII 26 (Control-Z). See note following the table. \ A backslash (“\”) character. \% A “%” character. See note following the table. _ A “_” character. See note following the table.

You have to escape the _ character.

See http://dev.mysql.com/doc/refman/5.0/en/string-syntax.html for more information

Todd Moses
  • 10,969
  • 10
  • 47
  • 65
  • Thanks Todd. Useful link. It turned out the data was being stored correctly in the mySQL database, but were being displayed 'incorrectly' when views using phpMySQL (maybe this is a phpMySQL bug). BTW, I am using the Propel ORM, so a lot of the (un)/escaping of string literals is done behind the scenes for me - thats why I was suprised when I saw the data. But I decided to check the data directly through the mysql console app and the data is stored correctly. – Stick it to THE MAN Feb 13 '10 at 14:59
  • If you meant that you are using PHPMyAdmin, then all non numeric fields are truncated to a specific length when browsing a result. The length is controlled by $cfg['LimitChars'] in config.inc.php . If you don't have access to control your installation of PHPMyAdmin, then when browsing any result, you can always click the "OPTIONS" link and select the "show full text" option will show all the text in every field. I hope this helps. – Mike Sherov Feb 13 '10 at 19:30