• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

How do I add a row inbetween two existing rows in MySQL?

I am trying to add a row between two other rows of the data.  my first column is Auto increment if that matters.

here is where I am at and it is not working, any suggestions?
$result = mysql_query("INSERT INTO universities2 
					  VALUES (5000, bubah, 3, 0) BETWEEN universityID='8' and universityID='9'");

Open in new window

0
NewWebDesigner
Asked:
NewWebDesigner
  • 3
  • 3
  • 2
  • +1
1 Solution
 
mrjoltcolaCommented:
Depends on what you mean by "between"

If only using universityId, then 8 and 9 are already taken. So you can't. Explain more?
0
 
HainKurtSr. System AnalystCommented:
there is no ordering in table... you query the data aand sort it when you get the data...
please post the table structure...

VALUES (5000, bubah, 3, 0) BETWEEN universityID='8' and universityID='9'");
-->
VALUES (5000, bubah, 3, 0) universityID BETWEEN '8' AND '9'");

but query is not valid :)
there is no such insert statement... maybe you need to insert like this

$result = mysql_query("INSERT INTO universities2 VALUES (5000, bubah, 3, 0);")

but it adds one record.. then when you get the data, use "order by" to sort the data

select * from universities2 order by some_column
0
 
austinstaceCommented:
There is no need in putting data in order in the database. You order the results in your query. BETWEEN is a comparison operator. You would actually use it in a statement like select all orders where the number of products sold is between 5 and 10. Not in an insert statement.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
NewWebDesignerAuthor Commented:
I need to insert another row of data after the 8th row and before the 9th row.  I would like the universityID of this row to be 5000.

Here is my database, I want it to look like this

universityID universityName stateID  notUniversityName
8                             a               1             0
(5000                     f                 1             0)    [this is the row i am trying to enter but can't
9                             b                1            0

0
 
HainKurtSr. System AnalystCommented:
you cannot do this unless you add another column to sort it (say sortid real/float/double)

then

update university2 set sortid=universityid

then insert

$result = mysql_query("INSERT INTO universities2 VALUES (5000, bubah, 3, 0, 8.5);");

then get it like this

select * from university2 order by sortid

:)
0
 
HainKurtSr. System AnalystCommented:
what query are you using to get the data?
0
 
mrjoltcolaCommented:
You cannot accomplish this, because 5000 is not between 8 and 9. Even if it were, MySQL would still store it as it wants to.

The only way you can affect the ordering in a table is by using a clustered index. But you'd need the id to be between 8 and 9, which there is none.

Don't worry about the physical ordering, as the other poster suggested, instead use indexes and order by to affect your results. If you really want to order it, then add an alternate column for ordering.
0
 
austinstaceCommented:
If your universityID is autoincrement, MySQL will set the value. Again, there is no need to put records in order in the database. You order them when you request the records back:

SELECT * from table ORDER BY universityID;

Open in new window


Will give you a list sorted by universityID

SELECT * from table ORDER BY universityName

Open in new window


Will give you the same data but sorted by universityName
0
 
NewWebDesignerAuthor Commented:
thanks for all the responses, let me ponder this.
0
 
austinstaceCommented:
Summing up what we have all said:

You do not need to worry about the order that records are stored a database. You order the records when you request them. In some instances, you may want to be able to order records in an arbitrary order instead of alphabetically or numerically by a field that represents real world information ( university name or year it was established). In that case, you add another field to your table, like display_order. Your table would look something like this:

record_num   universityID   universityName   stateID    notUniversityId   displayOrder


Now, with this structure, you can query the database for all records and order them by any column - alphabetically by universityName, or in the order you specify by displayOrder.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now