Forums

Dear All,
AT last, with the great support of Bob et al, I am on the final leg of my journey :-)

My form now successfully uploads images/pdfs/other file types directly into the database. I am now working on the retrieval of the same. I have established a CC to the database table and for test purposes set up a VERY simple table to display a link to the image/pdf etc. The link looks something like this

<a href="downloadfile.php?Id=<?php echo $row["Id"]; ?>">

You can see that this ref is passing an ID number from each record to the download.php file...

<?php
global $Id;
if(!is_numeric($Id))
die("Invalid Id specified");

$sConn = mysql_connect($dbServer, $dbUser, $dbPass) or die("Couldn't connect to database server");
$dConn = mysql_select_db($dbDatabase, $sConn) or die("Couldn't connect to database $dbDatabase");

$dbQuery = "SELECT blobType, blobData ";
$dbQuery .= "FROM my_table ";
$dbQuery .= "WHERE Id = $Id";
$result = mysql_query($dbQuery) or die("Couldn't get file list");

if(mysql_num_rows($result) == 1)
{
$fileType = @mysql_result($result, 0, "blobType");
$fileContent = @mysql_result($result, 0, "blobData");
header("Content-type: $fileType");
echo $fileContent;
}
else
{
echo "Record doesn't exist.";
}
?>

What I am not sure of is a. how to get the id for the href link in the first place? And b. the correct syntax to construct the href with the returned ID so that I can pass it on to the download.php file.

It has taken me a while to get this far and I couldn't have done it without the support I have received. Many thanks to everyone for that - it is greatly appreciated.

Best Regards

Chris

Hi Chris,

I'm assuming that the link is going to be in the ChronoConnectivity Body box; and the image will be displayed in a ChronoForm called 'show_image'

If the link is going to be in then it should look like this:

<a href="index.php?option=com_chronocontact&chronoformname=show_image&Id={Id}">Download file</a>

or alternatively like this

<a href="index.php?option=com_chronocontact&chronoformname=show_image&Id=<?php echo $MyRow->Id; ?>">Download file</a>

Note: you usually only need the $MyRow syntax if you need to use the value in a PHP calculation.

Here's the download.php code rewritten to be used in a ChronoForm Form HTML box:

<?php
$id =& JRequest::getInt('Id', 0, 'get');
if ( $id ) {
  $db =& JFactory::getDBO();
  $query = "
    SELECT `blobType`, `blobData`
      FROM `#__my_table`
      WHERE `Id` = $id ;
  ";
  $db->setQuery($query);
  $image = $db->loadResult();

  if ( count($image) ) {
    $header = "Content-type: ".$image->blobType;
    header($header);
    echo $image->blobType;
  }
} else { 
  echo "Record doesn't exist.";
}
?>

Not tested - probably needs debugging.

Bob

Hi Bob,
Many Thanks. Before I go ahead and try to work this one out....

My intention is/was to create a Chronoconnection called Annaveigh. Your response has confused me (not hard :-) ) It looks like you are suggesting I use this in a ChronoForm rather than a ChronoConnection? IS that correct? And if so, how do I get it to cycle through the table such that it will display all the records and create the links for each record returned. I thought that was what CC was used for specifically? Or am I just semantically confused?

Thanks again and Best regards

Chris

Hi Bob,
I was having a dense moment. I managed to work out your intention and created the following:

1. CC with the href in the body area - this loops and displays all the appropriate data and I checked that the record id was getting sent as a parameter to the show_image form.
2. CF called show_image with the following code in the header:

<?php
$id =& JRequest::getInt('id', 0, 'get');

if ($id) {
  $db =& JFactory::getDBO();
  $query = "SELECT `filemimetype`, `filedata` FROM `#__Annaveigh` WHERE `id` = $id";

  $db->setQuery($query);
  $image = $db->loadResult();

echo (count($image));

  if ( count($image) ) {
    $header = "Content-type: ".$image->filemimetype;

    header($header);
    echo $image->filedata;
  }
} else {
  echo "Record doesn't exist.";
}
?>

