Insert Varbinary data on Mysql table via php

Question

I have a small problem and i'm sure someone have the solution for it I have a binary data converted from a set of unsigned integers

$_BIN=pack('I*', 3563547,6587568,5468456,6458568,4568568);

and i want to insert it in a table that contain a varbinary column

$strSQL = "INSERT INTO table_name (id, ba) VALUES($id, $_BIN)";

for some reason i get the following error

Erreur SQL : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_' at line 1 SQL string : INSERT INTO table_name (id, ba) VALUES(467, ('ùZ¢O ^,¦EòÇjÞ|²EÎóÇjÞ|²EÎóp>)

Hoping you guys can help me out

Alexey · Accepted Answer · 2013-03-27 17:47:33Z

1

This works for me:

DROP TABLE IF EXISTS `binary`;
CREATE TABLE `binary`(
id INT NOT NULL AUTO_INCREMENT, 
ba VARBINARY( 100 ),
PRIMARY KEY(`id`)
) ENGINE=MYISAM;

and PHP code (using PDO):

<?php
$db = new PDO('mysql:dbname=test;host=localhost:4040','xxx','xxxx');

$_BIN=pack('I*', 3563547,6587568,5468456,6458568,4568568);

$rs = $db->prepare("INSERT INTO `binary`(`ba`) VALUES(?)");

$rs->execute(array($_BIN));

UPDATE

See below the code based on your suggested one with named parameters:

<?php
$_SNB = 23;
$_USR = "toto";
$_BIN = pack('I*', 24325, 2556456, 547577, 675746, 535646, 4564575, 575474, 4735);

$db = new PDO('mysql:host=localhost:4040;dbname=test','xxxx','xxxx');
$db->exec('SET CHARACTER SET utf8');
$rs = $db->prepare("INSERT INTO `spw_license` (`serial_num`, `user`, `ba_struct`) 
                  VALUES(:serial_num, :username, :ba_struct)");

$rs->bindValue(':serial_num', $_SNB, PDO::PARAM_INT);
$rs->bindValue(':username', $_USR);
$rs->bindValue(':ba_struct', $_BIN, PDO::PARAM_LOB);

try {
    if(!$rs->execute()) {
        var_dump($db->errorInfo());
    }
} catch(Exception $e) {
    echo 'got Exception: ' . $e->getMessage() . PHP_EOL;
}

Here is how the table looks like after this is launched:

mysql> select * from `spw_license`;
+------------+------+----------------------------------+
| serial_num | user | ba_struct                        |
+------------+------+----------------------------------+
|         23 | toto | ♣_  (☻' ∙ вO
 ^ _жE Є ⌂↕   |
+------------+------+----------------------------------+
1 row in set (0.00 sec)

edited Mar 27, 2013 at 17:47

answered Mar 25, 2013 at 18:22

Alexey

3,4847 gold badges32 silver badges47 bronze badges

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

6 Comments

Gibnem Over a year ago

many thanks Alexy but in all the rest of the code i'm not working with PDO i don't know if it is really the best thing to do now and change all the rest. I guess i need to do in MySql

Alexey Over a year ago

then try to $strSQL = spintf("INSERT INTO table_name (id, ba) VALUES($id, '%s')",mysql_real_escape_string($_BIN));

Alexey Over a year ago

what was the error? Can you install mysql-proxy and connect through it and udate your question with the query as you see it in mysql-proxy?

Gibnem Over a year ago

Hi Alexy, I tried writing my queries using the PDO but what i got on the database is an hex representation of my data on the table column and not real binary data

Alexey Over a year ago

did you check that it's really hex with mysql CLI client? Or any other client? And what if you do select in php PDO (same script), will it be hex or not?

|

Collectives™ on Stack Overflow

Insert Varbinary data on Mysql table via php

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related