Insert form $_POST array into a MySQL database using mysqli

Question

I have a $_POST array and I am trying to code an insert into a MySQL database without having to list through all the field names.

The array is a single row that needs to be inserted into the database.

The array looks like this:

Array
(
    [membership_type] => 4
    [title] => Mr
    [first_name] => John
    [last_name] => Smith
    [known_as] => John
    [address_1] => 10 High Street
    [address_2] => Big House
    [address_3] => Big Road
    [address_4] => Chipping Sodbury
    [address_5] => Bristol
    [post_code] => BS37 1AB
    [home_tel] => 01454 123456
    [mobile] => 07777 123456
    [email] => john@email.com
    [confirm_email] => john@email.com
    [day_dob] => 21
    [month_dob] => 09
    [year_dob] => 1974
    [volunteer] => on
    [employment_status] => employed
    [college_nus] => 
    [employment_address] => 50 Station Road
Chipping Sodbury
Bristol
BS37 2CD
    [occupation] => Managerial/Professional
    [employement_email] => john@work.com
    [employement_phone] => 01454 654321
    [terms] => 1
)

I have coded the form field names to correspond with the field names in the database for ease.

Many thanks,

John

This feels like a bad idea. How will you handle validation on a per-field level if you're just iterating over the fields and inserting whatever data the user provided? — Matt Raines, May 01 '16 at 07:25
@MattRaines The form is using Foundations data-abide so all the validation is being done before the form is submitted. — John Higgins, May 01 '16 at 07:26
This post may help you http://stackoverflow.com/questions/19665981/php-mysql-prepared-statement-to-insert-an-array — simon, May 01 '16 at 07:27
I don't know Foundations but it [looks like](http://foundation.zurb.com/sites/docs/v/5.5.3/components/abide.html) a **client-side** validation. If you follow any of the answers here or to the linked question you'll want to be absolutely certain the user didn't provide a form field called `id) VALUES(1, 2, 3, 4, 5, ...); DROP TABLE Students; -- ` — Matt Raines, May 01 '16 at 07:46
@smnvhn - I tried the code from that post and get a Fatal error: Call to undefined function insert_data() error on the 4th line of the answer code: insert_data($mysqli, $array, $table_name); — John Higgins, May 01 '16 at 07:47
@JohnHiggins The definition of insert_data function on the 5th line. Check for the typos in name or if you put function as a method in class maybe you have forgot to add $this? — simon, May 01 '16 at 07:57

score 3 · Accepted Answer · 2016-05-01T08:03:32.570

3

you can use this method :

NOTE :i use PDO

first connect to DB like:

$connection = new PDO('mysql:host=' . yourHost . ';dbname=' . youDbName . ';charset=utf8', DBUser, Dbpass);

$data=$_POST;
  $bind = ':' . implode(',:', array_keys($data));
        $field = explode(",", $bind);
        $returnQuery = "INSERT INTO `tableName` (" . implode(",", array_keys($data)) . ") VALUES (" . $bind . ") ";
        $bind = $connection ->prepare($returnQuery);
        $bind->execute(array_combine($field, array_values($data)));

hope this help

edited May 01 '16 at 08:03

answered May 01 '16 at 07:45

@mohshenshakibafar - tried the PDO method and get the following error - Fatal error: Using $this when not in object context in.... – John Higgins May 01 '16 at 08:00
hmm, i fix this problem for you :) – May 01 '16 at 08:01
@mohshenshakibafar - included the connection string and now get - Fatal error: Using $this when not in object context in... – John Higgins May 01 '16 at 08:12
@JohnHiggins , Are you sure you copy all code above and change variable and constant to your database ?(like:yourhost must change to localhost or ...) – May 01 '16 at 08:17
@mohshenshakibafar- This is the connection string I am using: $connection = new PDO('mysql:host=10.10.10.10;dbname=database', user, password); – John Higgins May 01 '16 at 08:21
@JohnHiggins , so , you still getting `Fatal error: Using $this when not in object context in`? – May 01 '16 at 08:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/110744/discussion-between-john-higgins-and-mohsen-shakibafar). – John Higgins May 01 '16 at 08:23
PDO is a much better solution. Thanks again for your help and persistence – John Higgins May 01 '16 at 08:44

score 0 · Answer 2 · answered May 01 '16 at 07:45

You may need something like:

<?php
$query = "";
if(isset($_POST)){
    foreach( $_POST as $key => $val ) {
    $query .= " `$key`='$val', ";
    }
$query = preg_replace('/,$/', '', $query); // removes the last comma
}
//$query: `name`='pedro', `email`='stack@stack.com'

Insert form $_POST array into a MySQL database using mysqli

2 Answers2