Mysql Timestamps

All that you want to know abouy MySQL timestamps is well documented here.

http://www.gizmola.com/blog/archives/93-Too-much-information-about-the-MySQL-TIMESTAMP.html

MySql GROUP_CONCAT() tip

Recently I found myself into a silly problem because of misunderstaning / confusion relating to data type of variable set in SELECT commmand using GROUP_CONCAT()

I wanted to get all integer ids as comma separated string to be used in other update query

my query:

SELECT GROUP_CONCAT(spm_ids) INTO @csvIds FROM someTable WHERE is_active = 1;

Whenever my select executed correctly it gave e.g. '2,5,6' as a string and when it failed it gave me '0' as a string.

before updating I checked

IF @csvIds <> 0 THEN
// perform some update on some other table
END IF;


I changed to below and my Stored procedure worked perfectly fine.

IF @csvIds <> '0' THEN
// perform some update on some other table
END IF;

Some of mysql String comparison
> select if('2' <> 0, 1, 0)
> 1

> select if('0,2' <> 0, 1, 0) //my problematic condition
> 0

> select if('0' <> 0, 1, 0)
> 0

> select if('2,5,6' <> 0, 1, 0)
> 1





MySql tip 2:
If you are writing too many Stored Procedures(SP) and also including below code in your SPs

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SELECT 2 AS `error_code`, 'Fail to update ' AS `error_message` ;
END;

Then I would suggest that you first create your SP entirely run it many times under different conditions, if all working fine than include above code and test it again for failure.
This is because you will save some of your precious time by knowing exactly where the query failed.

The Guru's

This image is posted here coz all these Guru's (Father's) have influenced me in some point of time.

Store procedure usage in PHP

Stored routines (procedures and functions) are supported in MySQL 5.1. A stored procedure is a set of SQL statements that can be stored in the server. Once this has been done, clients don't need to keep reissuing the individual statements but can refer to the stored procedure instead.

A stored routine is either a procedure . Stored routines are created with CREATE PROCEDURE statements. A procedure is invoked using a CALL statement, and can only pass back values using output variables. Stored routines may call other stored routines.
A stored procedure or function is associated with a particular database.

Procedures can be created using CREATE PROCEDURE command of mysql.
To know the exact syntax visit Online MySql documentation.

Once the procedure is created we have to use it in php code for this PHP's mysqli extension allows you to access the functionality provided by MySQL 4.1 and above

In order to have these functions available, you must compile PHP with support for the mysqli extension(MySQL Improved Extension).

To install the mysqli extension for PHP, use the --with-mysqli=mysql_config_path/mysql_config configuration option where mysql_config_path represents the location of the mysql_config program that comes with MySQL versions greater than 4.1.

mysqli functions are similar to mysql functions, there is substitute for each mysql functions in mysqli.
For e.g.

mysqli_query() has to be used instead of mysql_query()

using mysqli_query() the stored procedure query can be called and its result can be fetched.

Note: if you have to use any of mysqli functions then you will have to use mysqli functions throughout the current mysql connection
i.e. from mysqli_connect() to mysqli_close() all intermediate functions used must be of mysqli and not mysql.

references:

http://in.php.net/
http://dev.mysql.com/doc/refman/5.1/en/

Control Flow Functions of MySQL

While doing web programming many times you fetch data from database and depending on the value fetched, the user is shown something or other.
In case of optional fields in table it is quite likely that the value may be NULL or something default that you have set.
Take for example and option field called product awards which lists all awards bagged by that product.

When you write query
SELECT pro_id, pro_awards FROM products

This will give info regarding all the awards bagged by products.
However since pro_awards is optional many records will contain value NULL.

To tackle this NULL value you check in php whether the fetched value is NULL, if yes you display "No awards bagged by this product".

You can also do this checking at database level to reduce some load on webserver.
And as Databases are configured at their optimum level the processing time is much faster than your php / any script.

This can be done this way:
SELECT pro_id, IF(pro_awards IS NULL,'No awards bagged by this product', pro_awards) as pro_awards
FROM products
Above was use of if-else construct in MySQL

You can also make use of Switch-case construct effectively.
SELECT pro_id, CASE WHEN pro_awards IS NULL THEN No awards bagged by this product' ELSE pro_awards END FROM products

Above examples are very simple to understand, I am sure you can come out with difficult situations and use these Control flow functions of MySQL effectively to reduce script execution time.

references: http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html

Boolean Full-Text Searches

Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a literal string, not a variable or a column name.

Interesting Full Text searches for web developers can be Boolean Full-Text Searches

The following examples demonstrate some search strings that use boolean full-text operators:

'apple banana'
Find rows that contain at least one of the two words.

'+apple +juice'
Find rows that contain both words.

'+apple macintosh'
Find rows that contain the word “apple”, but rank rows higher if they also contain “macintosh”.

'+apple -macintosh'
Find rows that contain the word “apple” but not “macintosh”.

'+apple ~macintosh'
Find rows that contain the word “apple”, but if the row also contains the word “macintosh”, rate it lower than if row does not. This is “softer” than a search for '+apple -macintosh', for which the presence of “macintosh” causes the row not to be returned at all.

The + and - operators indicate that a word is required to be present or absent, respectively, for a match to occur. Thus, this query retrieves all the rows that contain the word “MySQL” but that do not contain the word “YourSQL”.

reference : http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html