Mysql

Very useful, so posting here. Read the php date function page for more details. $mysql_datetime = date("Y-m-d H:i:s"); Ref: http://stackoverflow.com/questions/136782/format-mysql-datetime-with-php

When we have stats, often times the timestamp is involved. So, say we have a table like the following CREATE TABLE IF NOT EXISTS `my_stats_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `created_datetime` datetime NOT NULL, `value1` int(11) NOT NULL, PRIMARY KEY (`id`) ) Now, all I want is to grab data for each day. Of course, there could be many rows in a day, and there are many ways to handle that. In my case, I need to grab the last row since it’s most accurate. How to do that? ...

I often need to do this so I figure I will write it down to remind myself later. First, download the mysql installation dmg file (version 5.5.20 as of now). osx already comes with mysql but I like the extra System Pref icon after the manual install. You can download at http://dev.mysql.com/downloads/mysql/. Pick Mac OS X 10.6 (x86, 64-bit), DMG Archive. Once you have the dmg file, open and install all three files ( mysql-5.5.20-osx10.6-x86_64.pkg, MySQL.prefPane, MySQLStartupItem.pkg) in there. ...

One incentive of converting a text column to a varchar column is that, you can index that column for quicker query. Before converting, you want to make sure you won’t be truncating anything. Run the following to make sure SELECT MAX( LENGTH( target_column ) ) FROM target_table As long as the returned length is less than your varchar length, you are good to go.

GROUP_CONCAT is very useful when you are doing a GROUP BY, and want to capture the values from other columns. For example: SELECT country, GROUP_CONCAT(city SEPARATOR ',') AS city_arr_str FROM myTable GROUP BY country ORDER BY country Then in say PHP, you can read the city names for each returned row: foreach ($rows as $r){ $country = $r["country"]; $city_arr_str = $r["city_arr_str"]; // parse the city names $city_arr = explode(",", $city_arr_str); // do your stuff... } Everything is fine and all, until later on your database gets much bigger. ...

This idea sounds easy and stuff. I agree. I assume you already got it working fine. But perhaps one day you may notice something is not working well, especially when you are dealing with bigger files. Below are a few things you want to look out for. Change your /etc/php.ini upload_max_filesize = 2M post_max_size = 8M memory_limit = 128M The default upload file size is 2M, which could be too small. If your upload file is larger than this, you will receive an empty upload file at your server side. So, increase this. While if you are using ajax to post-send, increase the post_max_size limit. Another thing to watch out for is memory_limit. If you somehow make copies of big blobs in your code, increasing PHP’s memory limit is also a good idea. ...

I just installed Lion and for some reasons my internal website stopped working due to the mysql lock file not being found. I found out that the new osx installation changed the mysql sock file default path from /var/mysql/mysql.sock to /tmp/mysql.sock. So, to re-enable everything, do: shut down your Web Sharing cp /etc/php.ini.default /etc/php.ini (for some reasons my old php.ini got renamed to php.ini.default php.ini-5.2-previous…) modify /etc/php.ini, change ALL /var/mysql/mysql.sock to /tmp/mysql.sock ...

I installed MySQL-Python (version 1.2.3 at the time being) from http://sourceforge.net/projects/mysql-python/ on my osx 10.6, python version 2.6.1. Just do the usual “python setup.py build” and “python setup.py install”. However though, in the python cli, when I do “import MySQLdb”, I got some error. >>> import MySQLdb Traceback (most recent call last): File "", line 1, in File "build/bdist.macosx-10.6-universal/egg/MySQLdb/__init__.py", line 19, in File "build/bdist.macosx-10.6-universal/egg/_mysql.py", line 7, in File "build/bdist.macosx-10.6-universal/egg/_mysql.py", line 6, in __bootstrap__ ImportError: dlopen(/Users/birdchan/.python-eggs/MySQL_python-1.2.3-py2.6-macosx-10.6-universal.egg-tmp/_mysql.so, 2): Library not loaded: libmysqlclient.18.dylib Referenced from: /Users/birdchan/.python-eggs/MySQL_python-1.2.3-py2.6-macosx-10.6-universal.egg-tmp/_mysql.so Reason: image not found >>> Obviously, the error is “Library not loaded: libmysqlclient.18.dylib”. A quick fix is to simply create a symlink. ...

I often need to choose between MyISAM and InnoDB. Here is a little list for me for future reference. MyISAM supports fulltext searching, but does not support transactional processing. Fulltext searching: Match(), Against(), WITH QUERY EXPANSION, IN BOOLEAN MODE InnoDB is a transaction-safe engine It does not support full-text searching. In addition, MEMORY is functionally equivalent to MyISAM. The data is stored in memory (instead of on disk), thus is extremely fast (and ideally suited for temporary tables). ...