Insert/view image into/from a MySQL DB

Question

I have a problem with inserting images in DB. The table has the following structure:

id->INT(3)->autoincrement
name->VARCHAR(30)
extension->VARCHAR(10) [maybe too short]
img->MEDIUMBLOB

The PHP code that insert the image is:

if($_FILES['file']['error']==0){
        $result = is_uploaded_file($_FILES['file']['tmp_name']);
        if(!$result){
            echo "Upload failed";
        }else{
            $type = explode("/", $_FILES['file']['type']);
            $extension = $type[1];

            $name = $_FILES['file']['name'];
            $img = $_FILES['file']['tmp_name'];
            $img = file_get_contents($_FILES['file']['tmp_name']);
            $img = addslashes ($img);
        }

        $sql = "INSERT INTO images (name, extension, img) VALUES ('$name', '$extension', '$img')";
        $result = $mysqli->query($sql);
        if($result){
            echo "insertion was successful";
        }else{
            echo "insertion failed: ".$mysqli->error;
        }

And this is how i try to see img:

$sql = "SELECT name, extension, img FROM images WHERE id='1'";
$result = $mysqli->query($sql);
if($result){
    $a = $result->fetch_assoc();
    header ("Content-type: image/".$a['estensione']);
    echo $a['img'];

}else{
    echo "AAAAAAAAA<hr>";
    echo $mysqli->error;
}

The insertion is Ok, but i can't view the image. In addition, there's another way to upload image in Db?

Note that INT"(3)" doesn't mean anything - at least not in this context — Strawberry, Mar 13 '14 at 11:54
I wouldn't store images to db, check [Answer](http://stackoverflow.com/questions/561447/store-pictures-as-files-or-in-the-database-for-a-web-app) out. — skywalker, Mar 13 '14 at 11:56

score 1 · Accepted Answer · answered Mar 13 '14 at 11:56

First in image tag example (the page where you want to display your results)

in the tag get the image from fetch_image_frm_db.php page, and display

<img src="fetch_image_frm_db.php?id=<?php echo $id_of_row;?>"/>

fetch_image_frm_db.php page

$id=$_GET['id'];

$query = "SELECT * FROM images WHERE id=$id";

$result=mysql_query($query) or die('Error, query failed'.mysql_error()); 
$row=mysql_fetch_array($result);

header("Content-type:image/jpeg");
stripslashes ($row['img']);

echo $row['img'];

** storing images in db is not good practise

score 0 · Answer 2 · answered Mar 13 '14 at 11:52

0

I think you just misspelled extension in your code on line 5:

$sql = "SELECT name, extension, img FROM images WHERE id='1'";
$result = $mysqli->query($sql);
if($result){
    $a = $result->fetch_assoc();
    header ("Content-type: image/".$a['extension']);
    echo $a['img'];

}else{
    echo "AAAAAAAAA<hr>";
    echo $mysqli->error;
}

answered Mar 13 '14 at 11:52

Stephan

426
5
13

why misspelled? $a['extension'] is the extension take from the type of the image property in $_FILES['file'], and in this case is jpeg. I also tried by inserting in db all the string of image type(so deletedet the command explode). the value stored in DB is now "image/jpeg" and modified the output page with: "header('Content-type: '.$a['extension']. Didn't work – Andrea_86 Mar 13 '14 at 12:06

score 0 · Answer 3 · answered Mar 13 '14 at 14:16

OK i solved in this way... The code for the index page is:

<?php
  $mysqli = new mysqli("127.0.0.1","root","root","prova");
  if($mysqli->connect_errno){
     echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
  }

if(isset($_POST['submit'])){
 if(isset($_FILES['file'])){
    if($_FILES['file']['error']==0){
        $result = is_uploaded_file($_FILES['file']['tmp_name']);
        if(!$result){
            echo "Impossibile eseguire l'upload";
        }else{
            $type = $_FILES['file']['type'];
            $nome = $_FILES['file']['name'];
            $img = file_get_contents($_FILES['file']['tmp_name']);
            $img = addslashes ($img);

        }

        $sql = "INSERT INTO immagini (nome, estensione, img) VALUES ('$nome', '$type', '$img')";
        $result = $mysqli->query($sql);
        if($result){
            echo "Insertion was succesfully executed";
        }else{
            echo "Insertion failed: ".$mysqli->error;
        }
    }

 }
}
    echo "<br><br><hr>";
    $query = "SELECT * FROM immagini";

    $result=$mysqli->query($query) or die('Error, query failed'.mysql_error());

    while($row = mysqli_fetch_array($result)){
        echo "<p><img height='250' width='250' src=\"fetch_img.php?id=$row[id]\">";
    }

?>

And this is the script fetch_img.php, taht effectively print image:

<?php
    $mysqli = new mysqli("127.0.0.1","root","root","prova");
    if($mysqli->connect_errno){
        echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
    }

    if($_GET['id']==""){
        header("Location: index.php");
    }

    $id=$_GET['id'];

    $query = "SELECT * FROM immagini WHERE id=$id";

    $result=$mysqli->query($query) or die('Error, query failed'.mysql_error()); 
    //var_dump($result);
    $row= $result->fetch_assoc();

    header("Content-type: image/jpeg");
    $rs_immagine=imagecreatefromstring($row['img']);
    imagejpeg( $rs_immagine, null, 100);
    imagedestroy($rs_immagine);

?>

Thanks all, also for advice that storing file, especially image, inside a DB is not reccomanded...I'm the first who say this, but i must do in this way.

Insert/view image into/from a MySQL DB

3 Answers3

Linked