Jump to content

Cookie DB


Destramic
 Share

Go to solution Solved by Barand,

Recommended Posts

hey guys im trying to create a cookie storage database...i want to update a cookie/row, but if the cookie doesnt exist i want to insert it a row, like so:

 

UPDATE cookies SET value = 1
                 , expiry = DATE_ADD(NOW(), INTERVAL 22 SECOND)
WHERE user_agent_checksum = 'fffff'
AND name = 'test'
AND domain = 'test'
AND path = '/'
AND expiry > NOW()
AND NOT EXISTS(
INSERT INTO cookies (name, value, path, domain, expiry, user_agent, user_agent_checksum, ip_addess, ip_address_checksum)
             VALUES ('test', 1, '/', 'test', DATE_ADD(NOW(), INTERVAL 22 SECOND), 1, 1 , 1, 1)
)

 

but i get a error:

 

Error Code: 1064. 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 'INSERT INTO cookies (name, value, path, domain, expiry, user_agent, user_agent_c' at line 8

 

so my question is how do i update a cookie/row, but if it doesnt exist then to create a new one please?

 

here is the table structure:

CREATE TABLE `cookies` (
  `cookie_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` blob NOT NULL,
  `value` blob NOT NULL,
  `path` varchar(45) NOT NULL,
  `domain` varchar(45) NOT NULL,
  `ip_address` blob NOT NULL
  `ip_address_checksum` blob NOT NULL,
  `user_agent` blob NOT NULL,
  `user_agent_checksum` blob NOT NULL,
  `expiry` timestamp NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cookie_id`)
)

 

thank you

Link to comment
Share on other sites

13 minutes ago, Barand said:

You are doing it backwards.

You need to insert but if it does exist then update instead.
 

INSERT INTO tablename ( ... ) VALUES (...)
ON DUPLICATE KEY UPDATE ....

https://dev.mysql.com/doc/refman/5.7/en/insert.html

 

thank you for your reply barand, but....

cookie id is the only key in the table which isnt present during the insert causing there never to be a duplicate key and just inserts and not update.

INSERT INTO cookies (name, value, path, domain, expiry, user_agent, user_agent_checksum, ip_address, ip_address_checksum)
             VALUES ('test', 1, '/', 'test', DATE_ADD(NOW(), INTERVAL 22 SECOND), 1, 1 , 1, 1)
ON DUPLICATE KEY
UPDATE value = 1
	 , expiry = DATE_ADD(NOW(), INTERVAL 22 SECOND)

 

i could just insert a new cookie/row regardless if it cookie exists...and the cookies like so?

 

SELECT DISTINCT(name)
	 , value
FROM cookies
WHERE expiry > NOW()
ORDER BY created DESC

 

unless you have a better way :)

 

Link to comment
Share on other sites

Quote

If a TEXT column is indexed, index entry comparisons are space-padded at the end. This means that, if the index requires unique values, duplicate-key errors occur for values that differ only in the number of trailing spaces. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error. This is not true for BLOB columns.

In most respects, you can regard a BLOB column as a VARBINARY column that can be as large as you like. Similarly, you can regard a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHAR in the following ways:

For indexes on BLOB and TEXT columns, you must specify an index prefix length. For CHAR and VARCHAR, a prefix length is optional. See Section 8.3.4, “Column Indexes”.

 

https://dev.mysql.com/doc/refman/5.7/en/blob.html

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

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

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  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.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.