CODEWITHSUNDEEP
phpmysqlsql
user4715795
user4715795

Reputation:

How to update mysql database, with a variable?

To all experts who is going to read this question, It is a basic problem that cost me more than hours to me,

I want to update previously entered row. As it can be seen in the code, I want to update Improvement column. Also I want to do that with the help of the last user id, which is a auto increment primary key entry. However with the code I wrote down here nothing changes in my Mysql database. 

$sql = "
UPDATE users 
SET Improvement='".$improvement."'
WHERE ORDER BY User_id DESC LIMIT 1 
";  

mysql_query($sql, $accounts);

And as you can see this is my table in mysql,

users   CREATE TABLE `users` (
 `Username` varchar(30) COLLATE latin1_general_ci NOT NULL,
 `Age` varchar(20) COLLATE latin1_general_ci NOT NULL,
 `Education` varchar(20) COLLATE latin1_general_ci NOT NULL,
 `Department` varchar(20) COLLATE latin1_general_ci NOT NULL,
 `Favourite` varchar(20) COLLATE latin1_general_ci NOT NULL,
 `Preference` varchar(20) COLLATE latin1_general_ci NOT NULL,
 `Lpreference` varchar(20) COLLATE latin1_general_ci NOT NULL,
 `Level` varchar(20) COLLATE latin1_general_ci NOT NULL,
 `Question` varchar(20) COLLATE latin1_general_ci NOT NULL,
 `Improvement` int(11) NOT NULL,
 `User_id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`User_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

I believe I cannot update Improvement column because of the syntax. In my page no error occurs, and nothing happens even though $improvement not equal to 0. I echo the $improvement variable it is not equal to 0. But noting changes in my database.

I am desperate for an answer, If it works I will select it as best answer for other people who is facing the same problem.

Upvotes: 0

Views: 163

Answers (4)

mbaula
mbaula

Reputation: 37

Try this out

//find the maximum id

$sql1 = "Select max(user_id) FROM users";
$result1 = mysql_query($sql1);
$row1 = mysql_fetch_array($result1);

$maxid = $row1['user_id'];

//do your update like this 

$sql2 = "UPDATE users SET Improvement='$improvement' WHERE   User_id = '$maxid'";
$result2 = mysql_query($sql2);

The above code should work, please ask if the answer is not clear. i would also recommend you start using mysqli

Upvotes: 0

Saty
Saty

Reputation: 22532

WHERE ORDER BY User_id DESC LIMIT 1 this is wrong in your update query.

use update query like this

 $sql = "UPDATE users SET Improvement='".$improvement."' WHERE   User_id = (SELECT max(User_id) FROM users)";

Read manual update query from here

Upvotes: 1

Sougata Bose
Sougata Bose

Reputation: 31739

Try with - 

$sql = "UPDATE users SET Improvement='".$improvement."' ORDER BY User_id DESC LIMIT 1";  

mysql_query($sql, $accounts);

Upvotes: 1

Andomar
Andomar

Reputation: 238078

It looks like you're trying to update the row with the highest User_id. You can do so with a subquery:

UPDATE  users 
SET     Improvement = 'GIGANORMOUS'
WHERE   User_id =
        (
        SELECT  max(User_id)
        FROM    users
        )

Note that if someone else inserts a row, the latest user will have changed. It might be better to store the newly created user id for later reference. Retrieving the auto_increment column of a newly inserted row can be done with the LAST_INSERT_ID() function.

Upvotes: 0

Related Questions