0

I have a table that looks like this:

name surname username password role

student student [email protected] student student

student2 student2 [email protected] 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

[email protected] <--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. :)

Improve this question

5 Answers 5

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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? <form action="edit_user.php" method="post"> <p>Username<input type="text"name="nusername" size="40"></p> <p>Password<input type="password"name="npassword" size="40"></p> <p>Name<input type="text"name="nname" size="40"></p> <p>Surname<input type="text"name="nsurname" size="40"></p> <p>Role<input type="text"name="nrole" size="40"></p> <p><input type="submit></p> </form>
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);
Improve this answer

2 Comments

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 Edited just for you :)
0 
   $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..

Improve this answer

Comments

0

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 = '[email protected]'     
    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>
Improve this answer

4 Comments

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?
<form action="edit_user.php" method="post"> <p>Username<input type="text"name="nusername" size="40"></p> <p>Password<input type="password"name="npassword" size="40"></p> <p>Name<input type="text"name="nname" size="40"></p> <p>Surname<input type="text"name="nsurname" size="40"></p> <p>Role<input type="text"name="nrole" size="40"></p> <p><input type="submit></p> </form>
Please edit your question with this as well. I can't understand this in comment sections because of it's formatting.
<form action="edit_user.php" method="post"> <p>Username<input type="text"name="nusername" size="40"></p> <p>Password<input type="password"name="npassword" size="40"></p> <p>Name<input type="text"name="nname" size="40"></p> <p>Surname<input type="text"name="nsurname" size="40"></p> <p>Role<input type="text"name="nrole" size="40"></p> <p><input type="submit></p> </form>
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

Improve this answer

1 Comment

Oh I forgot to mention that you should add the apostrophes to all of the POST variables

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.