MySQLi. Upload and save image file as BLOB. PostFile. PostText

[Juan_Antonio]

Hello friends,

In this tutorial we saw how we can save text in a MySQL database:
MySQLi. PHP. GET. POST. Insert. Update. Delete. Show. 000webhost

0.- Now we are going to upload an image file and save it as a BLOB in MySQL.

BLOB: Binary Large Object, it can store image, sound, video, pdf, video files…
There are several types:

• TINYBLOB: Stores up to 255 bytes.
• BLOB: Stores up to 65,535 bytes.
• MEDIUMBLOB: Stores up to 16,777,215 bytes.
• LONGBLOB: Stores up to 4,294,967,295 bytes.

We will use MEDIUMBLOB.

Using phpMyAdmin we create the table: dibujos with 4 columns (id, image, extension, name), note that the image column is of type mediumblob.

This tutorial in Spanish:
http://kio4.com/appinventor/366_mysql_blob_imagenes_Appinventor.htm

[Juan_Antonio]

1.- Upload and save an image file as a BLOB.

Using ImagePicker we select an image, we obtain its path and name.
We upload the image using the file blob_upload.php and PostFile.

p366i_mysqli_imagenes_blob.aia (3.1 KB)

PHP:
We connect to the database. We get the file using $ file = file_get_contents ('php://input');
We save the file as a BLOB in the database.
In addition we also save the file in the current directory.

blob_upload.php

<?php
 // 1.- IDENTIFICACION nombre de la base, del usuario, clave y servidor
$db_host="localhost";
$db_name="id1975555_data_base"; // fake
$db_login="id1975555_juan"; // fake
$db_pswd="Contraseña"; // fake

// 2.- CONEXION A LA BASE DE DATOS
$link = new mysqli($db_host, $db_login, $db_pswd, $db_name);

if ($link->connect_error) {
    die("Error de conexión: " . $link->connect_error);
} else {

// 3.- OBTENER ARCHIVO - GET FILE
  $file = file_get_contents('php://input');
   if (file_put_contents($_GET['imagen'],$file == TRUE))  {
   
		$nombre = $_GET['imagen'];
		$extension = pathinfo($nombre, PATHINFO_EXTENSION);
		// $extension = mime_content_type($nombre);
		
		// Archivo a MySQLi. BLOB. - File to MysQLi BLOB.
		$blob = mysqli_real_escape_string($link, $file);
		$resultado = mysqli_query($link, "INSERT INTO dibujos(imagen, extension, nombre) VALUES ('$blob', '$extension', '$nombre')");
		
		if ($resultado) {echo $nombre. " uploaded.";} 
		              else {echo "Error: ".msqli_error($link);}
					
		mysqli_close($link);	
   } else {
	echo "Error in GET file";  
   }
   
 }
?>

[Juan_Antonio]

2.- Upload and save an image file as a BLOB. List of files. Download a file.

p366Di_mysqli_imagenes_blob.aia (4.1 KB)

• Using ImagePicker we select an image file.
  Using the blob_upload.php file we upload the image to the BLOB database and also copy it to the current php directory. (same as previous example)

• Through blob_listado.php we obtain (in a ListView) a list of file names (BLOB) we save in the database.

• Using blob_obtener, we copy a BLOB file from the database to the current directory, we always call it temporal.png

• We can get the temporal.png file using its web address:
  https://kio40.000webhostapp.com/temporal.png

blob_listado.php

<?php
// Juan A. Villalpando
// KIO4.COM

 // 1.- IDENTIFICACION nombre de la base, del usuario, clave y servidor
$db_host="localhost";
$db_name="id19785555_my_base"; // fake
$db_login="id19785555_juan"; // fake
$db_pswd="Constraseña_1234"; // fake

// 2.- CONEXION A LA BASE DE DATOS
$link = new mysqli($db_host, $db_login, $db_pswd, $db_name);

// 3.- Lista nombre.
$sql = "SELECT nombre FROM dibujos"; 
$result = mysqli_query($GLOBALS['link'],$sql);
while($row = mysqli_fetch_array($result)) {
echo $row["nombre"]."\n"; 
}
mysqli_close($link);
?>

