[SOLVED] Help with PHP MySql query

[tqla]

hello.

 

I want UPDATE SET a particular row's 'position' field to 1 higher than the highest record.

 

In other words, I want to look at a numeric field in my DB called 'position'.

"SELECT position FROM $content"

 

I then want to find out what the highest number is of all of the records.

Whats the highest number in the 'position' field?

 

I then want to update the current row to 1 higher than the highest number in 'position':

"UPDATE $content SET position = (some code here I think) WHERE page_id = $ID

 

I know this aint the right code but I was wondering if someone could help out. Thanks.

[trq]

UPDATE $content SET position = (SELECT MAX(position)+1) WHERE page_id = $ID;

[tqla]

Thanks thorpe but it seems to be ignoring the highest number and just increments by 1. Here's the code:

 

$ID = $_GET['page_id'];
$sql = "UPDATE $content SET position = (SELECT MAX(position)+1) WHERE page_id = $ID;";
$result = mysql_query($sql) or die (mysql_error());

 

My highest number in position is 6 but the $ID doesn't go from 1 to 7, it just goes to 2.  ???

[tqla]

Solved! Thanks for pointing me in the right direction thorpe! In case anyone needs to know, here's the solution:

 

<?php

$result = mysql_query("SELECT MAX(position) AS maxposition FROM $content");

$row = mysql_fetch_array($result);

$p = $row['maxposition'];

$ID = $_GET['page_id'];

$sql = "UPDATE $content SET position = $p+1 WHERE page_id = $ID;";

$result = mysql_query($sql2) or die (mysql_error());

?>

[tqla]

ooops. change $result line from $sql2 to $sql like this:

$result = mysql_query($sql) or die (mysql_error());