working with blobs

[Marcus]

Hi all,

Quick question... I am storing jpegs as blobs in a mysql table, which is
fine on my live server because I am just uploading them using phpmyadmin
(I am working with a small number of images that will rarely change and
thus do not have a need for an upload script). My problem is I do all
my testing on my local host where I do not have phpmyadmin, I do
everything with mysql through the command prompt. How can I insert rows
of binary data (jpegs) into my table through the command prompt? Thanks
very much in advance.

Marcus

[SOR]

<comp.lang.php , Marcus , Ju********@aol.com>
<K5****************@newssvr31.news.prodigy.com>
<Sat, 23 Jul 2005 18:35:22 GMT>

Quick question... I am storing jpegs as blobs in a mysql table, which is
fine on my live server because I am just uploading them using phpmyadmin
(I am working with a small number of images that will rarely change and
thus do not have a need for an upload script). My problem is I do all
my testing on my local host where I do not have phpmyadmin, I do
everything with mysql through the command prompt. How can I insert rows
of binary data (jpegs) into my table through the command prompt? Thanks
very much in advance.

Excuse the silly question - but why not install phpmyadmin .

IMS you only need to change one or two lines in the config file for it
to work .

[Geoff Berrow]

I noticed that Message-ID:
<MP************************@no-cancel.newsreader.com> from SOR contained
the following:

How can I insert rows

of binary data (jpegs) into my table through the command prompt? Thanks
very much in advance.

Excuse the silly question - but why not install phpmyadmin .

Even sillier, why put images in the database in the first place?

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Michael Phipps]

> Even sillier, why put images in the database in the first place?

Perhaps it isn't suitable in this particular scenario, but if you are likely
to have thousands of images, isn't there a limit to the number of files you
can put into a particular directory?

I store photos in a database with no ill effects so far. No one has been
able to convince me one is better than the other - perhaps you can?

[Andy Hassall]

On Sat, 23 Jul 2005 18:35:22 GMT, Marcus <Ju********@aol.com> wrote:

Quick question... I am storing jpegs as blobs in a mysql table, which is
fine on my live server because I am just uploading them using phpmyadmin
(I am working with a small number of images that will rarely change and
thus do not have a need for an upload script). My problem is I do all
my testing on my local host where I do not have phpmyadmin, I do
everything with mysql through the command prompt. How can I insert rows
of binary data (jpegs) into my table through the command prompt? Thanks
very much in advance.

Look in the MySQL manual under "String functions":

"
LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. The file must be
located on the server, you must specify the full pathname to the file, and you
must have the FILE privilege. The file must be readable by all and be smaller
than max_allowed_packet bytes.

If the file doesn't exist or cannot be read because one of the preceding
conditions is not satisfied, the function returns NULL.

mysql> UPDATE tbl_name
SET blob_column=LOAD_FILE('/tmp/picture')
WHERE id=1;
"

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Geoff Berrow]

I noticed that Message-ID:
<42***********************@news.optusnet.com.au> from Michael Phipps
contained the following:

Even sillier, why put images in the database in the first place?

Perhaps it isn't suitable in this particular scenario, but if you are likely
to have thousands of images, isn't there a limit to the number of files you
can put into a particular directory?

Not that I'm aware of.
I store photos in a database with no ill effects so far. No one has been
able to convince me one is better than the other - perhaps you can?

I'm not saying you will have any ill effects (though I'm sure there
would be if the number of images got very large). It's just that there
is little or no benefit in storing images themselves. You can't search
on them or sort on them. However, with file names I can do all these
things. With sensible naming you can fetch your pictures in all sorts
of ways. Additionally the same picture can be accessed by any number of
database records without the overhead of storing it n number of times.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[Andy Hassall]

On Sun, 24 Jul 2005 15:59:00 +0100, Geoff Berrow <bl******@ckdog.co.uk> wrote:

It's just that there
is little or no benefit in storing images themselves.
The main benefits of storing the images in the database are:

(1) Single point of backup.
(2) Atomic operations on the image data in the same way as for the other
associated data. (This also applies to the backup point, since if you have
database + filesystem, it's harder to get a consistent backup of the two).
You can't search
on them or sort on them. However, with file names I can do all these
things.
With the image in or out of the database, you can search and sort on whatever
you like. Putting it in the filesystem doesn't make a difference here.
With sensible naming you can fetch your pictures in all sorts
of ways. Additionally the same picture can be accessed by any number of
database records without the overhead of storing it n number of times.

There's no need to store the same image multiple times in a normalised
database.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool