How to update values to table fields in MySql from html form?

Question

I am trying to update a table in my DB for the past two days. But I am unable to get it to work. Somebody please help me. I could connect to my database, see my table fields perfectly. Posted values from the form could be read perfectly from the destined PHP file. MySQL query doesn't seen to return any error. But I dont understand why the values are not getting updated into the table.

//form.html

        <form name="account" action="test.php" method="post">
    <td align="left" valign="top" class="labelstyle" width="25%">First Name</td>
                                          <td align="left" valign="top" class="labeltextstyle" width="75%"><input type="text" name="fname" value="" /></td>
    <td align="left" valign="top" class="labelstyle" width="25%">Last Name</td>
                                          <td align="left" valign="top" class="labeltextstyle" width="75%"><input type="text" name="lname" value="" /></td>
    <td align="left" valign="top" class="labelstyle" width="25%">Email</td>
                                          <td align="left" valign="top" class="labeltextstyle" width="75%"><input type="text" name="email" value="" /></td>


    <td  align="left" valign="top" class="labeltextstyle"><input type="submit" name="submit" value="Save" /></td>

    </form>

// test.php
    <?php

    $dbhost = "localhost"; 
    $dbname = "test"; 
    $dbuser = ""; 
    $dbpass = "";

    mysql_connect ( $dbhost, $dbuser, $dbpass)or die("Could not connect: ".mysql_error()); 
    mysql_select_db($dbname) or die(mysql_error()); 

    session_start();
    if(isset($_REQUEST['submit'])){

    // $query = "select * from form";
    // $result = mysql_query($query);
    // $numcolumn = mysql_num_fields($result);
    // for ( $i = 0; $i < $numcolumn; $i++ ) {
    // $columnnames = mysql_field_name($result, $i);
    // echo $columnnames;
    // }


    $fname = $_POST['fname']; 
    $lname = $_POST['lname'];  
    $email = $_POST['email'];

    echo $fname ;
    echo $lname ;
    echo $email ;

    $query = "update test set
               fname = $fname,
               lname = $lname,
               email = $email
               where 1 ";

    $result = mysql_query($query); 

    if ($query = 1) { 

    echo "IT WORKED"; 

    } else { 
    echo "DIDNT WORK";
    } 
    }else{
     echo "NOT SUBMITTED";
     }

?>

//form.html

<form name="account" action="test.php" method="post">
<td align="left" valign="top" class="labelstyle" width="25%">First Name</td>
                                      <td align="left" valign="top" class="labeltextstyle" width="75%"><input type="text" name="fname" value="" /></td>
<td align="left" valign="top" class="labelstyle" width="25%">Last Name</td>
                                      <td align="left" valign="top" class="labeltextstyle" width="75%"><input type="text" name="lname" value="" /></td>
<td align="left" valign="top" class="labelstyle" width="25%">Email</td>
                                      <td align="left" valign="top" class="labeltextstyle" width="75%"><input type="text" name="email" value="" /></td>


<td  align="left" valign="top" class="labeltextstyle"><input type="submit" name="submit" value="Save" /></td>

</form>

When i fill the form with values A, B and C and submit the form, I get the following output.

fnamelnameemailABCIT WORKED

please help me soon.

dev.mysql.com/doc/refman/5.0/en/update.html

Lawrence Cherone
– Lawrence Cherone

2012-08-12 12:33:15 +00:00
Commented Aug 12, 2012 at 12:33 — Lawrence Cherone
– Lawrence Cherone, Commented Aug 12, 2012 at 12:33

Conrad Lotz · Accepted Answer · 2012-08-12 12:46:49Z

1

There is no id in the where clause to indicate which row must be updated. This will update all the rows in the table. It should look like this:

 $query = "update test set
               fname = $fname,
               lname = $lname,
               email = $email
               where id = 1";

I assume that a row is created beforehand so that you know which row to update. If not then rather use an INSERT statement should be used.

$query = "insert into test (fname,lname,email) VALUES ($fname,$lname,$email);

answered Aug 12, 2012 at 12:46

Conrad Lotz

8,9533 gold badges25 silver badges28 bronze badges

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

4 Comments

wishchaser Over a year ago

Thanks. But 'where' is not the issue now. The table I mentioned is just a test table which has only one row. So it doesnt matter if all rows get updated. But the problem is, the rows are untouched afer the query execution. I need a solution for this issue only. Pls help.

Peter Ilfrich Over a year ago

That's not correct JLC007. You've only assumed that there is an ID column and "where 1" means every row.

Conrad Lotz Over a year ago

Use the following syntax instead. $query = "update test set fname = ".$fname.", lname = ".$lname.",email = ".$email." where id = 1";

Conrad Lotz Over a year ago

Do you have 2 separate tables? In the code above I see form table and test table. '// $query = "select * from form";

Peter Ilfrich · Accepted Answer · 2012-08-12 12:41:05Z

0

fname, lname and email are strings, which have to be escaped in the SQL query:

 $query = "update test set
           fname = '$fname',
           lname = '$lname',
           email = '$email'
           where 1";

You probably can leave the where 1 as well.

answered Aug 12, 2012 at 12:41

Peter Ilfrich

3,8263 gold badges34 silver badges35 bronze badges

4 Comments

wishchaser Over a year ago

Thanks for your reply. I forgot to mention that I am new to PHP. Could you please explain to do the 'escaping' u mentioned?? Thanks in advance.

Peter Ilfrich Over a year ago

If you send text (or words) to MySQL you need to define the starting point and end point of the text by quoting it (in your example you already used up the double quotes to define the query and need to use single quotes for the escaping). Without the quotes, how would MySQL know if the variable (if it contains 2 words) is the next key word (like WHERE or ORDER BY) or still the new value for the field.

Peter Ilfrich Over a year ago

Maybe try echo $query; and copy/paste the SQL into something like PHPmyAdmin or MySQL Workbench to get more debugging information and check if the query is a problem.

wishchaser Over a year ago

Thanks for ur help. I sorted out that problem and have learned how to debug such future errors.

Collectives™ on Stack Overflow

How to update values to table fields in MySql from html form?

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related