CODEWITHSUNDEEP
phpmysql
Malachi
Malachi

Reputation: 977

Need to find highest value in a column in table and increment that

I'm a beginner at php, so please be gentle.. :-) I've looked for answers, but time and time again I get the same error. I can't find the error and it's drivin' me nuts!

$query = "SELECT position FROM pages WHERE subject_id = '$subject_id' ORDER BY position DESC LIMIT 1";
$bla = mysql_query($query, $connection);
$position = $bla[0];
$position += 1;

Ok, the above code is at the top of my page. From what I understand, this should do the following: get the column called 'position' from the table 'pages' where the 'subject_id' matches the subject_id I passed, and sort this and pick 1 value (which would be the highest). So I would now have 1 column with 1 value. I then grab that value, put it in 'position' and increment that with 1.

But the error I get back is: "Column count doesn't match value count at row 1" Again, I'm a beginner (I'm used to another language), so this is probably something small and stupid that I forgot/did wrong.. can anyone help me out?

Upvotes: 1

Views: 148

Answers (2)

a1phanumeric
a1phanumeric

Reputation: 854

You could just change your query to:

$query = "UPDATE pages SET position=position+1 WHERE subject_id = '$subject_id' ORDER BY position DESC LIMIT 1";

then simply run

mysql_query($query, $connection);

Obviously, if you have two subject_ids with the same highest value, only one will update - but the way you have your script set up now would do the same anyway.

Upvotes: -1

Marc B
Marc B

Reputation: 360762

mysql_query() returns a statement handle, not the data you want. You have to fetch a row first:

$bla = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_row($bla);
$position = $row[0] + 1;

Upvotes: 5

Related Questions