Windows mySQL - two "auto_increments"

[ChaOs]

Hello everyone,

mySQL is driving me mad for several days yet. I thought twice, trice, googled, googled and googled as hell and would not ask if I wouldn't be f*kd up like now...

 

The starting situation is that I wanted to write a little blog system with PHP and SQL.

 

Software-Info:
* Windows 8.1 Professional Edition
* Apache/2.4.23 (Win32); OpenSSL/1.0.2h; PHP Version 7.0.9; PHP-Extension: mysqli
* phpMyAdmin 4.5.1; 10.1.16-MariaDB; DB-Client Version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 241ae00989d1995ffcbbf63d579943635faf9972 $

 

To create an entry and connecting with the file system with a smooth handling I need two columns (id and order) that are "unique". 
In this system it's not possible to make two columns unique. So there must a way to make both columns unique "virtually".

 

--------------------------------------------------------------------------------------
| id*            | headline | word         | unlocked | order* | timestamp           |
--------------------------------------------------------------------------------------
| 20180826235958 | Hello    | How are you? | 0        | 1      | 2018-08-26 23:59:58 |
| 20180826235959 | Hi       | I'm fine     | 0        | 2      | 2018-08-26 23:59:59 |

 

• id: int(255); datetime as only number - *must be unique as id
• headline: text
• word: text
• unlocked: tinyint(1); boolean to make in-/visible
• order: int(255); *must be unique, too; to reorder the entries without touching the id

 

The main problem is to make the INSERT command proof for no double entries.

In fact the id is not auto_increment but unique - it takes the datetime to be unique. This is important to the id-validation being 14 chars long.
Anyway so you can do something like this:
 

INSERT INTO `_myTable` 
 (`id` ,`headline`,`word`, `unlocked`, `order`, `timestamp`) 
VALUES 
 ('20180826235958' ,'','', 0, 1, '2018-08-26 23:59:58')

But what is, when you *click*, *click*, *click* in one second?
You can wait for returning false, when duplicate key(id) is fired and then re-ask again to:

INSERT INTO `_myTable` 
 (`id` ,`headline`,`word`, `unlocked`, `order`, `timestamp`) 
VALUES 
 (LAST_INSERT_ID(id)+1 ,'','', 0, 1, '2018-08-26 23:59:58')

This solution is a little ugly (because you can get an id like 20180826235960) but this seems be working.

--------------------------------------------------------------------------------------------------------------------------------
The minor problem is to count up the `order` field.

I tried sth:

INSERT INTO `_myTable`
 (`id` ,`headline`,`word`, `unlocked`, `order`, `timestamp`)
VALUES 
 ('20180826' ,'','', 0, myorder, '2018-08-26 16:37:02')
SELECT 
 `id` AS tmpID, IFNULL(MAX(`order`), 0) + 1 AS myorder FROM `_myTable`;
SELECT
 LAST_INSERT_ID();

