insert into MySQL from an array with multi-type elements PHP

Question

I'm trying to insert data from an array.The problem is when i implode the array , int values convert to String.Is there a way to keep each array element type?

$insData=array (
    0 => string 'sds' (length=3)
    1 => string 'dsds' (length=4)
    2 => string '1251' (length=4)
    3 => string 'jklj' (length=4)
    4 => string 'jklj' (length=4)
    5 => int 0
    6 => string 'jkkj' (length=4)
    7 => int 0
    8 => int 0
    9 => int 0
    10 => int 0
    11 => int 0
    12 => int 0
    13 => int 0
    14 => int 0
    15 => int 0
    16 => int 0
    17 => int 0
    18 => int 0
    19 => int 0
    20 => int 0
    21 => int 0
    22 => int 0
    23 => int 0
    24 => int 0
    25 => string '2017-06-28 10:06:24')

$escaped_values = array_map('mysql_real_escape_string',array_values($insData));
$values  = implode(", ", $insData);
$sql1 = "INSERT INTO `myTable` VALUES ($values)";
var_dump($sql1);
=> INSERT INTO `myTable` VALUES (sds, dsds, 1251, jklj, jklj, 0, jkkj, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2017-06-28)
try {
    $stmt1 = $bdd->prepare($sql1, array(PDO::ATTR_CURSOR, PDO::CURSOR_SCROLL));
    $stmt1->execute();
    $row1 = $stmt1->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT);
    $stmt1 = null;
  }
  catch (PDOException $e){
    print $e->getMessage();
  }

I'm getting a MySQl error .Is there a way to get this :

INSERT INTO `myTable` VALUES ('sds', 'dsds', 1251, 'jklj', 'jklj', 0, 'jkkj', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, '2017-06-28 10:06:24')

error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sds' in 'field list' — Maha
– Maha, Commented Jun 28, 2017 at 9:17
You would be better off using something like PDO, there you use bind variables, this means that the values will retain their original types anyway. stackoverflow.com/questions/12019826/… may help with that. — Nigel Ren
– Nigel Ren, Commented Jun 28, 2017 at 9:21

Nigel Ren · Accepted Answer · 2017-06-28 10:08:17Z

2

From the link I posted, using PDO prepared statements and linking the values when you execute the statement...

$valueString = implode(',', array_fill(0, count($insData), '?'));

$stmt = $bdd->prepare("INSERT INTO myTable VALUES ({$valueString})");
$stmt->execute(array_values($insData));

Edit: I think the difficulties you were having is that you wanted to retain the type whilst ensuring safety. This would involve putting quotes round only the string fields in your query and not the numeric ones.

You could have done a loop through the array and used is_string($value) and then only quoted these fields. But this adds a lot of complication and extra processing.

edited Jun 28, 2017 at 10:08

answered Jun 28, 2017 at 9:41

Nigel Ren

57.2k11 gold badges49 silver badges59 bronze badges

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

3 Comments

Frits Over a year ago

Not sure who downvoted you, but here's a +1 for you :)

Frits Over a year ago

@Maha don't forget to mark this answer as the accepted answer

Maha Over a year ago

though, i would love to have an explanation why PDO query didn't work ..

Rohit Dalal · Accepted Answer · 2017-06-28 09:15:47Z

0

$integerIDs = array_map('intval', explode(',', $string));

answered Jun 28, 2017 at 9:15

Rohit Dalal

8649 silver badges13 bronze badges

5 Comments

Frits Over a year ago

Please try to avoid just dumping code as an answer and try to explain what it does and why. Your code might not be obvious for people who do not have the relevant coding experience. Please edit your answer to include clarification, context and try to mention any limitations, assumptions or simplifications in your answer.

Maha Over a year ago

which $string ?

Rohit Dalal Over a year ago

@Frits My bad try this link to get complete info - w3schools.com/php/func_array_map.asp

Rohit Dalal Over a year ago

@Maha $string is the string you want to convert into array. In your case its $insData

Frits Over a year ago

No no no, you misunderstand me. Don't give me a link, add the relevant text in your answer. Your answer should be completely understandable before I click on the link you supply (which should be supplied in your answer, not the comments). The link should be merely to reference the source of your answer. Try adding the basic explanation to your answer via the edit function - this will greatly improve the answer and your chances of an upvote :)

Collectives™ on Stack Overflow

insert into MySQL from an array with multi-type elements PHP

2 Answers 2

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related