Category Archives: MySQL

Bit Mask Tricks (PHP & MySQL)

Very useful for testing switches:

MySQL test:

SELECT * FROM menus WHERE (Roles & BIT) = BIT

MySQL set:

UPDATE table SET field = (field | 8 )

MySQL reset:

UPDATE table SET field = ((field | 8 )  ^8)

PHP:

if (($VALIDATION & 8) == 8

PHP (SET):

function setflag (&$var, $flag, $set = true)
{
  
$var = $set ? ($var | $flag) : ($var & ~$flag);
}

Get correct ID from MySQL INSERT with ON DUPLICATE

A way to make things work is to use a dummy column, so if you have a table with auto_increment column ID and unique key a,b and a smallint dummy column for instance, the query might look like this:

INSERT INTO test (a,b) VALUES ('1','2') ON DUPLICATE KEY UPDATE ID=LAST_INSERT_ID(ID),Dummy = NOT dummy;

Now, SELECT LAST_INSERT_ID(); will return the correct ID.

MySQL Charset Setup

I found very confusing settings for right charset with MySQL server.

For PHP, first Query should be:

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';",$connect);