1. Login to your CPanel Account.
2. Find the Database you want to backup.
3. Get the Database MySQL Username and Password
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.
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)')
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 weekThe created_date column must be DATE or DATETIME type.
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
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.
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
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. 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: