Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-11-21
22
Medium Priority
?
842 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 4
  • 3
  • +4
22 Comments
 
LVL 31

Accepted Solution

by:
Marco Gasi earned 1600 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 35

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 31

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 31

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 400 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
 

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 31

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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…
Suggested Courses

604 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