Linked Up Design

Below you are going to find a very simple way to display multiple pictures using PHP and a MYSQL Database.

Please pay attention to the fact that I set this up to go along with another table that has form information that is being uploaded so the picture table is a completely different table. I have also set this up so the client can upload this through a CMS system so I have added a delete function to this which is embedded in to the picture display.

Because this is also being set up into an active edit page, there is no connection to the database so you will have to set that up for this to work. Here it is:

First this is to set up a new table with 2 columns “product_id_fk” varchar 32 and the second “image_path” varchar 64.

<?php
$id = $_GET['id'];

if(!empty($_GET['path']) && !empty($_GET['del'])) {
$path = $_GET['path'];
$query = “DELETE FROM image_relations WHERE image_path = ‘$path’”;
mysql_query($query) or die(mysql_error());
}

if(isset($_FILES['image_upload'])) {
$target_path = “uploads/”; //This is the name of the file on the server where the pictures are being uploaded to so you will need to set up this folder on your server with the correct path.
$rand = rand(10000,30000); //This puts a random number along with the $basename so you can add pictures with the same name without any display problems.
$basename = basename($_FILES['image_upload']['name']);
$file = $rand.”-”.$basename;
$target_path = $target_path . $file;
if (move_uploaded_file($_FILES['image_upload']['tmp_name'], $target_path)) {
$query = “INSERT INTO image_relations (product_id_fk,image_path) VALUES (‘$id’,'$file’)”;
mysql_query($query) or die(mysql_error());
echo “The file ” . $basename . ” has been uploaded”;
} else {
echo “<strong>There was an error uploading the file. Please try again.</strong><br />”;
echo $_FILES['image_upload']['name'];
}
}
?>

<br /><br />
<table width=”550″ align=”center”>
<tr>
<td width=”550″ align=”left” valign=”top”><hr />
<h4>Add image:</h4><br />
<form enctype=”multipart/form-data” method=”POST”>
image:
<input name=”image_upload” type=”file” />

<br />

<input type=”submit” value=”Upload”/>

</form>
<h4>Additional images:</h4>
Click Image to delete.

<?php
$a = 0;
echo “<table width=’450px’><tr>”;
$xyquery_2 = “SELECT image_path FROM image_relations WHERE product_id_fk = ‘$id’”; //This might have to be changed based on the second table you set up for the pictures
$xyresult_2 = mysql_query($xyquery_2) or die(mysql_error());
while ($path = mysql_fetch_array($xyresult_2)){
if($a > 3) //3 is a random number based on the size of the pictures I have set up and the size of the table that I set for the clients site.
{
$a = 0;
echo “</tr><tr>”;
}
//Display each record.
echo “<td bgcolor=’#ffffff’ colspan=’1′><a href=’(this is the page you are pulling the id from).php?id=$id&path=”.$path[0].”&del=1′><img src=’uploads/”.$path[0].”‘ width=’100px’ height=’100px’ ><br /></a></td>”;

$a++;
}
echo ‘</tr></table>’;
mysql_close();
?>

<br style=”clear:both;” />

</td></tr></table>
Save the page and use a <?php include_once(“additional_image”); ?> in the area that you want this to display on the CMS and to display this on a page you will need to have a connection to the database and call the database you are working with and then use the dispaly each record section. There is one change you will need to make so the delete function is removed which is below:

//Display each record.
echo “<td bgcolor=’#ffffff’ colspan=’1′><img src=’uploads/”.$path[0].”‘ width=’100px’ height=’100px’ ><br /></td>”;

$a++;
}
echo ‘</tr></table>’;
mysql_close();
?>

<br style=”clear:both;” />

</td></tr></table>

I basically took out the function to delete the picture. If you are having any trouble with this just let me know.

A client was looking for a CMS system for the site that I build for him. Part of this was to give my client the ability to upload pictures along with a descriptions of the cars that they are selling. The coding that I found earlier for this would upload a picture but would not carry along a description with it as well. So I had a thought, why can’t I upload a file to a folder along with uploading the file name and a description to a database and then reflect that back to a site using PHP.

Here is what I came up with:

1. First thing you will need to do is to set up your database with the tables that will reflect the form. Next create a new page called form.html adn set up your form giving it the action “add.php”. Then you will need to set up a folder in the directory that this file will be save in either locally or on your server’s file. This code will upload the picture to the above folder.

<?php

//This is the directory where images will be saved
$target = “uploads/”;
$target = $target . basename( $_FILES['uploadedfile']['name']);

2. Next you will need the code to upload information to your database. In this example, I am using a MySQL database:

//This gets all the other information from the form
$name=$_POST['name'];
$model=$_POST['model'];
$year=$_POST['year'];
$odometer=$_POST['odometer'];
$price=$_POST['price'];
$pic=($_FILES['uploadedfile']['name']);
$description=$_POST['description'];

mysql_connect(“www.mydatabase.com, “database name”, “password”) or die(mysql_error()) ;
mysql_select_db(“database”) or die(mysql_error()) ;

