Update multiple field in PHP/MYSQL

[devil_vin]

Hi,guys!I have a form need to update several fields in database,may I know what is the correct sql statement? One of the field is not editable,I just need to update other editable fields value to database. The below update statement seems went wrong.Thanks for your help...

if ($numrow != 0)
    {// fetch each record in result set
        for ($counter = 0; $row = mysql_fetch_row($result); $counter++)
        {
            foreach ($row as $key => $value)
            {
                if ($key == 0 || $key == 2)
                {
?>
                 <td><input type = "text" name = "edited1" size = "30" value = "<?php
                    echo $value; ?>"></td>
                 <?php
                }
                else
                    if ($key == 1)
                    {

?>    

                  <td><input type ="text" size = "30" value = "<?php
                        echo $value; ?>" readonly ="readonly"></td> 
					 <?php
                    }
                    else
                    {
?>		           

                       <td><textarea name = "edited2" rows = "3"><?php
                        echo $value; ?></textarea></td>
  <?php
                    }
            }
        }
    }
}

?>

		 </tr>
		</table>
		<table>
		<tr>
		<td>&nbsp;</td>
		<td width='4%'>&nbsp;</td>
		<td></tr>
		<tr>
		<td>&nbsp;</td>
		<td width='4%'>&nbsp;</td>
		<td><input type='submit' name='submit' value='Upgrade'></td>
		</table>
		</form>
		</body>
		</head>
		</html>

<?php

