Search the Community
Showing results for tags 'transaction'.
Found 1 result
-
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; } }
