Jump to content

Windows mySQL - two "auto_increments"


ChaOs

Recommended Posts

5 hours ago, ChaOs said:

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 $

:)

 

Link to comment
Share on other sites

Ooops, sorry I missed that info on your first post.

 

Try this to see if this works better:

 

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

 

Link to comment
Share on other sites

6 hours ago, n3g4n said:

Ooops, sorry I missed that info on your first post.

 

Try this to see if this works better:

 


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

 

Sry, too did not get the msg before I fell to sleep .... it was 5:30 AM here and I thought the day was long enough...

 

This doesn't work anyway .... the Syntax-Highlighter is driving me crazy so freakn hard.

But why SELECT before '20180826235955' ?

 

 

 

Opera Momentaufnahme_2018-08-27_121901_localhost.png

Link to comment
Share on other sites

So from what I gathered by quickly reading through it cos i'm at work 😛 your id will always be unique due to DATETIME but your  "order" must be unique too and i'm guessing you use PHP on the Server Side so just use

$order = date('U');

and insert that as order

Or in straight in mysql

 

INSERT INTO `table` (id, order) VALUES (1, UNIX_TIMESTAMP ( NOW () ) )

 

Link to comment
Share on other sites

3 hours ago, ChaOs said:

Sry, too did not get the msg before I fell to sleep .... it was 5:30 AM here and I thought the day was long enough...

 

This doesn't work anyway .... the Syntax-Highlighter is driving me crazy so freakn hard.

But why SELECT before '20180826235955' ?

 

 

 

Opera Momentaufnahme_2018-08-27_121901_localhost.png

No problem at all. 

 

You can give a default value to a SELECT.  So if you SELECT '20180826235955',  you force the first value of the SELECT query result to be '20180826235955'.

 

There must be something wrong with your phpMyAdmin settings since it's working perfectly in mine.  Can you try running this query in your PHP code to see if it works?

Edited by n3g4n
Link to comment
Share on other sites

6 hours ago, n3g4n said:

No problem at all. 

 

You can give a default value to a SELECT.  So if you SELECT '20180826235955',  you force the first value of the SELECT query result to be '20180826235955'.

 

There must be something wrong with your phpMyAdmin settings since it's working perfectly in mine.  Can you try running this query in your PHP code to see if it works?

Yes this is what I am going to try. And also testing the "lock"-Version ... gonna tell if something changed this way :)

 

Thx for it first btw :)

 

  • Like 2
Link to comment
Share on other sites

Just now, ChaOs said:

Yes this is what I am going to try. And also testing the "lock"-Version ... gonna tell if something changed this way :)

 

Thx for it first btw :)

 

You're welcome! :)

  • Like 1
Link to comment
Share on other sites

On 8/27/2018 at 2:16 PM, Afro. said:

So from what I gathered by quickly reading through it cos i'm at work 😛 your id will always be unique due to DATETIME but your  "order" must be unique too and i'm guessing you use PHP on the Server Side so just use


$order = date('U');

and insert that as order

Or in straight in mysql

 


INSERT INTO `table` (id, order) VALUES (1, UNIX_TIMESTAMP ( NOW () ) )

@Afro.

Yeah, thx ... but the fields are:

 

--------------------------------------------------------------------------------------
| 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 |

were order is 1,2,3,4,5,6, .... and no date, so you can stack the order.

and id is datetime as numeric string.

 

@n3g4n

I have a thought about that the second entry did not work ... because primary & int(255) ... so you get a collision because the biggest possible int (2147483648) was taken two times (the datetime-string is bigger than int255). Maybe that was the reason last time ... but the freakin' syntax parser everytime underlines that ... i'll try your method out again later, because first I tried the lock-version as I got the int problem this time and made the id simply double.

 

@Chuckun

As far you're saying that you're familar with that lock methods and as you said the lock only blocks until the request is done and the unlocking was given ... unfortunately I think the lock method doesn't work at all. It seems to lock nothing.

I can request and request and request and nothing prevents me from that.

 

For this case I wrote a little test-system (zip file) with clear outputs to imagine what is happening....

the 'insert_test_lock_method.php' calls the insert and the 'sql_unittest.php' is a frameset with 10 frames to call the .php-file 10 times with one click within 1-2 seconds ... a little ugly but useful to test this case anyway.

 

    $tbl = '___home_impressum_wordcascade_wordcascade';
        
    $config = array(
        'sql' => array(
            'server' => 'localhost',
            'user' => 'root',
            'password' => '',
            'database' => 'webgen_tkr'
        )
    );

 

those lines should be changed to own db settings in insert_test_lock_method.php

 

@n3g4n

maybe you could look at those way, too ...

 

thank you very much

 

 

 

test.zip

Edited by ChaOs
wrong file
Link to comment
Share on other sites

12 minutes ago, ChaOs said:

I have a thought about that the second entry did not work ... because primary & int(255) ... so you get a collision because the biggest possible int (2147483648) was taken two times (the datetime-string is bigger than int255). Maybe that was the reason last time ... but the freakin' syntax parser everytime underlines that ... i'll try your method out again later, because first I tried the lock-version as I got the int problem this time and made the id simply double.

In the table I created for testing the query I gave you, I had to change the id INT to BIGINT and it worked.  You could try with another id value, of course.

  • Like 2
Link to comment
Share on other sites

8 hours ago, n3g4n said:

In the table I created for testing the query I gave you, I had to change the id INT to BIGINT and it worked.  You could try with another id value, of course.

Yeah. But until I got that the id tried to be similar in this case I had to try the lock-version, because the syntax parser in my PHPmyAdmin, as u saw, everytime cried. So it was nearly impossible to fix up other errors which I didn't see by myself.

Now I go on testing the code by your select max version.

 

Stupid is also that the LOCK-Version seems not to be working. 

But this was the only way that came in my mind to check if it's working.

And by the way ... the lock is the version with the most work, true, because you jump between PHP and mySQL,

but it was also the most hopeful ... if that had worked you could turn around those way every time because it's flexible enough, you know?

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.