update or insert in a single query

[showman13]

I need to create a table to track page visits to landing pages on a website
based on the page name / the member ID / the Year / the Month and maintain the count of visits based on each unique combination

Basically, I think I can create a table with 6 fields rec_id | pg_name | mem_id | ct_year | ct_month | count

We have 30 landing pages currently and will be adding more, and need to provide members with how many of each of them has been hit using their individual link in various ways, like YTD / MTD / etc...

What I would like to know before I get started, is if there is a way to do a single query that would check to see if there is a record with the current pg_name/mem_id/ct_year/ct_month combination, and if there is, to simply increment the count by 1, but if there isn't one, then to do an insert of a record with a count of 1

If my logic if flawed, please let me know, and if you can do both in a single query, please let me know that as well

I can't give sample data or anything because I haven't even created the table yet. just want to find out if it is possible to do it before I get started.

Thanks
Douglas

[Ancient Dragon]

maybe something like this: Replacing the * with your own criteria

IF EXITS(select \* from tablename where <clause>)
THEN
   ' increment counter
ELSE
   ' insert new row
END IF

[cereal]

Otherwise use INSERT ... ON DUPLICATE KEY UPDATE, you have to create a unique key index:

ALTER TABLE tablename ADD UNIQUE KEY(pg_name,mem_id,ct_year,ct_month);

Then your insert query becomes:

INSERT INTO tablename (pg_name, mem_id, ct_year, ct_month, count) VALUES('page.html', 1, 2014, 7, 1) ON DUPLICATE KEY UPDATE count = count + 1;

Docs:

• http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

Bye! :)

[showman13]

Anchient Dragon, thanks for the response. Yes, I'm thinking that would work, but I was trying to do it in a single query...
That resolution requires doing a query to determine if a matching record exists, and then doing a second query to either update or insert a new record.

That is what I'll end up doing if I can't do it in a single query, but going to investigate the possibilities first.

Thanks
Douglas

[showman13]

cereal,

at any given time, it would need to check to see if there was a match on all 4 fields pg_name/mem_id/ct_year/ct_month in the table, and if there was then increment, and if not then insert.

Would that not preclude the use of the unique key? Or would/could you set up all 4 as unique and test the combination of them?

thanks
douglas

[cereal]

The unique index works on the combination of all four columns, so it's the equivalent of the select query proposed by Ancient Dragon, if the key combination does not exists there will be an INSERT, else there will be an update. Check this live example:

• http://sqlfiddle.com/#!2/f20c3f/2

Or I'm missing something?

[showman13]

could you explain how the unique key definition works?

  UNIQUE KEY `pg_name` (`pg_name`,`mem_id`,`ct_year`,`ct_month`)

is that naming the unique key as pg_name but making it include all 4 columns?

a little outside the realm of anything I've done, so I appreciate the education on this

Douglas

[showman13]

I know this might be stretching the bounds of reality, but just curious if there is any way to accomplish one more thing within this query.

BTW, that worked perfectly for tracking raw visits to those landing pages.

The other thing I would like to do if at all possible is to get the rec_id that was either inserted or updatedafter that query?

I would like to be able to update it one more time later in the script if someone filled out the form on the page, so we can compare the number of visits against the number of forms filled out.

I assume I could get it as the result of an insert by using mysql_insert_id, but what about if it is an update?

I'd like to just add another column to the table to count the forms completed.
Any great suggestions for that modification?

Thanks in advance.
Douglas

[showman13]

I decided to test it out rather than waiting for an answer...

After the insert / update optional query, I assigned the insert id to a session variable

I added a field to the table to track the number of forms filled out
And on the display script, when they fill out the form, before it is redirected to the members website, when I'm notifying the member through an email, I added an update to the tracking table incrementing the forms field.

tested it out and it works perfectly as well both on inserts and updates...

Thanks for the feedback. I'm glad to have that one worked out so quickly and easily..

Douglas

[cereal]

could you explain how the unique key definition works?

A unique index is like a primary key, in both cases it can be composed by a single column or by multiple columns: in the last case it works like a sorted array. For more information and examples check this:

• http://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html