April 16 2018

MySQL week() function not correct display

Tagged Under :

MySQL
I am try gather some statistics from a database and it need group by week numbers. and the results return unexpected.
SELECT create_date, WEEK(create_date) as week FROM `ttable` 
+---------------------+-------------+
| craete_date         | week        |
+---------------------+-------------+
| 2018-01-02 00:00:00 |           0 | 
| 2018-04-10 00:00:00 |          14 | 
| 2018-03-13 00:00:00 |          10 | 
| 2018-03-30 00:00:00 |          12 | 
+---------------------+-------------+
From the above you will found that the week number was wrong. and MySQL counts the first days of the year that do not belong week 1 as week 0.

This is because MySQL default week() function caused the issue. because in MySQL the first day of week is Sunday and it range was 0 to 53 weeks. And you need change the MySQL mode with the below table.

July 27 2016

Yii2 ActiveRecord model use MySQL function

Tagged Under : ,

Yii
To use the MySQL function on Yii2 ActiveRecord Model you to add “\yii\db\Expression” on your script instead of you direct use the “DATE_ADD” function.

Example:
$model->date = new \yii\db\Expression('DATE_ADD(CURRENT_DATE, INTERVAL - 7 DAY)')

May 16 2016

MySQL week of the month

Tagged Under : ,

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.

July 29 2015

Mysql query to display name range from a to g

Tagged Under :

MySQL
I need to listing out name of the person start from lowercase a to g and uppercase A to G in MySQL. You can use MySQL regexp to get the results you want.

Example SQL:
SELECT * FROM USERS WHERE name REGEXP '^[A-G|a-g]'

The above query will listing out all the results where name start from a to g

November 17 2014

Order by day of the week from Monday to Sunday

Tagged Under :

MySQL
A lot of people may be will ask how to sort day of the week with SQL query. Because Monday to Sunday it was string and MySQL cannot differentiate it was day or string inside the table.

But you are allow define inside the query how order the query with ORDER BY FIELD().

ORDER BY FIELD([fieldname], 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday');

ORDER BY FIELD actually is custom ORDER BY in MySQL. It easy to let you sort the results needed by you.

November 14 2014

Getting the number of rows with a GROUP BY query in MySQL

Tagged Under :

MySQL
If you use the COUNT() function with GROUP BY. It will count the number of rows within each group, not the total number of rows. If you want count the number of rows with the groups, you are not allow use GROUP BY inside the query.

Instead of use GROUP BY you may use DISTINCT inside the query. The DISTINCT function similar with GROUP BY, but it can put inside the COUNT() function.

SELECT COUNT( DISTINCT(id) ) FROM table

July 19 2014

Joomla prevent SQL injections in custom query

Tagged Under : , ,

Joomla
For custom query in Joomla is not prevent the SQL injections issue. So that, we need to add some script to the query.

In Joomla we can using $db->quote($param) to prevent SQL injections in custom query.

The Example of the query:
$db = JFactory::getDbo();
$query = "INSERT INTO table (`username`, `password`) VALUES (".$db->quote($username).", $db->quote($password))";
$db->setQuery($query);
$db->query();
In the query you not need to add Single Quote Symbol. Because it will help you add in your query.

June 04 2014

Adding number of days to Dates in MySQL

Tagged Under : ,

MySQL
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

June 04 2014

multiple counts with one SQL Query

Tagged Under : ,

MySQL
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:

March 29 2014

PHP how to insert Chinese character into MySQL

Tagged Under : ,

php
The way to insert Chinese character into MySQL table. You need set the table structure charset to utf8.
CREATE TABLE IF NOT EXISTS `contents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
After that, add the following code as below:
mysql_connect(HOST, USER, PASSWORD);
mysql_select_db(DBNAME);
mysql_query("SET NAMES 'utf8'");
mysql_query("SET CHARACTER_SET_CLIENT=utf8");
mysql_query("SET CHARACTER_SET_RESULTS=utf8");