Jump to content

Split a string and Insert in MySQL


rx3mer
Go to solution Solved by Psycho,

Recommended Posts

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!

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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:

Capture.jpg

 

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 :shrug:

Link to comment
Share on other sites

  • Solution

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;
} 
  • Like 1
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.