Using MySQL Insert Statement in PHP

16:11:00 , , , 0 Comments


In this article, we are going to look at how to use MySQL insert statements in PHP scripts. For examples in this article, we populate the values to be inserted within the scripts.
However in most PHP applications, values will come from user inputs via web forms or by other means like CSV files.
You can find the SQL commands for creating and truncating the data table used in examples (`employee`) here. Make sure you change database connection details before running the PHP scripts.
Following PHP script shows how to insert a single row into a database table.
01.<?php02. 03./* Change database details according to your database */04.$dbConnection = mysqli_connect('localhost', 'robin', 'robin123', 'company_db');05. 06.$firstName  = "Robin";07.$lastName   = "Jackman";08. 09.$query = "INSERT INTO `employee` (`first_name`, `last_name`) VALUES ('$firstName', '$lastName')";10. 11.if (mysqli_query($dbConnection, $query)) {12.echo "Successfully inserted " . mysqli_affected_rows($dbConnection) . " row";13.} else {14.echo "Error occurred: " . mysqli_error($dbConnection);15.}16. 17.?>
  • Query is enclosed with double quotes. This is to include variables inside the query. If you used single quotes, including variables is not possible.
  • There is no semicolon at the end of query (after last right parenthesis) as when you run insert statement in command-line or in a GUI tool. There is a semicolon at the end of line which ends the PHP expression.
  • Make sure single quotes are present around string values. If you had following statement (no single quotes around $firstName and $lastName), it would throw an error.
1.$query = "INSERT INTO `employee` (`first_name`, `last_name`) VALUES ($firstName, $lastName)";Inserting multiple rows involves bit of iterating for generating the value part of the statement.01.<?php02. 03./* Change database details according to your database */04.$dbConnection = mysqli_connect('localhost', 'emma', 'martins123', 'company_db');05. 06.$employees[0]['first_name'] = 'Robin';07.$employees[0]['last_name']  = 'Jackman';08.$employees[0]['job_title']  = 'Software Engineer';09.$employees[0]['salary']     = 5500;10. 11.$employees[1]['first_name'] = 'Taylor';12.$employees[1]['last_name']  = 'Edward';13.$employees[1]['job_title']  = 'Software Architect';14.$employees[1]['salary']     = 7200;15. 16.$employees[2]['first_name'] = 'Vivian';17.$employees[2]['last_name']  = 'Dickens';18.$employees[2]['job_title']  = 'Database Administrator';19.$employees[2]['salary']     = 6000;20. 21.$query = "INSERT INTO `employee` (`first_name`, `last_name`, `job_title`, `salary`) VALUES ";22. 23.$count = count($employees);24. 25.for ($i=0; $i<$count; $i++) {26. 27.$query .= "('{$employees[$i]['first_name']}', '{$employees[$i]['last_name']}', '{$employees[$i]['job_title']}', {$employees[$i]['salary']})";28. 29./* If not last iteration, add a comma and a space */30.if ($i < ($count - 1)) {31.$query .= ", ";32.}33. 34.}35. 36.if (mysqli_query($dbConnection, $query)) {37.echo "Successfully inserted " . mysqli_affected_rows($dbConnection) . " rows";38.} else {39.echo "Error occurred: " . mysqli_error($dbConnection);40.}41. 42.?>
  • First part of the INSERT statement is assigned to $query and value part is generated by a for loop and append to $query by string concatenation.
  • Curly brackets are used around array elements since otherwise it will lead to a syntax error (If you want to use array elements and method calls inside an expression enclosed with double quotes, you need to use curly brackets).
  • Salary values are not enclosed with single quotes since they are numerical values (If you wish, you can enclose numerical values with single quotes too).
  • As described in multiple inserts, except for last value segment, you need to add a comma after every other segment. if condition is used for this purpose.
Having single quotes (and few other special characters) in your insert query can cause errors when executing. Consider following PHP script.
01.<?php02. 03./* Change database details according to your database */04.$dbConnection = mysqli_connect('localhost', 'robin', 'robin123', 'company_db');05. 06.$firstName  = "Emmanuel";07.$lastName   = "Martins";08. 09.$query = "INSERT INTO `employee` (`first_name`, `last_name`) VALUES ('$firstName', '$lastName')";10. 11.if (mysqli_query($dbConnection, $query)) {12.echo "Successfully inserted " . mysqli_affected_rows($dbConnection) . " row";13.} else {14.echo "Error occurred: " . mysqli_error($dbConnection);15.}16. 17.?>When running above PHP script, a MySQL error will be thrown since $lastName contains a single quote. You will need to escape it with mysqli_real_escape_string() as below.1.$lastName = mysqli_real_escape_string($dbConnection, "Martins");Additionally, if you are getting values from an external source (like user inputs via a web form), as a security precaution, you need to escape the values to be used in SQL statements since there can be malicious user inputs attempting SQL injections.

0 comments :

Subscribe to: Post Comments ( Atom )