Re: [PHP-DB] Authomatic Sorting
When you delete a record it doesn't affect the other ones, so if you want to change the other ones you have to update them too. For this purpose I use the following SQL command after deleting a row: UPDATE table SET id_field=id_field-1 where id_fielddeleted_ID_value I hope this will help Dobromir Velev -Original Message- From: Wilmar Pérez [EMAIL PROTECTED] To: PHP List [EMAIL PROTECTED] Date: Wednesday, July 04, 2001 11:45 PM Subject: [PHP-DB] Authomatic Sorting Hello guys I have the following two problems: I've got a table with an autoincrement field which is the registry's ID, everything goes well until I delete any registry. MySQL doesn't re-sorts the information. That is, I have the following: 001 user1 002 user2 003 user3 If I delete user2, I would like to have something as shown next: 001 user1 002 user3 But instead I end up with the following: 001 user1 003 user3 Any idea or comment? --- Wilmar Pérez IT Manager - Central Library University of Antioquia Medellín - Colombia tel: ++57(4)2105145 --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Authomatic Sorting
HI, Look into Optimizing the Table.. it seems to clean up the holes. Ken - Original Message - From: Wilmar Pérez [EMAIL PROTECTED] To: PHP List [EMAIL PROTECTED] Sent: Wednesday, July 04, 2001 1:50 PM Subject: [PHP-DB] Authomatic Sorting Hello guys I have the following two problems: I've got a table with an autoincrement field which is the registry's ID, everything goes well until I delete any registry. MySQL doesn't re-sorts the information. That is, I have the following: 001 user1 002 user2 003 user3 If I delete user2, I would like to have something as shown next: 001 user1 002 user3 But instead I end up with the following: 001 user1 003 user3 Any idea or comment? --- Wilmar Pérez IT Manager - Central Library University of Antioquia Medellín - Colombia tel: ++57(4)2105145 --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Authomatic Sorting
Well, my comment on this is that the autoincrement field is giving you a primary key which is not changing, and this is good. Check the scripts you used to build the tables, and your MySQL docs for the MyISAM table type. When tables are defined as being of the type MyISAM, the autoincrement numbers don't change. In the newer versions of MySQL that may be the default and you will have to add tell MySQL to NOT use this table type. I don't know why you want to do this, as an unchanging, and really simple-to-implement primary key is a very useful feature, particularly as you can any sort order you desire in your SELECT statement. Regards - Miles Thompson At 10:10 AM 7/5/01 +0300, Dobromir Velev wrote: When you delete a record it doesn't affect the other ones, so if you want to change the other ones you have to update them too. For this purpose I use the following SQL command after deleting a row: UPDATE table SET id_field=id_field-1 where id_fielddeleted_ID_value I hope this will help Dobromir Velev -Original Message- From: Wilmar Pérez [EMAIL PROTECTED] To: PHP List [EMAIL PROTECTED] Date: Wednesday, July 04, 2001 11:45 PM Subject: [PHP-DB] Authomatic Sorting Hello guys I have the following two problems: I've got a table with an autoincrement field which is the registry's ID, everything goes well until I delete any registry. MySQL doesn't re-sorts the information. That is, I have the following: 001 user1 002 user2 003 user3 If I delete user2, I would like to have something as shown next: 001 user1 002 user3 But instead I end up with the following: 001 user1 003 user3 Any idea or comment? --- Wilmar Pérez IT Manager - Central Library University of Antioquia Medellín - Colombia tel: ++57(4)2105145 --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Authomatic Sorting
on 7/4/01 1:50 PM, Wilmar Pérez at [EMAIL PROTECTED] wrote: I've got a table with an autoincrement field which is the registry's ID, everything goes well until I delete any registry. MySQL doesn't re-sorts the information. That is, I have the following: snip Just add an ORDER BY clause to your query. That is, if you want to sort by reg_id, append ORDER BY reg_id. You can also add a DESC keyword to put them in reverse order. HTH. Sincerely, Paul Burney ++ Paul Burney Webmaster Open Source Developer UCLA - GSEIS - ETU (310) 825-8365 [EMAIL PROTECTED] http://www.gseis.ucla.edu/ ++ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Authomatic Sorting
D you ever create an index on the primary key to speed things up?? ken - Original Message - From: Paul Burney [EMAIL PROTECTED] To: Wilmar Pérez [EMAIL PROTECTED]; PHP List [EMAIL PROTECTED] Sent: Wednesday, July 04, 2001 3:18 PM Subject: Re: [PHP-DB] Authomatic Sorting on 7/4/01 1:50 PM, Wilmar Pérez at [EMAIL PROTECTED] wrote: I've got a table with an autoincrement field which is the registry's ID, everything goes well until I delete any registry. MySQL doesn't re-sorts the information. That is, I have the following: snip Just add an ORDER BY clause to your query. That is, if you want to sort by reg_id, append ORDER BY reg_id. You can also add a DESC keyword to put them in reverse order. HTH. Sincerely, Paul Burney ++ Paul Burney Webmaster Open Source Developer UCLA - GSEIS - ETU (310) 825-8365 [EMAIL PROTECTED] http://www.gseis.ucla.edu/ ++ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP-DB] Authomatic Sorting
The 'problem' here is the logic of sql databases.
you have one id and one 'username' and they belong to eachother.
id 1 - user 1
id n - user n
meaning if you delete user 23, only id 23 is affected. If you want the
userid 'resorted' you will have to issue a few commands (which also would
destroy the logic of having the id column at all)
alter table mytable drop idcolumn;
alter table mytable add idcolumn unsigned int not null auto_increment
[primary key];
add primary key if it is the primary key.
I would rather suggest you dropped the 001 002 003 column, and used php to
keep count.
example
user001
user002
user0..
user075 (lets say you have 65 users total and that somewhere in 30-50 you
have been deleting)
$res = mysql_query(select username from mytable);
$num = mysql_num_rows($res); // this would give 65
$i = 1;
while($row = mysql_fetch_row($res)) {
echo user id $i - username .$row[0]; $i++;
}
this would print a list from 001 - 065 with every username in it.
Mats Remman
PHP Developer/Mysql DBA
Coretrek, Norway
+47 51978597 / +47 916 23566
-Original Message-
From: Wilmar Pérez [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 04, 2001 10:50 PM
To: PHP List
Subject: [PHP-DB] Authomatic Sorting
Hello guys
I have the following two problems:
I've got a table with an autoincrement field which is the registry's ID,
everything goes well until I delete any registry. MySQL doesn't re-sorts
the information. That is, I have the following:
001 user1
002 user2
003 user3
If I delete user2, I would like to have something as shown next:
001 user1
002 user3
But instead I end up with the following:
001 user1
003 user3
Any idea or comment?
---
Wilmar Pérez
IT Manager - Central Library
University of Antioquia
Medellín - Colombia
tel: ++57(4)2105145
---
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