Result:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT 
`id` AS tmpID, IFNULL(MAX(`order`), 0) + 1 AS myorder FROM `_myTable' at line 5

 

By researching I found some interesting things but nothing of this worked EVEN they have rated up posts:

https://stackoverflow.com/questions/5360117/insert-and-set-value-with-max1-problems

 

1.
--------------------------------------------------------------------------------------------------------------------------------
The best way is to use a transaction but if your not using innodb tables then next best is locking the tables and then performing your queries. So:

Lock tables customers write;

$max = SELECT MAX( customer_id ) FROM customers;

Grab the max id and then perform the insert

INSERT INTO customers( customer_id, firstname, surname )
VALUES ($max+1 , 'jim', 'sock')

unlock tables;

--------------------------------------------------------------------------------------------------------------------------------
This is this one most interesting but I don't know if i cant trust locking tables:
1. if I lock tables then writing PHP-Code to calculate some out then continuing but the request crashes - what's up with the locked table when it's not unlocked?
2. if the table is locked with "write" what will be if another request on the table appears being blocked? Would the other request wait until the lock is released OR will it cancel the request?

 

2.
--------------------------------------------------------------------------------------------------------------------------------

INSERT INTO 
customers( customer_id, firstname, surname )
SELECT MAX( customer_id ) + 1, 'jim', 'sock' FROM customers;

--------------------------------------------------------------------------------------------------------------------------------
This is the way i tryed out above and does not work.

 

3.
--------------------------------------------------------------------------------------------------------------------------------

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id )+1 FROM customers cust), 'sharath', 'rock')

--------------------------------------------------------------------------------------------------------------------------------
This one does not work anyway - see screenshot. 
Cannot imagine that post getting 9 points up without working on syntax. Either I do sth. wrong here or nobody tests the code before rating up.

What do yu think is the best practice to write transaction proof table entries like this?

my test-source:

            $multiSQL = "
                
                INSERT INTO `".secure_sql($tbl)."` (".$field_str.") VALUES (".$value_str.")
                SELECT MAX(`order`)+1 AS iORDER FROM `".secure_sql($tbl)."`;
                SELECT LAST_INSERT_ID();
            
            ";
            $insert = mysqli_multi_query($token['connection'],$multiSQL);
            
            if($insert){
                echo 1 . '<br />';
                print_r(mysqli_fetch_assoc($insert));
            }else{
                echo $multiSQL;
                $multiSQL = "
                
                    INSERT INTO `".secure_sql($tbl)."` (".$field_str.") VALUES (LAST_INSERT_ID(id)+1 , ".$value_strD.")
                    SELECT MAX(`order`)+1 AS iORDER FROM `".secure_sql($tbl)."`;
                    SELECT LAST_INSERT_ID();
                    
                ";
                $insert = mysqli_multi_query($token['connection'],$multiSQL);
                if($insert){
                    echo 2 . '<br />';
                    print_r(mysqli_fetch_assoc($insert));
                }else{
                    echo $multiSQL;
                }
            }

 

[Chuckun]

Maybe I missed the point because I am tired and slipped through most of your post but if the order ID is unique then there is literally no need for a separate unique ID..  that unique order ID can be your primary key used for joining tables... 

 

My developments all do this (I am an e-commerce developer)

 

Edit - also, normally queries will stay in 'waiting for lock to be lifted' state when attempted on a locked table.  

Edited August 27, 2018 by Chuckun

[n3g4n]

Looks like one of your test isn't correct from what I'm seeing.  Try this instead and let me know if it works:

 

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id ) FROM customers cust)+1, 'sharath', 'rock')

 

Edited August 27, 2018 by n3g4n

[ChaOs]

Oops.

 

Maybe my english is too bad also my sql-abilities to describe the situation clearly enough...

 

let us devide between ID and id in my description. ID is the primary key and id is the column called "id".

 

the "id" like(20180826235959) is the ID, with no auto_increment the unique point of the id is the datetime as a number.

The "order" like(1) should not be the ID because the ID must never change in the system.

 

The next step after generating a row is writing a dir into the file-system called like ./articles/2018/08/26/235959 where files for exactly this post are stored.

I learnt in the past never storing files within a database for preventing several problems.

 

The order point is for reordering within by increasing one value and decresing the other one: 1 2 3 4 5 ... f.e.: the 3 becomes 4 and the 4 becomes 3 in one request. But as far as I know if order would be "real" unique that reordering would not be possible this way because for a short time two values of "4" in the example do exist wich is not possible as real unique ...

 

Thx for your fast answer btw

 

 

[n3g4n]

4 minutes ago, ChaOs said:

Oops.

 

Maybe my english is too bad also my sql-abilities to describe the situation clearly enough...

 

let us devide between ID and id in my description. ID is the primary key and id is the column called "id".

 

the "id" like(20180826235959) is the ID, with no auto_increment the unique point of the id is the datetime as a number.

The "order" like(1) should not be the ID because the ID must never change in the system.

 

The next step after generating a row is writing a dir into the file-system called like ./articles/2018/08/26/235959 where files for exactly this post are stored.

I learnt in the past never storing files within a database for preventing several problems.

 

The order point is for reordering within by increasing one value and decresing the other one: 1 2 3 4 5 ... f.e.: the 3 becomes 4 and the 4 becomes 3 in one request. But as far as I know if order would be "real" unique that reordering would not be possible this way because for a short time two values of "4" in the example do exist wich is not possible as real unique ...

 

Thx for your fast answer btw

 

 

 

 

Ok, from what I understand you want to be able to update the order (or sorting) values which are unique in the database which you can't because the previous order value already exists.  Correct?

 

[ChaOs]

15 minutes ago, n3g4n said:

Looks like one of your test isn't correct from what I'm seeing.  Try this instead and let me know if it works:

 

INSERT INTO customers
  ( customer_id, firstname, surname )
VALUES 
  ((SELECT MAX( customer_id ) FROM customers cust)+1, 'sharath', 'rock')

 

Thx for reply as well.

Unfortunately the syntax appears to be invalid in PHP myAdmin again 😕

the english msg like: a comma or a closing bracket was expected (at SELECT)

 

I really wished that would worked that fast 😕

 

[ChaOs]

1 minute ago, n3g4n said:

 

 

Ok, from what I understand you want to be able to update the order (or sorting) values which are unique in the database which you can't because the previous order value already exists.  Correct?

  

I just want that making "virtually" unique - not by SQL itself, else by practicing source ... checking out wich order is the highest value and putting that +1, then insert.

[n3g4n]

Can you copy/paste, or even better do an export of your customer table so I can add it to my table and try the query that's not working?  I don't need the data, just the CREATE TABLE customer with all the fields.

Edited August 27, 2018 by n3g4n

[ChaOs]

3 minutes ago, n3g4n said:

Can you copy/paste, or even better do an export of your customer table so I can add it to my table and try the query that's not working?

yes ... but i have no customer table ... I only checked up the syntax with the phpMyAdmin - The customer table was from stack overflow ...  -

 

but I give you the full code of my version. + mySQL-Table, just wait a bit pls.

[n3g4n]

13 minutes ago, ChaOs said:

yes ... but i have no customer table ... I only checked up the syntax with the phpMyAdmin - The customer table was from stack overflow ...  -

 

but I give you the full code of my version. + mySQL-Table, just wait a bit pls.

...or you can copy/paste the table that you're trying to work with.  

[ChaOs]

Already done (zip). What do you mean with copy/paste a table? ...

 

The structure is in the starting post if you mean that ...

 

--------------------------------------------------------------------------------------
| id*            | headline | word         | unlocked | order* | timestamp           |
--------------------------------------------------------------------------------------
| 20180826235958 | Hello    | How are you? | 0        | 1      | 2018-08-26 23:59:58 |
| 20180826235959 | Hi       | I'm fine     | 0        | 2      | 2018-08-26 23:59:59 |

 

id: unique

order: pseudo-unique

 

 

insert_max_order.zip

[n3g4n]

I tried adding this record, and it worked in my phpMyAdmin:

 

INSERT INTO `___home_impressum_wordcascade_wordcascade` 
 (`id` ,`headline`,`word`, `unlocked`, `order`, `timestamp`) 
VALUES 
 ('20180826235955' ,'aa','bb', 0, (SELECT MAX(`order`)+1 FROM`___home_impressum_wordcascade_wordcascade` `tmp`)  , '2018-08-26 23:59:55');

"order" needs to be in quotation because it's a MySQL parameter.  You could rename it to "sort_order" to avoid having a column named as a MySQL parameter.

 

I also had to change the type of "id" to bigint.

 

Edited August 27, 2018 by n3g4n

[n3g4n]

Also, in your code, you could change the two first lines to:

 

	$date 	= date('Y-m-d H:i:s');
	$unique = date('YmdHis', strtotime($date);

So the unique ID will always be created from the $date's value and not from a new created date() value.   I know your code will work the way it is, but you never know what can happen.  With the code above, you stay consistent.

Edited August 27, 2018 by n3g4n

[ChaOs]

10 minutes ago, n3g4n said:

INSERT INTO `___home_impressum_wordcascade_wordcascade` 
 (`id` ,`headline`,`word`, `unlocked`, `order`, `timestamp`) 
VALUES 
 ('20180826235955' ,'aa','bb', 0, (SELECT MAX(`order`)+1 FROM`___home_impressum_wordcascade_wordcascade` `tmp`)  , '2018-08-26 23:59:55');

"order" needs to be in quotation because it's a MySQL parameter.  You could rename it to "sort_order" to avoid having a column named as a MySQL parameter.

Oh, sry about that - in the original source it was quoted ...

But now we are a step in the right direction ... I submitted your code and it worked for the first time but highlighted as errors - see screen. This is terrifying.

But when I submit the code the second time, i get errors. I changed the "NULL" in order to 0 before to be sure:

 

Static Analysis:

10 Errors.

 

1. Ein Komma oder eine schließende Klammer wurde erwartet (near "SELECT" at position 165) //expected comma or closing bracket
2. Unerkanntes Schlüsselwort. (near "MAX" at position 172) //unknown keyword
3. Unerwartetes Zeichen. (near "(" at position 175) //unexpected sign
4. Unerwartetes Zeichen. (near "`order`" at position 176)
5. Unerwartetes Zeichen. (near ")" at position 183)
6. Unerwartetes Zeichen. (near "+1" at position 184)
7. Unerwartetes Zeichen. (near ")" at position 240)
8. Unerwartetes Zeichen. (near "," at position 243)
9. Unerwartetes Zeichen. (near "'2018-08-26 23:59:55'" at position 245)
10. Unerwartetes Zeichen. (near ")" at position 266)

 

 

8 minutes ago, n3g4n said:

Also, in your code, you could change the two first lines to:

 

	$date 	= date('Y-m-d H:i:s');
	$unique = date('YmdHis', strtotime($date);

So the unique ID will always be created from the $date's value and not from a new created date() value.   I know your code will work the way it is, but you never know what can happen.  With the code above, you stay consistent.

Thx for the review. I just thought ... because the id could change during INSERT if it's given because when at the same second (20180826235956) a post is submitted, the id gets LAST_INSERT_ID(id)+1 in the original code to be unique. and the timestamp not because that shall be the "real" time.

 

 

[n3g4n]

What is the version of your phpMyAdmin?