{"id":1126,"date":"2014-04-30T04:38:11","date_gmt":"2014-04-30T04:38:11","guid":{"rendered":"http:\/\/frowningbear.com\/blog\/?p=1126"},"modified":"2018-05-29T09:44:50","modified_gmt":"2018-05-29T09:44:50","slug":"php-mysql-inserting-records-reference","status":"publish","type":"post","link":"https:\/\/frowningbear.com\/codebase\/2014\/04\/30\/php-mysql-inserting-records-reference\/","title":{"rendered":"PHP MySQL Inserting Records Reference"},"content":{"rendered":"<p>First up there are three examples for inserting records into a MySQL database, one for each of the three following techniques:<\/p>\n<ul>\n<li>Using mysqli in a procedural manner.<\/li>\n<li>Using mysqli in an object-oriented fashion.<\/li>\n<li>Using PDO.<\/li>\n<\/ul>\n<p>After that there is an example of the same three approaches, but with the addition of using prepared statements for improved security and efficiency.<\/p>\n<p><!--more--><\/p>\n<h3>MySQLi Procedural<\/h3>\n<pre class=\"wrap:true lang:default decode:true \" title=\"MySQLi Procedural\">&lt;?php\r\n\r\n$servername = \"localhost\";\r\n$username = \"username\";\r\n$password = \"password\";\r\n$dbname = \"mydb\";\r\n\r\n\/\/ Create connection\r\n$conn = mysqli_connect($servername, $username, $password, $dbname);\r\n\/\/ Check connection\r\nif (!$conn) {\r\n    die(\"Connection failed: \" . mysqli_connect_error());\r\n}\r\n\r\n$sql = \"INSERT INTO mytable (firstname, lastname, email)\r\nVALUES ('John', 'Doe', 'john@example.com')\";\r\n\r\nif (mysqli_query($conn, $sql)) {\r\n    echo \"New record created successfully\";\r\n} else {\r\n    echo \"Error: \" . $sql . \"&lt;br&gt;\" . mysqli_error($conn);\r\n}\r\n\r\nmysqli_close($conn);\r\n\r\n?&gt;\r\n<\/pre>\n<h3>MySQLi Object-oriented<\/h3>\n<pre class=\"wrap:true lang:default decode:true \" title=\"MySQLi Object-oriented\">&lt;?php\r\n\r\n$servername = \"localhost\";\r\n$username = \"username\";\r\n$password = \"password\";\r\n$dbname = \"mydb\";\r\n\r\n\/\/ Create connection\r\n$conn = new mysqli($servername, $username, $password, $dbname);\r\n\/\/ Check connection\r\nif ($conn-&gt;connect_error) {\r\n    die(\"Connection failed: \" . $conn-&gt;connect_error);\r\n}\r\n\r\n$sql = \"INSERT INTO mytable (firstname, lastname, email)\r\nVALUES ('John', 'Doe', 'john@example.com')\";\r\n\r\nif ($conn-&gt;query($sql) === TRUE) {\r\n    echo \"New record created successfully\";\r\n} else {\r\n    echo \"Error: \" . $sql . \"&lt;br&gt;\" . $conn-&gt;error;\r\n}\r\n\r\n$conn-&gt;close();\r\n\r\n?&gt;<\/pre>\n<h3>PDO<\/h3>\n<pre class=\"wrap:true lang:default decode:true \" title=\"PDO\">&lt;?php\r\n\r\n$servername = \"localhost\";\r\n$username = \"username\";\r\n$password = \"password\";\r\n$dbname = \"mydb\";\r\n\r\ntry {\r\n    $conn = new PDO(\"mysql:host=$servername;dbname=$dbname\", $username, $password);\r\n    \/\/ set the PDO error mode to exception\r\n    $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);\r\n    $sql = \"INSERT INTO mytable (firstname, lastname, email)\r\n    VALUES ('John', 'Doe', 'john@example.com')\";\r\n    \/\/ use exec() because no results are returned\r\n    $conn-&gt;exec($sql);\r\n    echo \"New record created successfully\";\r\n    }\r\ncatch(PDOException $e)\r\n    {\r\n    echo $sql . \"&lt;br&gt;\" . $e-&gt;getMessage();\r\n    }\r\n\r\n$conn = null;\r\n\r\n?&gt;<\/pre>\n<h3>MySQLi Procedural with Prepared Statements<\/h3>\n<pre class=\"wrap:true lang:default decode:true \" title=\"MySQLi Procedural Prepared Statements\">&lt;?php\r\n\r\n\/\/ Create connection\r\n$conn = mysqli_connect($servername, $username, $password, $db);\r\n\r\n\/\/ Check connection\r\nif($conn === false){\r\n\tdie(\"ERROR: Could not connect. \" . mysqli_connect_error());\r\n}\r\n\r\n\/\/ Prepare insert statement\r\n$sql = \"INSERT INTO mytable (firstname, lastname, email)\r\nVALUES (?, ?, ?)\";\r\n\r\n$stmt = mysqli_prepare($conn, $sql);\r\n\r\n\/\/ Bind variables to the prepared statement as parameters\r\nmysqli_stmt_bind_param($stmt, \"sss\", $firstname, $lastname, $email);\t\t\r\n\r\n\/\/ set parameters and execute\r\n$firstname = \"Ron\";\r\n$lastname = \"Peters\";\r\n$email = \"ronpeters@gmail.com\";\r\nmysqli_stmt_execute($stmt);\r\n\r\necho \"New records created successfully\";\r\n\r\n\r\n\/\/ Close statement\r\nmysqli_stmt_close($stmt);\r\n\r\n\/\/ Close connection\r\nmysqli_close($conn);\r\n\r\n?&gt;\r\n<\/pre>\n<h3>MySQLi Object-oriented with Prepared Statements<\/h3>\n<pre class=\"wrap:true lang:default decode:true \" title=\"MySQLi Object-oriented Prepared Statements \">&lt;?php\r\n\r\n$servername = \"localhost\";\r\n$username = \"root\";\r\n$password = \"\";\r\n$db = \"mydb\";\r\n\r\n\/\/ Create connection\r\n$conn = new mysqli($servername, $username, $password, $db);\r\n\r\n\/\/ Check connection\r\nif ($conn-&gt;connect_error) {\r\n\tdie(\"Connection failed: \" . $conn-&gt;connect_error);\r\n}\r\n\r\n\/\/ prepare and bind\r\n$stmt = $conn-&gt;prepare(\"INSERT INTO mytable (firstname, lastname, email) \r\nVALUES (?, ?, ?)\");\r\n$stmt-&gt;bind_param(\"sss\", $firstname, $lastname, $email);\r\n\r\n\/\/ set parameters and execute\r\n$firstname = \"Tom\";\r\n$lastname = \"Jones\";\r\n$email = \"tomjones@gmil.com\";\r\n$stmt-&gt;execute();\r\n\r\necho \"New records created successfully\";\r\n\r\n$stmt-&gt;close();\r\n$conn-&gt;close();\r\n\r\n?&gt;<\/pre>\n<h3>PDO with Prepared Statements<\/h3>\n<pre class=\"wrap:true lang:default decode:true \" title=\"PDO Prepared Statements\">&lt;?php \r\n\r\n$servername = \"localhost\";\r\n$username = \"root\";\r\n$password = \"\";\r\n$dbname = \"mydb\";\r\n\r\ntry {\r\n    $conn = new PDO(\"mysql:host=$servername;dbname=$dbname\", $username, $password);\r\n    \/\/ set the PDO error mode to exception\r\n    $conn-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);\r\n\r\n    \/\/ prepare sql and bind parameters\r\n    $stmt = $conn-&gt;prepare(\"INSERT INTO mytable (firstname, lastname, email)\r\n    VALUES (:firstname, :lastname, :email)\");\r\n    $stmt-&gt;bindParam(':firstname', $firstname);\r\n    $stmt-&gt;bindParam(':lastname', $lastname);\r\n    $stmt-&gt;bindParam(':email', $email);\r\n\r\n    \/\/ insert a row\r\n    $firstname = \"John\";\r\n    $lastname = \"Doe\";\r\n    $email = \"john@example.com\";\r\n    $stmt-&gt;execute();\r\n\r\n    \/\/ insert another row\r\n    $firstname = \"Mary\";\r\n    $lastname = \"Moe\";\r\n    $email = \"mary@example.com\";\r\n    $stmt-&gt;execute();\r\n\r\n    echo \"New records created successfully\";\r\n    }\r\ncatch(PDOException $e)\r\n    {\r\n    echo \"Error: \" . $e-&gt;getMessage();\r\n    }\r\n$conn = null;\r\n\r\n?&gt;<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>First up there are three examples for inserting records into a MySQL database, one for each of the three following techniques: Using mysqli in a procedural manner. Using mysqli in an object-oriented fashion. Using PDO. After that there is an example of the same three approaches, but with the addition of using prepared statements for &hellip; <a href=\"https:\/\/frowningbear.com\/codebase\/2014\/04\/30\/php-mysql-inserting-records-reference\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;PHP MySQL Inserting Records Reference&#8221;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[11],"tags":[],"class_list":["post-1126","post","type-post","status-publish","format-standard","hentry","category-php"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/posts\/1126","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/comments?post=1126"}],"version-history":[{"count":1,"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/posts\/1126\/revisions"}],"predecessor-version":[{"id":1261,"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/posts\/1126\/revisions\/1261"}],"wp:attachment":[{"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/media?parent=1126"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/categories?post=1126"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/frowningbear.com\/codebase\/wp-json\/wp\/v2\/tags?post=1126"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}