CODEWITHSUNDEEP
phpmysql
Merlin
Merlin

Reputation: 199

Updating the row with the biggest value

In the "user_id" column of my table, I'd like to insert the ID of the user who just registred from my page. The idea is to associate his recent generated income with the users id, just to spot an eventual double registration of the income.

In order to do this, I tought to update the user_id column, on the row where income_id has the biggest value, i.e. the last generated income, but something isn't working. My code is:

    $query = "SELECT max( id_income ) FROM `affiliate_income`";
$last_income = mysql_query($query, $conn) or die(mysql_error());
$last = mysql_fetch_assoc($last_income);

        $updtsql = "UPDATE affiliate_income SET `id_user`=".$row_user_code['id_user']."WHERE id_income =".$last;
        $result = mysql_query($updtsql, $conn) or die(mysql_error());

any ideas?

Upvotes: 1

Views: 61

Answers (3)

zkanoca
zkanoca

Reputation: 9928

You try to get the max id_income but in the second query (updtsql) you try to find the id_income = array. Besides, you do not put a white space before WHERE clause. 

$query = "SELECT max( id_income ) AS ii FROM `affiliate_income`";
$last_income = mysql_query($query, $conn) or die(mysql_error());
$last = mysql_fetch_assoc($last_income);

$updtsql = "UPDATE affiliate_income SET `id_user`=".$row_user_code['id_user']." WHERE id_income =".$last['ii'];
$result = mysql_query($updtsql, $conn) or die(mysql_error());

Upvotes: 0

웃웃웃웃웃
웃웃웃웃웃

Reputation: 11984

You get the value of $last as array.So you have to giv the query like the following

$updtsql = "UPDATE affiliate_income SET `id_user`=".$row_user_code['id_user']."WHERE id_income =".$last['id_income'];

Upvotes: 0

John Woo
John Woo

Reputation: 263813

Actually you can do it in one query,

UPDATE  affiliate_income a
        INNER JOIN (SELECT MAX(id_income) id_income FROM affiliate_income) b    
           ON a.id_income = b.id_income
SET     a.id_user = 'valueHere'

Upvotes: 1

Related Questions