store image locations in mysql db for gallery

Dev Articles Community Forums Sponsor:

Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

June 23rd, 2003, 09:10 AM

jben.net

Contributing User

Join Date: Apr 2003

Posts: 51

Time spent in forums: 19 m 41 sec
Reputation Power: 6

store image locations in mysql db for gallery

Hi,

I've written a script that resizes and stores 3 versions of the same image to use in a gallery for a flash site I'm building.

I've got the uploading working fine and now I'm trying to store the locations of each image (big, preview and thumbnail) in a mysql table so I can retrieve them using php and send them to flash as xml.

I've created my db and table but I can't seem to get the info into the database.

my mysql set up sql is:

PHP Code:


		
			
 CREATE TABLE gallery (

    imageID INTEGER AUTO_INCREMENT PRIMARY KEY,

    galleryName VARCHAR(50),

    bigURL VARCHAR(100),

    previewURL VARCHAR(100),

    thumbURL VARCHAR(100),)

    caption VARCHAR(100);

My php for storing the images is:

PHP Code:


		
			
<?php



/*



    save_image.php

    stores 3 images (thumbnail, preview and big) in 3 seperate folders

    and their locations in the database with a caption for each image.

    

*/



// Define database connection details



include "../php/db.inc.php";



// [POST] vars



$gallery = stripslashes($_POST['gallery']);

$caption = stripslashes($_POST['caption']);



// normal vars



$bigMaxWidth = 800;       

$bigMaxHeight = 600;



$prevMaxWidth = 255;       

$prevMaxHeight = 300;



$thumbImgWidth = 40;

$thumbImgHeight = 40;



$bigDir = "big";

$prevDir = "preview";

$thumbDir = "thumbs";



// filename vars



$time = time();

$timeSub = substr($time, 4, 4);

            

$fileName = explode(".", $_FILES['strFile']['name']);

$name = str_replace(' ', '_', $fileName[0]);

$newImageName = $timeSub . "_$name.jpg";



$bigImageStored = "./$gallery/$bigDir/$newImageName";

$prevImageStored = "./$gallery/$prevDir/$newImageName";

$thumbImageStored = "./$gallery/$thumbDir/$newImageName";



echo "$bigImageStored<br>$prevImageStored<br>$thumbImageStored<br><br>Caption:<br>$caption<br><br>";



// accepted filetypes array



$acceptedTypes = array('image/jpeg','image/jpg');



// check uploaded image is correct type

