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
mysql and re organising the number entries in a column
Moderator: General Moderators
Perhaps something like this...
Make a copy of your existing table to play around with first tho.
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...
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
}Code: Select all
3
4
7
1
2
3Thanks 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:
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;
}
}
?>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.
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.