Inserting new 'Field' into Table Database

Forums › IT Pro and developers › Inserting new 'Field' into Table Database

hamisht

389 posts

Ultimate Geek

#71088 4-Nov-2010 13:53

Hi guys,

I've been looking on the net for a few hours now, but all the results that I find are ways of inserting records into tables, not creating a new field inside a table.

I want to create a new Field, which would be named what ever was filled out in the form below: [code]New Catagory: <input type="text" name="newcatagory" /><br />[/code]

[code]
<form action="uploadimage.php" method="post" name="upload" enctype="multipart/form-data">
    <input type="hidden" name="max_file_size" value="1500000" />
    Choose File (Max 1.5mb): <input type="file" name="userfile" /><br />
    Description / Keywords: <input type="text" name="description" /><br />
    New Catagory: <input type="text" name="newcatagory" /><br />
    Pre-Defined Catagory: <select name="imagegallery"><br />
    <input type="submit" name="submit" value="Upload" />
</form>
[/code]

I thought that the page 'uploadimage.php' would need this;

[code]mysql_select_db ('imagegallery');
    mysql_query ("insert into imagegallery (newcatagory) values ($newcatagory)");
    header ("Location:members.php");[/code]

but it doesn't add the new Field :( My Database is 'assessment', and the table is 'imagegallery'

inside the 'imagegallery' i have some fields (sports, arts, holiday), but I want the above form to add another one.

Any ideas?

My Blu-Ray Collection

1 | 2

6473 posts

Uber Geek

Retired Mod

Trusted

Lifetime subscriber

#400257 4-Nov-2010 16:12

I have to question your database design - what exactly are you trying to accomplish by adding a new field to a table? Surely there's better ways to do this?

dazzanz

214 posts

Master Geek

#400277 4-Nov-2010 17:04

It can be done but I agree, sounds like bad database design has lead you to this point. You shouldn't need to do this.

Edit:

To be more helpful what you should do is have three tables, one for the image data, one for categories and one to link images to categories.

Image table will have things like:
id
filename
extension
type
... etc

Categories table will have:
id
category

and the join table image_category will be
id
image_id
category_id

Then when you create a new category you insert it into the category table, and when you link an image to it, you insert the id of the category and the image id into the image_category table.

If you wanted you could do a tag system with just two tables

Image table:
id
filename
extension
type
... etc

Tag table:
id
image_id
tag

Then for each tag the image has you insert a new row into the tag table with the image id and the new tag eg "holiday".

hamisht

389 posts

Ultimate Geek

#400470 5-Nov-2010 08:23

Thanks mate, I will think about using that method :)

My Blu-Ray Collection

ScottStevensNZ

245 posts

Master Geek

#400483 5-Nov-2010 09:12

I would reccomend that you have a look at the wiki page on relational database design:

http://en.wikipedia.org/wiki/Relational_model

What it looks like is you have a potential many to many relationship - which in a relational database (like mysql) needs to be resolved -

1. One image must have atleast one or more categories
2. One Category may have one or many images

