Image upload database reference - image name row ID

[JamesCliff]

Hi all,

I have the following table structure:-

CREATE TABLE `sales` (
`SaleID` int(5) NOT NULL auto_increment,
`Title` varchar(255) NOT NULL default '',
`Image_Ref` varchar(255) NOT NULL default '',
`Description` text NOT NULL,
`Date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`SaleID`),
FULLTEXT KEY `Description` (`Description`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

I want to have a form in which i can fill in the fields for the database table above. And also select a picture to upload to a directory and automaticlly add a reference to the table (ive been told this is better than storing the image inside the table itself). I want it so when the image is uploaded to the directory the image reference is added to the database and the image name is changed to the specific row ID of the table.

How would i do this, can anyone help me because im stuck?

Thanks alot

Jim

 

[fireburner69]

Insert the data,
then check is user has selected an image.
Use mysql_insert_id() to get the id of the row you have inserted!
The make a function that will hanlde the image and save it with the id that you have from the row.
Finaly update the row above with the image name.

Hope this helps

 

[rockstardub]

Use this script, it's a redux form that talks to a MySQL database.
As long as you have a username and password the script will do the rest.
Also, make a folder called "uploads" with permissions 0777 in the same directory as this script.

Sample Database entry:
url - thing.jpg
name - Some Thing
date - 2005-05-05 12:00:00

Hope it helps...

<?


//Connect to MySQL
//If this information is correct, this smart script will handle the rest
DEFINE ('DB_USER', 'root');
DEFINE ('DB_PASSWORD', '');
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'database');
mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR 
die ($message .= 'Could not connect to MySQL: ' . mysql_error() );


//Select the databse
//If a it doesn't exist then create one
$dbs = mysql_select_db (DB_NAME);
if(!$dbs) {
     mysql_query("CREATE DATABASE `" . DB_NAME . "` ;");
     mysql_select_db (DB_NAME);
     $message .= '<p>The database "' . DB_NAME . '" has been created.';
}


//Select the table
//If it doesn't exist then create one
$table_name = 'uploads'; //Name of the table on the database that will store the information
$query = mysql_query("SELECT * FROM `$table_name`"); //Query the database for a duplicate visible name
if(!$query) { //If the database table doesn't exist, then create it.
     mysql_query("CREATE TABLE `$table_name` (`id` int(11) NOT NULL auto_increment, `url` text NOT NULL, 
     `name` text NOT NULL, `date` datetime NOT NULL default '0000-00-00 00:00:00', 
     PRIMARY KEY  (`id`)) TYPE=MyISAM AUTO_INCREMENT=15 ;");
     $message .= '<p>The table "uploads" has been created.';
}

$upload_dir = "uploads"; //Directory name (With path if there is one ie. /one/more/dir/uploads)(path is relative)

//Upload file processer
$dup_filename = FALSE;
$dup_vis_name = FALSE;
$upload_trouble = $_POST['new_vis_name']; //This is what the user entered as a visible file name

//Test for form submission
if($_POST['submit']) {

     //Test for file
     if($_FILES['file']['tmp_name']) { // If a file has been selected for uploading
          if(!is_uploaded_file($_FILES['file']['tmp_name'])) { //If the upload was successful     
               $message .= '<p>Failure. There was a problem uploading the file.</p>';
          }
     } else {
          $message .= '<p>Failure. No file was selected to upload.</p>';
     }
     
     //Test for visible name
     if($_POST['new_vis_name']) {
          $query = mysql_query("SELECT * FROM `uploads`"); //Query the database for a duplicate visible name
          while($result = mysql_fetch_array($query)) {
               if($_POST['new_vis_name'] == $result['name']) {
                   $dup_vis_name = TRUE; // There is a duplicate visible name
               }
          }
          if ($dup_vis_name) {
               $message .= '<p>Failure. The "Visible Name" is already in use. 
               Please write a new name and try again.</p>';
          }
     } else {
          $message .= '<p>Failure. You forgot to enter a "Visible Name."</p>';
     }
     
     //Test for filename
     if ($handle = opendir($upload_dir)) { //Open the upload directory
          while (false !== ($file = readdir($handle))) { //Correct way to list all files in a directory
               if($_FILES['file']['name'] == $file) { //Check for duplicate file names
                    $dup_filename = TRUE; // There is a duplicate filename
               }
          }
          if ($dup_filename) {
               $message .= '<p>Failure. The file name is already in use 
               (not the "Visible Name"), please rename the file and try again.</p>';
          }
     }
     
     if($_FILES['file']['tmp_name'] && $_POST['new_vis_name'] && !$dup_vis_name && !$dup_filename) {
          $test = move_uploaded_file($_FILES['file']['tmp_name'], $upload_dir . '/' . $_FILES['file']['name']);
          if(!$test) {echo 1;}
          mysql_query("INSERT INTO `$table_name` ( `id` , `url` , `name` , `date` ) 
          VALUES ('', '" . $_FILES['file']['name'] . "', '" . $_POST['new_vis_name'] . "', NOW() );");
          $message .= '<p>Success. The file has been successfully uploaded and added online.</p>';
          $upload_trouble = NULL;
     }

}

?>
<html>
     <body>
          <form name="main" method="post" enctype="multipart/form-data" action="">
               <h2>Upload a file here</h2>
               <? echo $message ?>
               <table cellpadding="5" border="1">
                    <tr>
                         <td><b>New File</td>
                         <td><input type="file" name="file" size="20"></td>
                    </tr>
                    <tr>
                         <td><b>Visible Name</td>
                         <td><input name="new_vis_name" value="" size="40"></td>
                    </tr>
                    <tr>
                         <td colspan="2" align="center"><input type="submit" name="submit" value="Upload File"></td>
                    </tr>
               </table>
               <br>
          </form>
     </body>
</html>

- Rockstar dUb

 

[JamesCliff]

Thanks alot m8,

Its really really appriciated

Ill let ya know how i get on with it.

Thanks again

Jim

 

[rockstardub]

No Prob, let me know if you have any trouble.
I know the database isn't constructed the same as your question,
so let me know how it works out.

- Rockstar dUb

 

[JamesCliff]

Right ok m8.

I got the script working great, thanks alot for your help.
I edited it slightly to accomodate for my needs.

Below is a sample database entry that i tested using the script after i had changed it slightly:-

SaleID Title Image_Ref Description Date
1 Citroen AX MVC-001F.JPG Citroen AX 2005-02-19
un-modded.
Blue / Green
64000 miles.

The image is uploaded successfully and added to the database as you can see above. The images are kept in a folder called photos.

How do i get it so i display all table entrys onto a page inside a table. So the title is there, the image is displayed, description is there and date at the bottom. I know howto display the contents of a table onto a php page but surely if i do that it will display everything but instead of displaying the actual picture it will display the picture file name as thats in the database and theres no linkage to the actual file if u get me.

Would i need to change something in the script or is it a bit of php coding i need to do in order to display the picture?

Thanks alot

Jim

 

[rockstardub]

Good good.
Its really not too hard, but first a foreword:
If all of your images will be in the same directory, then you don't need to insert a path into the database (just like it is now, no path, just the file name). The script checks for dup names as you may have seen, so theres no prob doing it this way.

So the next step would be to display all the info in your table, and when it comes to displaying the image, just run something like this...

<html>
     <body>
          <?
          //Get your DB Connection somehow
          $result = mysql_fetch_array(mysql_query("SELECT * FROM `uploads`")) //fetching an array lets you access column names
          if ($result) { 
               echo '<img src="photos/' . $result['url'] . '">
               <p>' . $result['title'] . ' | ' . $result['description'] . ' | ' . $result['date'] . '</p>';
          } else { 
               echo mysql_error;
          }
          ?>
     </body>
</html>

Can't test it right now, hope it works.

 

[JamesCliff]

Thanks for the prompt reply m8.

Yep all my pictures are been stored in one directory so your right in saying we dont need the path in the database. I used your above code but got a parse error on Line 23:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>

<body>
<div align="center">
<table width="724">
<!--DWLayoutTable-->
<tr>
<td width="100%" height="261" valign="top"><div align="center">
<p>sales</p>
<?php

//DB connect info
mysql_connect("127.0.0.1","root","") or die("Could not connect to SQL server!");
mysql_select_db("testdb") or die("Could not find database!");

$result = mysql_fetch_array(mysql_query("SELECT * FROM `sales`")) //fetching an array lets you access column names
if ($result) {
echo '<img src="photos/' . $result['Image_Ref'] . '">
<p>' . $result['title'] . ' | ' . $result['description'] . ' | ' . $result['date'] . '</p>';
} else {
echo mysql_error;
}
?>

</div></td>
</tr>
</table>
</div>
</body>
</html>

Can you identify the error m8? Line 23 is "if ($result) {"

Also the pictures that are been uploaded can variey in size in terms of pixels, this could lead to the distortion of my webpage when the images are displayed. I found this bit of code that forces the image to display at a set number of pixels:

<img src="files/images/' .$name. '.JPG" width="100" height="75" />

Now is there anyway of implementing the above code into the code you posted for me above? That way all the images that are displayed from the database will be displayed at that set number of pixels. I dont think the .JPG bit of the code will be needed because my images that are uploaded come in many different formats, so this bit can be left out. All that i need is for some sort of image resize code to be implemented into your above code, so that all images all displayed at a set number of pixels.

Thanks ever such alot m8, i appriciate your help a great deal. I would be stuck without you tbh.

Thanks again

Jim

 

[jpadie]

on the sytax error:
Add a semicolon at the end of the previous line (just after second closing bracket)

on the second question, just add the width and height parameters into the img tag within the echo statement. remember to escape the quotation marks surrounding the values or, if you are not using xhtml, use single quotes.

 

[JamesCliff]

Ok thanks m8,

Ive fixed the parse error, i then tried the script and it displayed just the picture on its own, successfull though, however it didnt show the other details for that database entry and it only displayed 1 entry when there are 2 in the table.

Also the image was huge.

So i added the height and width parameters to the code, this time the script displayed a picture box the size id specified (Width = 100 and Height = 75), and thats about it. There was no picture inside the box for some reason, whereas there was a picture before i used the pixel specify parameters. You can see the script in action as it is now after these changes at, ill leave it like it is now for the next 24 hours before i try changing it again, so you can see what the problem is:

http://www.briskfire.com/gb/index.php?page=sales

Its not displaying the image and its not displaying the other entrys. What are the 2 || lines for as well? Also how could i get each record displayed in its own table down the page, with each image displayed in the table.

Thanks alot - appricated

Jim

 

[rockstardub]

<?

//DB connect info
mysql_connect("127.0.0.1","root","") or die("Could not connect to SQL server!");
mysql_select_db("testdb") or die("Could not find database!");

$result = mysql_fetch_array(mysql_query("SELECT * FROM `sales`")); //fetching an array lets you access column names
if ($result) {
//You should keep your database columns in lowercase, for ease of programming, but I noticed you didn't
//So the rows below have been fixed and should work (this is a guess without seeing your database)
echo '<img src="photos/' . $result['Image_Ref'] . '" width="100" height="75"> ';
//Changing the image size in this way is not a smart idea, because if the images will always
//be different sizes and things, then you have 2 concerns to worry about.
//1.The image constraints will probably be wrong everytime, so youll get a streached photo or something
//2.The image will still be large, unless you cap the max size, it could take a full minute to load a thumbnail
//For a real image resizer, you should start another post, I haven't worked with that yet
echo '<p>' . $result['Title'] . ' | ' . $result['Description'] . ' | ' . $result['Date'] . '</p>';
//The || lines are there for basic formatting, only to seperate the info strings.
} else {
echo mysql_error();
}

?>

 

[jpadie]

to get all the records in the dataset you might want to use a "while" clause

 

[JamesCliff]

Thanks alot for the replys lads.

Im put rockstars code into action and it worked fine Thanks alot m8

I know it is a bad way of going about things when it comes to resizing the image, im going to do some more research into that when i have this script running the way i want, ill then change it to account for proper image resizing.

Also m8, i was woundering how i could get the code above to display all the table records onto the page in a list one after another, and have each record inside its own table which is split into cells accomodating each field eg. Title, then Image_Ref, then Description, then Date, all after each other in that order and inside its own table. Then after that first table with the first database record, the second database record in its own table in the same format as above. And so on....

I realise this is alittle more advanced, but if u could help me i would be greatful.

Thanks again

Jim

 

[jpadie]

rockstardub's code was mostly there. see the amends:

echo "<table>";
echo "<tr><td>Image</td><td>Title</td><td>Description</td><td>Date</td></tr>";
$table = "" ; //enter table name here
$sql = "select `Image_Ref`, `Title`, `Description`, `Date` from $table"; //You should keep your database columns in lowercase, for ease of programming, but I noticed you didn't
$rslt = mysql_query($sql);
while ($result = mysql_fetch_array($rslt))
{     
     //So the rows below have been fixed and should work (this is a guess without seeing your database)
     echo "<tr>";
     echo '<td><img src="photos/' . $result['Image_Ref'] . '" width="100" height="75"></td> ';
     //Changing the image size in this way is not a smart idea, because if the images will always
     //be different sizes and things, then you have 2 concerns to worry about.
     //1.The image constraints will probably be wrong everytime, so youll get a streached photo or something
     //2.The  image will still be large, unless you cap the max size, it could take a full minute to load a thumbnail
     //For a real image resizer, you should start another post, I haven't worked with that yet
     echo '<td>' . $result['Title'] . '</td><td>' . $result['Description'] . '</td><td>' . $result['Date'] . '</td></tr>';

}
echo "</table>";

 

[JamesCliff]

Thanks alot m8

I tried the code you gave me and got the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/local/apache2/

http://wwwroot/briskfire.com/gb/sales.php

on line 27

You can see the error for yourself at:

http://www.briskfire.com/gb/index.php?page=sales

This is the code in my php page, ive copied it accross so u can see wot line 27 is:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>

<body>
<div align="center">
  <table width="724">
    <!--DWLayoutTable-->
    <tr> 
      <td width="100%" height="261" valign="top"><div align="center">
          <p>sales</p>
         <?

//DB connect info
mysql_connect("127.0.0.1","jim11","") or die("Could not connect to SQL server!");
mysql_select_db("gbplantandmachinery") or die("Could not find database!");

echo "<table>";
echo "<tr><td>Image</td><td>Title</td><td>Description</td><td>Date</td></tr>";
$table = "" ; //enter table name here
$sql = "select `Image_Ref`, `Title`, `Description`, `Date` from $table"; //You should keep your database columns in lowercase, for ease of programming, but I noticed you didn't
$rslt = mysql_query($sql);
while ($result = mysql_fetch_array($rslt))
{     
     //So the rows below have been fixed and should work (this is a guess without seeing your database)
     echo "<tr>";
     echo '<td><img src="photos/' . $result['Image_Ref'] . '" width="100" height="75"></td> ';
     //Changing the image size in this way is not a smart idea, because if the images will always
     //be different sizes and things, then you have 2 concerns to worry about.
     //1.The image constraints will probably be wrong everytime, so youll get a streached photo or something
     //2.The  image will still be large, unless you cap the max size, it could take a full minute to load a thumbnail
     //For a real image resizer, you should start another post, I haven't worked with that yet
     echo '<td>' . $result['Title'] . '</td><td>' . $result['Description'] . '</td><td>' . $result['Date'] . '</td></tr>';

}
echo "</table>";

          ?>

          <p>&nbsp;</p></div></td>
    </tr>
  </table>
</div>
</body>
</html>

If you could amend the error i would be very appriciated.

Thanks m8

Jim

 

[jpadie]

you didn't fill in the table variable $table with the name of your table

 

[JamesCliff]

oh yeh,

thanks m8. I feel slightly embarrasses about that lol.

Right ive done that and the page is working correctly:

http://www.briskfire.com/gb/index.php?page=sales

However, im trying to customize the table slightly, for example add a border and set the width of the table and the width of each cell.

I adding the following:

echo "<table width="700" border="1">";

However it came up with a parse error

Also how would i set the cell widths?

Thanks alot

Jim

 

[JamesCliff]

Ive managed to set cell widths to what i want, ad well as add a new field to the database, this new field is price.

Ive changed the upload and view scripts to accomodate for the price field, and all is working well.

You can see the script in action and working with the test data i have used at:

http://www.briskfire.com/gb/index.php?page=sales

However i want to set a background colour to the headings of each column, these are the photo, make / model, description, date posted, price headings. So theres a bar of say red highlighting the background of all of them. Also how would i set font size and colour for the headings?Also i want to have 1 horizontal line going under each record that is displayed on the page. Therefore splitting them up abit more and generally making it easy for the reader to view.

Would i do this by creating the line on photoshop and saving it as something like recordsepline.jpg, then adding a function to the php code to display the line after each record? If so how would i do this? Or is theres an easier way i would be greatful if you could inform me.

Thanks alot

Jim

 

[rockstardub]

One of the best table formatting is like this...

<?

//DB connect info
mysql_connect("127.0.0.1","jim11","") or die("Could not connect to SQL server!");
mysql_select_db("gbplantandmachinery") or die("Could not find database!");

echo "<table>";
echo "<tr>
<td>Image</td>
<td>Title</td>
<td>Description</td>
<td>Date</td>
</tr>";
$table = "" ; //enter table name here
$sql = "SELECT * FROM `$table`";
$rslt = mysql_query($sql);

$cellcolor = "lightblue";
while ($result = mysql_fetch_array($rslt)) {
     echo "<tr>";
     echo '<td bgcolor="' . $cellcolor . '"><img src="photos/' . $result['Image_Ref'] . '" width="100" height="75"></td>
     <td bgcolor="' . $cellcolor . '>' . $result['Title'] . '</td>
     <td bgcolor="' . $cellcolor . '>' . $result['Description'] . '</td>
     <td bgcolor="' . $cellcolor . '>' . $result['Date'] . '</td>
     </tr>';
     if($cellcolor == 'lightblue') { $cellcolor = 'white'; } else { $cellcolor == 'lightblue'; }     
}
echo "</table>";

?>

 

[JamesCliff]

Thanks m8,

That way did work, however id like to expand on the original script.

How would i display a single horizontal line between each record? So that there is abit more seperation between the records making it more attractive and professional to the reader.

Thanks

Jim