It doesn't work so I have been debugging (of sorts). I put the echo statement in to display the $image count and it is returning 0 every time so I guess that's why it never gets to the $header code and onwards. I also echoed out the parameter getting passed in and tested using various links - this appears to be ok. So I am taking an educated guess that the issue lies within the query. Tried all manner of things, including those pesky ` ' " things but nothing seems to have a positive effect. Any thoughts?

TIA & Best Regards

Chris

Hi Chris,

I suspect from your previous post that this line

$id =& JRequest::getInt('id', 0, 'get');

should be

$id =& JRequest::getInt('Id', 0, 'get');

The capitalisation of Id must match the calling URL. (Which is why I pretty much never use capitals in variable names.)

If that isn't the problem then please echo out $query and test that in PHP MyAdmin or EasySQL (you'll need to replace #__ with jos_ in PHPMyAdmin. That should show you any MySQL errors there.

Bob

Hi Bob,
I don't know whether to get excited at the prospect of crossing this line (as I am so close), or lose the will to live :-) Sometimes I just want to be back in my truck :-)

I did get the capitalisation bit afterwards and changed it to make sure that id / $id were lowercase.

I put in some echo statements :

<?php
$id =& JRequest::getInt('id', 0, 'get');

echo "The id passed from the download link is " .$id;

if ($id) {
  $db =& JFactory::getDBO();
  $query = "SELECT `filemimetype`, `filedata` FROM `Annaveigh` WHERE `id` = $id";

$db->setQuery($query);

echo "  And the query is set to " .$query;

$image = $db->loadResult();

echo "  Here is the count " .(count($image));

if ( count($image) ) {$header = "Content-type: ".$image->filemimetype;
     header($header);
     echo $image->filedata;
   }
} 
else {echo "Record doesn't exist.";}
?>

Which generated the following output:

The id passed from the download link is 3 And the query is set to SELECT `filemimetype`, `filedata` FROM `Annaveigh` WHERE `id` = 3 Here is the count 1 

The `Annaveigh` table name is exactly as it is in the database - I am not using jos_ or any other prefix.

Many Thanks Bob.

Best Regards

Chris

Hi Chris,

if the image count is 1 then we are getting some data back from the query.

But - my mistake, loadResult won't do it correctly :-( Try

$image = $db->loadObject();

Bob

Hi Bob,
Getting closer. I used the loadObject() as suggested and the echo output is the same however, this time there is an output to the screen - unfortunately it is raw data rather than an image or pdf in this case. So it looks like the header construct is at fault now?

Regards

Chris

Hi Chris,

Look at the page source to see what is showing in the header - assuming that it displays. I've never tried to get an image out of the database so I'm not sure exactly what is required where.

Bob

Hi Bob,
The page source shows a lot of this:

%PDF-1.2 
1 0 obj
<<
/Type /Catalog
/Outlines 9 0 R
/Pages 3 0 R
/PageMode /UseOutlines
/PageLayout /OneColumn
>>
endobj
2 0 obj
<<
/Producer (Burrotech Scan2PDF Mobile - iPhone)
/Author ()
/CreationDate (D:20101123184449)
/Creator (www.burrotech.com)

and then the actual stream itself which is all the weird characters that eventually make up the image. I tried to remove the code that constructs the header type and replace it directly with

header("Content-type:application/pdf");

that produced nothing at all.

Best Regards

Chris

Hi Bob,
I just had a look at the makeup of the header statement and noticed the " " inside the brackets. Does this

if ( count($image) ) {$header = "Content-type:".$image->filemimetype;
     header($header);
     echo $image->filedata;
   }
}

create this

header("Content-type:application/pdf");

where the Content-type is enclosed in " "? Or am I clutching at straws?

Best Regards

Chris

Hi Chris,

It probably needs to be header("$header"); - but look at the page source to check what is actually there.

Bob

Sorry Bob,
I looked at the source but I don't really know what I am looking for specifically. I did a search on Content-type but got nothing back other than

<meta http-equiv="content-type" content="text/html; charset=utf-8" />

which is in the head tags at the top of the page.

Best Regards

Chris

Hi Bob,
SUCCESS :-) well, of a fashion. I used the following code to test

if (count($image))
{header('Content-Type: application/pdf');
 header('Content-Disposition: attachment; filename="example.pdf"');
 echo $image->filedata;
}

Which works great. It gives me the option to view the file on screen, and whether or not I choose to view it will automatically download it onto my computer. The final piece in this puzzle is converting that code so that it is constructed using $image->filemimetype syntax. I have tried and failed to get my head around the whole ` ' " and concatanation thingy. I need to construct the headers as you see them above but replace application/pdf with $image->filemimetype and replace "example.pdf" with $image->filename (which I have added to the db query. The code I tested last is

