Solved

is there a way to reorder mysql records from php or adjust correctly

Posted on 2010-11-21
22
783 Views
Last Modified: 2013-12-12
is there a way after you delete a record(row) from mysql with php to correct the gap lets say


lets say we have records(rows) of

ID        name   age
 1        Johnny   45
 2        Linda      52
 3        Janet      64
 4        Bob         22
 5        John       72

if i goto insert a record(row now the next one would be 6

if i delete bobs info row 4
then a gap will be in row 4 and the counter for the next row will still be at 6

how can i via php fix this. so the records(rows) are uniform and the id column is intact and in order?

thank you in advance for any code or help you may provide
0
Comment
Question by:Johnny
  • 11
  • 4
  • 3
  • +4
22 Comments
 
LVL 30

Accepted Solution

by:
Marco Gasi earned 400 total points
ID: 34182334
Run this query

mysql_query("ALTER TABLE yourTable AUTO_INCREMENT = 1");

after deleting query: alter table "table_name" auto_increment=1 resets auto_increment to 1 + max(auto_increment)

Cheers
0
 
LVL 34

Expert Comment

by:gr8gonzo
ID: 34182620
This is actually proper, intentional database design.

Keep in mind that when you're dealing with databases where tables connect with each other by relying on joining / matching up IDs, you don't WANT the IDs to change. Let's say you delete "Bob" (ID 4), and "John" now gets reordered to be 4, and your next person now becomes 5. That sounds fine until you think about any other database tables that depend on John being 5. Let's say you had another table that held John's order history. Inside that table, it identifies John by using ID 5. If you reorder the table containing the people, then the order history table isn't going to match up. Suddenly John is going to have Bob's order history and someone else will have John's and it's all a big confusing mess.

You could technically reorder EVERY bit of data, but that's pretty intensive work, and unnecessary. Your database knows how to find things properly, regardless of any gaps. Gaps are normal in almost every database. It's just a normal part of database activity.

0
 
LVL 10

Expert Comment

by:aboo_s
ID: 34182667
If ID is primary key then this is the right thing to happen.

The solution is simple, with the while loop that shows the contents echo the line number:
$i=1;
while(...)
{
...
echo $i;
echo ...
...
$i++;
}

The ID is a specific identification number that is usually not used for just numbering but other specific info, for instance if you want to know how many rows were inserted including the ones deleted then just get the ID of the last one(for example)!!!
0
 
LVL 3

Expert Comment

by:boraganesh
ID: 34182706
@Pern is the very bad idea to reset he auto increment to fill the gap between the deleted records. as we are using these records for external referance..
please think if we have deleted record id 4 and there are some records present in other table related to record id 4. but in after deletion we are creating nerw record having id 4 so these other record gets refernce of this older record..
0
 
LVL 9

Expert Comment

by:Shahzad Fateh Ali
ID: 34182766
Hi Pern,

Use the query below, replace t.* with your required column names and <table_name> with the actual table name. now the 'custom_id' will always give you a runtime generated id for every row.

SELECT @i:=@i+1 AS 'custom_id', t.* FROM <table_name> t, (SELECT @i:=0) k;

Open in new window

0
 

Author Comment

by:Johnny
ID: 34183135
im using the ID as how many records there are, ive made this database. and when i delete bob i have the table data printing out using ID as the record number so when theres a gap the display looks funny and is not the correct count. i have no other tables in this case relying on the id as a placeholder or pointer at all.

0
 

Author Comment

by:Johnny
ID: 34183174
@marqusG (and others)

not what i was looking for (mysql_query("ALTER TABLE yourTable AUTO_INCREMENT = 1");)

i have wrong data in a table see
+ Options





id	event_title	url	event_start	event_stop	event_body
			208	Easter Breakfast	 	2010-04-04 09:15:00	2010-04-04 10:15:00	Join us for a great meal to help the Youth
			3	Worship Service	 	2010-01-03 08:00:00	2010-01-03 09:00:00	Every Sunday
			5	Worship Service	 	2010-01-03 11:00:00	2010-01-03 12:00:00	Every Sunday
			6	Elder`s Meeting	 	2010-01-04 07:00:00	2010-01-04 08:00:00	First Monday of Each Month
			7	Bible Study@ Fullers	 	2010-01-11 19:30:00	2010-01-11 20:30:00	White Banks Rd, Middleboro, Ma Every Monday evenin...

Open in new window


the id needs to be 1,2,3,4,5,6,7 etc

and its all fubar, from deleting random lines(rows,records)

so i need to run a sql query that reorders id from 1 to (last end of row)

please bare in mind thank you all for pointing out reason for not doing this mainly if the table is linked, in this case it is not
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 34183222
Hi, Pern, let me understand: do you want reorder id or change them to get a ordered sequences?
In other words, you want reoder the entire recordset or do you wnat only "renumber" id column? And id column is auto-increment? If it is, can you change it to make it not auto-increment?
0
 

Author Comment

by:Johnny
ID: 34183247
i think i got it

`events2_test` is the table name
ALTER TABLE `events2_test` DROP `id`;
ALTER TABLE `events2_test` AUTO_INCREMENT = 1;
ALTER TABLE `events2_test` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Open in new window



heres the new output (reordered)
 
id	event_title	url	event_start	event_stop	event_body
1	Easter Breakfast	 	2010-04-04 09:15:00	2010-04-04 10:15:00	Join us for a great meal to help the Youth
2	Worship Service	 	2010-01-03 08:00:00	2010-01-03 09:00:00	Every Sunday
3	Worship Service	 	2010-01-03 11:00:00	2010-01-03 12:00:00	Every Sunday
4	Elder`s Meeting	 	2010-01-04 07:00:00	2010-01-04 08:00:00	First Monday of Each Month
5	Bible Study@ Fullers	 	2010-01-11 19:30:00	2010-01-11 20:30:00	White Banks Rd, Middleboro, Ma Every Monday evenin...
6	Men of Faith  Breakfast & Bible Study	 	2010-01-05 06:00:00	2010-01-05 07:00:00	Every first & third Tuesday of each month
7	Prayer Team	 	2010-01-05 19:00:00	2010-01-05 20:00:00	First Tuesday of each month

Open in new window


please note as others have said
that this will ruin any relationships you may have with any other tables.

but you basically:
 drop the primary key column "id"
 reset auto_increment = 1 (rolling back to beginning)
 re-create the primary key column "id"
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 34183254
Perfect, you got it! Cheers
0
 
LVL 9

Assisted Solution

by:Shahzad Fateh Ali
Shahzad Fateh Ali earned 100 total points
ID: 34183279
Hi Pern,

You are doing same thing by altering the actual table but if you run the query mentioned in my last comment you will not need to alter your table and you can keep any existing relationship.
Here is the actual modified query based on your table.
SELECT @i:=@i+1 AS 'id', t.event_title, t.url, t.event_start, t.event_stop, t.event_body FROM `events2_test` t, (SELECT @i:=0) k;

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Johnny
ID: 34183280
ok now how do i award the points here

@marqusG
i would not of gotten this if you didnt seed the idea to look up your first posts with mysql_query("ALTER TABLE yourTable AUTO_INCREMENT = 1"); this got me looking up Google ideas and i finally got a site that said to drop the column and set auto increment and recreate the column, i then made the code to do that.

so what do i do accept multiple and give you credit or do i just accept mine as answer.

id like others o see this when they search i think its valuable information.

any recommendations here?
0
 

Author Comment

by:Johnny
ID: 34183287
@shahzadfateh…

i dont have any relationships right now. but thanks so much for making a way to get around if i do (or anyone else does)
0
 
LVL 30

Expert Comment

by:Marco Gasi
ID: 34183298
Hi Pern, I'm glad if my answer was useful, but I don't think to deserve points this time.
Good luck with your project.

Cheers
0
 

Author Comment

by:Johnny
ID: 34183301
then im going to accept my solution and award the points to me,


and thank everyone for there comments and directions that this question put me into, i learned a few things to look for in the future and how to solve my problem.

thanks again
0
 

Author Comment

by:Johnny
ID: 34183326
@shahzadfateh…
id like to understand your solution a bit better please, please see new question
new question

0
 
LVL 9

Expert Comment

by:Shahzad Fateh Ali
ID: 34183388
As your solution is based on the inputs of some posts, I think you must give the shares to those whom you've followed to develop your own solution.
0
 
LVL 1

Expert Comment

by:warhero
ID: 34183416
I hoping you got what you are looking for, however i getting the feeling that ruining the relationship with other tables are going to be a problem for you in the future.

As for a solution:

I am assuming that you are viewing the database table in a PHP page, so i could recommend using id as pointer like attached
echo "ID, Title, URL, Start, Stop, etc";
$result = mysql_query($sql);
for(int i = 0; i < mysql_fetch_assoc($result); i++){
echo i + ","+$result['event_title']+",".... and so on;
}

Open in new window


so do not retrieve the ID data on to the page just the others and you would be able to order id's or start and end of events (which you have to append on the sql query)

Hope this helps tooo
0
 

Author Comment

by:Johnny
ID: 34183591
upon review ill cancel the close

and award points to marqusG for 400
 and shahzadfateh… for the output solution

again thanks all for your help

0
 

Author Comment

by:Johnny
ID: 34183593
i would like to award points should have in the first place
0
 

Author Closing Comment

by:Johnny
ID: 34183599
one more time in thanking you all for the help
0
 

Author Comment

by:Johnny
ID: 34183603
sorry warhero but i didnt see your suggestion till after thanks for an alternate way to display the info
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now