将图像插入MySQL表中列中的行中,而不是作为单独的列


Insert image into row within column in MySQL table rather than as an individual column?

我对PHP和mySql非常陌生,但我正在努力为一个项目学习。我已经学习了本教程http://www.formget.com/ajax-image-upload-php/能够将图像作为列上传到MySQL数据库中的blob表中,其中包含图像大小、id等行。

我有一个单独的数据表,我在其中为各个用户帐户创建列(每个帐户都有一行用户名、密码等)。我在这些列中创建了一行来存储blob。

我不需要教程为它们的图像创建的所有行(image_type、size等),但实际上只需要图像源(图像行)。我需要将此图像插入我的帐户列中的图像的ROW(取决于登录的帐户),而不是为每个图像创建新列。我不知道如何用我现有的代码来处理这个问题。这里是我的HTML表单的JavaScript:

$(document).ready(function (e) {
    //To transfer clicks to divs
     $(".upload-button").on('click', function() {
       $("#file").click();
    });
    $(".save").on('click', function() {
       $(".submit").click();
    });

        $("#uploadimage").on('submit',(function(e) {
        e.preventDefault();

        $.ajax({
        url: "upload.php", // Url to which the request is send
        type: "POST",             // Type of request to be send, called as method
        data: new FormData(this), // Data sent to server, a set of key/value pairs (i.e. form fields and values)
        contentType: false,       // The content type used when sending data to the server.
        cache: false,             // To unable request pages to be cached
        processData:false,        // To send DOMDocument or non processed data file it is set to false
        success: function(data)   // A function to be called if request succeeds
        {
        }
        });
        }));
        // Function to preview image after validation
        $(function() {
        $("#file").change(function() {
         // To remove the previous error message
        var file = this.files[0];
        var imagefile = file.type;
        var match= ["image/jpeg","image/png","image/jpg"];
        if(!((imagefile==match[0]) || (imagefile==match[1]) || (imagefile==match[2])))
        {
        $('.userimg').attr('src','noimage.png');
        return false;
        }
        else
        {
        var reader = new FileReader();
        reader.onload = imageIsLoaded;
        reader.readAsDataURL(this.files[0]);
        }
        });
        });
        function imageIsLoaded(e) {
        $("#file").css("color","green");
        $('#image_preview').css("display", "block");
        $('.userimg').attr('src', e.target.result);
        $('.userimg').attr('width', '250px');
        $('.userimg').attr('height', '230px');
        };
});

然后引用upload.php,这是需要进行更改的地方:

<?php
if(isset($_FILES["file"]["type"]))
{
$validextensions = array("jpeg", "jpg", "png");
$maxsize = 99999999;
$temporary = explode(".", $_FILES["file"]["name"]);
$file_extension = end($temporary);
if ((($_FILES["file"]["type"] == "image/png") || ($_FILES["file"]["type"] == "image/jpg") || ($_FILES["file"]["type"] == "image/jpeg")
) && ($_FILES["file"]["size"] < $maxsize)//Approx. 100kb files can be uploaded.
&& in_array($file_extension, $validextensions)) {
if ($_FILES["file"]["error"] > 0)
{
echo "Return Code: " . $_FILES["file"]["error"] . "<br/><br/>";
}
else
{
if (file_exists("images/" . $_FILES["file"]["name"])) {
echo $_FILES["file"]["name"] . " <span id='invalid'><b>already exists.</b></span> ";
}
else
{
$sourcePath = $_FILES['file']['tmp_name']; // Storing source path of the file in a variable
$targetPath = "images/".$_FILES['file']['name']; // Target path where file is to be stored
 $size = getimagesize($_FILES['file']['tmp_name']);
 /*** assign our variables ***/
 $type = $size['mime'];
 $imgfp = fopen($_FILES['file']['tmp_name'], 'rb');
 $size = $size[3];
 $name = $_FILES['file']['name'];

 /*** check the file is less than the maximum file size ***/
 if($_FILES['file']['size'] < $maxsize )
 {
 /*** connect to db ***/
 $dbh = new PDO("mysql:host=localhost;dbname=sqlserver", 'username', 'password');
 /*** set the error mode ***/
 $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
 /*** our sql query ***/
 $stmt = $dbh->prepare("INSERT INTO imageblob (image_type ,image, image_size, image_name) VALUES (? ,?, ?, ?)");
 /*** bind the params ***/
 $stmt->bindParam(1, $type);
 $stmt->bindParam(2, $imgfp, PDO::PARAM_LOB);
 $stmt->bindParam(3, $size);
 $stmt->bindParam(4, $name);
 /*** execute the query ***/
 $stmt->execute();
 $lastid = $dbh->lastInsertId(); 
 //Move uploaded File
 move_uploaded_file($sourcePath,$targetPath) ; // Moving Uploaded file
 if(isset($lastid))
 {
 /*** assign the image id ***/
 $image_id = $lastid;
     try {
     /*** connect to the database ***/
     /*** set the PDO error mode to exception ***/
     $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     /*** The sql statement ***/
     $sql = "SELECT image, image_type FROM imageblob WHERE image_id=$image_id";
     /*** prepare the sql ***/
     $stmt = $dbh->prepare($sql);
     /*** exceute the query ***/
     $stmt->execute(); 
     /*** set the fetch mode to associative array ***/
     $stmt->setFetchMode(PDO::FETCH_ASSOC);
     /*** set the header for the image ***/
     $array = $stmt->fetch();
     /*** check we have a single image and type ***/
     if(sizeof($array) == 2)
     {
         //To Display Image File from Database
         echo '<img src="data:image/jpeg;base64,'.base64_encode( $array['image'] ).'"/>';
     }
     else
     {
     throw new Exception("Out of bounds Error");
     }
     }
     catch(PDOException $e)
     {
     echo $e->getMessage();
     }
     catch(Exception $e)
     {
     echo $e->getMessage();
     }
     }
     else
     {
     echo 'Please input correct Image ID';
     }
 }
 else
 {
 /*** throw an exception is image is not of type ***/
 throw new Exception("File Size Error");
 }
}
}
}
else
{
echo "<span id='invalid'>***Invalid file Size or Type***<span>";
}
}
?>

我试着删掉对图像大小、类型等的引用,因为我觉得这些都是不必要的,但这会造成错误。我已经大量阅读了其他SO文章,但不明白如何简单地将图像插入mysql数据库中EXISTING列中的一行。我只能为图像创建新列。

我怎样才能做到这一点?

由于各种原因,通常不建议将文件存储在数据库中。虽然该规则有一些例外,但您应该确保这是最适合您的解决方案。

相反,首先要问为什么要这样做,为什么将文件存储在磁盘上,然后跟踪数据库中的文件名不是首选解决方案。一旦你可以证明你的用例是合理的(如果你可以证明用例是正确的),那么你应该询问如何实现它。