blob_obtener.php

<?php
// Juan A. Villalpando
// KIO4.COM

 // 1.- IDENTIFICACION nombre de la base, del usuario, clave y servidor
$db_host="localhost";
$db_name="id19785555_my_base"; // fake
$db_login="id19785555_juan"; // fake
$db_pswd="Constraseña_1234"; // fake

// 2.- CONEXION A LA BASE DE DATOS
$link = new mysqli($db_host, $db_login, $db_pswd, $db_name);

// 3.- BLOB to temporal.png .
$nombre = $_POST['nombre'];
echo $nombre;
$sql = "SELECT imagen, extension FROM dibujos WHERE nombre='$nombre' ";
	$result = mysqli_query($link,"$sql");
	$row = mysqli_fetch_array($result);
	// Copia el archivo desde el BLOB al directorio actual, siempre con el nombre temporal.png
	// file_put_contents('temporal.'.$row["extension"], $row["imagen"] );
	file_put_contents('temporal.png', $row["imagen"] );

mysqli_close($link);

?>

[Juan_Antonio]

3.- We convert a file to text using Base 64, upload it and save it as a BLOB.

Sometimes we cannot upload the file directly (as file), in this case we convert the file to text using the Base64 extension.
We upload the Base 64 string using the file blob_upload2.php and PostText

p366Bi_mysqli_imagenes_blob.aia (16.2 KB)

PHP:
We connect to the database. We get the text using $data = $_POST;
The PHP code decodes Base 64 and we save that information as BLOB.
[We could have saved the text directly as a string, but in this example we prefer BLOB]
In addition we also save the file in the current directory.

<?php
// Juan A. Villalpando
// KIO4.COM

 // 1.- IDENTIFICACION nombre de la base, del usuario, clave y servidor
$db_host="localhost";
$db_name="id13313319_kio4";
$db_login="id13313319_juanantonio";
$db_pswd="contraseña";

// 2.- CONEXION A LA BASE DE DATOS
$link = new mysqli($db_host, $db_login, $db_pswd, $db_name);

// 3.- OBTENER ARCHIVO - GET FILE
	$datos=$_POST;
	$contenido=$datos['contenido'];
	$nombre=$datos['nombre'];
	
	$extension = pathinfo($nombre, PATHINFO_EXTENSION);
	// $extension = mime_content_type($nombre);
    
	$original = base64_decode(str_replace(array('-', '_',' ','\n'), array('+', '/','+',' '), $contenido));

	// Archivo a MySQLi. BLOB. - File to MysQLi BLOB.
	$blob = mysqli_real_escape_string($link, $original);
	$resultado = mysqli_query($link, "INSERT INTO dibujos (imagen, extension, nombre) VALUES ('$blob', '$extension', '$nombre')");
	mysqli_close($link);
	
	// Archivo al directorio actual. - File to actual directory
	$ifp = fopen( $nombre, "wb");
	fwrite($ifp, $original);
	fclose($ifp);
	echo "Guardado. Saved."; 
?>

[Juan_Antonio]

4.- List of saved file names. Get the file saved in BLOB.

With...

$ sql = "SELECT imagen, extension FROM dibujo WHERE nombre = '$nombre'";
$ result = mysqli_query ($link, "$sql");
$ row = mysqli_fetch_array($result);
file_put_contents ('temporal.png', $row["image"]);

...we extract the content of BLOB and save it in a file in the current directory, the name of that file will be: temporal.png

We can see that file using a WebView component

p366Ci_mysqli_imagenes_blob.aia (17.1 KB)

<?php
// Juan A. Villalpando
// KIO4.COM

 // 1.- IDENTIFICACION nombre de la base, del usuario, clave y servidor
$db_host="localhost";
$db_name="id13313319_kio4";
$db_login="id13313319_juanantonio";
$db_pswd="contraseña";

// 2.- CONEXION A LA BASE DE DATOS
$link = new mysqli($db_host, $db_login, $db_pswd, $db_name);

