Find the highest value of a row, and update DB

[klemme]

Hi all,

I am updating the position og pages, which I get dynimacally from the DB.

It is d possible via the backend, for the admin of the site, to decide which position a page should have, when updating or creating a new page for the site.

I let the admin write the position in a textfield, whch then updates the database, and the positions in that specific row.

BUT, lets say there is only 3 ages under the subject which admin is either editing or creating a new page for. The admin can by mistake enter the position of a page to be lets say, position: 7 - And with less pages for that subject, I will have gaps in the position.

So I want to do the following:

If there is 3 pages, and admin writes a position number, which value is 2 or above, than the actual number of pages - Then I want to make that position number equal to the highest value, and +1, so it is the highest value, but leaving no gaps.

Can I do something like this:
Only showing the relevant bit..

$position = $_POST['position'];
$subjectid = $_POST['subjectid'];

// Here I want to check if the value of $position is 2 or more above the actual values in the DB..

$result = mysqli_query("SELECT * FROM PAGES WHERE subjectid = $subjectid");

$NumberOfRows = mysqli_num_rows($connection, $result);

// Isnt that the way to get the highest value from that specific row??

// HOW CAN I WRITE THIS BELOW????

if($position // is +2> (2 or higher than) $NumberOfRows) {

$position = $NumberOfRows; 

// Does this give me ONE value in $position (The highest)?

$query = ($connection, "INSERT INTO pages pagetitle, linklabel, subjectid, position, showing, description, keywords, pagebody) VALUES
('$pagetitle', '$linklabel' '$subjectid', '$position', '$showing', '$description', '$keywords', '$pagebody'");

// And then update the position after this query?

$QueryAdjustPositionOneUp = ($connection, "UPDATE pages SET position = position + 1 WHERE position = $position AND subjectid = $subjectid");
}

[diafol]

I thought we did all that here:

http://www.daniweb.com/web-development/php/threads/359091

Why not just check the MAX number for position for a particular subject. You then check the textbox value.If it is > max, give it a value of max + 1.

[klemme]

Thanks for answering,

Yeah all that is working fine, but if admin enters a value more than one above the actual pages, I am asking how I get the highest value..So I can compare -

Is mysqli_num_rows returning just ONE value - Say, 3 if the number of pages is 3.

Or does it return: 1, 2, 3 (3 values)? So I have 3 values in the variable..

Basic stuff, forgive me - I am learning at a slow speed, but I remember what I learn though ;-)

[diafol]

I said use MAX() in your sql call to get the total.

"SELECT MAX(`pos`) AS maxpos FROM `articles` WHERE article_id = $art_id"

[klemme]

Thanks,

I didnt know MAX was an sql statement, just what I was looking for!

Thank you for helping out!

[sqlhelperhelps]

Student Table:

SNAME ENGLISH TAMIL MATHS EID
Test 65 85 96 1
Test1 56 85 69 2
Test2 100 45 60 3

Need to find out the each student's highest mark in each subject..


O/p should be:
*.........................*
Ename  Mark
Test   96
Test1  85
Test2  100
*.........................*
please help out ...
Thanks in advance

[diafol]

Try:

$result = mysql_query("SELECT SNAME,ENGLISH,TAMIL,MATHS FROM Student");
$out = "No data";
if(mysql_num_rows($result)){
    $out = "<table><thead><tr><th>Ename</th><th>Mark</th></tr></thead><tbody>";
    while($data = mysql_fetch_assoc($result)){
        $out .= "<tr><td>{$data['SNAME'}]</td><td>" .max($data['ENGLISH'],$data['TAMIL'],$data['MATHS']) . "</td></tr>";
    }
    $out .= "</tbody></table>";
}
...
echo $out;