Split a string and Insert in MySQL

[rx3mer]

I am using AJAX to send an array of values to a PHP page that will insert the data into MySQL database. For some reason only the last 5 values are inserted into the MySQL database and I am woundering how to fix that. I am using foreach loop.

 

AJAX Request: Array:

[".testimonial", 1119, 316, 1663, 608, "#header", 723, 66, 1663, 608]

Posting the array:

Sending Array Parameters using " ; " to split.

clicks  
.testimonial;1119;316;1663;608;#header;723;66;1663;608

Source Sent:

clicks=.testimonial%3B1119%3B316%3B1663%3B608%3B%23header%3B723%3B66%3B1663%3B608

PHP Page

<?php


$clicks = $_GET["clicks"];
$clickArray = explode(";",$clicks);
$arrays = array_chunk($clickArray, 5);


$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "clickmap";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$keys = array('postIdentifier', 'postPos_x', 'postPos_y','postWindowWidth','postWindowHeight');

foreach ($arrays as $array_num => $array) {
  $values = array();
  foreach ($array as $item_num => $item) {
    $values[] = $item;
  }

  $data = array_combine($keys, $values);
  extract($data); // now your variables are declared with the right values http://php.net/manual/en/function.extract.php

  $sql = "INSERT INTO data (user_id, identifier_name, pos_x, pos_y, window_width, window_height, status)
  VALUES ('1', '$postIdentifier', '$postPos_x', '$postPos_y','$postWindowWidth','$postWindowHeight', 'ok')";

}

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>

Is this the correct way of coding it, and why does it post only the last 5 values? Any help would be much appreciated!

[Barand]

try

$clicks = '.testimonial;1119;316;1663;608;#header;723;66;1663;608';
$keys = array('postIdentifier', 'postPos_x', 'postPos_y','postWindowWidth','postWindowHeight');

$arr = explode(';', $clicks);
$data = array_chunk($arr, 5);

foreach ($data as $rec) {
    $sql = "INSERT INTO data (user_id, " . join(', ', $keys) . "', status) VALUES ";
    $sql .= "(1, '" . join("', '", $rec) . "', 'ok')";;
    echo $sql . '<br>';
}

(Note I echoed the queries instead of executing)

[rx3mer]

I had to slightly modify the code to make it work without errors, but I am still having the same issue.

INSERT INTO data (user_id, identifier_name, pos_x, pos_y, window_width, window_height, status) VALUES (1, '.testimonial', '1119', '316', '1663', '608', 'ok')
INSERT INTO data (user_id, identifier_name, pos_x, pos_y, window_width, window_height, status) VALUES (1, '#header', '723', '66', '1663', '608', 'ok')
New record created successfully

Only the last INSERT has been done:

 

Updated code:

<?php
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "clickmap";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}



$clicks = '.testimonial;1119;316;1663;608;#header;723;66;1663;608';
$keys = array('identifier_name', 'pos_x', 'pos_y','window_width','window_height');

$arr = explode(';', $clicks);
$data = array_chunk($arr, 5);

foreach ($data as $rec) {
    $sql = "INSERT INTO data (user_id, " . join(', ', $keys) . ", status) VALUES ";
    $sql .= "(1, '" . join("', '", $rec) . "', 'ok')";;
    echo $sql . '<br>';
}



if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

?>

Really strange

[Psycho]

The answer is simple.

 

You do a foreach() to define different INSERT statements in the $sql variable. Each iteration of the loop overwrites the preceding $sql statement with the new statement. Therefore, when the loop completes, $sql only contains the statement for the last INSERT statement. It isn't until after the loop completes (and $sql only contains the last INSERT statement) that you actually execute the statement.

 

You could put the execution of the statement in the loop

 

foreach ($data as $rec) {
    $sql = "INSERT INTO data (user_id, " . join(', ', $keys) . ", status) VALUES ";
    $sql .= "(1, '" . join("', '", $rec) . "', 'ok')";;
    echo $sql . '<br>';
 
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

 

But running queries in loops can be a performance issue. It's better to create one INSERT statement with all the records.

 

//Create an array of values for the insert statement
$values = array();
foreach ($data as $rec) {
    $values[] = "(1, '" . join("', '", $rec) . "', 'ok')";
}
 
//Create a single insert statement with all the values
$sql = "INSERT INTO data (user_id, " . join(', ', $keys) . ", status)";
$sql .= "VALUES " . implode(", ", $values);
echo $sql . '<br>';
 
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}