Chevrons Code

PHP, ASP.net, HTML, JavaScript, Yii and MySQL

Normally if want display week of certain of the month, we can using WEEK() function. But WEEK() function only return you week of the year.

But now, we want display WEEK of the month no week of the year. that means first, second, third, fourth and fifth week of the month.

Below example is the SQL to display the week number of the month:

SELECT (WEEK(created_date,5) - WEEK(DATE_SUB(created_date, INTERVAL DAYOFMONTH(created_date)-1 DAY),5)+1) as week

The created_date column must be DATE or DATETIME type.

One of my application need update the days when it have set the days inside the records. It was because sometimes user need extend the expired date to following number of days.

With the SQL statement it would be simple to complete the task.

UPDATE table 
SET expired_date = DATE_ADD(expired,INTERVAL 7 DAY) 
WHERE id = 1;

The above example only work on DATE or DATETIME field types. Remember it was day not days

It have few ways to get multiple counts with one SQL Query. But, it also will return more then one row records to user, and also the total count wouldn’t appear if it no inside the records.

In the same time, user need did few times loop and if else conditions to get the results. It will slow down the application performance and give developer more job to completed the job.

In here, I have a better way to process multiple counts with one SQL Query and it just return one row record only.

Let’s see below example how the SQL look like:

How to format datetime value or column into a specific date format. Is the most frequently asked question by the newbie. Below table is summary of the different date formats that come from SQL Server with the CONVERT function.

Please note that the output of these date formats are VARCHAR data type, not DATETIME data type. With this in mind, any date comparisons performed after the DATETIME value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Tables or result sets sometimes contain duplicate records. Sometimes, it is allowed but sometimes it is required to stop duplicate records. Sometimes, it is required to identify duplicate records and remove them from the table. This chapter will describe how to prevent duplicate records occurring in a table and how to remove already existing duplicate records.

You are allow use PRIMARY KEY or UNIQUE Index on a table with appropriate fields to stop duplicate records. Let’s create an example table with id and name as Primary Key:

CREATE TABLE `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`, `name`),
);

There may be times when a specific order is required in a SQL query which cannot be done using either ASC or DESC or using a special sort field. MySQL has a ORDER BY FIELD function which can be used to do this.

Example Data
The example data in this post uses as below. This is a somewhat simple table but it can be used to illustrate the point in this post quite well.

CREATE TABLE fruits (`id` int, `name` varchar(50));
	
INSERT INTO fruits (`id`, `name`)
VALUES (1, 'Apple'),
       (2, 'Durian'),
       (3, 'Banana'),
       (4, 'Lemon'),
       (5, 'Pear'),
       (6, 'Star fruit'),
       (7, 'Strawberry'),
       (8, 'Orange');

Because sometimes need to know last day of the previous month, and I was using procedure/routine to completed the query. That’s the reason I can’t use others programming language to do it. All the thing must finish in MySQL server only.

And very lucky, MySQL have “last_day()” function to get last day of the month.

Below example are showing how to use it.

Recently I reinstall MySQL Database because it suddenly cannot start and it crash. After I successful reinstall, I found an extra database in MySQL Server.

When I tried to drop the database, I received the following error:

“Can’t drop database ‘dbname’; database doesn’t exist”

And when I tried to create a new database with same name, it told me:

“Can’t create database ‘dbname’; database exists”

That just didn’t make any sense! And the PHPMyAdmin application still listed the database in the Catalog list.

Sometimes we keep all the records/logs in one table. But if we want retrieve each group last records in one SQL it look like impossible.

Below Example showed how to:

SELECT rs1.*
FROM TABLE rs1 LEFT JOIN TABLE rs2
  ON (rs1.name = rs2.name AND rs1.id < rs2.id)
WHERE rs2.id IS NULL;

Above method can get the last record results group by name.

If you want retrieve first records in each group, just change to

Duplicate row found and then update the row by one sql statement. This can be done by SQL, and not need any others server-side scripting.

Now let us see how it work. First, create table and insert some example data.

CREATE TABLE IF NOT EXISTS `foo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
);

INSERT INTO `foo` (`id`, `name`) VALUES
(1, 'foo'),
(2, 'foo2');