if (count($image))
{$header1 = 'Content-type:' .$image->filemimetype;
 $header2 = 'Content-Disposition: attachment; filename=' .$image->filename;
 header($header1);
 header($header2);
 echo $image->filedata;
}

but I can't figure out how to get the damned ' " in the right places or at all.

Best Regards

Chris

Hi Chris,

Please try this

if (count($image)) {
  $header1 = '"Content-type: '.$image->filemimetype.'"';
  $header2 = '"Content-Disposition: attachment; filename=\''.$image->filename.'\'"';
  header($header1);
  header($header2);
  echo $image->filedata;
}

Bob

Hi Bob,
Thanks for that - doesn't work I'm afraid. I just get the raw data dumped to screen.

Best Regards

Chris

Hi Chris & Bob,
As I roughly recall, the document type within both Form HTML and on Submit pages are locked to text/html, since it's rendered by a JDocumentHTML class instance. You may circumvein this by using the raw format instead of html (add a form input named "format" with the value "raw" to the submitted data). However, this will still not work with Form HTML or on Submit, as these try to add css and javascripts using the JDocumentHTML->addStyleDeclaration() / addScriptDeclaration() methods, which are not available in raw format.

What does work, however, is to use the "Extra" codes along with Raw format and custom headers.

/Fredrik

Hi Fredrik,
Many thanks for the input (no pun intended). I will take some time to digest what you said and try to understand it. I am sure Bob will testify to the fact that I am a complete novice when it comes to this stuff. But for the fantastic help and support I have experienced here in the forum I would still be on the starting blocks. I did manage to get it working for me insomuch as the pdf/image/other prompts with a download dialogue through which the file can be saved. In FF it can also be viewed through a chosen application. This is not my ideal scenario, but for now it is fit for purpose and I am happy to go with it. Once I have completed my project I will take some time to sit back and get my head into things, such as those brought up in your post.

A huge thank you to you, to Bob, and to anyone else that has helped me through this project. I know I have barely scratched the surface, and have a long way to go, but this has been a blast - infuriating, but great fun!!!!

Thanks again.

Best Regards

Chris
PS Must ask Santa for some good books :-)

Hi Chris,

I'd Googled around and come to the same conclusion as Fredrik - that we need to use the Extra Code boxes to get the image. I'll run a test in the morning.

Bob

Hi Chris,

