Update a row mysql in php

Question

I have a table that looks like this:

name surname username password role

student student student@csd.auth.gr student student

student2 student2 student2@csd.auth.gr 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

student2@csd.auth.gr <--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. :)

score 3 · Answer 1 · edited Jun 18 '14 at 09:39

There is a lot going on here.

Always us a integer as a primary key, example: id MEDIUMINT NOT NULL AUTO_INCREMENT. Then make it the primary key.
you need to sanitize your input to the database using mysql_real_escape_string()
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.

I did what you proposed but it still doesn't do anything...Is there a possibility that there is problem with the html part, from which the php file takes the values?

Username

Password

Name

Surname

Role — felice, Feb 16 '12 at 03:09

score 2 · Answer 2 · 2012-02-16T17:00:05.797

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);

edited Feb 16 '12 at 17:00

answered Feb 16 '12 at 02:46

1

Please edit your answer to include string escaping. If he actually did use this sql he'd be opening himself up to sql injection. – jeremysawesome Feb 16 '12 at 03:55
@jeremysawesome Edited just for you :) – Feb 16 '12 at 17:00

score 0 · Answer 3 · answered May 23 '13 at 16:57

0

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

answered May 23 '13 at 16:57

Manuel Alejandro Mijangos Gonz

71
4

Oh I forgot to mention that you should add the apostrophes to all of the POST variables – Manuel Alejandro Mijangos Gonz May 23 '13 at 16:58

Prasad Rajapaksha · Answer 4 · 2012-02-16T04:23:47.080

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) 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>

I did what you proposed but it still doesn't do anything...Is there any possibility that there is problem with the html part, from which the php file takes the values? — felice, Feb 16 '12 at 03:22
Please edit your question with this as well. I can't understand this in comment sections because of it's formatting. — Prasad Rajapaksha, Feb 16 '12 at 03:34

score 0 · Answer 5 · answered Feb 16 '12 at 02:54

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

Update a row mysql in php

5 Answers5

Linked