fileupload image path in db php value

[MeAppBuilder]

Hello,

I am using Ajax upload with an element (fileupload) and currently image seems to be saved in db like:

0,data:image/png;base64,iVBORw0KGgoAAAANSUhEUg

I am banging my head for the last 3h, but can't find an option to save the image path in db instead of base64. Could someone give me a hint?

I am running custom insert with this form and I am using

'{table_name___image}'

to insert image path. However, this value seems to hold the base64. Also tried

'{table_name___image_raw}'

.

 

[cheesegrits]

I am running custom insert with this form and I am using

What do you mean by a "custom insert"?

In AJAX mode, the filename is stored in the table as a JSON array of filepath and params, which contains the crop data (image dimensions, etc). On submit, the cropped image itself is submitted as base64, again as part of an array of data.

What you get in the placeholder will depend where / when you are looking at it.

-- hugh

 

[MeAppBuilder]

PHP Plugin on edit form / start of form submission (onBeforeProcess)

I am duplicating new entry, so I can handle 2 languages.

// values
$user = JFactory::getUser();
$user_username = $user->username;
$user_name = $user->name;
$user_id = $user->id;

$datetime = date('Y-m-d H:i:s');

// gk_id will group different languages
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "SELECT MAX(gk_id) FROM sc_exercise";
$db->setQuery($query);
$gk_id = $db->loadResult();
$gk_id = ++$gk_id;

// category is a databasejoin element and causes list to load duplicates because of the different languages
// we will insert the pk_id of table sc_exercise_category, so list will load the correct language data item
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "SELECT pk_id FROM sc_exercise_category WHERE gk_id = '{sc_exercise___category}' AND language = 'de_DE'";
$db->setQuery($query);
$sc_exercise_category___pk_id___de = $db->loadResult();

// category is a databasejoin element and causes list to load duplicates because of the different languages
// we will insert the pk_id of table sc_exercise_category, so list will load the correct language data item
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "SELECT pk_id FROM sc_exercise_category WHERE gk_id = '{sc_exercise___category}' AND language = 'en_EN'";
$db->setQuery($query);
$sc_exercise_category___pk_id___en = $db->loadResult();


// insert
$db = JFactory::getDbo();
$query = $db->getQuery(true);

$columns = array('uk_id', 'sk_id', 'gk_id', 'category', 'label', 'description', 'image', 'video', 'build_author', 'build_datetime', 'edit_author', 'edit_datetime', 'language', 'published');
$values = array();
// proper escaping/quotes should be done here, and probably in a loop, but cluttered the answer, so omitted it
$values[] = "'".$user_id."', '{sc_exercise___sk_id}', '".$gk_id."', '".$sc_exercise_category___pk_id___de."', '{sc_exercise___label}', '{sc_exercise___description}', '{sc_exercise___image}', '{sc_exercise___video}', '".$user_id."', '".$datetime."', '".$user_id."', '".$datetime."', 'de_DE', '1'";
$values[] = "'".$user_id."', '{sc_exercise___sk_id}', '".$gk_id."', '".$sc_exercise_category___pk_id___en."', '{sc_exercise___label}', '{sc_exercise___description}', '{sc_exercise___image}', '{sc_exercise___video}', '".$user_id."', '".$datetime."', '".$user_id."', '".$datetime."', 'en_EN', '1'";

$query->insert($db->quoteName('sc_exercise'));
$query->columns($columns);
$query->values($values);
$db->setQuery($query);
$db->query();

// exit form
return false;

I might change this to onAfterProcess and just duplicate new entry instead of creating them myself onBeforeProcess.

That is what I mean. Crop image is in database. I thought all images are stored in filesystem "images" folder. That is what my settings say. All I need in database is relative path to image, instead of base64. Currently I can't find this folder: /images/{$my->id}/sc_exercise/crop

Or are there advantages for base64 image in database?

element "image"
-> Details -> Options -> Upload Directory (/images/{$my->id}/sc_exercise/)
-> Details -> Thumbnails -> Upload Directory (/images/{$my->id}/sc_exercise/thumbnails)
-> Details -> Crop -> Upload Directory (/images/{$my->id}/sc_exercise/crop)

 

[MeAppBuilder]

Strange...

When I create a new row, image is stored like:

0,data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAARYAAACcCAYAAAC6EYRFAAAgAElEQVR4Xu2d6XedxZXuS7MsWZY823gA2xgzGW

When I edit this new row and upload a new image, I get what I was looking for:

[{"file":"\/images\/721\/sc_exercise\/hVRQYTKT6s.jpg","params":"{\"rotation\":0,\"scale\":100,\"imagedim\":{\"x\":329,\"y\":240,\"w\":480,\"h\":360},\"cropdim\":{\"x\":300,\"y\":175,\"w\":280,\"h\":158},\"crop\":true,\"modalId\":\"fileupload-modal-sc_exercise___image-widget-mocha\",\"quality\":0.9,\"mainimagedim\":{\"x\":200,\"y\":200,\"w\":480,\"h\":360}}"}]

 

[MeAppBuilder]

ohhhh...ok...it's probably because I am using my own PHP with form onBeforeProcess and exit form submit. At that point the form value might be the base64 string and that is why I don't see the "crop" folder. I will test some more, but that might have caused it.

 

[cheesegrits]

Yeah, I'm 99.987645% sure we never store that cropped image data in the table. At least not deliberately. If you look in the element code:

https://github.com/Fabrik/fabrik/blob/master/plugins/fabrik_element/fileupload/fileupload.php#L1241

