How do I save my current POST array to MySQL

Question

This is my current $_POST output.

Array
(
    [fullname] => John Doe
    [email] => [email protected]
    [phone] => 123-455-6444
    [address] => 23-10 My Address
    [zip] => 12345

    [fullname_2] => Array
        (
            [0] => M. Owen
            [1] => V. Persie
        )

    [email_2] => Array
        (
            [0] => [email protected]
            [1] => [email protected]
        )

    [phone_2] => Array
        (
            [0] => 123-455-6555
            [1] => 222-111-1111
        )

    [submit] => Submit
)

I want the data above will store to my current database format looks like below.

id  | fullname  |  email             | phone         | address           | zip    | uid
----------------------------------------------------------------------------------------
1   | John Doe  | [email protected]      | 123-455-6444  | 23-10 My Address  | 12345  | 1
2   | M. Owen   | [email protected]     |               |                   |        | 1
3   | V. Persie | [email protected] |               |                   |        | 1

I'm getting blur how to save the array to MySQL.

$query = "INSERT INTO users SET fullname ='" . $fullname . "', email ='" . $email . "', phone='" . $phone . "', address ='" . $address . "', zip='" . $zip . "'";
$db->rq($query);

Let me know the clue.

Answer 1

1. Read about SQL Injection.
2. Take a look at simple SQL INSERT syntax.
3. Read about escaping special characters in PHP MySQL API.
4. Read about database normalization.

Modify this code to fit your needs:

$query = sprintf("INSERT INTO users (FieldName1, FieldName2, ...) VALUES ('%s', '%s', ...)", mysql_real_escape_string($value1), mysql_real_escape_string($value2), ...);

Good luck!

Answer 2

Firstly I assume that last column uid refers to id of user with all data inserted and is used to mark connection between first element and two others.

Secondly I do not know anything about php DB wrapper from Google (or can you provide any codename for this) so I assume that it is one of many db wrapper project hosted on code.google.com. Since I do'nt know which one is it , you'll have to look yourself for equivalent to php function mysql_insert_id().

And general algorithm to accomplish what you need:

1. Insert first row as in your example.
2. Get last insert id for this row.
3. Iterate through $_POST['fullname_2'] to collect data for next query
4. Run it ;)

Example:

$lastId = $db->functionToGetLastId();
$valuesArray = array();
foreach($_POST['fullname_2'] as $key => $value){
    $fullname = sanitizeFunction($value);
    $email = sanitizeFunction($_POST['email_2'][$key]);
    $phone = sanitizeFunction($_POST['phone_2'][$key]);
    $valuesArray[] = "('{$fullname}','{$email}','{$phone}',{$lastId})";
}
$secondQuery = "INSERT INTO users (fullname,email,phone,uid) VALUES " . implode(',',$valuesArray);
$db->rq($secondQuery);

Answer 3

Best use PDO and prepared queries instead of insert query 'as-is' Something like this

$fullname = $_POST['fullname'];
$phone = $_POST['phone'];

$conn = new PDO(...); //params of connection
$stmt = $conn->prepare('
   INSERT INTO users
   (fullname, phone)
   VALUES
   (?, ?)'
);
$stmt->execute(array(
     $fullname,
     $phone
));