move table row or insert blank row at specific location?

[DiamondLil]

Here's a quickie ...
Is there a way to either insert a blank row at a specific location (after a specified row) or to move a row of values up in a mysql table? Not worried about doing it with php, just straight command line -to avoid having to re-input a whole heck of a lot info because I missed inputting a row...?
-L

 

[sleipnir214]

Just add it to the end. If your query includes an "ORDER BY" clause, the order in which the records were inserted do not matter.
______________________________________________________________________
TANSTAAFL!

 

[DiamondLil]

But I need these values at a specific location not at the end and no, query does not have an order by.

 

[sleipnir214]

We got into this discussion once about renumbering records, didn't we?

If I felt compelled to do so, I would perform mysql_dump to get the queries to repopulate the table, delete all records in the table, edit the dump to add the record, then repopulate.

But you do know that there is no guarantee that the order in which you insert records will be the order in which the server returns them, right? ______________________________________________________________________
TANSTAAFL!

 

[DiamondLil]

Not exactly same discussion - we were talking about auto-increment fields.

I don't have an auto-increment in this table, I'm identifying the records a different way. And yes, even though that is how i currently have it structured to return records, I know that the server doesn't neccessary return records the way you insert them.

 

[sleipnir214]

This discussion and the previous one are just two specific variations on the same theme.

If the order that records are inserted have no bearing on the order they are returned, then talking about inserting a record in a specific place in a table or moving a record to a specific row doesn't mean anything with regard to a database server.

Either that, or I do not understand your question at all. Were you talking about adding a column to a table in a specific place? ______________________________________________________________________
TANSTAAFL!

 

[DiamondLil]

ok - I'm probably just not explaining this properly.

I have a list of the 52 weeks of the year, each with 6 slots for which a user can sign up. The relevant columns across are :Week # Week Ending (date), Theme, Available Slot, Retailer. That's the order in which I'm telling the server to pluck & display the values. It might be easier if you saw it in prog:

http://www.mc2ads.com/liltestretail/retailtest/index.php

Anyway, say as I am putting the information in, I miss adding one of the 6 available slots for that week (say instead of mall, platinum, gold, silver bronze, copper - I skip platinum), if I just add it add the bottom I'll have to figure out some way to hard code it to pluck that particular value and display it in between certain other values in order for the user to be able to see the slot in its proper week. If I want it ordered by the date - which would be the only thing by which I could order it - since there are 6 slots that will have the same date, there still be no way to ensure that the slot appears in the order that it's supposed to.

Does that make any sense?

(I have since gone back and added an auto-increment field).

 

[sleipnir214]

Aha.

I put a suggestion in one of my earlier posts to work around your problem.

I strongly recommend that you use related tables for the data, rather than what must be a flat single table. Something like:

A table to record a week
and
A table to record a slot (your options of copper, gold, etc).
and
A table of retailers
and
A table that relates each of the above three into a tuple.

This makes the adding of record wherever you need them easy. ______________________________________________________________________
TANSTAAFL!

 

[DiamondLil]

drat - I thought I had it covered. I already have 3 tables
for related data - one for password/ login info, one for user (retailer) details, and one for the newsletter sign-up (which is the page you were looking at) If I add any more tables - that's it! I'm going to be completely over my head (which is not to say I'm not already), and it will be necessary for me to find a very tall building and jump.

thread434-381499 (4th post down), that's pretty much how I have things structured. Would you have done it differently?

re: mysql_dump to get the queries to repopulate the table, delete all records in the table, edit the dump to add the record, then repopulate.

Thanks for the solution with that issue. Haven't done that yet - so it may take a little to figure out how. I guess I don't really need to do that for right now....

 

[sleipnir214]

Everything but the mall table. I would use tables to define properties, and another table to tie them all together, as I suggested above.

Think about it. What you want is a record of who's doing what in which slot of which week, right? That's the tuple table. ______________________________________________________________________
TANSTAAFL!

 

[DiamondLil]

hmm..bear with me just another moment...

what is a tuple table - is that the same thing as a lookup table?

"..
A table to record a week and
A table to record a slot (your options of copper, gold, etc)..."

are you saying that ultimately i'm going to have 1 table for every set of 6 slots for every week?

 

[sleipnir214]

I don't know what you mean by a lookup table. And no, you're not going to have 1 table for every set of 6 slots...you're going to have one table. period.

A "tuple" is an arbitrary number of things connected in some way. When a person is talking about her boyfriend and herself as a unit as a "couple", the "couple" is an example of a tuple. In tuple notation, they are a 2-tuple.

Your a row in this table will record that week x, slot y, and merchant z are connected as a 3-tuple.

This way, your other tables define a week of a specific year, a slot, or a merchant. This allows you to reuse information as much as possible. ______________________________________________________________________
TANSTAAFL!