PHP and MySQL

[quicknk]

I can't seem to find a good example that does what I'm looking for. For example, once the database table is populated with data, lets say for example i have this:

fields : ID and NAME

data stored is:

1 James

2 Jack

3 Sam

4 John

 

Now that I have some data to work with. I want to use the ID field to manage where the name will be placed when

queried by the ID field from smallest to largest. This is where I come into some problems with making these changes. I'm not what the most effective way is in doing this type of data alteration.

 

basically it may look something like this list bellow for the final listing:

1 John

2 James

3 Jack

4 Sam

 

 

any help would be appreciated.

 

 

[teng84]

try using the auto_increment thing

[quicknk]

right, that's i have set ID to be for MySQL. It auto increments when you add data to this table.

 

But my questions is, once I add data to the table, and I wish to change the value of where it stands when displayed to the end user(s):

• do I need to add a third table field such as LIST_ID that handles the swapping of location in the displayed list?

• Is this possible with just the ID table?

 

 

 

[teng84]

what??/

first primary key are the key that is use for determining what particular record to  view edit and delete

 

now whats ur point

 

[trq]

do I need to add a third table field such as LIST_ID that handles the swapping of location in the displayed list?

 

Yes. If you want to be able to create your own display order you will need another field to order by. You can easily order alphabetically by name using...

 

SELECT name FROM tbl ORDER by name

 

However, for custom order by clauses you will need a custom field.

[quicknk]

thorpe, thanks.

 

so if I create another field, such as LIST_ID (ID, NAME and LIST_ID) and have:

1 James 2

2 Jack 4

3 Sam 3

4 John 1

 

so when displayed to users using LIST_ID they see:

John

James

Sam

Jack

 

 

what code would I use to change the LIST_ID if i want to rearrange the order of the names? say I want Sam to be before John. user will see:

Sam

John

James

Jack

 

but in the table it will be:

1 James 3

2 Jack 4

3 Sam 1

4 John 2

 

 

 

[trq]

You would simply use an update statement to rearrange the LIST_ID's. Its pretty easily achieved if you display all the data in one form.