Update a row mysql in php

问题:

I have a table that looks like this:

name surname username password role

student student [e-mail] student student

student2 student2 [e-mail] student2 student

and I want to be able to edit a row in php. The values are taken from a html file like this:

Username

[e-mail] <--This will be written in a text box

Password

student2 <--This will be written in a text box

Name

student2 <--This will be written in a text box

Surname

student2 <--This will be written in a text box

Role

student <--This will be written in a text box

My php file is:

<?php
$hostname = "localhost"; 
$database = "mydb"; 
$username = "myuser"; 
$password = "mypsw";
$link = mysql_connect( $hostname , $username , $password ) or 
        die("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");
?>

1.The update does not happen, so there's something wrong in the php file, that I can't find.

2. How can I achieve already filled boxes in the html file, with the right values, if I choose a certain username?

Can someone help me? Thank you in advance. :)

回答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 ) or 
        die("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.

回答2:

When you have a query issue, always echo the query itself to see if the correct data is going through! Furthermore, I would write the query like this:

$sql = "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])."'";

// test
echo $sql;

mysql_query($sql);

回答3:

   $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..

回答4:

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 = '[e-mail]'     
    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) or die(mysql_error());

$row = mysql_fetch_assoc($result);

Then put your HTML code. Ex:

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

回答5:

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