Reading from a blob or a file which is better?

[John]

I have over 5000 thumbnail pictures of size 5kb each. I would like to
able to load all 5000 pictures and view 50 per page using
mysql_data_seek(). I would like to know what are the advantages and
disadvantages of using a MySQL blob field rather than reading the
images directly from the file? How does one insert an image into a
blob field? Can it be done dynamically?

Thank you
John

[Agelmar]

You do not want to use mysql_data_seek. You will be fetching 24Mb of data
100 times if you do it that way (you will be fetching the entire result on
each page (100 of them), and then seeking to picture 50, 100, whatever.
Limit the results in your query, for the love of god. (Look up LIMIT in the
mysql docs - e.g. SELECT pic FROM pictures LIMIT 1,50 etc.)

As for storing in the database vs. storing filenames in the database - the
database will give you a better chance for sequential disk I/O, and will
reduce overhead most likely (i.e. storing a lot of small files that are
around 5kb will likely mean you are using up 2 4kb clusters for each file,
which is a huge waste of space. The database will likely have some wasted
space as you have variable-length records, but at least the table will be in
one physical file, leaving the specifics up to the database.)

As for inserting binary data into a blob field, search the newsgroup or the
web, there are plenty of examples and tutorials.

My $.02
// Ian Fette
// Proponent comp.lang.php

John wrote:

I have over 5000 thumbnail pictures of size 5kb each. I would like to
able to load all 5000 pictures and view 50 per page using
mysql_data_seek(). I would like to know what are the advantages and
disadvantages of using a MySQL blob field rather than reading the
images directly from the file? How does one insert an image into a
blob field? Can it be done dynamically?

Thank you
John

[John]

Thank you very much Agelmar for the detail information. I have tried
implementing using blob field but I am having a problem displaying the
images. I read from other articles in the newsgroup that in order to
display the image one need to also store the filetype and then use
header() to show the image. But this can only work if one is
displaying only one image and where the image has to be displayed
before any other output is displayed in the browser, otherwise one
would get the dreaded header error. My application is a catalog that
displays 50 products per page with each product having its own image
and information. Is there not a simpler way to show the images? My
images are only jpeg files.

Thank you
John

"Agelmar" <if**********@comcast.net> wrote in message news:<bs************@ID-30799.news.uni-berlin.de>...

You do not want to use mysql_data_seek. You will be fetching 24Mb of data
100 times if you do it that way (you will be fetching the entire result on
each page (100 of them), and then seeking to picture 50, 100, whatever.
Limit the results in your query, for the love of god. (Look up LIMIT in the
mysql docs - e.g. SELECT pic FROM pictures LIMIT 1,50 etc.)

As for storing in the database vs. storing filenames in the database - the
database will give you a better chance for sequential disk I/O, and will
reduce overhead most likely (i.e. storing a lot of small files that are
around 5kb will likely mean you are using up 2 4kb clusters for each file,
which is a huge waste of space. The database will likely have some wasted
space as you have variable-length records, but at least the table will be in
one physical file, leaving the specifics up to the database.)

As for inserting binary data into a blob field, search the newsgroup or the
web, there are plenty of examples and tutorials.

My $.02
// Ian Fette
// Proponent comp.lang.php

John wrote:

I have over 5000 thumbnail pictures of size 5kb each. I would like to
able to load all 5000 pictures and view 50 per page using
mysql_data_seek(). I would like to know what are the advantages and
disadvantages of using a MySQL blob field rather than reading the
images directly from the file? How does one insert an image into a
blob field? Can it be done dynamically?

Thank you
John

[Agelmar]

John - you are correct about the header bit, but you are incorrect about its
limitations. What you would do is something like:

<img src="showimage.php?imageid=2428" alt="whatever">

and then showimage.php has header("content-type: whatever...."); at the top,
followed by the something that spits out the data for the image. Thus it
sends the header information for each image. (The "header before any output"
rule is still observed, because you are sending the header before any output
for the page in question. Yes, data will have already been sent for your
overall page, but for showimage.php?imageid=2428 (your source for the
image), you will be sending the header before any output for *that* data
request.)

http://www.sum-it.nl/en200319.php3 has a tutorial that at least shows the
general idea (jump down to the "Show Thumbnail" section. They should not be
using stripslashes() on the result from the mysql_query unless
magic_quotes_runtime or whatever is on (not gpc, but runtime or sybase), but
you get the general idea.

Hope this helps clarify things a bit...

John wrote:

Thank you very much Agelmar for the detail information. I have tried
implementing using blob field but I am having a problem displaying the
images. I read from other articles in the newsgroup that in order to
display the image one need to also store the filetype and then use
header() to show the image. But this can only work if one is
displaying only one image and where the image has to be displayed
before any other output is displayed in the browser, otherwise one
would get the dreaded header error. My application is a catalog that
displays 50 products per page with each product having its own image
and information. Is there not a simpler way to show the images? My
images are only jpeg files.

Thank you
John

"Agelmar" <if**********@comcast.net> wrote in message
news:<bs************@ID-30799.news.uni-berlin.de>...

You do not want to use mysql_data_seek. You will be fetching 24Mb of
data 100 times if you do it that way (you will be fetching the
entire result on
each page (100 of them), and then seeking to picture 50, 100,
whatever.
Limit the results in your query, for the love of god. (Look up LIMIT
in the
mysql docs - e.g. SELECT pic FROM pictures LIMIT 1,50 etc.)

As for storing in the database vs. storing filenames in the database
- the
database will give you a better chance for sequential disk I/O, and
will
reduce overhead most likely (i.e. storing a lot of small files that
are
around 5kb will likely mean you are using up 2 4kb clusters for each
file,
which is a huge waste of space. The database will likely have some
wasted
space as you have variable-length records, but at least the table
will be in
one physical file, leaving the specifics up to the database.)

As for inserting binary data into a blob field, search the newsgroup
or the
web, there are plenty of examples and tutorials.

My $.02
// Ian Fette
// Proponent comp.lang.php

John wrote:

I have over 5000 thumbnail pictures of size 5kb each. I would like
to
able to load all 5000 pictures and view 50 per page using
mysql_data_seek(). I would like to know what are the advantages and
disadvantages of using a MySQL blob field rather than reading the
images directly from the file? How does one insert an image into a
blob field? Can it be done dynamically?

Thank you
John

[John]

Agelmar

I have implemented as you have explained, but the code <img
src="showimage.php"> is not calling the PHP file. Only an empty X icon
is showing on the browser.

John

"Agelmar" <if**********@comcast.net> wrote in message news:<bs************@ID-30799.news.uni-berlin.de>...

John - you are correct about the header bit, but you are incorrect about its
limitations. What you would do is something like:

<img src="showimage.php?imageid=2428" alt="whatever">

and then showimage.php has header("content-type: whatever...."); at the top,
followed by the something that spits out the data for the image. Thus it
sends the header information for each image. (The "header before any output"
rule is still observed, because you are sending the header before any output
for the page in question. Yes, data will have already been sent for your
overall page, but for showimage.php?imageid=2428 (your source for the
image), you will be sending the header before any output for *that* data
request.)

http://www.sum-it.nl/en200319.php3 has a tutorial that at least shows the
general idea (jump down to the "Show Thumbnail" section. They should not be
using stripslashes() on the result from the mysql_query unless
magic_quotes_runtime or whatever is on (not gpc, but runtime or sybase), but
you get the general idea.

Hope this helps clarify things a bit...

John wrote:

Thank you very much Agelmar for the detail information. I have tried
implementing using blob field but I am having a problem displaying the
images. I read from other articles in the newsgroup that in order to
display the image one need to also store the filetype and then use
header() to show the image. But this can only work if one is
displaying only one image and where the image has to be displayed
before any other output is displayed in the browser, otherwise one
would get the dreaded header error. My application is a catalog that
displays 50 products per page with each product having its own image
and information. Is there not a simpler way to show the images? My
images are only jpeg files.

Thank you
John

"Agelmar" <if**********@comcast.net> wrote in message
news:<bs************@ID-30799.news.uni-berlin.de>...

You do not want to use mysql_data_seek. You will be fetching 24Mb of
data 100 times if you do it that way (you will be fetching the
entire result on
each page (100 of them), and then seeking to picture 50, 100,
whatever.
Limit the results in your query, for the love of god. (Look up LIMIT
in the
mysql docs - e.g. SELECT pic FROM pictures LIMIT 1,50 etc.)

As for storing in the database vs. storing filenames in the database
- the
database will give you a better chance for sequential disk I/O, and
will
reduce overhead most likely (i.e. storing a lot of small files that
are
around 5kb will likely mean you are using up 2 4kb clusters for each
file,
which is a huge waste of space. The database will likely have some
wasted
space as you have variable-length records, but at least the table
will be in
one physical file, leaving the specifics up to the database.)

As for inserting binary data into a blob field, search the newsgroup
or the
web, there are plenty of examples and tutorials.

My $.02
// Ian Fette
// Proponent comp.lang.php

John wrote:
I have over 5000 thumbnail pictures of size 5kb each. I would like
to
able to load all 5000 pictures and view 50 per page using
mysql_data_seek(). I would like to know what are the advantages and
disadvantages of using a MySQL blob field rather than reading the
images directly from the file? How does one insert an image into a
blob field? Can it be done dynamically?

Thank you
John

[Agelmar]

Would you mind posting your code for showimage.php?

"John" <jk*****@yahoo.com> wrote in message
news:f6*************************@posting.google.co m...

Agelmar

I have implemented as you have explained, but the code <img
src="showimage.php"> is not calling the PHP file. Only an empty X icon
is showing on the browser.

John

"Agelmar" <if**********@comcast.net> wrote in message

news:<bs************@ID-30799.news.uni-berlin.de>...

John - you are correct about the header bit, but you are incorrect about its limitations. What you would do is something like:

<img src="showimage.php?imageid=2428" alt="whatever">

and then showimage.php has header("content-type: whatever...."); at the top, followed by the something that spits out the data for the image. Thus it
sends the header information for each image. (The "header before any output" rule is still observed, because you are sending the header before any output for the page in question. Yes, data will have already been sent for your
overall page, but for showimage.php?imageid=2428 (your source for the
image), you will be sending the header before any output for *that* data
request.)

http://www.sum-it.nl/en200319.php3 has a tutorial that at least shows the general idea (jump down to the "Show Thumbnail" section. They should not be using stripslashes() on the result from the mysql_query unless
magic_quotes_runtime or whatever is on (not gpc, but runtime or sybase), but you get the general idea.

Hope this helps clarify things a bit...

John wrote:

Thank you very much Agelmar for the detail information. I have tried
implementing using blob field but I am having a problem displaying the
images. I read from other articles in the newsgroup that in order to
display the image one need to also store the filetype and then use
header() to show the image. But this can only work if one is
displaying only one image and where the image has to be displayed
before any other output is displayed in the browser, otherwise one
would get the dreaded header error. My application is a catalog that
displays 50 products per page with each product having its own image
and information. Is there not a simpler way to show the images? My
images are only jpeg files.

Thank you
John

"Agelmar" <if**********@comcast.net> wrote in message
news:<bs************@ID-30799.news.uni-berlin.de>...
> You do not want to use mysql_data_seek. You will be fetching 24Mb of
> data 100 times if you do it that way (you will be fetching the
> entire result on
> each page (100 of them), and then seeking to picture 50, 100,
> whatever.
> Limit the results in your query, for the love of god. (Look up LIMIT
> in the
> mysql docs - e.g. SELECT pic FROM pictures LIMIT 1,50 etc.)
>
> As for storing in the database vs. storing filenames in the database
> - the
> database will give you a better chance for sequential disk I/O, and
> will
> reduce overhead most likely (i.e. storing a lot of small files that
> are
> around 5kb will likely mean you are using up 2 4kb clusters for each
> file,
> which is a huge waste of space. The database will likely have some
> wasted
> space as you have variable-length records, but at least the table
> will be in
> one physical file, leaving the specifics up to the database.)
>
> As for inserting binary data into a blob field, search the newsgroup
> or the
> web, there are plenty of examples and tutorials.
>
> My $.02
> // Ian Fette
> // Proponent comp.lang.php
>
> John wrote:
>> I have over 5000 thumbnail pictures of size 5kb each. I would like
>> to
>> able to load all 5000 pictures and view 50 per page using
>> mysql_data_seek(). I would like to know what are the advantages and
>> disadvantages of using a MySQL blob field rather than reading the
>> images directly from the file? How does one insert an image into a
>> blob field? Can it be done dynamically?
>>
>> Thank you
>> John

[Agelmar]

OK John - I whipped up a quick example. You will obviously want to add error
checking and more features, but this should get you going. Three files:
addpic.php, getpic.php, and pic.html (which calls getpic.php)

This is tested with php 4.3.4 and mysql 4.0.12 (yeah I know I should upgrade
to 4.0.17 but I haven't found the time) :-)

The structure of pics is as follows:

id, which is an int and primary key and auto_increment
pic, which is blob
With that:
addpic.php:

<html>
<body>
<form name="addpic" method="post" action="addpic.php"
enctype="multipart/form-data">
<input type="file" name="pict">
<input type="submit" value="add pic"></form>

<?php
if (isset($_FILES['pict']))
{
echo "Adding picture to DB";
$MySQL_Link = mysql_connect("localhost", "ian", "my_password");
mysql_select_db("ian");
$file = fopen($_FILES['pict']['tmp_name'], "r");
$pic = fread($file, 1000000); // this will read up to 1MB... change if you
like
mysql_query("INSERT INTO pics VALUES (NULL,
\"".mysql_escape_string($pic)."\")", $MySQL_Link);
}
?>
</body>
</html>
getpic.php: (You definitely want to add error checking, this is just a
crappy example)
<?php
header("Content-type: image/jpeg");
$MySQL_Link = mysql_connect("localhost", "ian", "my_password");
mysql_select_db("ian");
$pic = mysql_query("SELECT pic FROM pics WHERE id = '{$_GET['id']}'",
$MySQL_Link);
$data = mysql_fetch_array($pic);
echo $data['pic'];
?>
pic.html:
<html>
<body>
<p>blah blah</p>
<p><img src="getpic.php?id=2"></p>
<p><img src="getpic.php?id=1"></p>
</body>
</html>

"Agelmar" <if**********@comcast.net> wrote in message
news:bs************@ID-30799.news.uni-berlin.de...

Would you mind posting your code for showimage.php?

"John" <jk*****@yahoo.com> wrote in message
news:f6*************************@posting.google.co m...

Agelmar

I have implemented as you have explained, but the code <img
src="showimage.php"> is not calling the PHP file. Only an empty X icon
is showing on the browser.

John

"Agelmar" <if**********@comcast.net> wrote in message news:<bs************@ID-30799.news.uni-berlin.de>...

John - you are correct about the header bit, but you are incorrect about its

limitations. What you would do is something like:

<img src="showimage.php?imageid=2428" alt="whatever">

and then showimage.php has header("content-type: whatever...."); at

the

top, followed by the something that spits out the data for the image. Thus

it sends the header information for each image. (The "header before any

output" rule is still observed, because you are sending the header before any output for the page in question. Yes, data will have already been sent for your overall page, but for showimage.php?imageid=2428 (your source for the
image), you will be sending the header before any output for *that* data request.)

http://www.sum-it.nl/en200319.php3 has a tutorial that at least shows the general idea (jump down to the "Show Thumbnail" section. They should not be

using stripslashes() on the result from the mysql_query unless
magic_quotes_runtime or whatever is on (not gpc, but runtime or

sybase),

but you get the general idea.

Hope this helps clarify things a bit...

John wrote:
> Thank you very much Agelmar for the detail information. I have tried
> implementing using blob field but I am having a problem displaying

the > images. I read from other articles in the newsgroup that in order to
> display the image one need to also store the filetype and then use
> header() to show the image. But this can only work if one is
> displaying only one image and where the image has to be displayed
> before any other output is displayed in the browser, otherwise one
> would get the dreaded header error. My application is a catalog that
> displays 50 products per page with each product having its own image
> and information. Is there not a simpler way to show the images? My
> images are only jpeg files.
>
> Thank you
> John
>
> "Agelmar" <if**********@comcast.net> wrote in message
> news:<bs************@ID-30799.news.uni-berlin.de>...
>> You do not want to use mysql_data_seek. You will be fetching 24Mb of >> data 100 times if you do it that way (you will be fetching the
>> entire result on
>> each page (100 of them), and then seeking to picture 50, 100,
>> whatever.
>> Limit the results in your query, for the love of god. (Look up LIMIT >> in the
>> mysql docs - e.g. SELECT pic FROM pictures LIMIT 1,50 etc.)
>>
>> As for storing in the database vs. storing filenames in the database >> - the
>> database will give you a better chance for sequential disk I/O, and
>> will
>> reduce overhead most likely (i.e. storing a lot of small files that
>> are
>> around 5kb will likely mean you are using up 2 4kb clusters for each >> file,
>> which is a huge waste of space. The database will likely have some
>> wasted
>> space as you have variable-length records, but at least the table
>> will be in
>> one physical file, leaving the specifics up to the database.)
>>
>> As for inserting binary data into a blob field, search the newsgroup >> or the
>> web, there are plenty of examples and tutorials.
>>
>> My $.02
>> // Ian Fette
>> // Proponent comp.lang.php
>>
>> John wrote:
>>> I have over 5000 thumbnail pictures of size 5kb each. I would like
>>> to
>>> able to load all 5000 pictures and view 50 per page using
>>> mysql_data_seek(). I would like to know what are the advantages and >>> disadvantages of using a MySQL blob field rather than reading the
>>> images directly from the file? How does one insert an image into a
>>> blob field? Can it be done dynamically?
>>>
>>> Thank you
>>> John

[Agelmar]

Also - I should mention - for getpic.php: the very first line of getpic.php
should be <?php (no blank spaces and/or blank lines before it) and the very
last bit should be ?> (no blank spaces / lines afterwards)
"Agelmar" <if**********@comcast.net> wrote in message
news:bs************@ID-30799.news.uni-berlin.de...

OK John - I whipped up a quick example. You will obviously want to add error checking and more features, but this should get you going. Three files:
addpic.php, getpic.php, and pic.html (which calls getpic.php)

This is tested with php 4.3.4 and mysql 4.0.12 (yeah I know I should upgrade to 4.0.17 but I haven't found the time) :-)

The structure of pics is as follows:

id, which is an int and primary key and auto_increment
pic, which is blob
With that:
addpic.php:

<html>
<body>
<form name="addpic" method="post" action="addpic.php"
enctype="multipart/form-data">
<input type="file" name="pict">
<input type="submit" value="add pic"></form>

<?php
if (isset($_FILES['pict']))
{
echo "Adding picture to DB";
$MySQL_Link = mysql_connect("localhost", "ian", "my_password");
mysql_select_db("ian");
$file = fopen($_FILES['pict']['tmp_name'], "r");
$pic = fread($file, 1000000); // this will read up to 1MB... change if you like
mysql_query("INSERT INTO pics VALUES (NULL,
\"".mysql_escape_string($pic)."\")", $MySQL_Link);
}
?>
</body>
</html>
getpic.php: (You definitely want to add error checking, this is just a
crappy example)
<?php
header("Content-type: image/jpeg");
$MySQL_Link = mysql_connect("localhost", "ian", "my_password");
mysql_select_db("ian");
$pic = mysql_query("SELECT pic FROM pics WHERE id = '{$_GET['id']}'",
$MySQL_Link);
$data = mysql_fetch_array($pic);
echo $data['pic'];
?>
pic.html:
<html>
<body>
<p>blah blah</p>
<p><img src="getpic.php?id=2"></p>
<p><img src="getpic.php?id=1"></p>
</body>
</html>

"Agelmar" <if**********@comcast.net> wrote in message
news:bs************@ID-30799.news.uni-berlin.de...

Would you mind posting your code for showimage.php?

"John" <jk*****@yahoo.com> wrote in message
news:f6*************************@posting.google.co m...

Agelmar

I have implemented as you have explained, but the code <img
src="showimage.php"> is not calling the PHP file. Only an empty X icon
is showing on the browser.

John

"Agelmar" <if**********@comcast.net> wrote in message news:<bs************@ID-30799.news.uni-berlin.de>...

> John - you are correct about the header bit, but you are incorrect about

its

> limitations. What you would do is something like:
>
> <img src="showimage.php?imageid=2428" alt="whatever">
>
> and then showimage.php has header("content-type: whatever...."); at the

top,

> followed by the something that spits out the data for the image. Thus it > sends the header information for each image. (The "header before any

output"

> rule is still observed, because you are sending the header before

any output

> for the page in question. Yes, data will have already been sent for your > overall page, but for showimage.php?imageid=2428 (your source for

the > image), you will be sending the header before any output for *that*

data > request.)
>
> http://www.sum-it.nl/en200319.php3 has a tutorial that at least shows the

> general idea (jump down to the "Show Thumbnail" section. They should not

be

> using stripslashes() on the result from the mysql_query unless
> magic_quotes_runtime or whatever is on (not gpc, but runtime or

sybase),

but

> you get the general idea.
>
> Hope this helps clarify things a bit...
>
> John wrote:
> > Thank you very much Agelmar for the detail information. I have tri

ed > > implementing using blob field but I am having a problem displaying

the > > images. I read from other articles in the newsgroup that in order to > > display the image one need to also store the filetype and then use
> > header() to show the image. But this can only work if one is
> > displaying only one image and where the image has to be displayed
> > before any other output is displayed in the browser, otherwise one
> > would get the dreaded header error. My application is a catalog that > > displays 50 products per page with each product having its own image > > and information. Is there not a simpler way to show the images? My > > images are only jpeg files.
> >
> > Thank you
> > John
> >
> > "Agelmar" <if**********@comcast.net> wrote in message
> > news:<bs************@ID-30799.news.uni-berlin.de>...
> >> You do not want to use mysql_data_seek. You will be fetching 24Mb of > >> data 100 times if you do it that way (you will be fetching the
> >> entire result on
> >> each page (100 of them), and then seeking to picture 50, 100,
> >> whatever.
> >> Limit the results in your query, for the love of god. (Look up LIMIT > >> in the
> >> mysql docs - e.g. SELECT pic FROM pictures LIMIT 1,50 etc.)
> >>
> >> As for storing in the database vs. storing filenames in the database > >> - the
> >> database will give you a better chance for sequential disk I/O, and > >> will
> >> reduce overhead most likely (i.e. storing a lot of small files that > >> are
> >> around 5kb will likely mean you are using up 2 4kb clusters for each > >> file,
> >> which is a huge waste of space. The database will likely have some > >> wasted
> >> space as you have variable-length records, but at least the table
> >> will be in
> >> one physical file, leaving the specifics up to the database.)
> >>
> >> As for inserting binary data into a blob field, search the newsgroup > >> or the
> >> web, there are plenty of examples and tutorials.
> >>
> >> My $.02
> >> // Ian Fette
> >> // Proponent comp.lang.php
> >>
> >> John wrote:
> >>> I have over 5000 thumbnail pictures of size 5kb each. I would like > >>> to
> >>> able to load all 5000 pictures and view 50 per page using
> >>> mysql_data_seek(). I would like to know what are the advantages and > >>> disadvantages of using a MySQL blob field rather than reading the > >>> images directly from the file? How does one insert an image into a > >>> blob field? Can it be done dynamically?
> >>>
> >>> Thank you
> >>> John