Update MySQL Table in PHP

Execute UPDATE operation on MySQL Table using PHP

Following the Common Steps to execute any SQL statements in PHP, we define a string variable “$sql_statement” which contains the actual SQL statement to update data into a mysql table user_info which has two fields userid and password. This is explained as below.

$sql_statement = “Update user_info set password=’newSecret’”;

The above SQL statement when executed, will set the password of all users in the table user_info to “newSecret”. If you want this to happen, It’s OK. If you want to update the password of the user john only, then you should use the Where clause of the SQL statement.

This is explained as below.


$sql_statement = “Update user_info set password=’newSecret’ Where userid=’john’”;

A dynamic SQL update statement in PHP would be something like this.


$sql_statement = “Update user_info set password=’”.$new_password.”’ Where userid=’”.$userid.”’”;

Notice the use of dot operator and quotes.

After creating the statement, we execute this query using the PHP inbuilt function mysql_query () as explained below.

mysql_query ($sql_statement, $conn);

As explained earlier, $conn is the database connection string retrieved using the mysql_connect () function.

Hence the complete code to update a mysql table would be as below.


$sql_statement = “Update user_info set password=’”.$new_password.”’ Where userid=’”.$userid.”’”;
mysql_query($sql_statement,$conn) or die(“Could not update”);

After the execution of this statement, the password field of the table userinfo will be changed to the value of $new_password where the value of the userid field equals the value of $userid. Loosely speaking the password of $userid will be changed to $new_secret.

Example to create a MySQL Table

This source code creates a webform. The user is allowed to enter the name of the database where the table is to be created and the name of the table he wishes to create. This web form is processed and the table is created in the sepecified database. Error is thrown if anything goes wrong. The name of this file is "update_table.php" Try it yourself!


<?php
//Retrieve the database and table name
$database = $_GET["db"];
$table = $_GET["table"];

$fld = $_GET["fld"];
$fld_value = $_GET["fld_value"];
$where = $_GET["where"];

//Continue if database name is non blank
if($database!="" && $table!="")
{
$sql_statement = "Update ".$table. "Set ".$fld." ".$where;

//Connect to the MySQL Database
$conn = mysql_connect ("host","userid", "password");;

//Select the database
mysql_select_db($database,$conn);
//Execute query
mysql_query($sql_statement,$conn) or die("Could not update table");
echo "Table ".$table. " updated successfully in database ". $database;
}
?>
<html>
<head>
<title>Update table</title>
</head>
<body>
<form action="update_table.php">
<table align=center>
<tr><td>Database Name:</td><td><input type=text name=db></td></tr>
<tr><td>Table Name:</td><td><input type=text name=table></td></tr>
<tr><td>Field Name:</td><td><input type=text name=fld></td></tr>
<tr><td>New Field Value:</td><td><input type=text name=fld_value></td></tr>
<tr><td>Where Clause:</td><td><input type=text name=where></td></tr>
</form>
</body>
</html>

  • 405 reads