Send array from js to php to SQL database

Question

I hope I'm missing something easy here.

I have an array created in js, say: var ids = [1,2,3,4,5];

Now I want this array to populate a column in my SQL database table.

I have the pipeline setup for adding single elements to my table like this:

request is sent via ajax:

$.ajax({
  type: "POST",
  url: "some.php",
  data: {
    ids: ids,
  }
});

some.php receives the data (connection, etc. is set up):

$ids = $_POST['ids'];

SQL is used to insert single values to the column COL_ID

$sql = "INSERT INTO `mydb`.`dbtable`(`COL_ID`) VALUES ('$ids)";

This pipeline works for single values (e.g. of ids = 2 but fails for an array.

What I'd want is for COL_ID to contain each array element as a row

| COL_ID    |
|--------   |
| 1         |
| 2         |
| 3         |
| 4         |
| 5         |

I suspect it's in the SQL query. Any help is welcome.

Arjan · Accepted Answer · 2020-05-21 22:25:14Z

2

First, use prepared statements, don't insert post data directly into a database query. Using post data directly means you are vulnerable to sql injection attacks.

As @DanielReed indicated, the correct format is

INSERT INTO table_name (column_list) VALUES (value_list_1), (value_list_2), (value_list_3);

You can build this dynamically:

$ids = $_POST['ids'];
// Make sure we get the correct number of ? for prepared statements
$params = str_repeat('(?), ', count($ids));
// Strip the trailing space and comma
$params = substr($params, 0, -2);

$sql = 'INSERT INTO `mydb`.`dbtable`(`COL_ID`) VALUES ' . $params;

Now you can use $sql as your query and $ids as the values for the prepared statement. This prevents sql injection.

answered May 21, 2020 at 22:25

Arjan

9,8941 gold badge33 silver badges43 bronze badges

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

3 Comments

ben_aaron Over a year ago

Thanks. Sorry for the question: if I use $sql as my query in, say, mysqli_query($con,$sql), where do I specify that it should use the $ids? Maybe I misunderstand what you mean by "use ... $ids as the values for the prepared statement".

Arjan Over a year ago

You could use mysqli_prepare($con, $sql); and then mysqli_stmt_bind_param(...). See the manual for examples: php.net/manual/en/mysqli-stmt.bind-param.php

DanielRead Over a year ago

This is a better answer then mine. @ben_aaron You should always bind the value using what he said otherwise people can perform SQL injection attacks on you.

DanielRead · Accepted Answer · 2020-05-21 23:04:54Z

1

PHP receives it as an array.

SQL Syntax wants it in the following syntax:

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

So what you could do is:

$sql = "INSERT INTO `mydb`.`dbtable`(`COL_ID`) ";
foreach($ids as $value){
     $sql .= "(".$value.") ";
}

edited May 21, 2020 at 23:04

answered May 21, 2020 at 22:08

DanielRead

2,8442 gold badges31 silver badges42 bronze badges

3 Comments

ben_aaron Over a year ago

Thanks. If I understand correctly, this creates multiple $sql (one in each run). Where would I make the query, e.g. with mysqli_query($con,$sql)?

DanielRead Over a year ago

Yeah so the .= means concatenate. It pretty much appends it to the end of the string. So if you were to say $str = "Hello"; $str .= " My name is"; $str .= " Daniel"; --- the value of $str would be "Hello My name is Daniel". So you would run the query the same as you mentioned in your comment. You may have to add a space after the parenthesis since it's going to just join them together, i'll edit my answer to add that.

DanielRead Over a year ago

Oh and they need commas after each one too, so my answer wont work without tweaking

Collectives™ on Stack Overflow

Send array from js to php to SQL database

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related