if(!in_array($_FILES['strFile']['type'], $acceptedTypes) || trim($_FILES['strFile']['tmp_name']) == "" || trim($_FILES['strFile']['tmp_name']) =="none")

    {

                    

        echo 'You didnt suply an image a valid image.';

                    

    }

    else

    {

                    

        $imgOriginal = ImageCreateFromJpeg($_FILES['strFile']['tmp_name']);

                            

                            

        $imgOrigSize = getimagesize($_FILES['strFile']['tmp_name']);

        $imgOrigWidth = $imgOrigSize[0];

        $imgOrigHeight = $imgOrigSize[1];

        

        

        // define sizes for big

        

        if($imgOrigWidth >= $bigMaxWidth || $imgOrigHeight >= $bigMaxHeight)

        {

            $mlt_w = $bigMaxWidth / $imgOrigWidth;

            $mlt_h = $bigMaxHeight / $imgOrigHeight;

            $mlt = $mlt_w < $mlt_h ? $mlt_w:$mlt_h;

                                                

            #### Calculate new dimensions

            $bigImgWidth =  round($imgOrigWidth * $mlt);

            $bigImgHeight =  round($imgOrigHeight * $mlt);

            

            // save resized-image

            $bigImgResized = Imagecreatetruecolor($bigImgWidth, $bigImgHeight);

            imagecopyresized($bigImgResized, ImageCreateFromJpeg($_FILES['strFile']['tmp_name']), 0 , 0 , 0 , 0, $bigImgWidth, $bigImgHeight, $imgOrigWidth, $imgOrigHeight);

            

            ImageJPEG($bigImgResized, $bigImageStored);            

            $imgNewSize = filesize("$bigImageStored") . " in bytes";

            ImageDestroy($bigImgResized);

            

            echo "bigImgWidth = $bigImgWidth and bigImgHeight = $bigImgHeight<br>Filesize = $imgNewSize";

            echo "<br><br>";

            

        }

        else

        {

            

            $bigImgWidth =  $imgOrigWidth;

            $bigImgHeight =  $imgOrigHeight;

            

            // save original image

            ImageJPEG($imgOriginal, $bigImageStored);

            $imgNewSize = filesize("$bigImageStored") . " in bytes";

            

            echo "bigImgWidth = $bigImgWidth and bigImgHeight = $bigImgHeight<br>Filesize = $imgNewSize";

            echo "<br><br>";

            

        }

        

        

        // define sizes for preview

        

        if($imgOrigWidth >= $prevMaxWidth || $imgOrigHeight >= $prevMaxHeight)

        {

            $mlt_w = $prevMaxWidth / $imgOrigWidth;

            $mlt_h = $prevMaxHeight / $imgOrigHeight;

            $mlt = $mlt_w < $mlt_h ? $mlt_w:$mlt_h;

                                                

            #### Calculate new dimensions

            $prevImgWidth =  round($imgOrigWidth * $mlt);

            $prevImgHeight =  round($imgOrigHeight * $mlt);

            

            // save resized-image

            $prevImgResized = Imagecreatetruecolor($prevImgWidth, $prevImgHeight);

            imagecopyresized($prevImgResized, ImageCreateFromJpeg($_FILES['strFile']['tmp_name']), 0 , 0 , 0 , 0, $prevImgWidth, $prevImgHeight, $imgOrigWidth, $imgOrigHeight);

            

            ImageJPEG($prevImgResized, $prevImageStored);            

            $imgNewSize = filesize("$prevImageStored") . " in bytes";

            ImageDestroy($prevImgResized);

            

            echo "prevImgWidth = $prevImgWidth and prevImgHeight = $prevImgHeight<br>Filesize = $imgNewSize";

            echo "<br><br><img src='$prevImageStored'>";

            echo "<br><br>";

            

        }

        else

        {

            

            $prevImgWidth =  $imgOrigWidth;

            $prevImgHeight =  $imgOrigHeight;

            

            // save original image

            ImageJPEG($imgOriginal, $prevImageStored);

            $imgNewSize = filesize("$prevImageStored") . " in bytes";

            

            echo "prevImgWidth = $prevImgWidth and prevImgHeight = $prevImgHeight<br>Filesize = $imgNewSize";

            echo "<br><br><img src='$prevImageStored'>";

            echo "<br><br>";

            

        }

        

        // resize image for thumbnail

        

        $thumbImgResized = Imagecreatetruecolor($thumbImgWidth, $thumbImgHeight);

        imagecopyresized($thumbImgResized, ImageCreateFromJpeg($_FILES['strFile']['tmp_name']), 0 , 0 , 0 , 0, $thumbImgWidth, $thumbImgHeight, $imgOrigWidth, $imgOrigHeight);



        ImageJPEG($thumbImgResized, $thumbImageStored);            

        $imgNewSize = filesize("$thumbImageStored") . " in bytes";

        ImageDestroy($thumbImgResized);

            

        echo "thumbImgWidth = $thumbImgWidth and thumbImgHeight = $thumbImgHeight<br>Filesize = $imgNewSize";

        echo "<br><br><img src='$thumbImageStored'>";

        echo "<br><br>";

        

        // add info to database

        // Attempt to connect to MySQL server

        $link = @mysql_connect($dbHost, $dbUser, $dbPass);



        // If the connection was unsuccessful...

        if (!$link)

        {

            // Report error to Flash and exit

                echo "couldn't connect to database";

            exit;

        }

        

        // setup MySQL query

        $query = "INSERT INTO gallery (galleryName, bigURL, previewURL, thumbURL, caption)

          VALUES($gallery, $bigImageStored, $prevImageStored, $thumbImageStored, $caption)";

          

        // Execute Query

        $result = @mysql_query($query);

        

        // If query was successful

        if ($result)

        {

               // Report success

                echo "image added succesfully";

        }

        else

        {

            // Report failure

                echo "image upload failed";

    }

}



?>

Everytime I try running the script all the images are sized and stored fine but the mysql query always fails, anyone got any ideas why ??

Thanks in advance,

Jon

June 23rd, 2003, 09:34 AM

_rainbow_

Registered User

Join Date: Jun 2003

Location: Lj, Slovenia

Posts: 27

Time spent in forums: 4 m 28 sec
Reputation Power: 0

One or two things stand out. Why on earth do you use @ in front of all mysql statments without the needed (OK prefered) or die("msg") at the end? This way if an error occures, you are not notified. Try it this way:

PHP Code:


		
			
// add info to database    ********

// Attempt to connect to MySQL server    ********

$link*=*mysql_connect($dbHost,*$dbUser,*$dbPass) or die("An error has ocured: ".mysql_error().":".mysql_errno());

Use this function or die() with all of your attempts with SQL (connect, select_db, query and so on).

Involving query: I prefer to use ' ' around all of the values I pass along with the query. Let me explain.

PHP Code:


		
			
 $query*=*"INSERT*INTO*gallery*(galleryName,*bigURL,*previewU  RL,*thumbURL,*caption) VALUES('$gallery',*'$bigImageStored',*'$prevImageS  tored',*'$thumbImageStored',*'$caption')";

It has never failed me.

Try your script with the suggested or die() function and see if any error mesages pop up. If they do paste them here.

P.S.: I apologize for my disability to write correct in english.

Last edited by _rainbow_ : June 23rd, 2003 at 09:41 AM.