So the logic is, If you only have an image table, and that image must have one or many categories then how many categories do you limit it to (you can't have a table with infinate columns)?

So the answer is:

Table:Image
ImageKey|ImageName
1|Image1
2|Image2
3|Image3
4|Image4
5|Image5

Table:Category
CategoryKey|CategoryName
1|Sport
2|Nature
3|Cars
4|TopSecretNuclearPlans
5|KidsToys

Joining these tables together is ImageCategory

ImageKey|CategoryKey
1|4
1|5
2|2
2|1

This lets you show that:
Image1 belongs to the TopSecretNuclearPlans and KidsToys categories
Image2 belongs to the Sport and Nature Categories

Load & Performance Tester/PHP/JSP/C/PERL/MYSQL/LoadRunner8->11/HTML/CSS/XML/XSLT/2B|!2B/Cervelo Soloist/EMC Equip4/ Samsung Galaxy S /Darkys 10.2 Extreme

Do androids dream of electric sheep?
use strict;
my $sheepCount;

Yes, they can.

hamisht

389 posts

Ultimate Geek

#401639 8-Nov-2010 12:35

Thanks, I have this sorted now and it works well!

I have one other question, when the user uploads the file (filename.jpg), I want to re-name it according to data in a field;

(Table) - images
-----(Field) - imageid

I want the file to be named 'xxxxx.jpg' where xxxxx is the information in the imageid field (example, '14'...... so 14.jpg).

I have this code in my PHP, but it doesn't want to work...

[code]
$uploaddir = 'images/';
$uploadfile = $uploaddir . $imageid.".jpg";
move_uploaded_file($_FILES[userfile] ['tmp_name'], $uploadfile);
[/code]

My Blu-Ray Collection

ScottStevensNZ

245 posts

Master Geek

#401680 8-Nov-2010 13:47

hamisht: Thanks, I have this sorted now and it works well!

I have one other question, when the user uploads the file (filename.jpg), I want to re-name it according to data in a field;

(Table) - images
-----(Field) - imageid

I want the file to be named 'xxxxx.jpg' where xxxxx is the information in the imageid field (example, '14'...... so 14.jpg).

I have this code in my PHP, but it doesn't want to work...

[code]
$uploaddir = 'images/';
$uploadfile = $uploaddir . $imageid.".jpg";
move_uploaded_file($_FILES[userfile] ['tmp_name'], $uploadfile);
[/code]

update images set imageid='14.jpg' where imageid='xxxxx.jpg'

hamisht

389 posts

Ultimate Geek

#401944 9-Nov-2010 08:52

I don't want them all named '14' as they will just overwrite each other.

the 'imageid' field is an auto-incremented field

My Blu-Ray Collection

Equinox IT

Cloud spending continues to surge globally, but most organisations haven’t made the changes necessary to maximise the value and cost-efficiency benefits of their cloud investments. Download the whitepaper From Overspend to Advantage now.

ScottStevensNZ

245 posts

Master Geek

#401948 9-Nov-2010 08:58

Thats why specify the condition in the where clause, only update the field to '14.jpg' if the filename is 'abc123.jpg'. abc234.jpg, bcd678.jpg will all remain unchanged

hamisht

389 posts

Ultimate Geek

#401950 9-Nov-2010 09:02

Sorry I am new to PHP.

Where would it go in the code?

[code]
$uploaddir = 'images/';
$uploadfile = $uploaddir . $imageid.".jpg";
update images set imageid='14.jpg' where imageid='xxxxx.jpg'
move_uploaded_file($_FILES[userfile] ['tmp_name'], $uploadfile);
[/code]

My Blu-Ray Collection

ScottStevensNZ

245 posts

Master Geek

#401951 9-Nov-2010 09:02

I would also look at your table design and requirements. Reading your post again, if you have an auto-increment for imageId then it sounds like that is your primary key for that table.

I would structure it:

ImageId* | AutoIncrement
FileName | String
DateUpleaded | Date
etc
...

and leave the pK field alone and let mysql look after it

ScottStevensNZ

245 posts

Master Geek

#401954 9-Nov-2010 09:09

I see the problem. Can you take me back to the basics and tell me what we're trying to get done.

What I have picked up so far is that there is a MySQL database somewhere which holds some file information about some JPG files. Part of the requirements you have is to rename the file in the database? Am I in the right ball park :) ?

hamisht

389 posts

Ultimate Geek

#401958 9-Nov-2010 09:17

Ok, basically I have three tables:

members
---email
---password
---fullname

image
---imageid
---email
---catagory
---description
---date

catagory (i know, spelt wrong)
---arts
---personal
---holiday

