Now, I want to change that statement, to insert with another extra col, sequence_number. the value to be inserted needs to be queried from the same table and be the highest value + 1 for this category
Where should the select statement go?
insert into image_gallery_headers
( business_id, gallery_category, gallery_header, sequence_number )
values( ?, ?, ?, ig.sequence_number )
on duplicate key
update business_id = ?
, gallery_category = ?
, gallery_header = ?
, sequence_number = ig.sequence_number
where ig.sequence_number = (select max(sequence_number) +1
from image_galleries as ig
where business_id = ?
and gallery_category = ?
and gallery_header=?
)
four cols to be inserted into or updated. (maybe I should just use insert ignore?)
the sequence number cannot be auto_increment. it is a sequence number to control the order in which images display on the web page. when inserting a new record, I want the sequence number to be 1 more than the maximum sequence_number already inserted as a default sequence_number, if you like.
This ensures that new images won’t interfere with a sequence already set up.
I have done a query which just uses insert ignore. how would I add on to that, the on duplicate update section, so as to prevent errors, if the page is refreshed or the submit button clicked twice.
insert ignore into image_gallery_headers
( business_id, gallery_category, gallery_header, sequence_number )
select '477', 'main photo album', 'Test', max(sequence_number)+1
from image_gallery_headers
where business_id = 477
and gallery_category = 'main photo album'
my approach to that problem is to have a default sequence number, so that unless i give the entry a specific sequence number, it’ll get sorted in with all the other entries with the same (default) sequence number, and then, in any SELECT where i have an ORDER BY on sequence, i also have date_added as the secondary sort column, so this MAX()+1 nonsense (which is not peculiar to mysql) can be avoided
the first thing you have to do is figure out which key you expect will be duplicated