//Writes the information to the database
mysql_query(“INSERT INTO `sale` VALUES (‘$name’, ‘$model’, ‘$year’, ‘$odometer’, ‘$price’, ‘$pic’, ‘$description’)”) ;

3. Now the above will file this information on your database. This code has will reflect an error if the file did not go to the file on your server. Now for the rest of the code to send the photo to the file:

//Writes the photo to the server
if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target))
{

//Tells you if its all ok
echo “The file “. basename( $_FILES['uploadedfile']['name']). ” has been uploaded, and your information has been added to the directory”;
}
else {

//Gives and error if its not
echo “Sorry, there was a problem uploading your file.”;
}
?>

4. Save this file as add.php

5. Now here is the code for the display of what you just uploaded:

<?php
mysql_connect(“www.mydatabase.com, “database name”, “password”) or die(mysql_error()) ;
$data = mysql_query(“SELECT * FROM your table”) or die(mysql_error());?>

<?php $info = mysql_fetch_array( $data ); ?>

<?php do { ?>
<table width=”500″ border=”1″ cellspacing=”0″ cellpadding=”0″ align=”center”>
<tr>
<td>Photo</td>
<td>Make</td>
<td>Model</td>
<td>Year</td>
<td>Odometer</td>
<td>Price</td>
<td>Description</td>
</tr>
<tr>
<td><img src=”<?php echo “http://www.yourwebsite.com/uploads/”.$info['photo'] .”"; ?> ” alt=”" name=”" width=”100″ height=”90″ border=”1″ /></td>
<td><?php echo $info['name']; ?></td>
<td><?php echo $info['model']; ?></td>
<td><?php echo $info['year']; ?></td>
<td><?php echo $info['odometer']; ?></td>
<td><?php echo $info['price']; ?></td>
<td><?php echo $info['description']; ?></td>

</tr>
</table>
<?php } while ($info = mysql_fetch_assoc($data)); ?>

6. Save this as display.php. What I have in this coding is a repeat command that will show everything in the database so you can show all at the same time.

That should do it. Play with it and see if there are areas that can be improved upon and let me know.

Recently I had a client that need to upload a 2000 row 13 column excel spreadsheet to a MySQL database. The client is a car broker who basically helps people find specific cars and he also buys luxury cars and sells them via his website.

Being that his distributor gives him access to his database, we thought it would be a good idea to feature this on his website so we decided to add a search feature to the site so if someone was looking for a specific make of car, the search would bring back his distributor’s inventory of that make and then break it down from there. Sound simple but being that I almost no training when it comes to SQL or MySQL, I had to figure out a way to upload this inventory list to a MySQL database. Of course I tried for days to find a way to do this via Google but came up short until I found a someone who lead me in the right direction. So here is what I figured out!

First thing is first, what was I working with:

1. Excel for Mac 2007 version.

2. phpMyAdmin 3.2.2.1

3. MySQL 5.4.3 beta version

4. Running off my local host which really does not matter too much.

So here is how I went about this:

1. First step was to establish in MySQL a database and set up the columns of the database. (If you are not up to that stage, feel free to contact me and I can walk you through this!!) Obviously the columns should match the columns in your spreadsheet. Please note, that if you, which you should have, added a column for “id” Primary Key and Auto Increment, you will need to add this to your first column of your spreadsheet and sequentially add the numbers to the column to go along with the number of items in the spreadsheet.
2. Then in your excel spreadsheet you will want to remove the top line or the column names so you are just showing the data that you want to upload in to the database.
3. Make sure that all the cells in your spreadsheet are all flushed left. You can not have any arbitrary spacing in this format.
4. In the column after the last data entry column you are going to want to add “|” in each of the cells to match up with the rows of data. This is going to be used in your SQL upload code as the break for each row.  Your spreadsheet ends at Column “R” then place the “|” in Column “S”.
5. Save As the file as a CSV. (If you want, you can open this file in a text editor program and you will see the layout of the rows like so (item 1, item 2, item 3, item 4, item 5 | ) This would continue on each row with the ” | ” at the end again to show a stop in the row.
6. Now go to your MySQL program whether it is with your hosting company or on a local host, usually this will be a phpMyAdmin format.
7. Go in to the new database and then select “import” tab but make sure you have selected the database that you are looking with.
8. You will notice that at the top of the page you can upload the file that you are looking to use which you should do now.
9. Once you press the import button will notice now that they format of page has changed a bit. FORMATS OF IMPORTED FILE has changed and added a new option, CSV using LOAD DATA which you are going to want to select.
10. In the Options you are going to want to check “Replace table data with File”. Then skip down to the next section.
11. There should be 4 short boxes and then 1 long one. In the first box, “Field terminated by: put in a coma “,” then delete what is in the next 2 boxes.
12. The 4th box should read the Lines Terminated by and you are going to put a “|” in this box. Basically what you are doing is telling SQL that you are loading information in to the fields of the existing database and the break of each item is a “,” and break of each line is a “|”.
13. Now delete what ever is in the long box and hit submit.

As long as you have matched up your columns with your database columns this will work. It is probably a bit unconventional but at the same it worked for what I was doing and it should work for you.