Sorry to report a complete failure so far. I can get the file contents to output but not as an image :-(

Bob

Hi Bob, Fredrik,
Thanks Bob. I have been looking into it also but came up with nothing. But I had been thinking about what Fredrik said in his post...

A thought - in the interim do you think it would be possible to echo the image data out to a fresh browser window rather than the confines of Joomla / CF? It may not be an ideal solution but if it works....

Best Regards

Chris

Hi Chris,

I've tried all the variants that I can think of and can't get an image to display. Last version was to modify a copy of the chrono_verification.php file - I can get a string of binary data but no image.

Bob

Hi Bob,
Many thanks for your effort and willingness to go further on this - huge appreciation! I am also drawing a blank but I believe that a solution is there somewhere and I will find it at some point. For now I have a system that works and does what I need it too for the most part. Out of interest I re-wrote the header code I was having trouble with - remember those damned ` ' " I took the variable out of the equation and coded the header statement directly, concatenating the file name and file mime type etc see below

if (count($image)) {
header('Content-type: '.$image->filemimetype.' \'');
header('Content-Disposition: attachment; filename="'.$image->filename .'"\'');
echo $image->filedata;

This works a treat and for now at least, I am happy with the outcome. Needless to say I could not have done it without all the help and support from you, Fredrik et al so a great big thank you to you all.

Warm Regards

Chris

Hi Bob,
Just when you think it's safe.....

I have transferred all that work over to the live server and I get the following behaviour. When a file is attahced and uploaded all the details are stripped and entered into the table fields however, if the file is a pdf the mime type that gets entered into the table is text/html not application/pdf (as it is on my test server), if it is a jpg the mime type is entered correctly. Now when downloading the file the pdf comes down fine - even though the mime type is text/html, but the image doesn't, even though the mime type in the table is correct - I get a file maybe corrupted error.

Is there anything I should know when mirroring my forms/code on a live server? I did make the necessary changes where table names were concerned, and I would not have thought the syntax would be any different?

Best Regards

Chris

Hi Bob & Chris,
Thought I'd just post an example on how to get this working:
Using the Extra codes for this..
Extra 1:

<?php
$doc =& JFactory::getDocument();
$doc->setMimeEncoding('image/png');
readfile(JPATH_BASE . DS . 'media' . DS . 'system' . DS . 'images' . DS . 'closebox.png');
?>

Then access this form using this url: http://www.example.tld/index.php?option=com_chronocontact&chronoformname=imagetest&task=extra&format=raw
This should then display an image of a white cross on a black background. Doing this with pdf-documents would be as trivial as replacing the 'image/png' value with 'text/pdf', and replacing readfile(....); with something outputting the actual pdf-content.

/Fredrik

Hi Fredrik,

Have you tested this reading from a blob in the database? That was where I got stuck :-(

Bob

Hi Bob,
That worked just as well, when I tested using the database.

The actual code I used:
Add image to database:

<?php
$db = JFactory::getDBO();
$pst = 'INSERT INTO %s (%s, %s) VALUES (%s, %s)';
$query = sprintf($pst,
  $db->nameQuote('blob_image'),
  $db->nameQuote('data'),
  $db->nameQuote('type'),
  $db->Quote(file_get_contents(JPATH_BASE . DS . 'media' . DS . 'system' . DS . 'images' . DS . 'closebox.png')),
  $db->Quote('image/png')
);
$db->setQuery($query);
$db->query();

Read and display image from database:

<?php
$db =& JFactory::getDBO();
$pst = 'SELECT %s, %s FROM %s WHERE %s = %s';
$query = sprintf($pst,
  $db->nameQuote('type'),
  $db->nameQuote('data'),
  $db->nameQuote('blob_image'),
  $db->nameQuote('id'),
  1
);
$db->setQuery($query);
$data = $db->loadObject();

$doc =& JFactory::getDocument();
$doc->setMimeEncoding($data->type);
echo($data->data);

The database table definition looks like this:

CREATE TABLE IF NOT EXISTS `blob_image` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `data` mediumblob NOT NULL,
  `type` varchar(64) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

/Fredrik

Hi Fredrik,
I can pick pieces of this code and sort of understand what it is doing, but I have no experience of this style of coding (seen it around for sure though). I need to understand how this would fit into my form(s) in order for it to produce the effect I am looking for. Can you point me in the right direction re help files / reading material / what should I google so that I may get a grounding and better understanding of the style and syntax you are using here? Your solution looks great but not coming from this background I need to be able to understand what I am looking at.

By the way, I found that there is a known bug in FF that confuses mime types and I think that is what is happening to me now. I am going to test what I have in other browsers to see what the outcome is.

Best Regards

Chris

Hi Fredrik,
I did some digging and found some material which helped clear up your code - I understand what is happening now.

I ran your sql to create the database table blob_image. I then created a very simple CF with just a submit button on it and pasted your code 'verbatum' into the On Submit Code area. Run, press submit, check table - fields populated :-) I created a second form to show the image and pasted the code into the html area, the On Submit area, and finally the extra code area. In all cases I used format=raw, and in the extra code I used task=extra&format=raw. I got the same response from all - The image (url .........) cannot be displayed because it contains errors. I have checked this using a jpg and a pdf and the outcome was the same i.e. no image. I checked the raw data stored in the table and it is exactly as it should be - 1png, 1jpg, and 1pdf - and displays the images/pdf in full.

Any thoughts?

Best Regards

Chris

Hi Chris,
Sorry about that, things get so "obvious" when you deal with this kind of programming on a daily basis, and you forget how things might not be so obvious for others.

Soo, what do I do in the code?
Well, first off, I use Joomla's JDatabase Classes, rather than mysql_* functions. It's mainly a driver abstraction layer, though as of now, it only implements the mysql and mysqli drivers. Still, it helps writing prettier code in most of the cases, compared to directly using the mysql_* commands.
Further, the nameQuote() and quote() methods adds the (sometimes) needed `` and '' name- and string-boundaries, so there's no need to worry about them in the query.

Secondly, I try to write my queries in a "Prepared Statement"-like manner. The general idea of these are that you build the query with placeholders rather than actual data, and use certain methods for injecting the data into the query just before issuing the SQL transaction.
In languages such as Java, there's a great performance-boost for iterative queries doing it this way, though the main advantage in PHP (which unfortunately lacks real support for prepared statements), is a "cleaner"-looking code.

To achieve this, I use sprintf(), which takes a format-string, looks for %-makros, and replace these with the other arguments passed to it. In this case, I simply settled for %s, which means "insert data as string", though there are many other macros and format codes.

Next, when inserting the different values, I "pass them through" the nameQuote() and Quote() methods of the database-object. These does the proper escaping, such as calling the proper escape-command (mysql_real_escape since we're using a mysql database-driver), and surrounding the strings with '', and column-names with ``.

Once the query is completed, I load it into the database object using the setQuery() method.

Now, this is where I must think a little. If I'm writing a data manipulation query (insert/update/delete), there won't be a resultset. Thus we can't call any of the load*() methods to fetch data. Instead we call the query() method, to instruct the database object to actually run the query.
Simply using setQuery() won't actually run it, just prepare it.
On the other hand, if we've got a data selection query (select/show/explain, etc..), we don't call query(), but the load*() method, which will instruct the database object to run the query and then directly fetch the result and return it to you.

Moving on to setting headers and stuffs...
When dealing with Joomla, you should avoid using the native headers() command, and it's alikes... This is because Joomla expects to have full control over all sent headers, and will in some cases remove any and all headers already set in favor of it's own.

So, what's the "Joomla-way" to do this?
Get an instance of the current JDocument object (JFactory::getDocument()). To set the document-type, I call the setMimeEncoding() method, which will take care of all the gory details of header management in Joomla.
If you'd like to add some other, custom, headers you'll have to make use of the JResponse class though. It's responsible for handling everything sent back to the browser. Adding a header such as "x-custom: a value" would look like this:
JResponse::setHeader('x-custom', 'a value');


Now, for the broken image:
Have you tried looking at the generated image with an hex-editor or such?
I'm wondering if this might be a character-set conversion issue..

/Fredrik

Hi Chris & Fredrik,

@Fredrik, thanks for the very helpful posts. I'll go tweak my code alter and see if I can work out where it is breaking: my current guess from your post is either the DB quoting or handling the headers.

I did compare the 'raw' image code and the version I got returned appeared to be truncated aroudn the 90% mark at a < symbol. It was at about that point I decided that I need to go and do other things before I dug myself in a deeper hole.

Bob

PS I don't use Fredrik's sprintf() method - just because I come from a different background and find it harder to get my head around than the straight statement. But as a result I occasionally miss quoting a value that needs it.

Hi Fredrik, Bob,
More late nights and more digging. I ran various tests using FF, Opera, Safari & Chrome both for the uploading of an image to the db and subsequently retrieving the image. I tested all 4 browsers with png, pdf & jpg files and this is what I have found out:

1. FF has major issues in relation to mime handling and I don't know how to deal with that - a bridge too far perhaps. The uploading of pdf files via FF produced a mime type of text/html, the png & jpg were correct. Chrome, safari & Opera uploaded ALL files with the correct mime type.
2. In all 4 browsers the download/viewing of the pdf file worked great, but in ALL browsers the png/jpg files were broken and unviewable.
3. When trying to view the png/jpg files I was unable to view the page source code, select the broken image icon, or carry out any further manipulation or inspection of the element so I don't know how to get the info you are asking for in relation to the hex editor.

Best Regards

Chris

Hi Chris, Bob,
Bob: Data being trunkated but not damaged in any other way would suggest you might've hit a datatype size-limit, either in the stored data in the DB, or in the PHP-variables holding the data in the transactions. How large files have you tried sofar? Had a 730kb blob pass through cleanly myself.

Chris: I would not trust the web browser to supply the mime-type at all.
Since the file is stored temporarily while being uploaded, I'd suggest you use the mime_content_type() function to investigate the file on the server instead.

Leaving the mime-type text/plain or text/html even though it is an image, should allow you to see the raw content. Next, viewing the original image in a text browser (wordpad should do fine), compare the first "page" of the original image and the downloaded image. If they seem identical, then proceed to the end of each file, and see if they're the same as well.
Also try and see if the size of the original and the downloaded files differ.

/Fredrik

Hi Fredrik,
Following your guide I managed to check the raw data from the show_image form (as text/plain) and the original image - they appear to be different. I did try to paste a line or two here but for whatever reason it doesn't stay. I can pick out a significant number of similarities, but also an equally significant number of discrepancies. As for the file size - I am at a loss. Nothing is being downloaded from the show_image form for me to compare against the original.

I am crossing some big hurdles (for me) and am learning a great deal. I really don't want to give up on this so would you mind if I pm'd you with some very specific (yet sometimes basic) questions?

Thanks again for all your help and support.

Best Regards

Chris

Hi Chris,
That would suggest that your image data gets mangled by character set conversions, either during the upload, or during the DB storage/DB read.

Could you verify that your upload-form uses the multipart/form-data encoding rather than the standard application/x-www-urlencoded, and replace the addslashes() command with mysql_real_escape_string(), then upload a new image, and see if you see any improvements on the returned image data?

/Fredrik

Hi Fredrik,
I can confirm I am using the correct encoding

enctype="multipart/form-data"

As for replacing the addslashes - I am not using them. The code I am working with is a slightly modified version of the code you posted. The only difference is that I am reading the file in from a file input box on the form rather than a hardcoded path to an image.

Thanks and Regards

Chris

Hi Chris,
Roger that.
That should work then. Since the datatype in the DB is BLOB, the DB-server should handle the information as binary data, and not do any charset conversions on it.

While Googling around, I did find this post: http://lists.mysql.com/mysql/222658
Though no solution is posted, it does suggest there might be some client issues with the encoding used in the client->server communications.

Try adding this line just after you've created your $db object, but before you issue the setQuery method:

$db->setUTF();

/Fredrik

Hi Fredrik,
To recap - I have created two forms, one to upload an image, the other to show the image. I have used your code verbatim for the upload and added the setUTF line. I have ran this form successfully such that it loads the closebox.png image. I aam using Sequel Pro on a Mac and when I mouse over the entry in the database it shows me the image that gets uploaded - this displays correctly so I am assuming the upload is fine and no character conversion / corruption is taking place at that stage. The second form is the show_image form, and again my code is a direct copy of your code. When I run this form I get the file maybe corrupted error. I tried changing the (id) that was being used when querying the table so that a different image would get displayed but the same thing happens - even though I can see clearly that ALL images were uploaded to the table correctly.

Best Regards

Chris

Hi Chris,
Ok, that does limit the possible sources for the corruption; unfortunately, I'm running low on ideas. I suppose you could try and write the data from the query into a file on the web-server, and then ftp it back to your Mac.
If the image now looks ok, then PHP is doing some odd conversion that mangles it; if it's still damaged, then I'd be inclined to blame the sql client library.

/Fredrik