Issue on Inserting JavaScript Array Data into MySQL Database

Question

Can you please take a look at following snippets and let me know why I am not able to insert array values into the database. In Js file I have:

 <script>
        var arr = ["foo", "2015/11/04", "Jill", "Smith", "60"];
        var serializedArr = JSON.stringify( arr );
      $("#loader").on("click", function(){
        var upload = $.ajax({
                            type: "POST",
                            url: "loader.php",
                            data: {array:serializedArr},
                            cache: false,
                            beforeSend: function() {
                            }
        });
});

and in PHP file

<?php
   define ( 'DB_HOST', 'localhost' );
   define ( 'DB_USER', 'root' );
   define ( 'DB_PASSWORD', '' );
   define ( 'DB_NAME', 'dbapp' );

 //$array = json_decode($_POST['array']);
$array = $_POST['array'];
//Open a new connection to the MySQL server
$mysqli = new mysqli('DB_HOST','DB_USER','DB_PASSWORD','DB_NAME');

$arr_one =   $mysqli->real_escape_string($array[0]);
$arr_two =   $mysqli->real_escape_string($array[1]);
$arr_three = $mysqli->real_escape_string($array[2]);
$arr_four =  $mysqli->real_escape_string($array[3]);
$arr_five =  $mysqli->real_escape_string($array[4]);

if ($mysqli->connect_error) {
    die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
}

//MySqli Insert Query
$insert_row = $mysqli->query("INSERT INTO `models` (name, date, nip, tip, age) VALUES($arr_one, $arr_two, $arr_three, $arr_four, $arr_five)");

if($insert_row){
    print 'Success! ID of last inserted record is : ' .$mysqli->insert_id .'<br />'; 
}else{
    die('Error : ('. $mysqli->errno .') '. $mysqli->error);
}
// close connection 
$mysqli->close();
?>

I tried both

$array = json_decode($_POST['array']);

and

$array = $_POST['array'];

but I am not able to load the data and even no error message on page?!

I'd expect $mysqli = new mysqli('DB_HOST','DB_USER','DB_PASSWORD','DB_NAME'); to fail — hjpotter92
– hjpotter92, Commented Nov 5, 2015 at 7:32
Do you see anything in the console as the ajax request is made? As @hjpotter92 said, the call to mysqli uses strings rather than the previously defined constants — Professor Abronsius
– Professor Abronsius, Commented Nov 5, 2015 at 7:36
What exactly is in $_POST['array'] when you process the request? — Jiri Tousek
– Jiri Tousek, Commented Nov 5, 2015 at 8:05

hjpotter92 · Accepted Answer · 2015-11-05 08:15:06Z

1

Use the defined constants instead of strings when establishing the connection.
Use prepared statements instead of passing variables inside the query.
As stated in the other answer, you are trying to access index of a string variable (since the data sent was JSON string).
Use success, error handles in the ajax call, to output the results/errors to console.

So, you'll need to json_decode the received data before doing anything with it.

$array = json_decode( $_POST['array'] );
$mysqli = new mysqli( DB_HOST, DB_USER, DB_PASSWORD, DB_NAME );

$insert_row_stmt = $mysqli->prepare( "INSERT INTO `models` (name, date, nip, tip, age) VALUES(?, ?, ?, ?, ?)");
$insert_row_stmt->bind_param( 'ssssi', 
    $array[0],
    $array[1],
    $array[2],
    $array[3],
    $array[4]
);
$insert_row = $insert_row_stmt->execute();

var upload = $.ajax({
    type: "POST",
    url: "loader.php",
    data: {array:serializedArr},
    cache: false,
    success: function(d) {
        console.log(d);
    }
});

edited Nov 5, 2015 at 8:15

answered Nov 5, 2015 at 7:43

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Behseini Over a year ago

Thanks hjpotter92 this is working BUT there is one issue here! the code is inserting 0 for name which must be foo but the others are correct

hjpotter92 Over a year ago

@Behseini Put a var_dump($array); and check the result you get.

Behseini Over a year ago

but I am posting the data! then how can I use the var_dump($array);

hjpotter92 Over a year ago

By putting console.log in a success handler of your $.ajax call.

Behseini Over a year ago

i really like to learn this debugging! so console.log what?

|

Christopher · Accepted Answer · 2015-11-05 07:37:47Z

1

The problem is you are JSON.stringify'ing the array before sending it through the AJAX request. That means array = "["foo", "2015/11/04", "Jill", "Smith", "60"]", which is technically not a valid JSON object.

Try wrapping the array in a js object then stringifying it. such as

var arr = {data: ["foo", "2015/11/04", "Jill", "Smith", "60"] };

then try

$array = json_decode($_POST['array']).data;

answered Nov 5, 2015 at 7:37

Christopher

4323 silver badges12 bronze badges

1 Comment

Behseini Over a year ago

Thanks Christooher but I got confused here! so how about data: {array:serializedArr}, ?

Muhammad Ali · Accepted Answer · 2015-11-05 08:14:56Z

0

<div id="loader"></div>
<script src="jquery.min.js"></script>
<script>
    var arr = ["foo", "2015/11/04", "Jill", "Smith", "60"];
    $("#loader").load('load.php',{arr:arr});
</script>

load.php:

 <?php
       $arr = $_POST['arr'];
       $insert_row = $mysqli->query("INSERT INTO `models` (name, date, nip, tip, age)
                      VALUES($arr[1], $arr[2], $arr[3], $arr[4], $arr[5])");
    ?>

you can add values like this in database.

edited Nov 5, 2015 at 8:14

answered Nov 5, 2015 at 7:54

Muhammad Ali

2,6582 gold badges18 silver badges22 bronze badges

Collectives™ on Stack Overflow

Issue on Inserting JavaScript Array Data into MySQL Database

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related