... the crop() method, where we save the image data to the file path, then set the form input data, $formModel->updateFormData(), so it's just the file path. And after that runs, placeholders should then be the path. But I think this happens after onBeforeProcess.

Basically, for upload elements, placeholders just aren't aren't going to work, for lots of reasons. Partly because on submit, file names are actually in PHP's $_FILES[] input array, not in the "form data", and we put metadata in the submitted value for that element, then "do stuff" depending on whether it's AJAX or not, cropped or not, multiple uploads or not, is a file being uploaded or not, was there an existing file for this element prior to uploading or not, etc.

By the time post-processing rolls around, like onAfterProcess, I think the placeholder should work (although maybe not even then in some cases).

 

[MeAppBuilder]

Indeed my onBeforeProcess PHP code caused the issue. I "exit;" the form with that code.

I have changed the logic and duplicate the new row onAfterProcess now, setting another language with one column.

 

[MeAppBuilder]

In case someone is using multiple languages and you need to duplicate a database row after form gets submitted:

edit form -> Plug-in -> PHP (onAfterProcess)

pk_id = primary key id
uk_id = user key id
sk_id = sort key id: sort data
gk_id = group key id: group data

// get current user
$user = JFactory::getUser();
$uk_username = $user->username;
$uk_name = $user->name;
$uk_id = $user->id;
// get current datetime
$datetime = date('Y-m-d H:i:s');



// category is a databasejoin element and causes list to load duplicates because of the different languages
// we will insert the pk_id of joined table, so list will load the correct language value from the joined table only
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "SELECT gk_id FROM myTable_category WHERE pk_id = '{myTable___category}'";
$db->setQuery($query);
$myTable_category___gk_id___de = $db->loadResult();

// category is a databasejoin element and causes list to load duplicates because of the different languages
// we will insert the pk_id of joined table, so list will load the correct language value from the joined table only
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "SELECT pk_id FROM myTable_category WHERE gk_id = '".$myTable_category___gk_id___de."' AND language = 'en_GB'";
$db->setQuery($query);
$myTable_category___pk_id___en = $db->loadResult();

// category is a databasejoin element and causes list to load duplicates because of the different languages
// we will insert the pk_id of joined table, so list will load the correct language value from the joined table only
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "SELECT pk_id FROM myTable_category WHERE gk_id = '".$myTable_category___gk_id___de."' AND language = 'de_DE'";
$db->setQuery($query);
$myTable_category___pk_id___de = $db->loadResult();



// get row details
$pk_id = $formModel->getElementData('myTable___pk_id');
$sk_id = $formModel->getElementData('myTable___sk_id');
$gk_id = $formModel->getElementData('myTable___gk_id');
$label = $formModel->getElementData('myTable___label');
$description = $formModel->getElementData('myTable___description');
$video = $formModel->getElementData('myTable___video');
$language = $formModel->getElementData('myTable___language');
// element image value will loose escaping strings if pulled with $formModel->getElementData('myTable___image') only
// we will need to get the real escape string
$image = $formModel->getElementData('myTable___image');
$image = mysql_real_escape_string($image);



// insert duplicate depending on the language which already exists
if ($language == 'de_DE') {

    // insert
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $columns = array('uk_id', 'sk_id', 'gk_id', 'category', 'label', 'description', 'image', 'video', 'build_author', 'build_datetime', 'edit_author', 'edit_datetime', 'language', 'published');
    $values = array();
    // proper escaping/quotes should be done here
    $values[] = "'".$uk_id."', '".$sk_id."', '".$gk_id."', '".$myTable_category___pk_id___en."', '".$label."', '".$description."', '".$image."', '".$video."', '".$uk_id."', '".$datetime."', '".$uk_id."', '".$datetime."', 'en_GB', '1'";
    $query->insert($db->quoteName('myTable'));
    $query->columns($columns);
    $query->values($values);
    $db->setQuery($query);
    $db->query();

}
elseif ($language == 'en_GB') {

    // insert
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);
    $columns = array('uk_id', 'sk_id', 'gk_id', 'category', 'label', 'description', 'image', 'video', 'build_author', 'build_datetime', 'edit_author', 'edit_datetime', 'language', 'published');
    $values = array();
    // proper escaping/quotes should be done here
    $values[] = "'".$uk_id."', '".$sk_id."', '".$gk_id."', '".$myTable_category___pk_id___de."', '".$label."', '".$description."', '".$image."', '".$video."', '".$uk_id."', '".$datetime."', '".$uk_id."', '".$datetime."', 'de_DE', '1'";
    $query->insert($db->quoteName('myTable'));
    $query->columns($columns);
    $query->values($values);
    $db->setQuery($query);
    $db->query();

}

 

[MeAppBuilder]

And for delete I am using PHP onDeleteRowsForm which will delete all associated languages

// get all pk_id which have been selected in frontend for a delete
$app = JFactory::getApplication();
$array_row_id = $app->input->get('ids', array(), 'array');

// get all gk_id in order to prepare the delete of both languages
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "SELECT gk_id FROM myTable WHERE pk_id IN (".implode(',',$array_row_id).")";
$db->setQuery($query);
$array_gk_id = $db->loadRowList();

// prepare multi dimensional array
$str;
foreach ($array_gk_id as $array) {
$str .= $array[0] . ",";
}
$str = trim($str, ','); //removes the final comma
$array_gk_id = $str;

// DEBUG
// print_r ($array_row_id);
// echo '</br>';
// echo $array_gk_id;
// echo '</br>';
// exit;

// delete all gk_id in order to delete both languages
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query = "DELETE FROM myTable WHERE gk_id IN (" . $array_gk_id . ")";
$db->setQuery($query);
$db->loadResult();