MySQL PHP sorting question

php4ever

Hi all, I have a question...I learned PHP on my own so I hope this makes sense.

I created a site at work that is basically a MySQL driven PHP table that allows us to add/delete/modify rows.

The problem is that I have a primary key that is auto indexed, so it goes from 1,2,3,4...etc. When one of the rows gets deleted, it still works fine, because I use what the database has as a # for the row in the form, for when I choose to modify or delete it. Only problem is, the numbers do not get reindexed, and the numbering scheme goes on ..and on..and on...I don't want this to eventually balloon out of hand to some huge gigantic number. Is it difficult to re-number the database in a loop of some sort? It is an extremely..extremely low traffic database. I just want to put a function in one of the pages to renumber the darn thing.

Thanks!!!

Mike

paulnaj

Hi Mike,

Yeah ... I went through this sort of thing as well. In the end, the best thing is to just forget about using the pk as a 'row' number.

All you have to do is just create a SELECT statement that extracts the data in exactly the order you want! To get the 'row' #, merely increment an integer on each pass of the while loop (that calls mysql_fetch_array or whatever), and use that.

Hope that helps.

Paul 🙂

PS. Auto-incrementing an integer guarantees uniqueness and is, er ... automatic, so great for pk's. Not so good for anything else 😉

php4ever

Originally posted by paulnaj
Hi Mike,

Yeah ... I went through this sort of thing as well. In the end, the best thing is to just forget about using the pk as a 'row' number.

All you have to do is just create a SELECT statement that extracts the data in exactly the order you want! To get the 'row' #, merely increment an integer on each pass of the while loop (that calls mysql_fetch_array or whatever), and use that.

Hope that helps.

Paul 🙂

PS. Auto-incrementing an integer guarantees uniqueness and is, er ... automatic, so great for pk's. Not so good for anything else 😉

Ok maybe I am a noob...what is a "pk"? I don't have problem extracting data in the order I want, or even working with the data when I do modify delete update or what not. I do have a counter that auto increments, that helps me label the numbers in the table when I list everthing.

When rows are deleted and added, it still works because my php script will use the row # passed to it to remove it.

So if I remove the "num" in my structure, I can still be able to remove and add them in my script? This is what my table looks like(disregard the junk data in it)

This is a screen shot of the table structure, with the missing 'num'. I wish I can at least make it use #'s that have gone away...But if not, when I do update/delete/etc, do I just be very selective? the other tuples are not guaranteed to be unique.

Mike

paulnaj

Mike,

pk just stood for primary key ... sorry bout that.

The point is, if you select the rows in order ...

<?php
$result = mysql_query('SELECT * FROM mytable ORDER BY num');
$row_number = 1;
while($row = mysql_fetch_array($result){
    echo '<br>row: '.($row_number++).', num: '.$row['num'];
}
?>

... and use an integer to count the rows, you've got what you want and don't have to worry about what happens to the primary key when rows are deleted.

Paul.

php4ever

Originally posted by paulnaj
Mike,

pk just stood for primary key ... sorry bout that.

The point is, if you select the rows in order ...
<?php
$result = mysql_query('SELECT * FROM mytable ORDER BY num');
$row_number = 1;
while($row = mysql_fetch_array($result){
    echo '<br>row: '.($row_number++).', num: '.$row['num'];
}
?>
... and use an integer to count the rows, you've got what you want and don't have to worry about what happens to the primary key when rows are deleted.

Paul. [/B]

Awesome!! I don't know why i didn't think of this before..I think i was using the UPDATE query incorrectly..I made a modification of your script to get this to just update my table =D =D :

//re-sort…

$result = mysql_query('SELECT * FROM apps');
$row_number = 0;
while($row = mysql_fetch_array($result)){
    echo '<br>row: '.($row_number).', num: '.$row['num'];
	$var = $row['num'];
$result2 = mysql_query("UPDATE apps SET num ='$row_number' WHERE num='$var'");
$row_number++;
}

without the echos of course 😉
thanks!!!