Wwwebber's Blog

Clean up unwanted characters in a Mysql Database

Posted in Uncategorized by wwwebber on July 25, 2009

I recently had to cleanse a large MySql Database–over 130,000 records and many of the records had one or both of the following problems

1) quotes were not escaped so you’d see a varchar with unescaped single quotes scattered throughout the string–that’s a problem since a single quote indicates the end of the varchar to MySql!

2) The PHP side of the app did not work well ( in fact at all) with periods or commas…and of course we had a lot of them throughout the DB

So here’s the SQL that might help you

the SQL to remove all periods from someTable’s column called someColumn

UPDATE  `someTable` SET  `someColumn`= replace( `someColumn` , ‘.’, ” )

A little trickier looking was escaping all of the single quotes thorughout the db. Here’s how you add a single escape slash before the single quotes:

UPDATE  `someTable` SET  `SsomeColumn`= replace( `someColumn` , ‘\”, ‘\\\” )

I know that last bolded part probably looks wierd–just remember that you have to escape the escape character as well as the quote!

Comments Off on Clean up unwanted characters in a Mysql Database

Migrating an access file to mysql

Posted in Uncategorized by wwwebber on July 17, 2009

UPDATE TO THIS POST: avoid using data-lists.com– I bought two databases from them. One was fine  but the other was terrible, and I’m fighting them to live up to their website’s claim that you can get a refund. Again avoid these guys. If you need a good public or private school database, here’s a reliable one digitalpoint . Moreover, this guys list is available in various formats including a MySql dump.

Ok, this post will help you if

1) your data is an Access file (who the hell uses Access databases anyway?)

2) You need that data in a MySql databases

3) you have mysql  and phpadmin up and running on your computer (you probably also have apache up and running)

The background : After doing a lot of comparison shopping for a few different databases ( I needed one for all public and private schools in the US), the best deal–by a long shot-was this site: http://www.data-lists.com/ — their data was way more complete–1000’s more unique records than  the others I saw and the price was even a bit better than most of the others that I saw out there. There was only 1 major draw back (at least at first it seemed like a draw back) the data only comes in 1 format: a Microsoft Access file–crap. I thought I was in for a major pain in the butt–and before I started researching how to try to parse an Access file into something I could import into mysql I discovered an awesome mysql to Access conversion  tool that is, amazingly enough, free and does a lot of the work for you–it’ll  import your Access db into your MySql db… All you need is mysql up and running on your computer and this baby’ll convert the entire thing for you!.

To be able to do this my way (the easy way), just follow these steps

1) make sure that you log into mysql and start it up, make sure that you have phpmyadmin booted up also

2) download and install bullzip’s convertor tool , then run the tool. It’s pretty straightforward.

3) you will be asked for a bit of mysql info–port number etc..

4) At the prompt you want to do a ‘direct transfer’ which will just take the data and put into a database on your mysql server

5) then you’re good. It’s in your server. Below is a screen shot of the converter tool in action!

myqsql-convertor

Comments Off on Migrating an access file to mysql

Actionscript 3: loading external bitmapdata and sounds

Posted in Uncategorized by wwwebber on July 12, 2009

loading_flashI recently made a math game involving equivalent fractions –it was a lot of fun, my favorite kind of programming. It makes use of about 10 sound files including 4 sound tracks and about 40 rasterized images…needless to say the final file size was too big and the game loaded too slow..initially, the game was  almost 800 kb!  well, i  have almost got it below 500 kilobytes by loading all of the images externally as well as most of the sounds. It’s amazing what a difference the loadiong processcan  make for the end user. If you do it right, the user can just the directions about how to play the game while most or all of the graphics and sounds are being loaded in the background.

Still, my biggest problem about preloading is the fact that flash does not support loading wav files at run time! This is amazingly annoying especially when you consider the fact mp3 files are a  pain in the but to deal with–if  you have to loop them–since mp3’s have header and leader space . If you need to loop mp3’s seemleassly just read this article compliments of 8bitrocket.com

Comments Off on Actionscript 3: loading external bitmapdata and sounds