Images within databases...

[eZe616]

I'm fairly knew to programming with PHP, I would say I've been coding for almost 3 months now. Not everyday or everyweek, but still enough to build a custom and simple CMS for a small real estate site.

Now I've been working at this IT store for a summerjob, and these last week i've been given a chance to build there production department website. I've chosen to use PHP, since it's the only programming lanuage I currently know.

 

Now I had to build a CMS for them to be able to upload the work for clients. Now my superior wants the client to be able to see different drafts of the job being done, ie, posters, ads, etc. and be able to approve a certain image.

 

I have a several table in the database, i.e one calle jobs, imgs, and drafts. Now the table imgs will contain all the paths of the images, including the different drafts. Now I have thought of giving the images,  and rand() property and the end of the name, but they'll have the same Job ID, to belong to a certain Job. From them I'll just Select them from the database being ordere by the id.

 

Now some of the other developer there told me they would've just saved the whole image inside the database as a blob, instead of the path. I have read several threads that say it would be better to store the path, and is the big reason I save the path of the image instead of the image itself. They explained that it's easier that way to create a history of the drafts. Other wise it'll make it so that i'll have to write more code. Now I'm not to fond of doing it that way, inserting the whole image.

 

So I was wondering which way would be better??

 

Also they work mostly with MSSQL and .NET, while i'm more PHP and MySQL. would that make a difference. Do they other types of databases handle the image blob thing better than MySQL, cause I was starting to feel i've been going about this project entirely wrong.

 

any help would be appreciate. TnX

 

 

[cmgmyr]

Well I have a few takes on this, hopefully it will help you out.

 

