position update in the database table

i want to have all my articles position acording to the position number. 1,2,3,4 and so on.

i did it nicely by making a script that looks in the database table articles and counts the number of rows and use that data incremented by 1 as the select options on the add article form. it working and it has no problem.

 <td>Position</td>
      <td><select name="position">
          <?php 
	  //position of the article
					$sql = "SELECT position FROM  article";
					$results = mysql_query($sql) or die(mysql_error);
					$numrow = mysql_num_rows($results);
					for($pos=1; $pos<=$numrow+1; $pos++){
						echo "<option value=\"{$pos}\">{$pos}</option>";
					}
					?>
        </select></td>
    </tr>

now on my edit form i went ahead and desplayed the correct position field on the edit form for editing as bellow.

<td><select name="position">
          <?php 
	  
	  //position of the menu
	 				 $sql = "SELECT position FROM article";
					$results = mysql_query($sql) or die(mysql_error);
					$numrow = mysql_num_rows($results);
					for($pos=1; $pos<=$numrow+1; $pos++){
						echo "<option value=''";
if(($row['position'])==$pos){ echo " selected";}
echo">{$pos}</option>";

					}

					?>
        </select></td>
    </tr>

it also works

but my problem is i had 29 article making my position field having up to 29 position field. if lets say some articles were deleted and i only remained with one article in the table which is the 29th article and in the 29th position, then my logic doesnt work any more. can some one help and show me how tokeep the position field updated automatically after delition or update of the table?

I'm thinking there is a more efficient way to get what you want rather than having such rigid requirements on the position field. But, you can run a single query to update the position field when records are deleted. This will handle when there are gaps in the position and will also reorder if two records have the same position. Which one comes first will be dependent upon the ORDER BY parameters. You must have the ORDER BY use the position first, but then you can add name or something else to determine which record would come first if there were duplicates on the position. But, if you don't supply anything it would most likely be the one created first will come first in the position.

Also, this would only work if there is a unique ID column in your table, which I would assume you have. just replace with your applicable table and field names

UPDATE table_name AS primary
JOIN (SELECT secondary.id, secondary.position,
             @rownum := @rownum + 1 AS new_position
      FROM table_name AS secondary
      JOIN (SELECT @rownum := 0) AS r
      ORDER BY secondary.position) as temp
  ON primary.id = temp.id
SET primary.position = temp.new_position

The double-post on this topic (mysql forum) shows that it is the ID column that is being updated.

My advice is NEVER change the primary key ID field. If you need to know the order the rows were added, use a datetime field and order by that.

The double-post on this topic (mysql forum) shows that it is the ID column that is being updated.

 

My advice is NEVER change the primary key ID field. If you need to know the order the rows were added, use a datetime field and order by that.

am sorry for trying to ask again but it was out of desperacy that i did this. anyway my point is not with the id field, it is with the position field.

psycho has my point

you can run a single query to update the position field when records are deleted

though my problem is puting in practise what you have just showed me in php and mysql concept am new in php can you please give me a simple example of how it should work.