0

I'm trying to post values into db but this is getting a bit to tricky for me. Since there gonna be multiple posts with "username" each date I can't make any of these a key value.

So I was thinking, if i put a value called "kay" that throws date combined with user this can be made key since that combined value never will be throwed twice and work as wanted..

But my query wont work, guessing it's a problem posting this as a string?

 INSERT INTO hsm_static (date, username, stats, stats5, stats16,
 stats20, kay) VALUES (CURDATE(), username, 1, 0, 0,0,usernameCURDATE()) ON
 DUPLICATE KEY UPDATE     stats=stats +1 WHERE username=username AND date = CURDATE()

Anyone got a solution? Feels like I've tried everything with no luck.. Thankful for any help!

Petrus Alli
  • 15
  • 5
  • You can create a unique index with more than one column. – frlan Dec 07 '16 at 16:09
  • I'm not sure that you're doing it the right way but to fix your query `usernameCURDATE()` looks wrong, perhaps try `CONCAT(username, CURDATE())` – jonadams51 Dec 07 '16 at 16:15
  • Possible duplicate of [Insert into a MySQL table or update if exists](http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists) – frlan Dec 07 '16 at 17:16
  • @jonadams51 that worked PERFECTLY! – Petrus Alli Dec 07 '16 at 17:17
  • I doubt using concat() is the solution here as the issue sappers to be a primary key based upon two columns + some value stored for it. – frlan Dec 08 '16 at 06:57
  • concat worked, I needed to combine the 2 colum values into the value "kay". CONCAT generated username2016-01-01 (for exempel) exactly as wanted. – Petrus Alli Dec 10 '16 at 07:35

1 Answers1

0

You don't need to introduce a generated variable. MySQL is able to build up unique index over multiple colums.

You should try something like this:

ALTER TABLE `hsm_static ` 
ADD UNIQUE INDEX `hsm_static_uniq_myidx` (`username `, `date`);

You could also use this a PRIMARY, but this might will need some additional thinking on tables referring to hsm_static.

frlan
  • 6,408
  • 3
  • 24
  • 67
  • I'm unfamiliar to the use off UNIQUE INDEX, but I do need to be able to INSERT once a day to a "new" row, and update that row multiple times and as I understand there's no function for this in MySql. As I cant query a "if username and CURDATE() exists in the same row then update instead of insert" or am I wrong? – Petrus Alli Dec 07 '16 at 17:12
  • Oh I see. Than its a duplicated question ;) – frlan Dec 07 '16 at 17:16
  • You say that and still the "duplicated question" do not tell anything about CONCAT that solved my question. – Petrus Alli Dec 07 '16 at 18:41