Insert 4 new rows before ID:1

MySQL database
#13

So to be clear…

There is NO kind of date/timestamp on these ‘blog entries’… at all?

As everyone has mentioned… this would be (by far) the correct way to do things.

A clear table layout would probably help clear up some questions as well…

So you have an ID column (like row id, that is auto-incremented?) and a other column(s) with other ‘id’ values?

row? sortOrder?

If this is:

  • a custom ‘blog platform’
  • you have access to do whatever you want

Then the correct approach IS to add a new column, date/timestamp… and use that for your sorting and display order.

You can still use a ‘slug’ id for direct linking…etc… but using values (ie: timestamp) that have direct meaning and related to a purpose is much cleaner.

#14

The whole problem here is you are incorrectly depending on sequential ids to determine time order. If there is a post date, use that, if there is not one, add it and then sort by that. Problem solved.

As mentioned, id’s are ONLY unique row identifiers.

#15

My point of confustion is in regards to adding a datestamp. Wouldn’t that give all the blogs the same datestamp for the day the stamp is initialized? For example, if I add a datestamp to all the blogs tomorrow, won’t they all have the same date stamp?

The table columns are id, category, Fname, Fdate, title, paragraph1, story, pic, picAlt, direcotry

In my previous post I mentioned another that might be called SortOrder (it would be a new column). They used the Fname/Fdate fields for uploaded files. They set up one table to handle three different types of posts - blogs, files, and other writings(poems/essays/etc)

There is a field for day/month/year that was set up as a text field, apparently the original designers simply used it for displaying the date of publishing. In the db they appear as text - Nov. 21, 2019. I looked at the datestamp function in mysql but haven’t actually studied it. Can it be used to convert that text form to something usable?

#16

To clarify, you need a Timestamp. That is a date with the time down to the seconds.

#17

Ok, so to get it done so the newly found old blogs appear in the desired position, I should timestamp them first, then go the existing blogs and timestamp them in the appropriate order so they will continue to appear in the desired order on their website.

#18

Yes, that is it exactly!

#19

Excellent, now I have a plan. I’m no sql expert, do you think it is possible to write a sql query that will create the timestamps on the rows with id 5 and above (something that would be run after manually inserting the 4 new rows)?

#20

You can add a new DATETIME column to the table and use STR_TO_DATE() in a single UPDATE query to populate the new column from the existing text column values.

#21

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
#22

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.

#23

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.

#24

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.

#25

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)

#26

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);
#27

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?

#28

OP has a ‘date’… (no timestamp)… and an ID… which could be used to get an ‘order’ going.

#29

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.)

#30

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?

#31

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.

#32

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.

Sponsor our Newsletter | Privacy Policy | Terms of Service