Something loosely like this should do it. But, backup your database first so you can test and go back if needed. OR, even better make a second copy of the table and test on that first…
Change the table, incoming text field and new datetime field as needed.
UPDATE table_name SET datetime_field_name = STR_TO_DATE(date_text_name, '%d/%m/%Y') WHERE id>4
unless you need specific dates/times (if you even have them?)
you can run something like this on your table * (after you create a new column of course… in this example the column name is ‘submitted_date’)
UPDATE calibration SET submitted_date = ADDTIME(submitted_date, calibration_id) WHERE calibration_id > 4;kinda ‘cheats’ and uses the ID column as the time offset to increment the timestamps for you.
Offtopic:
In this unknown state i think it’s not better, nor worse to offer an extra solution over the most obvious. What you don’t know is the same what i don’t know, so for me the minimal approach and an alternate “better” (or maybe not) solution could exist in parallel.
Hi Offtopic, I provided more info, was it not enough? What else do you need to know?
Whispers: in regards to the time, time of submit was not provided, only month/day/year. It appears the addtime() requires some already existing time as part of the first argument, yes?
At this time I used your first example successfully, this is fine as long as nobody submits 2 or more blogs in the same day. What I should do is add the time to the required input so the person adding the new blog will have to input the date and the time, and from this point onwards all will be good (the past blogs are not a problem, there’s never been 2 or more on the same day).
hmm, this just occurred to me - because the existing blogs do not include the time of submission, is it possible that the addtime() can take a variable as the second argument? Can I set up a hidden form field in the php form that grabs the now() date/time and sends it to the db where it is converted with str_to_date()? That would take the date/time entry away from the person entering the blog, letting the computers do the work.
How is your new column set up? timestamp? or just date?
If you set up the table to ‘auto’ add the timestamp upon each row entry… you shouldnt have to do anything… as each save to the database will automatically add a timestamp for you.
Actually I ran @ErnieAlex 's line first… noticed it didnt increment the time, and tweaked his approach a bit. (so I guess technically it DID have a time in there first)
i still would stick to a “sort-column”, instead of faking dates, with an offset like
alter table blogs add column sort int;
update blogs set sort = id + 4;
insert into blogs(title, ..., sort) values
($title1, ..., 1),
($title2, ..., 2),
($title3, ..., 3),
($title4, ..., 4);
DateTime is more ideal, but you are essentially rebuilding a table you don’t have enough info on due to the poor initial design.
The hard part is what date to assign to a record when you don’t know when it was added, correct?
OP has a ‘date’… (no timestamp)… and an ID… which could be used to get an ‘order’ going.
Thanks for the conversation, it’s been very helpful. I have the db fully updated so it displays the existing blogs sorted as they want them. With that completed I have now added a field to the sql insert statement in the php processor page so it includes the mysql now() to the sortOrder column. This way, all new blogs entered will be stamped and sort as those people expect, even if they insert 2 or more blogs in one day. I also set the “date” entry field on their form so it is simply a varchar field so they can enter any form of any date they want to display at the top of the blog and it has no effect on the sorting. (Of course, if they decide to backdate a blog they’ll have to contact me to adjust the sortOrder column in the db.)
cant you just configure the table/column to have a default value of current timestamp (ie: CURRENT_TIMESTAMP) upon a new record entry?
then no worrying about adding anything to any queries?
Are you referring to the user input field? or the field in which I am using now()?
The reason for keeping the user input field is so they can enter any form of the date, different people will use different forms, and they are happy with that - let us use whatever form we want - month/day or month/day/year or day/month/year or whatever they want.
I’m referring to the table itself.
you can set columns up with a default value… in MySQL tables, you an configure the column to have a default value of: CURRENT_TIMESTAMP so that whenever a new record is inserted… the current timestamp automatically gets added.