$boton = $_POST['boton'];

// 3.- OBTENER ARCHIVO - GET FILE
if ($boton == "btnInsertar"){
	$contenido=$_POST['contenido'];
	$nombre=$_POST['nombre'];
	
	$extension = pathinfo($nombre, PATHINFO_EXTENSION);
	// $extension = mime_content_type($nombre);
    
	$original = base64_decode(str_replace(array('-', '_',' ','\n'), array('+', '/','+',' '), $contenido));

	// Archivo a MySQLi. BLOB. - File to MysQLi BLOB.
	$blob = mysqli_real_escape_string($link, $original);
	$resultado = mysqli_query($link, "INSERT INTO dibujos (imagen, extension, nombre) VALUES ('$blob', '$extension', '$nombre')");
	mysqli_close($link);
	
	// Archivo al directorio actual - File to actual directory.
	$ifp = fopen($nombre, "wb" );
	fwrite($ifp, $original);
	fclose($ifp);
	echo "Guardado."; 
}

// 4.- MOSTRAR NOMBRE DE IMAGENES - SHOW NAME FILES
if ($boton == "btnListado"){
		$sql = "SELECT nombre FROM dibujos"; 
		$result = mysqli_query($GLOBALS['link'],$sql);
		while($row = mysqli_fetch_array($result)) {
		echo $row["nombre"]."\n"; 
		}
}		

// 5.- Copiar BLOB a Archivo temporal.png - Copy file as temporal.png	
if ($boton == "btnCopiar"){
	$nombre = $_POST['nombre'];
	echo $nombre;
	$sql = "SELECT imagen, extension FROM dibujos WHERE nombre='$nombre' ";

	$result = mysqli_query($link,"$sql");
	$row = mysqli_fetch_array($result);
	// Copia el archivo desde el BLOB al directorio actual, siempre con el nombre temporal.png
	// file_put_contents('temporal.'.$row["extension"], $row["imagen"] );
	file_put_contents('temporal.png', $row["imagen"] );
	}
	
mysqli_close($link);
?>

[Mustakin_Musmus]

How to load blob image file in mit app inventor android apps??
I want to show pictures from mysql database in mit app inventor..
Thx

[Juan_Antonio]

You can convert an image file to base64 string and upload this string, then download that base64 string and convert it to image.
You can also directly download a BLOB using PHP code.

http://kio4.com/appinventor/360_mysql_blob_imagenes.htm

[Tariq_Mehmood]

Respect Sir,

I am trying to save image in mysql table so I am using the top most example
1.- Upload and save an image file as a BLOB.

I have these blocks

After selecting an image from gallery the Screen looks like this

Here are my php codes

if ($button == "btn_save_gallery")
{
$file = file_get_contents('php://input');


   if (file_put_contents($_GET['imagen'],$file == TRUE))  {
   
		$nombre = $_GET['imagen'];
		$extension = pathinfo($nombre, PATHINFO_EXTENSION);
		// $extension = mime_content_type($nombre);
		
		// Archivo a MySQLi. BLOB. - File to MysQLi BLOB.
		$blob = mysqli_real_escape_string($link, $file);
		$resultado = mysqli_query($link, "update vouchers set imagen='$blob', extension='$extension', nombre='$nombre' where ddate='$datex' and vou_type='$vou_type'  and vou_no='$txt_dr_vou_no'  ");
		mysqli_close($link);
		
		// Archivo al directorio actual. - File to actual directory
        if (file_put_contents($nombre, $file) == TRUE)  {
          echo "Image Saved.";
         } else {
           echo "Image not Saved.";
         }
		
		
   } else {
	echo "Error";  
   }
}

When I Click save button then this error message appears

Table structure is like this

What I am doing wrong?

Regards

[Juan_Antonio]

Copy Paste 1.- Upload and save an image file as a BLOB.

Insert not update.

[Taifun]

@Tariq_Mehmood

What I am doing wrong?

You are using a PatchFile and Get request at the same time, which does not make sense
Taifun

[Slaney_Tales]

