Insert into MySQL Table

Insert Data in a MySQL Table in 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 insert data into a mysql table user_info which has two fields userid and password. This is explained as below.

$sql_statement = “Insert into user_info values(‘john’,’secret’)”;

A dynamic insert statement would be something like this.
$sql_statement = “Insert into user_info values(‘”.$userid.”’,’”.$password.”’)”;

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 create a mysql table would be as below.

$sql_statement = “Insert into user_info values(‘”.$userid.”’,’”.$password.”’)”;
mysql_query($sql_statement,$conn) or die(“Could not create new record”);

After the execution of this statement, one record will be added to the mysql table user_info. The values of the fields userid and password of the user_info table will be the values of the variables $userid and $password respectively.

Example to insert row into a MySQL table
This source code creates a webform. The user is allowed to enter the name of the database, the table in which data is to be inserted. In a separate input box the user enters the values of the table fields separated by commas. Once the form is processed, one new row is inserted into the specified table.

Error is thrown if anything goes wrong. The name of this file is "insert_data.php" . Try it yourself on you local machine.

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

$values = $_GET["values"];

//Continue if database name is non blank
if($database!="" && $table!="")
{
//Prepare the SQL Statement
$sql_statement = "Insert into ".$table." values(".$values.")";

//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 create table");
echo "One record inserted successfully into ".$table. " in database ". $database;
}
?>
<html>
<head>
<title>Insert into MySQL Table | A Demo!</title>
</head>
<body>
<form action="insert_data.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>Values:</td><td><input type=text name=values></td></tr>
</form>
</body>
</html>

  • 398 reads