Mysqli and BLOB binary database fields

This post exists to save you hours of research on the web. The following things may be true about you:

• You recently transitioned from mysql PHP functions to mysqli functions (staying current)
• You prefer procedural PHP over object-oriented PHP (functions and arrays are cooler than classes)
• You are storing some data in a MySQL BLOB, MEDIUMBLOB, or LONGBLOB field, which is Binary

Now, the only advice I found involved storing a file that is already binary. Fine for them, but what if the binary data is PHP created?

I use the following functions to store Arrays in MySQL BLOB fields by converting them to JSON, then using gzcompress to shrink the data down as well as speed up the transfer time and reduce disk I/O. NOTE: The gzcompress() function returns binary data.

function array_encode($array,$mysqli){ // Convert Array for DB storage json compressed in BLOB $result = gzcompress(json_encode($array)); if ($mysqli){ mysqli_real_escape_string($mysqli,$result); } return $result; }   function array_decode($blob){ // Convert into Array from json compressed BLOB in DB return json_decode(gzuncompress($blob),true); }

I’m not going to get into the discussion of when this may or may not be appropriate for you to use, but I will discuss the problem you run into when doing this in mysqli compared to mysql.

Simply put, you can’t just send binary data normally. It requires a prepared statement. Since I prefer procedural code, I’ve put together a simple example below that creates an empty array.

$arr = []; // empty array for BLOB $data = array_encode($arr,$mysqli); // json, compress; now binary // ? is used in prepared statements as the input that will be replaced later $query = "INSERT INTO (`data`) VALUES (?)"; $stmt = mysqli_stmt_init($mysqli); // initialize a prepared statement object mysqli_stmt_prepare($stmt, $query); // prepare the query $null = NULL; // because you can't pass NULL directly into the next line mysqli_stmt_bind_param($stmt, "b",$null); // You could replace $null with $data here and it also works mysqli_stmt_send_long_data($stmt,0,$data); // the magic. sending the binary data $result = mysqli_stmt_execute($stmt) or die(mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt);

While I recommend reading the documentation on mysqli_stmt_bind_param() function, the basics is that you are passing over the prepared statement object ($stmt), then declaring the type of value it should expect (b = binary), and then giving it that value (we’re using $null temporarily).

The following line mysqli_stmt_send_long_data() is what actually switches it to your binary data and makes it all work. The second passed value (0 in the above example) represents the position (order of the ? like a numerically indexed array) in the prepared statement. Below you’ll find an example where this will be clearer.

Let’s look at a more complicated function that uses two prepared variables.

function update_blob($mysqli,$table,$primarykey,$array=[]){ $data = array_encode($array,$mysqli); // uses the function at the beginning // The query works like an upsert, adding it or updating it depending on if it exists $query = "INSERT INTO `$table` (`primarykey`,`data`) VALUES (?,?) ON DUPLICATE KEY UPDATE `data`=VALUES(`data`)"; $stmt = mysqli_stmt_init($mysqli); mysqli_stmt_prepare($stmt,$query); $null = NULL; mysqli_stmt_bind_param($stmt,"ib",$primarykey,$null); // ib = integer first, binary second mysqli_stmt_send_long_data($stmt,1,$data); // note the 1. 0 would be the integer and incorrect $result = mysqli_stmt_execute($stmt) or die(mysqli_stmt_error($stmt)); mysqli_stmt_close($stmt); return $result;