MySQL EXTRACT() Function

The EXTRACT() function is used to return a single part of a date/time, such as year, month, day, hour, minute, etc. Syntax EXTRACT(unit FROM date) Where date is a valid date expression and unit can be one of the following: Unit Value MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH...

MySQL Date Functions

The following table lists the most important built-in date functions in MySQL: FunctionDescription NOW()Returns the current date and time CURDATE()Returns the current date CURTIME()Returns the current time DATE()Extracts the date part of a date or date/time expression EXTRACT()Returns a single part of a date/time DATE_ADD()Adds a specified time interval to a date DATE_SUB()Subtracts a specified time interval from a date DATEDIFF()Returns the number of days between two dates DATE_FORMAT()Displays date/time data in different formats...

MySQL: Alter table add column with primary key at first syntax

ALTER TABLE users ADD rec_id INT( 11 ) NOT NULL AUTO_INCREMENT FIRST , ADD PRIMARY KEY ( rec_id ...

Mysql: How in mysql do I select a specific column followed by all columns

SELECT column2 new_name, t.* FROM table_name ...

Mysql: Select where field has more than 2 non duplicated records sql

This should be pretty easy but I am just not able to figure this one out. Got a table with customers order information. There is a field within this table called customers_email_address and customers_id along with a number of other non-essential fields. I need to locate distinct records where the entries in the customers_email_address field are different but unique to the same customers_id. In other words, I need to find out what customers have changed their email address in their account since their account's inception so I can update...

MYSQL - SELECT from multiple rows, same user, different values

Below is my table called fittest. I need to find which students based on student id (studid) have taken the pre and post test as designated in the prepost column. So based on the simple table below I would need to return studid 123456. How do I write the SELECT query for this? SELECT studid, prepost FROM `fittest` LIMIT 0, 30 ; studid prepost 123456 pre 123456 post 1031460 pre   Solution:   CREATE TABLE fittest (`studid` int, `prepost` varchar(4)); INSERT INTO fittest(`studid`, `prepost`)  VALUES (123456,...

Mysql: Select last 2 elements ascending followed by 1st element

I want to select the last two elements in ascending order followed by the first element. E.g. for 20 products now I'm getting 20, 19, 1 (ordered by id). I'm trying to get 19, 20, 1. Solution: CREATE TABLE Table1     (`id` int);     INSERT INTO Table1    (`id`) VALUES    (1),    (2),    (3),    (4),    (5),    (6),    (7),    (8),    (9),    (10),   ...

Mysql cross table join

2 mysql table as follow: items id item_name user_id 1 test1 1 2 test2 1 3 test3 1 4 test4 1 project id user_id items 1 1 1,3 2 1 2,4 how can write a join query that can return each items in a project? project1 => item1=> [id1] => [name1] => item3=> [id3] => [name3] => First of all don't store strings of delimited values in your db. You're...

Mysql: Split (explode) comma delimited column values to rows

Description Following is Table Structure: eligibility_table ID COURSE_ID BRANCH_IDS 1 501 621,622,623 1 502 1 503 625 2 501 621 2 505 650 3 500 Now, I am making new table structure as describe below and inserting course_table,branch_table through eligibility_table. So following, final output I want course_table ID COURSE_ID 1 501 1 502 1 503 2 501 2 505 3 500 branch_table ID BRANCH_ID 1 621 1 622 1 623 1 625 2 621 2 650     Solution: INSERT...

MySQL update single table with join

I have a values in table "REVIEW" that need to change REVIEW_STATUS from "UNDER_REVIEW" to "Abstain" based upon other records in the table that have the same REFERENCE_NUMBER , A specific REVIEW_TYPE and that REVIEW_STATUS is "ABSTAIN". Solution: It was the word TABLE UPDATE REVIEW AS REV1 LEFT JOIN REVIEW AS REV2 ON (REV1.REFERENCE_NUMBER=REV2.REFERENCE_NUMBER) SET REV1.REVIEW_STATUS='ABSTAIN' WHERE REV1.REVIEW_TYPE ='QOC' AND REV1.REVIEW_STATUS='UNDER_REVIEW' AND REV2.REVIEW_TYPE ='MED_NEC' AND (REV2.REVIEW_STATUS ='ABSTAIN' );...

Mysql: Return row count from second table using one query

I have the following two tables. I need to select all the lightboxes from lightboxes_tbl where author ='scott@co.com'. That's obviously the easy part. Where I am stuck is that I also want to select in the same query the number of assets in each lightbox. For example, 'aircraft-types' lightbox (id = 100 / lightbox_id = 100) would return 2 assets. The 'maintenance' lightbox (id = 101 / lightbox_id = 101) would return 1 asset. Thanks! lightboxes_tbl +-----+----------------+---------------+---------------------+ |id |lightbox_name |author...

Mysql: insert if row doesn't exist, else update.. is there a simpler command?

$sql = "INSERT INTO results (user_id, logo_id, result_tries) VALUES ('$user_id', '$logo_id', 0) ON DUPLICATE KEY UPDATE result_tries = result_tries + 1"...

PHP: Things to Remember While Developing a Website

These days website is an essential marketing platform for your business and key part of your marketing mix. If you are planning to have one for your own business then you can hire any web developer or you can do it on your own. Developing a website is not the hard part, hard part is, it must serve the purpose for which you have created it. If you are able to answer the following questions then you get the winning website. Otherwise follow the blog, it will take you through the steps you may have missed during your website development. Is...

Mysql: Difference between datetime and timestamp in mysql

It's important to note that datetime has a range of 1000-9999, but the range for timestamp is only 1970-2038. this can be a problem if your system has to store birthdates, or you have to handle something like the payment plan for a 30-year mortgage. In version 5.6.5, it is possible to set a default value on a datetime column, and even make a column that will update when the row is updated. The type definition: CREATE TABLE foo ( creation_time DATETIME DEFAULT CURRENT_TIMESTAMP, modification_time DATETIME ON UPDATE CURRENT_TIMESTAMP )       ...

mysql query To get the top two salary from each department

**Department table name** **following with fields name** id , empid ,salary ,departid ,status how to get the top two highest salaries from each department with single query in mysql Try SELECT id, empid, salary, departid, status FROM ( SELECT id, empid, salary, departid, status, @n := IF(@g = departid, @n + 1, 1) rownum, @g := departid FROM table1 ORDER BY departid, salary DESC ) q WHERE q.rownum <= 2 What it does it generates in inner select a rank for each employee in each department based on...

Mysql: How to select rows with multiple specific column values in single query?

Table A itemNo colorNo 1 3 1 4 2 4 2 70 3 9 3 10   Try SELECT * FROM A WHERE (itemNo = '1' AND colorNo = '4') OR (itemNo = '2' AND colorNo = '70') OR (itemNo = '3' AND colorNo = '9') or you can also do this SELECT * FROM A WHERE (itemNo, colorNo) IN ((1, 4),(2, 70),(3, 9)) Output: | ITEMNO | COLORNO | -------------------- | 1 | 4 | | 2 | 70 | | 3 | 9 |  ...

Mysql: converting comma separated values into rows in mysql

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value   FROM table1 t CROSS JOIN  (    SELECT a.N + b.N * 10 + 1 n      FROM      (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a    ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION...