Update A Row Mysql In Php

I have a table that looks like this: name surname username password role student student student@csd.auth.gr student student student2 student2 stu

Solution 1:

There is a lot going on here.

1. Always us a integer as a primary key, example: id MEDIUMINT NOT NULL AUTO_INCREMENT. Then make it the primary key.

2. you need to sanitize your input to the database using mysql_real_escape_string()

3. you need to concatenate your query, so it should look like this:

   mysql_query("UPDATE user SET username = '".mysql_real_escape_string($_POST[nusername])."' SET password = '".mysql_real_escape_string($_POST[npassword])."' SET name = '".mysql_real_escape_string($_POST[nname])."' SET surname = '".mysql_real_escape_string($_POST[nsurname])."' SET role = '".mysql_real_escape_string($_POST[nrole])."' WHERE username='".mysql_real_escape_string($_POST[us])."'");

Here is corrected code:

<?php$hostname = "localhost"; 
$database = "mydb"; 
$username = "myuser"; 
$password = "mypsw";
$link = mysql_connect( $hostname , $username , $password ) ordie("Prosoxi!Provlima stin sundesi me ton server : " . mysql_error());
mysql_select_db($database,$link);

mysql_query("UPDATE user 
         SET username = '".mysql_real_escape_string($_POST['nusername'])."', 
         SET password = '".mysql_real_escape_string($_POST['npassword'])."', 
         SET name = '".mysql_real_escape_string($_POST['nname'])."', 
         SET surname = '".mysql_real_escape_string($_POST['nsurname'])."', 
         SET role = '".mysql_real_escape_string($_POST['nrole'])."' 
         WHERE username='".mysql_real_escape_string($_POST['us'])."'");

mysql_close($link);
header("Location: users.php");
?>

notice the single quote surrounding the _POST var, $_POST['nusername'] you had $_POST[nusername].

Try it now, and see if it updates.

Solution 2:

$query =  mysql_query("UPDATE user 
                SET username = '" .mysql_escape_string($_POST[nusername]) . "' 
                password = ' " .mysql_escape_string($_POST[npassword]) . "'
                name = '" . mysql_escape_string($_POST[nname]) . " '
                surname = '" . mysql_escape_string($_POST[nsurname])."'
                SET role = '".mysql_escape_string($_POST[nrole]) . "'
                WHERE username='" .mysql_escape_string( $_POST[us]) . "'");

If everything else fails echo the SQL statement then paste on SQL Browser/PHPMyAdmin then debug it there. Then you just replace the code with the error-free one.

---

You need to make sure that the data you are sending are sql-inject free as well. Someone might just bypass it..

Solution 3:

Your query need to be modified as bellow.

mysql_query("UPDATE user 
            SET username = '" .$_POST[nusername] . "' ,
            password = ' " .$_POST[npassword] . "',
            name = '" . $_POST[nname] . " ',
            surname = '" . $_POST[nsurname]',
            role = '$_POST[nrole] . "'
            WHERE username='" . $_POST[us] . "'");

Also you are open for the SQL Injection attacks. So you better use mysql_real_escape_string() function as well.

http://php.net/manual/en/function.mysql-real-escape-string.php

Also I would like to suggest you few steps to over come this kind of issue. This is just an example.

Step 1

When you need a SQL statement in your PHP code. You better write it in your MySQL tool first and test it with sample values.

UPDATE subscriber 
    SET
    Subscriber_Name ='Test' , 
    Email ='test@test.com'WHERE
    Subscriber_ID ='2' ;

Step 2:

If the query works fine then copy it to php. And replace values with mysql_real_escape_string() support.

$sql = "UPDATE subscriber 
    SET
    Subscriber_Name = '" .  mysql_real_escape_string($_POST['name']) . "' , 
    Email = '" .  mysql_real_escape_string($_POST['email']) . "'    
    WHERE
    Subscriber_ID = '" .  mysql_real_escape_string($_POST['id']) . "' ;"

Step 3:

Execute your query.

$result = mysql_query($sql);

Step 4 :

You can see any if there any errors available.

echo mysql_error();

EDIT:

Answer for you Question 2 "How can I achieve already filled boxes in the html file, with the right values, if I choose a certain username?" could be like this.

First you have to write a select statement and get whatever data you want. Ex.

$sql = "SELECT user.username, user.name, user.surname , user.role  FROM USER WHERE user.username = '" . mysql_real_escape_string($_POST[us]) . "'";

$result = mysql_query($sql, $link) ordie(mysql_error());

$row = mysql_fetch_assoc($result);

Then put your HTML code. Ex:

<formaction="edit_user.php"method="post"><p>Username<inputtype="text"name="nusername"size="40"value="<?phpecho$row['username'];?>"></p><p>Password<inputtype="password"name="npassword"size="40"></p><p>Name<inputtype="text"name="nname"size="40"value="<?phpecho$row['name'];?>"></p><p>Surname<inputtype="text"name="nsurname"size="40"value="<?phpecho$row['surname'];?>"></p><p>Role<inputtype="text"name="nrole"size="40"value="<?phpecho$row['role'];?>"></p><p><inputtype="submit></p>
</form>

Solution 4:

Apparently there is a syntax error in the latest code you posted, when you obtain the data from post you have $_POST[nusername] and it should be $_POST['nusername']since it is an index of the array, and I also recommend echoing the query and commenting the header call so you can see what is the query that is being sent to MySQL