working with blobs

**SOR** · Jul 23 '05, 07:05 PM

Re: working with blobs

<comp.lang.ph p , Marcus , JumpMan222@aol. com>
<K5wEe.1322$DE1 .622@newssvr31. news.prodigy.co m>
<Sat, 23 Jul 2005 18:35:22 GMT>
[color=blue]
> 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.
>[/color]

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** · Jul 23 '05, 08:55 PM

Re: working with blobs

I noticed that Message-ID:
<MPG.1d4ca19eae 0fe6d7989d61@no-cancel.newsread er.com> from SOR contained
the following:
[color=blue]
>How can I insert rows[color=green]
>> of binary data (jpegs) into my table through the command prompt? Thanks
>> very much in advance.
>>[/color]
>
>Excuse the silly question - but why not install phpmyadmin .[/color]

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** · Jul 24 '05, 12:55 PM

Re: working with blobs

> Even sillier, why put images in the database in the first place?[color=blue]
>[/color]

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** · Jul 24 '05, 01:15 PM

Re: working with blobs

On Sat, 23 Jul 2005 18:35:22 GMT, Marcus <JumpMan222@aol .com> wrote:
[color=blue]
>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.[/color]

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_pac ket 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=LOA D_FILE('/tmp/picture')
WHERE id=1;
"

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**Geoff Berrow** · Jul 24 '05, 03:05 PM

Re: working with blobs

I noticed that Message-ID:
<42e38edc$0$254 27$afc38c87@new s.optusnet.com. au> from Michael Phipps
contained the following:
[color=blue][color=green]
>> Even sillier, why put images in the database in the first place?
>>[/color]
>
>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?[/color]

Not that I'm aware of.[color=blue]
>
>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?[/color]

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** · Jul 24 '05, 04:45 PM

Re: working with blobs

On Sun, 24 Jul 2005 15:59:00 +0100, Geoff Berrow <blthecat@ckdog .co.uk> wrote:
[color=blue]
>It's just that there
>is little or no benefit in storing images themselves.[/color]

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).
[color=blue]
>You can't search
>on them or sort on them. However, with file names I can do all these
>things.[/color]

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.
[color=blue]
>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.[/color]

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

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

working with blobs

working with blobs

Comment

Comment

Comment

Comment

Comment

Comment