1. What I usually do if I have control over what the users are uploading (say .jpg's only) I set up a directory that they upload to and I rename the file to a random 16 char file name (so pretty much you can have a lot of them without worrying about if there are 2 files with the same name...even though you should still check.) Then I just store that file name in the database. This means that the database carries very little data.

 

2. If I have no control over what they are uploading (different files types) I've used a blob in the database and a varchar to store the file type. Then when they call you download it, the file type goes in the header, and you print the blob (short version). This is good so that you don't need to worry about what they upload, but if you do this your database is going to get VERY large quickly, which this would be true for you because they would be uploading very large files.

 

I think that you should go with #2, but there is no reason to have more then 1 table to hold this data. You can just make a column for "project_type" to hold if it's a job, img, etc...

 

Hope this helps you out!

[eZe616]

Well I have a few takes on this, hopefully it will help you out.

 

1. What I usually do if I have control over what the users are uploading (say .jpg's only) I set up a directory that they upload to and I rename the file to a random 16 char file name (so pretty much you can have a lot of them without worrying about if there are 2 files with the same name...even though you should still check.) Then I just store that file name in the database. This means that the database carries very little data.

 

Well I will have control over what they will upload. Since it's a custom CMS, and my superior is going to be uploading the pictures/videos himself. It's going to be .jpg for images, and .flv for videos, so I think number 1 here might be good though.

 

2. If I have no control over what they are uploading (different files types) I've used a blob in the database and a varchar to store the file type. Then when they call you download it, the file type goes in the header, and you print the blob (short version). This is good so that you don't need to worry about what they upload, but if you do this your database is going to get VERY large quickly, which this would be true for you because they would be uploading very large files.

 

I think that you should go with #2, but there is no reason to have more then 1 table to hold this data. You can just make a column for "project_type" to hold if it's a job, img, etc...

 

Hope this helps you out!

 

Yeah, Currently I have a table name Jobs, which has the fields of. unique id, client-id, job type, contact person, contact person cell, etc. and a different table for path to the imgs, with unique id, and job_id, since this was really my first project of this kind I somewhat feel I've overdone it with the tables, and looking back at my coding. I feel like beginning all over.

 

Which way would it be easier though to hold the history of the drafts for the projects? I would think the 1st one is easy enough.

[cmgmyr]

Well you should really have a table for users, jobs, job_users, and files

 

users

-----

userid

name

phone

email

ect...

 

jobs

-----

job_id

name

ect...

 

job_users (This is to give users access to certain jobs)

-----

ju_id

userid

job_id

ect...

 

files

-----

file_id

job_id

name

file

type

ect...

 

hope that sends you in the right direction

[eZe616]

Well yeah, that kinda gives me an idea. I'll post the db with tables I have tomorrow, if you're willing to give some advice on what could've been done bether.

[roopurt18]

I don't think saving the files on the disk and just storing their name, and possibly path, is going to create any more programming than if you stored the files as BLOBs inside the database; this goes for both PHP and .NET.

 

In both cases you need to detect the mime type of the file when it is uploaded and save that, so you're not losing or saving any time there.

 

In both cases you have to gather the contents of the file and dump it back to the client, setting headers before you do.  You have maybe two or three lines of extra code if the file is stored on disk because you have to fopen, fpassthru, fclose, but that's it.

 

It doesn't even really add extra work in the case of saving revisions or file history.

 

jobs

id

name

 

drafts

id

job_id

name

 

draft_revisions

id

draft_id

mime_type

orig_name

 

I'm assuming a structure where you have multiple jobs and each job has multiple drafts; as such, the jobs and drafts tables are fairly self explanatory.  The interface that I imagine is one where the user selects a project and is given a list of drafts; they select a draft and are given a list of revisions for that draft; clicking on a particular revision serves that file.

 

Let's save the uploaded files in:

/home/user/public_html/drafts

 

Let's add a subdirectory for each project created:

/home/user/public_html/drafts/<project_id>

We do this only because we might have to inspect the directory manually; we don't want to be bombarded with a billion files plopped into a single directory.  There is probably no need to add further subdirectories unless we expect lots of revisions; in that case, I'd suggest subdirectories like so:

/home/user/public_html/drafts/<project_id>/<draft_id>

 

We store the files in public_html to make them web accessible and serving them easier; we can just provide direct paths and apache can serve the file without invoking PHP.  If the files are sensitive, you will want to NOT put them in public_html, but instead place them higher in the directory structure and use a PHP script to fetch their contents (like if they had been saved as a BLOB).

 

A couple of notes about the draft_revisions table; it looks like its missing some fields!  Actually, it's not.  It has everything we need right there.  If the id column is an auto_incrementing primary key, it is guaranteed to be unique; thus when saving files to the file system we can use this id.  When files are requested, we will know which project, draft, and revision they are seeking; since we have a predetermined path layout, this provides us with everything we need to load the file.

 

For example, a user creates the first project in the system, calling it test.  They then create a draft, named a_draft, and upload the file word.doc.  They then upload a revision, calling it word2.doc.

 

Tables

+------+-------------+--------------+
| jobs |      id     |     name     |
+------+-------------+--------------+
|      |      1      |     test     |
+------+-------------+--------------+

+--------+------+---------+------------+
| drafts |  id  |  job_id |    name    |
+--------+------+---------+------------+
|        |  1   |    1    |  a_draft   |
+--------+------+---------+------------+

+-----------------+------+-----------+------------+-----------+
| draft_revisions |  id  |  draft_id |  mime_type | orig_name |
+-----------------+------+-----------+------------+-----------+
|                 |  1   |    1      |  ms word   | word.doc  |
+-----------------+------+-----------+------------+-----------+
|                 |  2   |    1      |  ms word   | word2.doc |
+-----------------+------+-----------+------------+-----------+

 

Our files will be stored here:

<i>word.doc</i>: /home/user/public_html/drafts/1/1

<i>word2.doc</i>: /home/user/public_html/drafts/1/2

 

You might be wondering how we determine which revisions are newest and which are oldest for a particular draft; you can add a DATETIME field or, since the id column is an auto_incrementing primary key, you can assume lower IDs represent older files (if you don't need the exact time it was uploaded).

 

Some PHP:

<?php
  function list_drafts($job){
  $Clean = Array();
  $Clean['Job'] = // Clean the variable $job
  // We want numbered output
  mysql_query("SET @numbering = 0");
  // Get the drafts from the job
  $sql = "SELECT "
       . "id, name, @numbering := @numbering + 1 AS row "
       . "FROM drafts WHERE job_id={$Clean['Job']} "
       . "ORDER BY id";
  $q = mysql_query($sql);
  // Now process $q
}

function list_revisions($draft){
  $Clean = Array();
  $Clean['Draft'] = // Clean the variable $draft
  // We want numbered output
  mysql_query("SET @numbering = 0");
  // Get the revisions from the draft
  $sql = "SELECT "
       . "r.mime_type, r.orig_name, "
       . "@numbering := @numbering + 1 AS row, "
       . "CONCAT( 'drafts/', d.job_id, '/', r.id ) AS file "
       . "FROM draft_revisions r, drafts d "
       . "WHERE "
       . "d.id={$Clean['Draft']} AND "
       . "d.id=r.draft_id "
       . "ORDER BY r.id";
  $q = mysql_query($sql);
  // Now process $q
}
?>

[eZe616]

Well currently I have this DB structure...

 

clients:

id 

client

username

password

clearance_l

address

telefon

fax

logo

 

jobs:

id

client_id

project

pcontact

pcontacttel

discription

reqdate

deadline

budget

 

imgs:

id

job_id

thumb1

imagepath

thumb2

 

drafts:

id

job_id

draft

 

--------

 

Now in the clients table comes the normal clients information that the admin will add himself, then later give the client his/hers password to view his files.

 

In the Jobs table, comes the basic information of every job that has been requested, with it's individual information for that current job.

 

In the images table, comes the images for the jobs, with the job_id and the paths to the images, so that they can be called upon when the specific job is requested to be viewed. I have a script that creates two resized smaller images, for thumbnails and such. Currently all the images are being stored in a folder called jobs. So eventually they will have alot of images in the folder. I'm not entirely sure how to create a folder every time for a new job.

 

Now the draft table i'm not sure if I even need that one. Thinking about it now I don't think it's really necessary do have it at all. Since like you said roopurt, I could just call them, by the higher ID as the latest and by the same job_id

 

Now another brainstorm I'm having is that my superior[the company] wants their client to be able to see the current draft, and all before it, and be able to comment on it, and when the clients satisfied, approve a certain draft with a link, witch I think can be accomplished with a Field called `approved` within the Job and Imgs table. Where 0=not approved and 1=approved.

 

 

to think I came in as a graphic designer at first.

[roopurt18]

I don't have time to look at your current setup in any detail right now, but I'll come back on my lunch break and see what I can't recommend for you.

 

In the meantime, if you want to create a new directory it's pretty easy.

 

  // Assume $details is an array of information describing what goes
  // into the job table.  Creating the record in the DB is not important,
  // it's creating the directory that we care about right now.
  function create_job($details){
    // Sanitize incoming data, prepare your sql, and insert into the
    // database; assume the result from mysql_query is stored in $q
    if($q){
      // Success, so we can create a directory
      $baseDir = "/home/user/public_html/drafts/";
      // Now we append to the directory the id of the last inserted record
      $baseDir .= mysql_insert_id();
      // We can't create a directory if it already exists
      if(!file_exists($baseDir) || !is_dir($baseDir) ){
        $result = mkdir( $baseDir ); // Default permissions are 0777, specify a second
        // parameter if you want
        // At this point, we know the directory exists so we can use
        // move_uploaded_file to copy the file into the directory, don't forget to
        // rename it based on mysql_insert_id()
      }
    }
  }

[roopurt18]

If the images are attached to the drafts, you should probably keep the drafts table.  Otherwise you're likely to end up with redundant data in either the project or images because you need a way of connecting images to a draft and linking the draft to the project.

 

The only thing I think you're missing is the draft_id column in your images table.  Also, you have two columns for thumbnails, what happens when you need to include more than two thumbnails?  You going to add an extra column?  I wouldn't organize it in this approach.

 

If you're dealing with multiple thumbnails, pick one of the following IMO:

 

Set up a thumbnails table:

id

image_id

 

If you name the files after the id in the thumbnails table and the mime type is always the same, thats probably all you need to store in there.  You create a new row for each thumbnail of an image.

 

The alternate method is to develop a file naming mechanism where if the main image is named 1, then the thumbnails are named 1_th1, 1_th2, 1_th3, etc.  You'd then have to have a function that scanned the file system for an image's thumbnails.

 

Personally I think the DB approach is better.

 

But the last thing you want is to have an image table with the following columns:

thumb1, thumb2, thumb3, ..., thumbN (where N ends up being some number like 10 or 20 or even higher)

[eZe616]

If the images are attached to the drafts, you should probably keep the drafts table.  Otherwise you're likely to end up with redundant data in either the project or images because you need a way of connecting images to a draft and linking the draft to the project.

 

The only thing I think you're missing is the draft_id column in your images table.  Also, you have two columns for thumbnails, what happens when you need to include more than two thumbnails?  You going to add an extra column?  I wouldn't organize it in this approach.

 

Now how about if a current job has multiple drafts? Which need to be shown, if the client has decided which one he/she wants? I think I understand the draft_id in the images, but I do feel a little lost right now.

[roopurt18]

That's why you have the job_id in the drafts table.  Each job has it's own id, multiple drafts can refer to the same id, thus a single project can have multiple drafts attached to it.

 

You said the client wants to be able to select a revision as the chosen one for the draft.  You can do this in two ways.  The first is add a TINYINT(1) column to the revisions table; 1 indicates the revision is chosen for a draft, 0 otherwise.  For all the revisions for a particular draft, only one can be value 1.

 

The other way is to include an INT column in the drafts table, call it `selected`.  It holds 0 when no revision has been chosen and the id of the corresponding image when a revision has been chosen.

 

Each method is about the same amount of work for clearing the selected revision or changing it to a new one, although the INT column in the drafts table might equate to slightly less.

 

Depending on the amount of space MySQL uses for TINYINT(1) compared to INT, one might equate to less disk usage given an average of N revisions per draft.  I don't think I'd be overly concerned with that though.