mysql and re organising the number entries in a column

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

mysql and re organising the number entries in a column

Post by mikebr »

I want to get the values from a non auto increment int column 'entry_num' and then update them in order of 1, 2, 3, 4 starting from the lowest entry number, so if the lowest entry number of the 'blue' column was 2 I need to set this to 1, then if the next entry of the 'blue' column is 4 set this to 2 etc. Even if there is a repeat of say 4 in the 'blue' column it would then be set to 3

Can anyone suggest a way of doing this? I am using php4

Thanks
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Perhaps something like this...
Make a copy of your existing table to play around with first tho. ;)

Code: Select all

$result = mysql_query("select value from test where info = 'blue' order by value"); // sorting by value, making them come 2, 5, 7, 47 etc...
    $i = 0; // start counter
    while ($row = mysql_fetch_assoc($result)) {
        echo $row['value'].'<br />'; // debugging
        $i++; // incr. counter
        mysql_query("update test set value = $i where info = 'blue' and value = $row[value]"); // update using the old value as reference
    }

    // debugging changes...
    $result = mysql_query("select value from test where info = 'blue' order by value");
    while ($row = mysql_fetch_assoc($result)) {
        echo $row['value'].'<br />'; // debugging changes
    }
I got the following result, when I tried the above using only 3 rows in my database with values 3, 4 and 7. Result went 1, 2 and 3...

Code: Select all

3
4
7
1
2
3
mikebr
Forum Contributor
Posts: 243
Joined: Sat Sep 28, 2002 7:05 am

Post by mikebr »

Thanks Jam, Sorry, I didn't explain myself clearly the firt time, I would like to perform the action in one query if where possible, at the moment I do it a similer way to you suggest. I have used the variable names that I actually use, I replaced them in the first example as I thought it would make it simpler.

At the moment I do it like this:

Code: Select all

<?php
$query = "SELECT * FROM table WHERE category = '$category' AND entry = 'credit'";

$mysql_result=mysql_query($query,$connection);
$num_rows=@mysql_num_rows($mysql_result);
					
if ($num_rows > 0) {

# -- GET THE ID NUMBERS FOR ALL THE ENTRIES OF THAT CATEGORY IN ORDER OF entry_order
			
$query = mysql_query("SELECT ID FROM table WHERE category = '$category' ORDER BY entry_order");	
			
# -- SET THE ORDER OF entry_order NUMBERS FOR ALL THE ENTRIES OF THAT CATEGORY

$thenum = 1;
			
while ($entryno = mysql_fetch_row($query)) {
	for ($i=0, $count=sizeof($entryno); $i < $count; $i++) {
	$theid = $entryno[$i].' ';

	$sql= "UPDATE table SET entry_order=$thenum WHERE ID = $theid AND category = '$category' AND entry = '$entry'";
		
		if (!mysql_query($sql,$connection)) {
		$reordered = "";
		} else {
		$reordered = "<BR />The order was reorganised.";
		}
		
	$thenum = $thenum+1;
	
	}		
}		
?>
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Ah, oh...
I do see the similarity. But I do not know how to make this possible using one query only. Doesn't mean that there is no way tho...

In theory, using GROUP BY <field> and LIMIT <offset>, <offset> should do the trick, but apparently LIMIT is not working as I want when using update.

Later versions of mysql can manage mysql_query(<query>; <query) I belive, so that might be worth looking into also.

Good luck.
Post Reply