SQL Statement snippets
SELECT td.name AS term_name , td2.name AS content_type , COUNT(meta.request_id) AS total_requests , SUM(meta.total_plans) total_plans , SUM(meta.invoice_total) AS total_amount , DATE_FORMAT(FROM_UNIXTIME(n.created), '%Y%m') AS date_of_the_request , DATE_FORMAT(FROM_UNIXTIME(meta.request_approved), '%Y%m') AS date_of_the_approval FROM the_meta_table meta INNER JOIN node AS n ON n.nid = meta.request_id INNER JOIN field_data_field_reference AS fr ON fr.bundle = 'the_content_type' AND fr.field_data_field_reference_target_id = meta.request_id INNER JOIN field_data_field_term_name AS mn ON mn.bundle = 'the_types' AND mn.entity_id = fr.entity_id INNER JOIN taxonomy_term_data AS td ON td.tid = mn.field_the_tid INNER JOIN taxonomy_term_data AS td2 ON td2.tid = 1 WHERE meta.request_approved != '' GROUP BY td2.tid, td.tid ORDER BY td.tid ASC
// Webform getting total submissions, and join only one result for no reason :) SELECT ws.webform_id, COUNT(ws.sid), DATE_FORMAT(FROM_UNIXTIME(ws.created), '%Y-%m') FROM webform_submission ws LEFT JOIN webform_submission_data wsd ON wsd.webform_id = ws.webform_id AND wsd.sid = ( SELECT sid FROM webform_submission_data WHERE webform_id = ws.webform_id ORDER BY sid DESC LIMIT 1 ) LEFT JOIN webform_submission_log wsl ON wsl.sid = wsd.sid WHERE ws.webform_id IN ('WEBFORM_ID1', 'WEBFORM_ID2', 'WEBFORM_ID3') GROUP BY ws.webform_id
// Convert date to timestamp UNIX_TIMESTAMP() // current timestamp UNIX_TIMESTAMP('2022-06-15') // getting timestamp from a date// Created users last 24 hours SELECT COUNT(u.uid) AS total FROM users_field_data AS u WHERE u.created >= UNIX_TIMESTAMP() - 86400;
// Last access in 24 hours SELECT COUNT(u.uid) AS total FROM users_field_data AS u WHERE u.access >= UNIX_TIMESTAMP() - 86400;
600 = 10 minutes 900 = 15 minutes 3600 = hour 21600 = 6 hours
SELECT REPLACE( REPLACE(FIELD_NAME , 'yes', 'super hero') , 'no', 'not super hero') AS is_super_hero
// Getting latest watchdog logs in 24 hoursSELECT REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(severity , 0, 'Emergency') , 1, 'Alert') , 2, 'Critical') , 3, 'Error') , 4, 'Warning') , 5, 'Notice') , 6, 'Info') , 7, 'Debug') AS severity_level
, COUNT(wid) AS total FROM watchdog WHERE timestamp >= UNIX_TIMESTAMP() - 86400 GROUP BY severity ORDER BY severity
// Latest log entires // CAST Hex field value of log variables SELECT DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d %h:%i %p') AS time, uid, type, message, CAST(variables AS CHAR(10000) CHARACTER SET utf8) AS vars, severity, link, location, referer, hostname FROM watchdog WHERE severity IN (0,1,2,3) ORDER BY timestamp DESC LIMIT 50
// Exclude weekends from a specific period between two dates (By Rakan, thanks) // VALUE FOR (Friday+Saturday as a weekend): 0123334401222334011122340001123400012344001234440 // VALUE FOR (Friday+Saturday as a weekend): 0123334401222334011122340001123400012345001234440 (With case of Starting Saturday and finishing by Friday as 5 working days) // VALUE FOR (Saturday+Sunday as a weekend): 0123444401233334012222340111123400001234000123440 // NOTE: If the created and finished were on the same day, the working days will be 0 days. Even if the created date has been in a weekend day and finished in the first working day it will be 0 working days. // Source: https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates/6762805#6762805 SELECT (5 * (DATEDIFF(finished_at, created_at) DIV 7) + MID('0123334401222334011122340001123400012344001234440', 7 * WEEKDAY(created_at) + WEEKDAY(finished_at) + 1, 1)) AS days, FROM mytable where DATEDIFF(finished_at, created_at) <= 3// PHP Code to generate the long number | M T W T F S S -|-------------- M| 0 1 2 3 3 3 4 T| 4 0 1 2 2 2 3 W| 3 4 0 1 1 1 2 T| 2 3 4 0 0 0 1 F| 1 2 3 4 0 0 0 S| 1 2 3 4 4 0 0 S| 1 2 3 4 4 4 0 <?php $day_off = [4,5]; $days = [0,1,2,3,4,5,6]; $days_list = [0,1,2,3,4,5,6]; $result = []; foreach ($days as $i1 => $current_day) { $workdays = 0; $result[$i1] = [ 0 => 0, 1 => 0, 2 => 0, 3 => 0, 4 => 0, 5 => 0, 6 => 0, ]; foreach ($days_list as $i => $day_to_compare) { if ($current_day == $day_to_compare) { $result[$i1][$day_to_compare] = 0; continue; }; if (!in_array($day_to_compare, $day_off)) { $workdays++; } $result[$i1][$day_to_compare] = $workdays; } unset($days_list[$i1]); $days_list[] = $i1; } foreach ($result as $res) { print implode($res, ''); }
// Getting largest DB tables SELECT TABLE_NAME ASTable
, ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1034) ASSize (GB)
FROM information_schema.TABLES WHERE TABLE_SCHEMA = "drupal" ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC limit 10;
// count duplicated rows with same data value from queue table SELECT data, COUNT() AS duplicates FROM queue GROUP BY data HAVING COUNT()>1 ORDER BY duplicates DESC;
// Get the biggest (max length rows) SELECT * FROM queue ORDER BY LENGTH(data) DESC LIMIT 1;