Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.



280 posts

Ultimate Geek


Topic # 71088 4-Nov-2010 13:53 Send private message

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?




View this topic in a long page with up to 500 replies per page Create new topic
 1 | 2
3bit.com
5720 posts

Uber Geek
+1 received by user: 104

Moderator
Trusted
Subscriber

Reply # 400257 4-Nov-2010 16:12 Send private message

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?




99 posts

Master Geek
+1 received by user: 2


  Reply # 400277 4-Nov-2010 17:04 Send private message

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".

 



280 posts

Ultimate Geek


  Reply # 400470 5-Nov-2010 08:23 Send private message

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




245 posts

Master Geek


  Reply # 400483 5-Nov-2010 09:12 Send private message

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.



280 posts

Ultimate Geek


  Reply # 401639 8-Nov-2010 12:35 Send private message

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]





245 posts

Master Geek


  Reply # 401680 8-Nov-2010 13:47 Send private message

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'




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.



280 posts

Ultimate Geek


  Reply # 401944 9-Nov-2010 08:52 Send private message

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

the 'imageid' field is an auto-incremented field




245 posts

Master Geek


  Reply # 401948 9-Nov-2010 08:58 Send private message

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




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.



280 posts

Ultimate Geek


  Reply # 401950 9-Nov-2010 09:02 Send private message

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]




245 posts

Master Geek


  Reply # 401951 9-Nov-2010 09:02 Send private message

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




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.

245 posts

Master Geek


  Reply # 401954 9-Nov-2010 09:09 Send private message

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 :) ?




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.



280 posts

Ultimate Geek


  Reply # 401958 9-Nov-2010 09:17 Send private message

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'.






280 posts

Ultimate Geek


  Reply # 401972 9-Nov-2010 09:42 Send private message

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");
?>




245 posts

Master Geek


  Reply # 401976 9-Nov-2010 09:55 Send private message

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






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.



280 posts

Ultimate Geek


  Reply # 401979 9-Nov-2010 09:59 Send private message

Thanks ScottStevens, I will give that a work!




 1 | 2
View this topic in a long page with up to 500 replies per page Create new topic








Twitter »
Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when new jobs are posted to our jobs board:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:




News »

Trending now »
Hot discussions in our forums right now:

Forms of government for New Zealand
Created by charsleysa, last reply by KiwiNZ on 20-Apr-2014 18:55 (120 replies)
Pages... 6 7 8


MH370 - Call for Search & Rescue Help
Created by DS248, last reply by Sideface on 17-Apr-2014 17:28 (735 replies)
Pages... 47 48 49


why does the tax payer have to pay for the prince and princess' 6 star holiday?
Created by joker97, last reply by Geektastic on 17-Apr-2014 15:49 (67 replies)
Pages... 3 4 5


Naked Broadband, who's made the switch.
Created by Presso, last reply by richms on 20-Apr-2014 23:04 (31 replies)
Pages... 2 3


galaxy s4 now on 4.4.2
Created by nzrock, last reply by jeffnz on 20-Apr-2014 14:10 (59 replies)
Pages... 2 3 4


Whats the best wife friendly media centre?
Created by amorpeth, last reply by jonolynn on 19-Apr-2014 20:20 (14 replies)

Snap suffering Trans-Tasman congestion 18/04?
Created by Lias, last reply by NonprayingMantis on 19-Apr-2014 00:05 (26 replies)
Pages... 2


Help ! Home business connection and VDSL dead. yikes.
Created by Scotsman, last reply by Scotsman on 17-Apr-2014 21:10 (26 replies)
Pages... 2



Geekzone Live »
Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.