inserting a record in a specific place?

[garyed]

Is there a way to insert a record in a specific place in a table?

I have about 1000 records in the table I'm working on so lets say I want to add "Jack" in between "Alex" & "Suzie":

Name      Age

Jim          37

Gregg      29

Alex          42

Suzie        63

 

How would I do that?

 

 

[awjudd]

Unless you have a key which you can order by, there is no way of doing what you are wanting.

 

The only way would be to add a "DisplayOrder" column and then fill that in with the corresponding order and then when selecting out of your table, you would add "ORDER BY DisplayOrder" to your query.

 

~awjudd

[cpd]

And you should not use an auto-incrementing PK to do what awjudd is suggesting! Just in-case you were considering it.

[garyed]

I'm really kind of shocked that you can't just insert a record after or before another record.

This problem came about because I was adding the name of some Canadian villages to an existing table in alphabetical order & for some rerason they were not being inserted to the end of the database but about 10 records before the end.  Evidently I deleted about five records in that spot so every time I inserted a new record it went back to the place where the old ones were deleted. What I finally did was insert five dummy records & now when I insert a record it goes at the end of the table.  When I get through with all the entries I'm going to delete the dummy ones & see if that works O.K.

 

 

[cpd]

The way in which data is entered into a database is not particularly important provided each row can be identified independently with a primary key. If you want to order something alphabetically you should use the "ORDER BY" syntax in your SQL statement. Ordering by the village name in an ascending order with the "ASC" syntax will give you the statements in an alphabetical order starting with A; "DESC" will obviously reverse this.

 

Don't get hung up on the order in which your data appears on your screen when you haven't ordered it. Doing this will cause serious aggravation when dealing with 10,000 rows or even worse a million.

[garyed]

Actually what I'm trying to do is populate a drop down menu with a bunch of US or Canadian cities & villages after a particular state or province has been selected.

 

<option value="" selected="selected" style="width:250px;" > ----  </option> 
<?php
// This will populate the drop down menu with the database entries for cities
while ($city_row=mysql_fetch_array($new_city))
{
$state=$city_row["state"];
$city=$city_row["city"];  
?>
<option value="<?php echo $city; ?>" style="width:250px;"> <?php echo $city; ?> </option>
<?php
}
?>

 

 

If they are already in alphabetical order the above code works fine.

If I learn how to use ORDER BY will it work in this instance if they were not in alphabetical order already?

 

[cpd]

If your looking at normalising your database you should separate state and city as the state can be derived from the city, unless you've already done that? When they select a state just filter for all the cities by that state and order it alphabetically with the ORDER BY syntax.

[garyed]

I've got a separate field for state & city in the same table.

After the state is selected then the cities from that state are in the drop down.

[garyed]

Now I've got it,

To sort the cities in Georgia alphabetically:

select * from cities where state="GA" ORDER BY city;

 

cities is the table , state & city are fields.

 

Thanks for the help