June 25th, 2003, 05:21 AM

jben.net

Contributing User

Join Date: Apr 2003

Posts: 51

Time spent in forums: 19 m 41 sec
Reputation Power: 6

got it!

Thanks rainbow, that was it. Now on to the next dilema. I want to be able to edit the captions (but not image locations) of each row and also be able to delete an entire row, how would you reccomend going about this ??

I've written a deleteimage.php script ...

PHP Code:


		
			
<?php



/*



    deleteimage.php

    deletes a specific row from the gallery table

    

*/



// Define database connection details



include "./db.inc.php";



$ID = $_POST['ID'];



$query = "DELETE FROM 'gallery' WHERE 'imageID' = $ID LIMIT 1";



// Attempt to connect to MySQL server

$link = mysql_connect($dbHost ,$dbUser ,$dbPass ) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());

        

$dbconnect = mysql_select_db($dbName);

        

// setup MySQL query

$query = "DELETE FROM 'gallery' WHERE 'imageID' = $ID LIMIT 1";



// Execute Query

$result = mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ()); 



?>

and I was thinking of calling this by first querying the db and outputing a list of links which called the deleteimage.php?ID=5 is this the right idea ???

I'm thinking I should have 2 links for each row, edit and delete, what do you think ??

Thanks,

Jon

June 25th, 2003, 06:03 AM

_rainbow_

Registered User

Join Date: Jun 2003

Location: Lj, Slovenia

Posts: 27

Time spent in forums: 4 m 28 sec
Reputation Power: 0

That's about right. You actually don't need LIMIT 1 at the end of your DELETE statement. You use it only if you have several images with the same id, but that's not supposed to happen, right

All you have to do now is to write the UPDATE SQl statement correct and you're almost done.

I see that you have missunderstood me (did I spell this right) regarding single quotes around variables you send with your query. I ment it like this:

PHP Code:


		
			
 $query*=*"DELETE*FROM*gallery*WHERE*imageID =*'$ID'"; //only around variables

Clear now?

Another thing: why did you set up your query twice? Once is enough. Probably (this is defintely spelled wrong

) just a wrong copy+paste

June 25th, 2003, 06:14 AM

jben.net

Contributing User

Join Date: Apr 2003

Posts: 51

Time spent in forums: 19 m 41 sec
Reputation Power: 6

getting SQl errors

cheers,

noticed the duplicate $query var, have deleted. I'm getting errors though when I call the deleteimage.php?ID=whatever script...

PHP Code:


		
			
 An error has ocured: You have an error in your SQL syntax near ''gallery' WHERE 'imageID' = '1'' at line 1:1064

here's my script...

PHP Code:


		
			
<?php



/*



    deleteimage.php

    deletes a specific row from the gallery table

    

*/



// Define database connection details



include "./db.inc.php";



// Attempt to connect to MySQL server

$link = mysql_connect($dbHost ,$dbUser ,$dbPass ) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ());

        

$dbconnect = mysql_select_db($dbName);

        

// setup MySQL query

$query = "DELETE FROM 'gallery' WHERE 'imageID' = '$ID'";



// Execute Query

$result = mysql_query($query) or die( "An error has ocured: " .mysql_error (). ":" .mysql_errno ()); 



?>

Any ideas why ??

Also, what is the syntax to update a column in a specific row in mysql ??

Your spelling's been fine mate!

Thanks,

Jon

June 25th, 2003, 06:35 AM

jben.net

Contributing User

Join Date: Apr 2003

Posts: 51

Time spent in forums: 19 m 41 sec
Reputation Power: 6

oops

Sorry, should've read your post properly, removed the quotes from gallery and imageID and now it works fine, thanks.

PHP Code:


		
			
 $query*=*"DELETE*FROM*gallery*WHERE*imageID*=*'$ID'";

Now it's onto the editcaption script, any help / pointers would be great.

thanks again,

Jon

June 25th, 2003, 07:09 AM

_rainbow_

Registered User

Join Date: Jun 2003

Location: Lj, Slovenia

Posts: 27

Time spent in forums: 4 m 28 sec
Reputation Power: 0

If you figured out how to INSERT and DELETE you should have no problems with UPDATE. Just to be certian take a look at this site.

June 25th, 2003, 08:21 AM

jben.net

Contributing User

Join Date: Apr 2003

Posts: 51

Time spent in forums: 19 m 41 sec
Reputation Power: 6

SQl probs

Hey man,

Still having probs, could you take a quick look at this and let me know where I'm going wrong. The errors I'm getting are:

PHP Code:


		
			
 Warning: Unexpected character in input: ' in /Library/WebServer/Documents/vicky_devine/new/fla/php/editcaption.php on line 44



Warning: Unexpected character in input: ' in /Library/WebServer/Documents/vicky_devine/new/fla/php/editcaption.php on line 44

PHP Code:


		
			
<?php



/*



    editcaption.php

    updates a caption from a specific row in the gallery table