at the moment, everything is working correctly.. a logged in user fills out a form (members.php) consiting of 'email, file, description, catagory, date'.. that gets sent to my file 'uploadimage.php' and all the data gets put into the localhost database (the image is hosted in an 'images/' folder.

The image is then displayed on the page 'members.php' under the form. it is resized to a thumbnail (although the thumbnail is not going into the folder 'images/thumbs/' but thats not quite as important as renaming.

The file needs to re-named while being uploaded, or after uploading, because someone might upload 'photo1.jpg' and overwrite someone elses 'photo1.jpg'.

My Blu-Ray Collection

hamisht

389 posts

Ultimate Geek

#401972 9-Nov-2010 09:42

this is my page 'uploadimages.php':

<?php
    include ('connection.php');

    // UPLOAD PHOTO
    $uploaddir = 'images/';
    //$uploadfile = $uploaddir . $imageid.".jpg";
    $uploadfile = $uploaddir . basename($_FILES["userfile"]['name']);
    move_uploaded_file($_FILES["userfile"] ['tmp_name'], $uploadfile);

    // RESIZE PHOTO BEGINS
    $originalImage = imagecreatefromjpeg('images/' .$userfile.".jpg");
    $originalWidth = imagesx($originalImage);
    $originalHeight = imagesy($originalImage);

    $divisorX = (int) ($originalWidth /150);
    $divisorY = (int) ($originalHeight /150);

    if ($divisorX >= $divisorY) {
        $divisor = $divisorX;
    }
    else {
        $divisor = $divisorY;
    }

    $newWidth = (int) ($originalWidth / $divisor);
    $newHeight = (int) ($originalHeight / $divisor);
    $newImage = imagecreatetruecolor ($newWidth, $newHeight);

    if (!imagecopyresized($newImage,$originalImage,0,0,0,0,$newWidth,$newHeight,$originalWidth,$originalHeight)) {
        echo "Could not produce the thumbnail";
        die;
    }
    imagejpeg($newImage, 'images/thumbs/' .$userfile.".jpg");

    // NEW CATAGORY INPUT
    $newcatagory = $_POST['newcatagory'];

    // RECORD INPUT
    $emailupload = $_POST['emailupload'];
    $description = $_POST['description'];
    $date = $_POST['date'];

    if(strlen($newcatagory) >0) {
        $query = "insert into catagory (catagory) values ('$newcatagory')";
        $catagory = $newcatagory;
        $result = mysql_query($query);
        if(!$result) {
            die ('Could not execute the query: '.mysql_error());
        }
    }
    else {
        $catagory = $_POST['existingcatagory'];
    }

    //RECORD INPUT QUERY
    $query = "insert into image (email , description , date , catagory) values ('$emailupload' , '$description' , '$date', '$catagory')";
    $result = mysql_query($query);
    if(!$result) {
        die ('Could not execute the query: '.mysql_error());
    }

    // REDIRECT BACK TO
    header ("Location:members.php");
?>

My Blu-Ray Collection

ScottStevensNZ

245 posts

Master Geek

#401976 9-Nov-2010 09:55

hamisht: this is my page 'uploadimages.php':

    include ('connection.php');

    // UPLOAD PHOTO
    $uploaddir = 'images/';
    //$uploadfile = $uploaddir . $imageid.".jpg";
    $uploadfile = $uploaddir . basename($_FILES["userfile"]['name']);
    move_uploaded_file($_FILES["userfile"] ['tmp_name'], $uploadfile);

    // RESIZE PHOTO BEGINS
    $originalImage = imagecreatefromjpeg('images/' .$userfile.".jpg");
    $originalWidth = imagesx($originalImage);
    $originalHeight = imagesy($originalImage);

    $divisorX = (int) ($originalWidth /150);
    $divisorY = (int) ($originalHeight /150);

    if ($divisorX >= $divisorY) {
        $divisor = $divisorX;
    }
    else {
        $divisor = $divisorY;
    }

    $newWidth = (int) ($originalWidth / $divisor);
    $newHeight = (int) ($originalHeight / $divisor);
    $newImage = imagecreatetruecolor ($newWidth, $newHeight);

    if (!imagecopyresized($newImage,$originalImage,0,0,0,0,$newWidth,$newHeight,$originalWidth,$originalHeight)) {
        echo "Could not produce the thumbnail";
        die;
    }
    imagejpeg($newImage, 'images/thumbs/' .$userfile.".jpg");

    // NEW CATAGORY INPUT
    $newcatagory = $_POST['newcatagory'];

    // RECORD INPUT
    $emailupload = $_POST['emailupload'];
    $description = $_POST['description'];
    $date = $_POST['date'];

    if(strlen($newcatagory) >0) {
        $query = "insert into catagory (catagory) values ('$newcatagory')";
        $catagory = $newcatagory;
        $result = mysql_query($query);
        if(!$result) {
            die ('Could not execute the query: '.mysql_error());
        }
    }
    else {
        $catagory = $_POST['existingcatagory'];
    }

    //RECORD INPUT QUERY
    $query = "insert into image (email , description , date , catagory) values ('$emailupload' , '$description' , '$date', '$catagory')";
    $result = mysql_query($query);
    if(!$result) {
        die ('Could not execute the query: '.mysql_error());
    }

    // REDIRECT BACK TO
    header ("Location:members.php");
?>

Ok, so there a couple ways of doing this:

1. Bung all the data into the image table as you would normally:

$query = "insert into image (email , description , date , catagory) values ('$emailupload' , '$description' , '$date', '$catagory')";

2. Query the database to get the imageId:

$query = "select imageid from image where filename='$filenameUploadedByUser'" or whatever clause will return the image id
$imageid=mysql_query($query);

3. do funky string append: $uploadfile = $uploaddir . $imageid.".jpg";

4. Update the database and rename the filename:
$query = "update image set filename='$imageid.jpg' where filename = '$filenameUploadedByUser'"

OR

you can get the next id from the database first:

$r = mysql_query("SHOW TABLE STATUS LIKE 'table_name' ");
$row = mysql_fetch_array($r);
$Auto_increment = $row['Auto_increment'];
mysql_free_result($r);

THEN do funky string append:

$uploadfile = $uploaddir . $Auto_increment .".jpg";

Then fire the record into the database all at once

hamisht

389 posts

Ultimate Geek

#401979 9-Nov-2010 09:59

Thanks ScottStevens, I will give that a work!

My Blu-Ray Collection

1 | 2