$tbl_name = "member";
if (isset($_POST['submit']))
{
    $insert = mysql_query("UPDATE $tbl_name SET name = '" . $_POST['edited1'] . "'
	          WHERE name = '" . $_REQUEST['name'] . "');

[devil_vin]

I have tried the following script to update multiple field but data not save into database

<?php

$tbl_name = "member";
if (isset($_POST['submit']))
{
    $update = mysql_query("
	          UPDATE 
			      $tbl_name
	          SET
			      name = '" . mysql_real_escape_string($_POST['edited1']) . "',
				  telephone = '" . mysql_real_escape_string($_POST['edited1']) . "',
				  address = '" . mysql_real_escape_string($_POST['edited2']) . "'
			  WHERE
			 	  name = '" . mysql_real_escape_string($_REQUEST['name']) . "',
			 	  telephone = '" . mysql_real_escape_string($_REQUEST['telephone']) . "',
			 	  address = '" . mysql_real_escape_string($_REQUEST['address']) . "'
			  or die(mysql_error())
			  ");




if ($update)
{
?>   <script type="text/javascript">
      alert("Your profile already updated.");
      history.back();

 </script>
<?php

    //THIS CODE IS UNCOMMENTED
    //So that people without JS can still see the message.
    die("Your profile already updated");
    //END
}

}
?> 	  

I am using action = "<? echo $HTTP_SERVER_VARS['PHP_SELF'] .
"?frame=profile"; ?>", which reflect the page(profile.php) itself.

[devil_vin]

I have tried the following script to update multiple field but data not save into database

<?php

$tbl_name = "member";
if (isset($_POST['submit']))
{
    $update = mysql_query("
	          UPDATE 
			      $tbl_name
	          SET
			      name = '" . mysql_real_escape_string($_POST['edited1']) . "',
				  telephone = '" . mysql_real_escape_string($_POST['edited1']) . "',
				  address = '" . mysql_real_escape_string($_POST['edited2']) . "'
			  WHERE
			 	  name = '" . mysql_real_escape_string($_REQUEST['name']) . "',
			 	  telephone = '" . mysql_real_escape_string($_REQUEST['telephone']) . "',
			 	  address = '" . mysql_real_escape_string($_REQUEST['address']) . "'
			  or die(mysql_error())
			  ");




if ($update)
{
?>   <script type="text/javascript">
      alert("Your profile already updated.");
      history.back();

 </script>
<?php

    //THIS CODE IS UNCOMMENTED
    //So that people without JS can still see the message.
    die("Your profile already updated");
    //END
}

}
?> 	  

I am using action = "<? echo $HTTP_SERVER_VARS['PHP_SELF'] .
"?frame=profile"; ?>", which reflect the page(profile.php) itself.

[devil_vin]

Can anybody solve my problem?I really you guys help to work it out...Thanks...

[Rodney_H_]

This is the query you said you tried:

    $update = mysql_query("
              UPDATE
                  $tbl_name
              SET
                  name = '" . mysql_real_escape_string($_POST['edited1']) . "',
                  telephone = '" . mysql_real_escape_string($_POST['edited1']) . "',
                  address = '" . mysql_real_escape_string($_POST['edited2']) . "'
              WHERE
                   name = '" . mysql_real_escape_string($_REQUEST['name']) . "',
                   telephone = '" . mysql_real_escape_string($_REQUEST['telephone']) . "',
                   address = '" . mysql_real_escape_string($_REQUEST['address']) . "'
              or die(mysql_error())
              "); 

What error message did you get?

Maybe try separating the query from the execution of the query.

Also, perhaps try updating the value in the DB based on the userID (this would be the auto-incremented primary key in your DB table...):

$query = "UPDATE $tbl_name
		  SET
		  	name = '" . mysql_real_escape_string($_POST['name']) . "',
            telephone = '" . mysql_real_escape_string($_POST['phone']) . "',
            address = '" . mysql_real_escape_string($_POST['address']) . "'
          WHERE
		   	userID = " . (int)$_POST['userID'] . "
		  LIMIT 1";

$result = mysql_query($query) or exit(mysql_error());

[devil_vin]

Thanks for your reply.

It showed You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

For the id,I didn't output it in table,so when I refer back id in database,what should I try?I have tried the following but problem remain.The data type of my id is already int,so can take off casting right?

userID = " . (int)$_REQUEST['userID'] . "

 id =  '" . mysql_real_escape_string((int)$_REQUEST['id']) . "'

Rodney H. wrote:

This is the query you said you tried:

    $update = mysql_query("
              UPDATE
                  $tbl_name
              SET
                  name = '" . mysql_real_escape_string($_POST['edited1']) . "',
                  telephone = '" . mysql_real_escape_string($_POST['edited1']) . "',
                  address = '" . mysql_real_escape_string($_POST['edited2']) . "'
              WHERE
                   name = '" . mysql_real_escape_string($_REQUEST['name']) . "',
                   telephone = '" . mysql_real_escape_string($_REQUEST['telephone']) . "',
                   address = '" . mysql_real_escape_string($_REQUEST['address']) . "'
              or die(mysql_error())
              "); 

What error message did you get?

Maybe try separating the query from the execution of the query.

Also, perhaps try updating the value in the DB based on the userID (this would be the auto-incremented primary key in your DB table...):

$query = "UPDATE $tbl_name
		  SET
		  	name = '" . mysql_real_escape_string($_POST['edited1']) . "',
            telephone = '" . mysql_real_escape_string($_POST['edited1']) . "',
            address = '" . mysql_real_escape_string($_POST['edited2']) . "'
          WHERE
		   	userID = " . (int)$_POST['userID'] . "
		  LIMIT 1";

$result = mysql_query($query) or exit(mysql_error());

[devil_vin]

Thanks for your reply.

It showed You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

For the id,I didn't output it in table,so when I refer back id in database,what should I try?I have tried the following but problem remain.The data type of my id is already int,so can take off casting right?

userID = " . (int)$_REQUEST['userID'] . "

 id =  '" . mysql_real_escape_string((int)$_REQUEST['id']) . "'

Rodney H. wrote:

This is the query you said you tried:

    $update = mysql_query("
              UPDATE
                  $tbl_name
              SET
                  name = '" . mysql_real_escape_string($_POST['edited1']) . "',
                  telephone = '" . mysql_real_escape_string($_POST['edited1']) . "',
                  address = '" . mysql_real_escape_string($_POST['edited2']) . "'
              WHERE
                   name = '" . mysql_real_escape_string($_REQUEST['name']) . "',
                   telephone = '" . mysql_real_escape_string($_REQUEST['telephone']) . "',
                   address = '" . mysql_real_escape_string($_REQUEST['address']) . "'
              or die(mysql_error())
              "); 

What error message did you get?

Maybe try separating the query from the execution of the query.

Also, perhaps try updating the value in the DB based on the userID (this would be the auto-incremented primary key in your DB table...):

$query = "UPDATE $tbl_name
		  SET
		  	name = '" . mysql_real_escape_string($_POST['edited1']) . "',
            telephone = '" . mysql_real_escape_string($_POST['edited1']) . "',
            address = '" . mysql_real_escape_string($_POST['edited2']) . "'
          WHERE
		   	userID = " . (int)$_POST['userID'] . "
		  LIMIT 1";

$result = mysql_query($query) or exit(mysql_error());

[Rodney_H_]

devil_vin wrote:

It showed You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

Maybe try echo() -ing out the query before executing it.

For example: echo($query); exit();

devil_vin wrote:

For the id,I didn't output it in table, so when I refer back id in database, what should I try?

You have to output the ID and save it in the form as a hidden form field and value so you will know which record to update.

You may also, when retrieving the original values that the user will be editing, save the ID as a $_SESSION value instead, which is a little bit safer than "exposing" the User ID in the form.

Hope that help.s

[devil_vin]

I have add one more field,id to display it in the form and echoing the query before execution.However all textfields and textarea missing again.Only the id "1" displayed outside the form.

if (isset($_SESSION['pri_id']))
{
    $tbl_name = "member";
    $sql = "SELECT id,name,email,telephone,address FROM $tbl_name";

$result = @mysql_query($sql, $connection) or die("Cannot execute query.");
$numrow = mysql_num_rows($result);

if ($numrow != 0)
{// fetch each record in result set
    for ($counter = 0; $row = mysql_fetch_row($result); $counter++)
    {
        foreach ($row as $key => $value)
        {
            // dispay id in hidden field
            if($key == 0)
            {
              ?>
              <td><input type = "hidden" name="id" size = "10" value ="<?php echo $value; ?>"></td>
              <?

		    }
            // name and telephone are editable
            if ($key == 1 || $key == 3)
            {
?>
                 <td><input type = "text"  name = "edited1" size = "30" value = "<?php
                    echo $value; ?>"></td>
                 <?php
                }
                else
                    // email adress is not ediable
                    if ($key == 2)
                    {

?>    

                  <td><input type ="text"   size = "30" value = "<?php
                        echo $value; ?>" readonly ="readonly"></td> 
					 <?php
                    }
                    else // address is editable
                    {
?>		              

                       <td><textarea name = "edited2" rows = "3"><?php
                        echo $value; ?></textarea></td>
  <?php
                    }
            }
        }
    }
}

?>

		 </tr>
		</table>
		<table>
		<tr>
		<td>&nbsp;</td>
		<td width='4%'>&nbsp;</td>
		<td></tr>
		<tr>
		<td>&nbsp;</td>
		<td width='4%'>&nbsp;</td>
		<td><input type='submit' name='submit' value='Upgrade'></td>
		</table>
		</form>
		</body>
		</head>
		</html>

<?php

$tbl_name = "member";
if (isset($_POST['submit']))
{
    $update = mysql_query("
	          UPDATE 
			      $tbl_name
	          SET
			      name = '" . mysql_real_escape_string($_POST['edited1']) . "',
				  telephone = '" . mysql_real_escape_string($_POST['edited1']) . "',
				  address = '" . mysql_real_escape_string($_POST['edited2']) . "'
			  WHERE
			       id =  '" . mysql_real_escape_string((int)$_REQUEST['id']) . "'
		        LIMIT 1");
			 	  //name = '" . mysql_real_escape_string($_REQUEST['name']) . "',
			 	  //telephone = '" . mysql_real_escape_string($_REQUEST['telephone']) . "',
			 	  //address = '" . mysql_real_escape_string($_REQUEST['address']) . "'
			echo($update);
	   	    exit();
			$result = mysql_query($update) or die(mysql_error());





if ($update)
{
?>   <script type="text/javascript">
      alert("Your profile already updated.");
      history.back();

 </script>
<?php

    //THIS CODE IS UNCOMMENTED
    //So that people without JS can still see the message.
    die("Your profile already updated");
    //END
}

}
?> 	    

Rodney H. wrote:

Maybe try echo() -ing out the query before executing it.

For example: echo($query); exit();

You have to output the ID and save it in the form as a hidden form field and value so you will know which record to update.

You may also, when retrieving the original values that the user will be editing, save the ID as a $_SESSION value instead, which is a little bit safer than "exposing" the User ID in the form.

Hope that help.s