Linked Up Design

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.

I have had a few wild things that I have had to learn for a new site that I am working on so I will be writing a few new articles very soon.

First is going to be on uploading Excel Spreadsheets to MySQL directly, not using PHP or ASP code to get this done.

Other is going to be on making a search function for a website using MySQL and PHP. This one will be submitted to Dreamweaver directly because there is a huge mistake on their instructions on how to do this. I found 3 websites that pretty much gave directions on doing this exactly the same and all 3 forgot to include the most fundamental actions in the form that make the search work!

I will put these up right before the new year as I have a few websites that I am working on right now.

I spent pretty much the whole evening looking up how to get a screen shot of something on my MacBook today and I must admit that I feel a bit silly not know this but here it is:

Hold down shift, command and the number 3.

Once you do this, a screen shot will be saved on your desktop. It will be a shot of your entire screen so if you are looking for something specific you can take it in to any image program and then crop it and save it in what ever format you need.

If you are looking for screen shots of a website there are a ton of programs that you can download to use for this but if you want to avoid having to pay for something that take a few minutes to do for free then follow my instructions above.

I just posted an ad from Lynda.com on my site. Reason for this is that of all of the on-line training sites that I have looked over or used this is by far the best one. There literally nothing that you can not find a class for and I assure you that you will learn a ton from the class that you do.

I found code that you can use to be able to upload pictures to a MySQL server with PHP. This is useful code to use if you are making a Contact Management System or a back end system for yourself or your client so that they can upload photos to a database which will then be echoed back to your server. I put this together using phpMyAdmin, MySQL and Dreamweaver CS4.

First thing you need to do is to go in to your phpMyAdmin program. Click the tab SQL and then cut and paste in this text to create the proper database for the php code below:

CREATE TABLE IF NOT EXISTS `files` (
`fid` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘unique id’,
`name` varchar(30) NOT NULL COMMENT ‘file name’,
`type` varchar(30) NOT NULL COMMENT ‘MIME type’,
`size` int(11) NOT NULL COMMENT ‘file size’,
`content` mediumblob NOT NULL COMMENT ‘actual file’,
`description` varchar(100) NOT NULL,
PRIMARY KEY (`fid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT=’Uploaded files’ AUTO_INCREMENT=7 ;

Open an html page and save it as “form.html”. Then place in this code between the body tags:

<form action=”upload.php” method=”post” enctype=”multipart/form-data” name=”uploadform”>
<input type=”hidden” name=”MAX_FILE_SIZE” value=”350000″>
<input name=”picture” type=”file” id=”picture” size=”50″>
<input name=”upload” type=”submit” id=”upload” value=”Upload Picture!”>
</form>

Now open a new php page and save it as upload.php making sure you are saving it in the same folder as the form.html.

<?php require_once(‘your connection‘); ?>

<?php

// if something was posted, start the process…
if(isset($_POST['upload']))
{

// define the posted file into variables
$name = $_FILES['picture']['name'];
$tmp_name = $_FILES['picture']['tmp_name'];
$type = $_FILES['picture']['type'];
$size = $_FILES['picture']['size'];

// get the width & height of the file (we don’t need the other stuff)
list($width, $height, $typeb, $attr) = getimagesize($tmp_name);

// if width is over 600 px or height is over 500 px, kill it
if($width>600 || $height>500)
{
echo $name . “‘s dimensions exceed the 600×500 pixel limit.”;
echo  “<a href=\”form.php\”>Click here to try again.</a>”;
die();
}

// if the mime type is anything other than what we specify below, kill it
if(!(
$type==’image/jpeg’ ||
$type==’image/png’ ||
$type==’image/gif’
)) {
echo $type .  ” is not an acceptable format.”;
echo  “<a href=\”form.php\”>Click here to try again.</a>”  ;
die();
}

// if the file size is larger than 350 KB, kill it
if($size>’350000′) {
echo $name . ” is over 350KB. Please make it smaller.”;
echo “<a href=\”form.php\”>Click here to try again. </a>”  ;

die();
}
// if your server has magic quotes turned off, add slashes manually
if(!get_magic_quotes_gpc()){
$name = addslashes($name);
}

// open up the file and extract the data/content from it
$extract = fopen($tmp_name, ‘r’);
$content = fread($extract, $size);
$content = addslashes($content);
fclose($extract);  ?>

<?php  // connect to the database
include(‘your connection‘); ?>
<?php
// the query that will add this to the database
$addfile = “INSERT INTO files (name, size, type, content, description ) “.
“VALUES (‘$name’, ‘$size’, ‘$type’, ‘$content’, ‘$description’)”;

mysql_query($addfile) or die(mysql_error());

// get the last inserted ID if we’re going to display this image next
$inserted_fid = mysql_insert_id();

mysql_close();
// display the image
?>
<div align=”center”>
<strong><? echo $name; ?><br>
</strong><img name=”<? echo $name; ?>” src=”getpicture.php?fid=<? echo $inserted_fid; ?>” alt=”Unable to view image #<? echo $inserted_fid; ?>”>
<br>
<a href=”upload.php”>upload more images</a>
</div>
<p>
<?
// we still have to close the original IF statement. If there was nothing posted, kill the page.
}else{die(“No uploaded file present”);
}
?>

Now open a new php file and save it as getpicture.php and drop in this code:

<?php

if(isset($_GET['fid']))
{
// connect to the database

include "your connection";

// query the server for the picture
$fid = $_GET['fid'];
$query = “SELECT * FROM files WHERE fid = ’$fid’”;
$result = mysql_query($query) or die(mysql_error());

// define results into variables
$name=mysql_result($result,0,”name”);
$size=mysql_result($result,0,”size”);
$type=mysql_result($result,0,”type”);
$content=mysql_result($result,0,”content”);

// give our picture the proper headers…otherwise our page will be confused
header(“Content-Disposition: attachment; filename=$name”);
header(“Content-length: $size”);
header(“Content-type: $type”);
echo $content;

mysql_close();
}else{
die(“No file ID given…”);
}
?>

Now that should do it for you.  From there you can set this up to work in several different formats but now you have the ability to upload photos and then have them show. Any questions please let me know.