Page 1 of 1

Database Format

Posted: Wed Mar 07, 2007 7:19 pm
by Trenchant
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


I've just recently finished a huge client website almost totally backed by a mysql database.  All webpages and content are loaded from the database so I've used html entities and mysql real escape string to protect the scripts from failing.  My client does have a custom made panel to modify her websites from.

The website is done and I was just moving it over to her perminate host.  Its all setup and working fine except the formatting is off.  Almost all ' s are being converted and formatted wrong.  When I imported the SQL into the final database it would be stored on I did have to remove some things from the sql.

To make PHPMyAdmin on the clients new server accept the SQL statement it was changed from this:
[syntax="sql"]CREATE TABLE `web_pages` (
  `id` int(255) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `top_banner_1` varchar(255) NOT NULL default '',
  `top_banner_2` varchar(255) NOT NULL default '',
  `top_banner_3` varchar(255) NOT NULL default '',
  `menu_bar` longtext NOT NULL,
  `menu` varchar(255) NOT NULL default 'yes',
  `page_pre_content` longtext NOT NULL,
  `page_content` longtext NOT NULL,
  `page_footer` longtext NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=50 ;
to this:

Code: Select all

CREATE TABLE `web_pages` (
  `id` int(255) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL default '',
  `top_banner_1` varchar(255) NOT NULL default '',
  `top_banner_2` varchar(255) NOT NULL default '',
  `top_banner_3` varchar(255) NOT NULL default '',
  `menu_bar` longtext NOT NULL,
  `menu` varchar(255) NOT NULL default 'yes',
  `page_pre_content` longtext NOT NULL,
  `page_content` longtext NOT NULL,
  `page_footer` longtext NOT NULL,
  UNIQUE KEY `id` (`id`)
) AUTO_INCREMENT=50 ;
I checked the "engine" type and it does still match. However, I can't seem to find in PHPMyAdmin where I can find the charset value. I'm guessing thats where the problem is from.

How can I fix this? Is it MySQL or is it because the string was escaped on my server and it has a different encoding then the public server? Any idea's?


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]

Posted: Wed Mar 07, 2007 9:58 pm
by anjanesh
Its not the charset thats your problem because charset is supported only in MySQL 5.x.

So dropping ENGINE=MyISAM DEFAULT CHARSET=latin1 is correct since your host database version seems to be < 5.0.
But when exporting from phpMyAdmin, it should've escaped the strings properly.

Re: Database Format

Posted: Wed Mar 07, 2007 11:10 pm
by volka
Web Dummy wrote:Almost all ' s are being converted and formatted wrong.
meaning?

Posted: Thu Mar 08, 2007 3:24 am
by onion2k
anjanesh wrote:Its not the charset thats your problem because charset is supported only in [urlhttp://dev.mysql.com/doc/refman/5.0/en/charset.html]MySQL 5.x[/url].
This is completely and utterly wrong. CHARSET on a CREATE TABLE statement has been available since MySQL 3.23.

Posted: Thu Mar 08, 2007 3:46 am
by anjanesh
Oh...Sorry.

Its just that when importing in phpMyAdmin, using CHARSET=latin1 in CREATE TABLE doesnt seem to work.

Posted: Thu Mar 08, 2007 3:54 am
by onion2k
anjanesh wrote:Its just that when importing in phpMyAdmin, using CHARSET=latin1 in CREATE TABLE doesnt seem to work.
Works fine for me. Unless you can cite a mysql error message noone is going to be able to help you with that.

Posted: Thu Mar 08, 2007 8:34 am
by feyd
This is a PHP question?

Posted: Thu Mar 08, 2007 2:44 pm
by RobertGonzalez
This is not a PHP question, it is a MySQL question. And what you are looking to do, in phpMyAdmin, will not be available to you unless your database server is MySQL 5. Yes, charsets were supported prior to that, but no installation of PMA I have used allows insight into the charset unless the server is MySQL 5 or better.

If it is, then you can easily change the charset for database/table in the Operations tab of PMA. For an individual field, you do it in the Structure area of PMA where you edit the field names, types, etc.

PS Moved to database.

Posted: Thu Mar 08, 2007 3:38 pm
by Trenchant
I wasn't sure if this was a database or PHP question. The reason being was that I believed it could have been from using commands like HTML entities and mysql real escape string on the information.

The part I can't figure out is why this is only happening to some 's. For example. Look at this page:
http://www.jodispilates.com/new_design/ ... cation.php

You will notice the weird symbol after "Jodi" and before "s Pilates" but then at the bottom of the page in the page footer there isn't a problem.

Posted: Thu Mar 08, 2007 3:58 pm
by RobertGonzalez
It think your data is UTF8. When switching the character encoding on my browser, it renders fine.

Posted: Fri Mar 09, 2007 12:20 pm
by Trenchant
I just re-entered the 's and it seems to be workign fine now. Not sure why.