B4J Question [BANano] [VuetifyAD3] Upload image to MySql [SOLVED]

[Star-Dust]

Based on the example of Mashy Package Manager I am trying to upload small images to the MySql DB. The image seems to be loaded from the html page but then you can't send it to php to save it in MySql.

I'm probably making mistakes, but which ones?

Private Sub VFileInputLogo_Change (fileObj As Object)
    If banano.IsNull(fileObj) Or banano.IsUndefined(fileObj) Then Return
    VFileInputLogo.UpdateLoading(Page, True)
    Dim fileDet As FileObject
    fileDet = BANanoShared.GetFileDetails(fileObj)
    Dim fn As String = fileDet.FileName
  
    fileDet=BANanoShared.UploadFileWait(fileObj)
    Dim sstatus As String = fileDet.Status
  
    Select Case sstatus
        Case "error"
            vuetify.ShowSwalNotificationError($"file ${fn} non caricato"$,10000)
        Case "success"
            Dim res As String
            res = banano.CallInlinePHPWait("writeimegedb", CreateMap("table": "categorie","field":"logoRidotto","id":ID,"image":fileDet))
            vuetify.ShowSwalNotificationSuccess($"file ${fn} caricato: ${res}"$,3000)
    End Select
  
    VFileInputLogo.UpdateLoading(Page, False)
End Sub

function writeimegedb($table,$field,$id,$image) {
    $servername = "";
    $username = "";
    $password = "";
    $dbname = "";

    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
  
        //check If File Is Not empty
        If(!empty($_FILES["image"]["name"])) {

            //File info
            $file_name = basename($_FILES["image"]["name"]);
            $file_type = pathinfo($file_name, PATHINFO_EXTENSION);

            //make an Array of allowed File extension
            $allowed_file_types = Array('jpg','jpeg','png','gif');
            ECHO "!";
            //check If upload File Is an image
            If( in_array($file_type, $allowed_file_types) ){

                $tmp_image = $_FILES['image']['tmp_name'];
                $img_content = addslashes(file_get_contents($tmp_image));

                //now run update query
                $query = $db->query("UPDATE ". $table. " SET ". $field. " = '$img_content' WHERE id=". $id);

                 //check If successfully inserted
                If($query){
                    echo "ok";
                }else{
                    echo "Something went wrong when uploading image!!!";
                }
            }else{
                echo "Only support jpg, jpeg, png, gif format";
            }
        } else {
            echo "Empty";
        }
      
    $conn->close();
 }

The image I send is empty and gives me the error message

 

[Mashiane]

Excellent Question.

I think change this line to

res = banano.CallInlinePHPWait("writeimegedb", CreateMap("table": "categorie","field":"logoRidotto","id":ID,"image":fileObj))

With that said...

It is not recommended to store image files inside the database, this will create a bloated database, rather store the file URL instead. Let me explain

This part of your code...

fileDet=BANanoShared.UploadFileWait(fileObj)

returns a fileDet which is of data-type Type

This is defined in BANanoShared as

Type FileObject(FileName As String, FileDate As String, FileSize As Long, FileType As String, Status As String, FullPath As String, FileDateOnly As String, FileOK As Boolean, FO As BANanoObject)

The BANanoShared.UploadFileWait, receives the real file object received from the FileChooser, calls upload.php to save the contents of the file to the assets folder of your webapp. This then updates the FullPath of the fileDet so that you know where your file is i.e. URL.

Sub UploadFileWait(fileO As Map) As FileObject
    'get the file details
    Dim fileDet As FileObject = GetFileDetails(fileO)
    'get the file name
    Dim fn As String = fileDet.FileName
    'start uploading the file
    Dim fd As BANanoObject
    fd.Initialize2("FormData", Null)
    fd.RunMethod("append", Array("upload", fileO))
    '
    Dim Res As String = BANano.CallAjaxWait("./assets/upload.php", "POST", "", fd, True, Null)
    Dim result As Map = BANano.FromJson(Res)
    Dim sstatus As String = result.Get("status")
    fileDet.Status = sstatus
    If sstatus = "success" Then
        fileDet.FileOK = True
    Else
        fileDet.FileOK = False
    End If
    fileDet.FullPath = $"./assets/${fn}"$
    Return fileDet
End Sub

That is the URL that you can then store in your DB to reference the file as it has the actual file location.

This code

function writeimegedb($table,$field,$id,$image) {

Seeks to receive the real file Object, then read the file contents and store the file contents in the db. You can rather just pass the full URL as received from the fileDet variable here and then call file_get_contents($image) to get the file contents using the URL and store them or just simply store the URL on your DB to point to the file in the assets folder.

All the best...

 

[Star-Dust]

Thank you Mashiane for your help. I'll try it and let you know

It is not recommended to store image files inside the database, this will create a bloated database, rather store the file URL instead. Let me explain

This part of your code...

I am aware of this, unfortunately it is a specific request of the client, I would have done differently

 

[Star-Dust]

it works properly. I made these two changes.

res = banano.CallInlinePHPWait("writeimegedb", CreateMap("table": TableName,"field":FieldName,"id":ID,"filename":$"./assets/${fileDet.FileName}"$))

  function writeimegedb($table,$field,$id,$filename) {
    $servername = "";
    $username = "";
    $password = "";
    $dbname = "";

    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    
        //check If File Is Not empty
        If(!empty($filename)) {
            //File info
            $img_content = addslashes(file_get_contents($filename));

            //now run update query           
            $query = $conn->query("UPDATE ". $table. " SET ". $field. " = '$img_content' WHERE id=". $id);

             //check If successfully inserted
            If($query){
                echo "ok";
            }else{
                echo "Something went wrong when uploading image!!!";
            }   
        } else {
            echo "Empty";
        }
        
    $conn->close();
 }

Now the problem will be the reverse reading the photo from a mysql field. but this maybe will be for another post

 

[Star-Dust]

Reading works fine too

 function readimagedb($table,$field,$id) {
    $servername = "";
    $username = "";
    $password = "";
    $dbname = "";
 
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
         die("Connection failed: " . $conn->connect_error);
    }
   
    $result = $conn->query("SELECT ". $field. " FROM ". $table. " WHERE id=". $id);
    $row = $result->fetch_assoc();
    //check If File Is Not empty
    If(!empty($row[$field])) {
        echo base64_encode($row[$field]);
    } else {
        echo "";
    }
       
    $conn->close();
  }

Dim img As String = banano.CallInlinePHPWait("readimagedb", CreateMap("table":TableNamee" ,"field":FieldName,"id":ID))
VImg1.SetImage(Page,$"data:image/jpg;charset=utf8;base64,${img}"$)

 

[Mashiane]

Please tick the response as the solution. Thanks.