Skip to content

Instantly share code, notes, and snippets.

@notbenh

notbenh/report_id.txt

Created December 20, 2011 07:42
Show Gist options
  • Save notbenh/1500678 to your computer and use it in GitHub Desktop.
Save notbenh/1500678 to your computer and use it in GitHub Desktop.
Download ZIP
making a report id on the fly
Raw
report_id.txt
mysql> select * from status_type;
+----+-------------+
| id | status_name |
+----+-------------+
| 1 | created |
| 2 | started |
| 3 | canceled |
| 4 | finished |
+----+-------------+
4 rows in set (0.00 sec)
mysql> showt tables;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'showt tables' at line 1
mysql> show tables;
+----------------------------+
| Tables_in_order_thing |
+----------------------------+
| print_order |
| print_order_complete_id |
| print_order_status_history |
| status_type |
+----------------------------+
4 rows in set (0.00 sec)
mysql> desc print_order_status_history;
+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| print_order_id | int(10) unsigned | YES | | NULL | |
| status_id | int(10) unsigned | YES | | NULL | |
| stamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.01 sec)
mysql> insert into print_order_status_history set print_order_id = 1, status_id = 1
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from print_order_status_history;
+----+----------------+-----------+---------------------+
| id | print_order_id | status_id | stamp |
+----+----------------+-----------+---------------------+
| 1 | 1 | 1 | 2011-12-19 22:53:53 |
+----+----------------+-----------+---------------------+
1 row in set (0.02 sec)
mysql> insert into print_order_status_history set print_order_id = 1, status_id = 2;
Query OK, 1 row affected (0.00 sec)
mysql> select * from print_order_status_history;
+----+----------------+-----------+---------------------+
| id | print_order_id | status_id | stamp |
+----+----------------+-----------+---------------------+
| 1 | 1 | 1 | 2011-12-19 22:53:53 |
| 2 | 1 | 2 | 2011-12-19 22:54:03 |
+----+----------------+-----------+---------------------+
2 rows in set (0.00 sec)
mysql> insert into print_order_status_history set print_order_id = 1, status_id = 4;
Query OK, 1 row affected (0.00 sec)
mysql> show tables;
+----------------------------+
| Tables_in_order_thing |
+----------------------------+
| print_order |
| print_order_complete_id |
| print_order_status_history |
| status_type |
+----------------------------+
4 rows in set (0.00 sec)
mysql> desc print_order_complete_id;
+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| stamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| print_order_id | int(10) unsigned | YES | | NULL | |
+----------------+------------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
mysql> insert into print_order_complete_id set print_order_id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from print_order_id;
ERROR 1146 (42S02): Table 'order_thing.print_order_id' doesn't exist
mysql> select * from print_order_complete_id;
+----+---------------------+----------------+
| id | stamp | print_order_id |
+----+---------------------+----------------+
| 1 | 2011-12-19 22:56:49 | 1 |
+----+---------------------+----------------+
1 row in set (0.00 sec)
mysql> desc print_order_status_history;
+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| print_order_id | int(10) unsigned | YES | | NULL | |
| status_id | int(10) unsigned | YES | | NULL | |
| stamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)
mysql> select CONCAT(YEAR(stamp),'-',id) as complete_id where status_id =4;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where status_id =4' at line 1
mysql> select CONCAT(YEAR(stamp),'-',id) as complete_id from print_order_status_history where status_id = 4;
+-------------+
| complete_id |
+-------------+
| 2011-3 |
+-------------+
1 row in set (0.00 sec)
mysql> select CONCAT(YEAR(stamp),'-',lpad(hex(id),8,0)) as complete_id from print_order_status_history where status_id = 4;
+---------------+
| complete_id |
+---------------+
| 2011-00000003 |
+---------------+
1 row in set (0.00 sec)
mysql> select hex(1000000);
+--------------+
| hex(1000000) |
+--------------+
| F4240 |
+--------------+
1 row in set (0.00 sec)
mysql> select hex(100000000);
+----------------+
| hex(100000000) |
+----------------+
| 5F5E100 |
+----------------+
1 row in set (0.00 sec)
mysql> select hex(100000);
+-------------+
| hex(100000) |
+-------------+
| 186A0 |
+-------------+
1 row in set (0.00 sec)
mysql> select CONCAT(YEAR(stamp),'-',lpad(hex(10000),5,0)) as complete_id from print_order_status_history where status_id = 4;
+-------------+
| complete_id |
+-------------+
| 2011-02710 |
+-------------+
1 row in set (0.00 sec)
mysql> select CONCAT(YEAR(stamp),'-',lpad(hex(id),5,0)) as complete_id from print_order_status_history where status_id = 4;
+-------------+
| complete_id |
+-------------+
| 2011-00003 |
+-------------+
1 row in set (0.00 sec)
mysql> select CONCAT(YEAR(stamp),'-',lpad(hex(99292),5,0)) as complete_id from print_order_status_history where status_id = 4;
+-------------+
| complete_id |
+-------------+
| 2011-183DC |
+-------------+
1 row in set (0.00 sec)
mysql> select CONCAT(YEAR(stamp),'-',lpad(hex(id),5,0)) as complete_id from print_order_status_history where status_id = 4;
+-------------+
| complete_id |
+-------------+
| 2011-00003 |
+-------------+
1 row in set (0.00 sec)
mysql> desc print_order_status_history;
+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| print_order_id | int(10) unsigned | YES | | NULL | |
| status_id | int(10) unsigned | YES | | NULL | |
| stamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)
mysql> show tables;
+----------------------------+
| Tables_in_order_thing |
+----------------------------+
| print_order |
| print_order_complete_id |
| print_order_status_history |
| status_type |
+----------------------------+
4 rows in set (0.01 sec)
mysql> desc print_order
-> ;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.00 sec)
mysql> drop table print_order; create table print_order (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, start TIMESTAMP, complete DATETIME)
Query OK, 0 rows affected (0.00 sec)
-> ;
Query OK, 0 rows affected (0.09 sec)
mysql> desc print_order;
+----------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| start | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| complete | datetime | YES | | NULL | |
+----------+------------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
mysql> insert into print_order set id =1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into print_order set complete = NOW();
Query OK, 1 row affected (0.00 sec)
mysql> select * from print_order;
+----+---------------------+---------------------+
| id | start | complete |
+----+---------------------+---------------------+
| 1 | 2011-12-19 23:13:09 | NULL |
| 2 | 2011-12-19 23:13:24 | 2011-12-19 23:13:24 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> update print_order set complete = NOW();
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from print_order;
+----+---------------------+---------------------+
| id | start | complete |
+----+---------------------+---------------------+
| 1 | 2011-12-19 23:14:15 | 2011-12-19 23:14:15 |
| 2 | 2011-12-19 23:14:15 | 2011-12-19 23:14:15 |
+----+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> select *, CONCAT(YEAR(complete),-,LPAD(HEX(id),6,0)) AS report_id FROM print_order where complete IS NOT NULL;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LPAD(HEX(id),6,0)) AS report_id FROM print_order where complete IS NOT NULL' at line 1
mysql> select *, CONCAT(YEAR(complete),-,LPAD(HEX(id),6,'0')) AS report_id FROM print_order where complete IS NOT NULL;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LPAD(HEX(id),6,'0')) AS report_id FROM print_order where complete IS NOT NULL' at line 1
mysql> select *, CONCAT(YEAR(complete),'-',LPAD(HEX(id),6,'0')) AS report_id FROM print_order where complete IS NOT NULL;
+----+---------------------+---------------------+-------------+
| id | start | complete | report_id |
+----+---------------------+---------------------+-------------+
| 1 | 2011-12-19 23:14:15 | 2011-12-19 23:14:15 | 2011-000001 |
| 2 | 2011-12-19 23:14:15 | 2011-12-19 23:14:15 | 2011-000002 |
+----+---------------------+---------------------+-------------+
2 rows in set (0.01 sec)
mysql> select *, CONCAT(YEAR(complete),'-',LPAD(HEX(id),6,'0')) AS report_id FROM print_order where complete IS NOT NULL;
+----+---------------------+---------------------+-------------+
| id | start | complete | report_id |
+----+---------------------+---------------------+-------------+
| 1 | 2011-12-19 23:14:15 | 2011-12-19 23:14:15 | 2011-000001 |
| 2 | 2011-12-19 23:14:15 | 2011-12-19 23:14:15 | 2011-000002 |
+----+---------------------+---------------------+-------------+
2 rows in set (0.00 sec)
mysql> create table print_order_history (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, print_order_id INT UNSIGNED, status VARCHAR(20), note TEXT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> create table print_order_history (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, print_order_id INT UNSIGNED, status VARCHAR(20), note TEXT);
Query OK, 0 rows affected (0.39 sec)
mysql> alter print_order_history add stamp TIMESTAMP after note;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'print_order_history add stamp TIMESTAMP after note' at line 1
mysql> alter print_order_history add (stamp TIMESTAMP) after note;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'print_order_history add (stamp TIMESTAMP) after note' at line 1
mysql> alter print_order_history add (stamp TIMESTAMP);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'print_order_history add (stamp TIMESTAMP)' at line 1
mysql> alter table print_order_history add (stamp TIMESTAMP);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc print_order_history;
+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| print_order_id | int(10) unsigned | YES | | NULL | |
| status | varchar(20) | YES | | NULL | |
| note | text | YES | | NULL | |
| stamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> insert into print_order_history set print_order_id = 2, status = 'done', note = 'cats are furry';
Query OK, 1 row affected (0.00 sec)
mysql> insert into print_order_history set print_order_id = 1, status = 'done', note = 'dogs are furry too';
Query OK, 1 row affected (0.00 sec)
mysql> select * from print_order_history;
+----+----------------+--------+--------------------+---------------------+
| id | print_order_id | status | note | stamp |
+----+----------------+--------+--------------------+---------------------+
| 1 | 2 | done | cats are furry | 2011-12-19 23:29:53 |
| 2 | 1 | done | dogs are furry too | 2011-12-19 23:30:07 |
+----+----------------+--------+--------------------+---------------------+
2 rows in set (0.00 sec)
mysql> desc print_order;
+----------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| start | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| complete | datetime | YES | | NULL | |
+----------+------------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.00 sec)
mysql> alter table print_order add (complete_history_id INT UNSIGNED) after complete;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'after complete' at line 1
mysql> alter table print_order add (complete_history_id INT UNSIGNED);
Query OK, 2 rows affected (0.40 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from print_order;
+----+---------------------+---------------------+---------------------+
| id | start | complete | complete_history_id |
+----+---------------------+---------------------+---------------------+
| 1 | 2011-12-19 23:14:15 | 2011-12-19 23:14:15 | NULL |
| 2 | 2011-12-19 23:14:15 | 2011-12-19 23:14:15 | NULL |
+----+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> update print_order set complete = NULL;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> update print_order set complete = 2011-12-19 23:29:53, complete_id = 1 where id = 2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '23:29:53, complete_id = 1 where id = 2' at line 1
mysql> update print_order set complete = '2011-12-19 23:29:53', complete_id = 1 where id = 2;
ERROR 1054 (42S22): Unknown column 'complete_id' in 'field list'
mysql> update print_order set complete = '2011-12-19 23:29:53', complete_history_id = 1 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from print_order_history;
+----+----------------+--------+--------------------+---------------------+
| id | print_order_id | status | note | stamp |
+----+----------------+--------+--------------------+---------------------+
| 1 | 2 | done | cats are furry | 2011-12-19 23:29:53 |
| 2 | 1 | done | dogs are furry too | 2011-12-19 23:30:07 |
+----+----------------+--------+--------------------+---------------------+
2 rows in set (0.00 sec)
mysql> update print_order set complete = '2011-12-19 23:30:07', complete_history_id = 1 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from print_order;
+----+---------------------+---------------------+---------------------+
| id | start | complete | complete_history_id |
+----+---------------------+---------------------+---------------------+
| 1 | 2011-12-19 23:32:37 | 2011-12-19 23:30:07 | 1 |
| 2 | 2011-12-19 23:32:10 | 2011-12-19 23:29:53 | 1 |
+----+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> update print_order set complete_history_id = 2 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from print_order;
+----+---------------------+---------------------+---------------------+
| id | start | complete | complete_history_id |
+----+---------------------+---------------------+---------------------+
| 1 | 2011-12-19 23:33:01 | 2011-12-19 23:30:07 | 2 |
| 2 | 2011-12-19 23:32:10 | 2011-12-19 23:29:53 | 1 |
+----+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from print_order_history;
+----+----------------+--------+--------------------+---------------------+
| id | print_order_id | status | note | stamp |
+----+----------------+--------+--------------------+---------------------+
| 1 | 2 | done | cats are furry | 2011-12-19 23:29:53 |
| 2 | 1 | done | dogs are furry too | 2011-12-19 23:30:07 |
+----+----------------+--------+--------------------+---------------------+
2 rows in set (0.00 sec)
mysql> select *, CONCAT(YEAR(complete),'-',LPAD(HEX(complete_history_id),5,'0') AS report_id FROM print_order;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM print_order' at line 1
mysql> select *, CONCAT(YEAR(complete),'-',LPAD(HEX(complete_history_id),5,'0')) AS report_id FROM print_order;
+----+---------------------+---------------------+---------------------+------------+
| id | start | complete | complete_history_id | report_id |
+----+---------------------+---------------------+---------------------+------------+
| 1 | 2011-12-19 23:33:01 | 2011-12-19 23:30:07 | 2 | 2011-00002 |
| 2 | 2011-12-19 23:32:10 | 2011-12-19 23:29:53 | 1 | 2011-00001 |
+----+---------------------+---------------------+---------------------+------------+
2 rows in set (0.00 sec)
mysql> select *, CONCAT(YEAR(complete),'-',LPAD(HEX(complete_history_id),5,'0')) AS report_id FROM print_order order by complete_history_id;
+----+---------------------+---------------------+---------------------+------------+
| id | start | complete | complete_history_id | report_id |
+----+---------------------+---------------------+---------------------+------------+
| 2 | 2011-12-19 23:32:10 | 2011-12-19 23:29:53 | 1 | 2011-00001 |
| 1 | 2011-12-19 23:33:01 | 2011-12-19 23:30:07 | 2 | 2011-00002 |
+----+---------------------+---------------------+---------------------+------------+
2 rows in set (0.00 sec)
mysql> select *, CONCAT(YEAR(complete),'-',LPAD(HEX(complete_history_id),5,'0')) AS report_id FROM print_order order by complete;
+----+---------------------+---------------------+---------------------+------------+
| id | start | complete | complete_history_id | report_id |
+----+---------------------+---------------------+---------------------+------------+
| 2 | 2011-12-19 23:32:10 | 2011-12-19 23:29:53 | 1 | 2011-00001 |
| 1 | 2011-12-19 23:33:01 | 2011-12-19 23:30:07 | 2 | 2011-00002 |
+----+---------------------+---------------------+---------------------+------------+
2 rows in set (0.00 sec)
mysql> show tables;
+----------------------------+
| Tables_in_order_thing |
+----------------------------+
| print_order |
| print_order_complete_id |
| print_order_history |
| print_order_status_history |
| status_type |
+----------------------------+
5 rows in set (0.00 sec)
mysql> desc print_order_history;
+----------------+------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+-------------------+-----------------------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| print_order_id | int(10) unsigned | YES | | NULL | |
| status | varchar(20) | YES | | NULL | |
| note | text | YES | | NULL | |
| stamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------+------------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
mysql> select * from print_order_history;
+----+----------------+--------+--------------------+---------------------+
| id | print_order_id | status | note | stamp |
+----+----------------+--------+--------------------+---------------------+
| 1 | 2 | done | cats are furry | 2011-12-19 23:29:53 |
| 2 | 1 | done | dogs are furry too | 2011-12-19 23:30:07 |
+----+----------------+--------+--------------------+---------------------+
2 rows in set (0.00 sec)
mysql> insert into print_order_history set print_order_id = 1, status = 'reported', note = (SELECT CONCAT(YEAR(complete),'-',LPAD(HEX(complete_history_id),5,'0')) from print_order where id = 1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from print_order_history;
+----+----------------+----------+--------------------+---------------------+
| id | print_order_id | status | note | stamp |
+----+----------------+----------+--------------------+---------------------+
| 1 | 2 | done | cats are furry | 2011-12-19 23:29:53 |
| 2 | 1 | done | dogs are furry too | 2011-12-19 23:30:07 |
| 3 | 1 | reported | 2011-00002 | 2011-12-19 23:40:34 |
+----+----------------+----------+--------------------+---------------------+
3 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment