My Monkey Do

A Log of Coding Solutions

MySQL – Get Next Auto Increment Value

by

Problem:

When adding an item to my mysql table, I needed to get the ID for the next inserted row.  The ID is just an auto increment column in the table.  At first I had:

select max(table_id) + 1 as next_table_id from tablename

I quickly realized how wrong that was.  When inserting ids 1, 2, 3, 4 and then deleting ID 4, my “next ID” query would return 4 instead of 5.  The MAX ID for a table varies depending on what is currently in the table and is not a reliable source for the next inserted ID.

 

Solution:

The query to use is:

select auto_increment
from information_schema.TABLES
where TABLE_NAME='tablename'
and TABLE_SCHEMA='basename'

 

An alternative solution is to use

SHOW TABLE STATUS WHERE name='tablename'

I have seen some comments that “SHOW TABLE STATUS” is not transaction safe.  Although I cannot find that in the MySQL documents.  Also if the table is partitioned, the first query should be used as documented here.

 

 

 

No Multiple Upload for IE

by

Problem:

I wanted a way for users to upload multiple files to a server on a webpage.  I found a great jQuery plugin that does drag & drop and multiple file upload.   Under browser support you can see that IE is supported, however there are special notes.  MSIE does not support drag & drop and multiple file support.  So basically, the script works with IE where users can upload 1 file at a time.

 

Solution:

From my research, it turns out that it is impossible to do multiple upload (same goes for drag and drop) to IE.  The browser just simply does not support it.  There are many scripts out there that say “multiple upload” and say it works with IE, but it’s not truly multiple file upload.  The script actually just allows you to upload multiple files with one request, not select multiple files at one time.  So in other words, the file browser opens and you can only select one file.  Then you can open the file browser again and select another file.  If you had 10 files, you would have to repeat this process 10 times instead of just highlighting multiple files if it had true multiple file support.

Anyway, that is the “workaround” which is not acceptable for me.  So IE users just won’t have a multiple upload feature in my project.

VARCHAR vs TEXT Performance

by

Ever wonder if VARCHAR(255) or TEXT is a better data type?  This thread suggests VARCHAR(255) is better because it is allowed to use MEMORY (or RAM).  The TEXT data type is not allowed in there.

http://forums.mysql.com/read.php?24,105964,105964#msg-105964

 

Although it was also found that a larger VARCHAR like VARCHAR(3000) has worse performance than TEXT (in MyISAM).

http://nicj.net/2011/01/20/mysql-text-vs-varchar-performance

 

The general rule appears to be to use VARCHAR if it is less than 255 characters and to use TEXT if it’s 255 or more.

Diff Merge

by

DiffMerge is an application to visually compare and merge files within Windows, Mac OS X, and Linux.  Best of all it is free! as in it’s free for you to use, you don’t need to pay anything, ever.  This is a very useful tool when you are trying to find a differences between two files.  You can use it as an external tool for CVS or SVN when diff-ing multiple versions.  Other features include:

  • Graphically shows differences between two files
  • Side by side comparison between two folders
  • Right click and compare two files in Windows Explorer
  • Configurable
  • Compatible with 42 different encodings
  • Cross-platform (Windows, Mac, Linux)

The best feature that makes DiffMerge stand out against the rest of the diff tools is their Folder Diff.  I work on a project I work on multiple files at a time and I loose track of what file I touched.  If I ever need to see the changes I’ve made in an entire folder, DiffMerge can easily show me that.

DiffMerge can be downloaded here: http://www.sourcegear.com/diffmerge/index.html