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

Depends on what you mean by "between"

If only using universityId, then 8 and 9 are already taken. So you can't. Explain more?

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

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.

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

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

:)

what query are you using to get the data?

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.

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;

Select allOpen in new window

Will give you a list sorted by universityID

SELECT * from table ORDER BY universityName

Select allOpen in new window

Will give you the same data but sorted by universityName

thanks for all the responses, let me ponder this.