Hi there !
Did you find a solution for this ? Are phones prevented from doing this ?

[Taifun]

Look again carefully into the example, which was provided above

@Juan_Antonio

1.- Upload and save an image file as a BLOB.

Taifun

[Slaney_Tales]

Does that put the image in tiny DB so it can be sent to the PHP from there, and not hidden by Samsung

[Slaney_Tales]

Wonderful ! I finally got it working. Thank you Taifun and Juan and all. The base 64 example above was the answer. Oh what was I doing ? Thank you

[Juan_Antonio]

5.- Upload and save an image file as a BLOB. List of files. Download a file.

Using this code made by @TIMAI2 we can upload, list and download BLOB files in a MySQL database.

updnBlob.aia (3.8 KB)

upBlob.php

<?php
$db_host="localhost";
$db_name="xxxxxx";
$db_login="xxxx";
$db_pswd="xxxxxxxx";
$conn = new mysqli($db_host, $db_login, $db_pswd, $db_name);
$file = file_get_contents('php://input');
	if (file_put_contents($_GET['file'],$file == TRUE))  {   
		$filename = $_GET['file'];
		$extension = pathinfo($filename, PATHINFO_EXTENSION);
		$imageBlob = mysqli_real_escape_string($link, $file);
		$sql = "INSERT INTO blobData (imageBlob, extn, filename) VALUES ('$imageBlob', '$extension', '$filename')";
		if ( mysqli_query($conn, $sql)) {
		$id = mysqli_insert_id($conn);
		echo "[" . $id . ", " . $filename . "]";	
		} else {
		  echo "Error: " . mysqli_error($sql) . "<br>" . mysqli_error($conn);
		}	
		mysqli_close($conn);	
	} else {
	echo "Error getting file";  
   	}		
?>

dnBlob.php

<?php
$servername = "localhost";
$username = "xxxx";
$password = "xxxxxxxx";
$dbname = "xxxxx";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
$id = $_GET['id'];
$sql = "SELECT * FROM blobit WHERE id =" . $id;
$result = $conn->query($sql);
  list($id, $imageBlob, $extension, $filename ) = mysqli_fetch_array($result);
  header("Content-Length: " . strlen($imageBlob));
  header("Content-Type: image/" . $extension);
  header("Content-Disposition: attachment; filename=$filename");
  ob_clean();
  flush();
  echo $imageBlob;
  $link->close();
?>

[Juan_Antonio]

6.- Simplified code to download BLOB image.

p366_mysql_bajarBlob.aia (2.2 KB)

<?php
 // 1.- IDENTIFICACION nombre de la base, del usuario, clave y servidor
$db_host="localhost";
$db_name="id19781186_base"; // fake
$db_login="id19781186_juan";  // fake
$db_pswd="Contraseña";  // fake

// 2.- CONEXION A LA BASE DE DATOS
$link = new mysqli($db_host, $db_login, $db_pswd, $db_name);

if ($link->connect_error) {
    die("Error de conexión: ".$link->connect_error);
} else {

// 3.- OBTENER EL ARCHIVO. GET FILE.
$nombre = $_GET['imagen'];
$resultado = mysqli_query($link, "SELECT * FROM dibujos WHERE nombre = '$nombre'");
if ($resultado && mysqli_num_rows($resultado) > 0) {
    $fila = mysqli_fetch_assoc($resultado);
    $imagenBlob = $fila['imagen'];
    $extension = $fila['extension'];

// 4.- ENVIAR EL ARCHIVO. SEND FILE.
	header("Content-Type: image/".$extension);
    header("Content-Disposition: attachment; filename=$nombre.$extension");
	echo $imagenBlob;
}

mysqli_close($link);
}
?>

[Slaney_Tales]

Is it viable to select all the BLOB files at once from a table if it begins to get large ? If you have 1000 or more records and select them all for display - will it crash the APP - Browser. ?

[Juan_Antonio]

@Slaney_Tales

select all the BLOB files at once

An idea would be to put a clock (or two) and check the download of a file.

[Slaney_Tales]

Would it be quicker (possible) to get the images from the file system ?