Insert 4 new rows before ID:1

MySQL database
#1

I have a db that has blogs, the oldest is ID 1 and then every blog since has incremented, there are aprox 600 entries. Now I have received 4 blogs which were found and are dated before the existing oldest (row ID 1). I’m supposed to insert those into the db before row ID 1 so the historical order is maintained.

I thought maybe exporting and using Excel to renumber the existing rows, but when I try exporting as sql or csv the column that contains the row number also contains a parenthesis (in the csv, and even more extra data in the sql).

How can I insert 4 new rows before row ID 1?

#2

An ID is a unique identifier, and beyond it’s nature of frquently coming in the form of an incrementing interger, thats not a property to rely on. Even the database must not give you data in this order due to optimizations. If you want to sort by a historical value, use a historical value like datetime, or generate and independent column with an integer value named 'sort' or whatever.

2 Likes
#3

Just write a script to do it. Run a query to get all the id’s. Make the last one +4, in other words, change the ID number on the last one and add four to it. Move all the way up the list from last-to-first, and the #1 ID would then become #5 and you have room to manually add in the new first four. Should be simple enough to do.

But, I do agree with Chorn! Normally, you use datetime fields for blogs and sort everything on that so the id of the row is not important!

#4

If you are already working within an existing project (database/data)

Then I think you should continue along your new ‘import’ approach.

You can clean up those quotes and anything else easily a number of ways.

#5

It isn’t worth doing. Your historical order should be implemented using the date it was created, not the id itself. If it was discovered that someone was born before you and never applied for a social security number, they don’t change what yours is to accommodate it, this is the same principle. For all intents and purposes the only reason the ID exists is for identifying that record in the database, nothing more nothing less.

1 Like
#6

If that is how his ‘whatever’ app/blog works… sure…

but, IMHO… as explained… the ID is the factor.

OP makes no mention of anything ‘date related’…

So is this some existing app/platform that DOES work the way the OP explained it does?

Or this something the OP has total control over?

The OP’s question was about how to correct/fix the import…

#7

The id is nothing more than an unique identifier. It makes sure the data row is unique the table. That is the end of its usefulness.

I asume it that in the database table there is a column that has the publication date for blog posts? Then you already have a way to maintain the historical order. When retrieving the data to show on the website, just order the resultset by the publication date.

When you start changing id’s you don’t know what side effects there will be. For example, if the idea for a blogpost is part of the URLS, then any shared urls will stop working or show the wrong data.

#8

If it works by the ID then it is a bad design, which is frankly a rampant problem with PHP developers and why the PHP language has the bad name it does. And if the OP has control over the database and is posting in a PHP forum he obviously has access to the code, which means he can properly fix it.

I have worked for too many company’s that have your belief system of, just throw a band aid on it to fix it. That doesn’t fix the problem, it doesn’t even mitigate it. The proper thing to do is to ACTUALLY fix the issue and use proper strategy to do so.

As I said, the ID is a unique identifier for use by the database, it has nothing to do with the actual content of the record it references. Historical means dates, not an arbitrary number that was assigned when it was inserted into the system.

1 Like
#9

I’m not saying that that approach is NOT a bad design… (dont confuse or allude to that I am championing for this type of approach)… but we dont know. We only know what the OP tells us… and what they ask.

All these assumptions mean absolutely nothing, unless/until the OP clarifies or states what ‘blog’ platform this is.

We have ZERO proof of anything on how his database/system works… (ordered by ID or timestamp)… and that was not the question either.

#10

True, but it then becomes an XYZ problem rather than a rational thought process on how to solve the underlying issue.

#11

I agree… but I would also say that the majority of that responsibility is on the OP.

We can really only offer help and/or advice on what is provided to us… nothing more.

#12

All interesting comments, thanks guys.
the blog platform in question is a house-made website, it doesn’t use any “canned” scripts other than one javascript for an entirely unrelated function.

The db/website is one I inherited. When the blogs were originally input they started data entry with the oldest first, thus id 1. Now I’ve been given 4 older blogs someone found and predate the first one in the db. Therefore they want to have them listed in the site before the current id1The blogs are listed on the site by the id in reverse order so the newest input is also the newest blog written, and so it goes so 1 is the oldest.

I suppose it will be easiest to add another column as was suggested by chorn, the new old blogs would be something like id 585 row 621 SortOrder 1 / id 586 row 622 SortOrder 2 / etc then the existing id1 row1 would change to id1 row 1 SortOrder 5, etc. hmm, I think I’ve got that straight. The new old blogs will have the biggest id and row numbers but the smallest SortOrder numbers. Then the next time a new blog is added it will be something like id 589 row 625 SortOrder 550 (whatever the numbers happen to work out to). At that point the latest/newest blog will have the biggest id/row/SortOrder numbers as it should.
hehehe, if you can figure all that out more power to you. :slight_smile:

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

Sponsor our Newsletter | Privacy Policy | Terms of Service