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

When making  a form on a website there are several options of where you want that information to go, either a database or to your email. This would be done with a site that you have a form that gathers information from a client who is requesting more information. For some time now I have been trying to work out how to have both actions, send the data to a database and to my email. This way I have captured the clients information in a database which I can then use to contact them at a later date. Having to go to my email helps me know who is trying to reach me about their website.

Now I have it set up so that both can happen and here is the php code for it for the form:

<?php
} else {
?>
<form action=”test.php” method=”post”>
<table width=”400″ border=”0″ align=”center” cellspacing=”2″ cellpadding=”0″>
<tr>
<td width=”29%”>Your name:</td>
<td width=”71%”><input name=”name” type=”text” id=”name” size=”32″></td>
</tr>
<tr>
<td>Email address:</td>
<td><input name=”email” type=”text” id=”email” size=”32″></td>
</tr>
<tr>
<td>Comment:</td>
<td><textarea name=”comment” cols=”45″ rows=”6″ id=”comment”></textarea></td>
</tr>
<tr>
<td>&nbsp;</td>
<td align=”left” valign=”top”><input type=”submit” name=”Submit” value=”Send”></td>
</tr>
</table>
</form> ?>

Here is the code for the email action:

<?php
if ($_POST["email"]<>”) {
$ToEmail = ‘youremail@site.com’;
$EmailSubject = ‘Site contact form ‘;
$mailheader = “From: “.$_POST["email"].”\r\n”;
$mailheader .= “Reply-To: “.$_POST["email"].”\r\n”;
$mailheader .= “Content-type: text/html; charset=iso-8859-1\r\n”;
$MESSAGE_BODY = “Name: “.$_POST["name"].”<br>”;
$MESSAGE_BODY .= “Email: “.$_POST["email"].”<br>”;
$MESSAGE_BODY .= “Comment: “.nl2br($_POST["comment"]).”<br>”;
mail($ToEmail, $EmailSubject, $MESSAGE_BODY, $mailheader) or die (“Failure”);
?>}

Couple of points that you need to pay close attention to in the email PHP code is that you need to fill in the $ToEmail variable so that it has your email address that you are sending it to. Also, make sure that the other variable’s strings throughout the form match the tables id’s. For example: $mailheader = “From: “.$_POST["email"].”\r\n”; the area $_POST["email"] is the id for the form which you will have to match with the database row. With my site I have it like this: $mailheader = “From: “.$_POST["txtEmail"].”\r\n”;. The txtEmail represents my row in the table for my database. So make you go through and match this up.

Very, very important update! You will need to add this hidden field just below the submit but still in the <form></form> area “<input name=”email” type=”hidden” id=”admin@linkedupdesign.com” value=”email” />”. The email will not be sent unless you have this in there. This is the hidden field that tells the <?php?> to work!!  make sure that the value=”email” and the name=”email” stay the same because that is the global $_POST variable name.

I have this email PHP code set up just about the form but not part of the <form></form>. Reason being is the form action is the recordset to post in to the databse so you don’t want to cross wires per say. These will be two separate action in the form.

If you have any questions about this please feel free to get in touch with me.

I have been recently working on a PHP tutorial from Lynda.com and I ran in to some trouble with it. The class that I am doing is from 2007 and I have had to install some programs, Apache, PHP, MySQL and phpMyAdmin in order to be able to go along with these classes. All of this stuff is a bit new to me to work with considering the sites that I have designed.

The class walks you through the instillation of each of these programs but they are a bit out dated. The one in particular that I had the most trouble with was phpMyAdmin so I am giving you the steps to handle this the way that I did in order to make this work for you. I did Google these but it was more for experienced programmers and web designers.

When I installed phpMyAdmin I got a #2002 error that had mentioned that my socket was not set up correctly or something like that. I some how managed to get it to work. These instructions are for the installation of phpMyAdmin version 3.2.2.1 which is the latest and this is being done on a Mac OS 10.5.8:

1. Download and unzip phpMyAdmin from the site phpMyAdmin.net. Have it download to the desktop.
2. Once it is unzipped, change the name to phpmyadmin.
3. Open up the finder and place the folder as follows: your home file=>sites. It needs to be in sites because this is where the local server is.
4. Open up phpMyAdmin and in the first set of php docs you will see config.sample.inc.php, you will want to open that up in your text editor program, text wrangler, dreamweaver or what ever program you can use to edit php style text. (Do not use Word or any other word processing programs)
5. Now Save As that file to config.inc.php and make sure you save it in the phpMyAdmin file.
6. What you are going to be doing here is adding some code in to this file config.inc.php that will give you your login access. If you where to try to go in and open phpMyAdmin on the local host you will more then likely have gotten this #2002 error which is why you are here. I am going to make this as simple as possible to do you bear with me. Here is what you are going to see in the first half or so of the code:<?php
   /* vim: set expandtab sw=4 ts=4 sts=4: */
   /**
   * phpMyAdmin sample configuration, you can use it as base for
   * manual configuration. For easier setup you can use setup/
   *
   * All directives are explained in Documentation.html and on phpMyAdmin
   * wiki <http://wiki.phpmyadmin.net>.
   *
   * @version $Id: config.sample.inc.php 12304 2009-03-24 12:56:58Z nijel $
   * @package phpMyAdmin
   */

   /*
   * This is needed for cookie based authentication to encrypt password in
   * cookie
   */
   $cfg['blowfish_secret'] = ”; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

   /*
   * Servers configuration
   */
   $i = 0;

   /*
   * First server
   */
   $i++;
   /* Authentication type */
   $cfg['Servers'][$i]['auth_type'] = ‘cookie’;
   /* Server parameters */
   $cfg['Servers'][$i]['host'] = ‘localhost’;
   $cfg['Servers'][$i]['connect_type'] = ‘tcp’;
   $cfg['Servers'][$i]['compress'] = false;
   /* Select mysqli if your server has it */
   $cfg['Servers'][$i]['extension'] = ‘mysql’;

7. From here you are going to be adding in your user name and password $cfg commands so here is what you do: I have highlighted this one section for you: $cfg['Servers'][$i]['auth_type'] = ‘cookie’;
   /* Server parameters */ $cfg['Servers'][$i]['host'] = ‘localhost’;
   $cfg['Servers'][$i]['connect_type'] = ‘tcp’;
   $cfg['Servers'][$i]['compress'] = false;       -Right between that ugly green type you are going to add the following code, please note that you can change this: $cfg['Servers'][$i]['user'] = ‘root’; //unless you know that this is different, then to not change this!!!//
   $cfg['Servers'][$i]['password']  = ‘cbb74bc’; // type between ‘ ‘ and you can change your password//
   $cfg['Servers'][$i]['auth_type'] = ‘config’;
8. With that done it should look something like the following with the password changed; /* Authentication type */
   $cfg['Servers'][$i]['auth_type'] = ‘cookie’;
   /* Server parameters */
   $cfg['Servers'][$i]['host'] = ‘localhost’;
   $cfg['Servers'][$i]['user'] = ‘root’;
   $cfg['Servers'][$i]['password'] = ’123456′; // make sure you change your password//
   $cfg['Servers'][$i]['auth_type'] = ‘config’;
   $cfg['Servers'][$i]['connect_type'] = ‘tcp’;
   $cfg['Servers'][$i]['compress'] = false;
   /* Select mysqli if your server has it */
   $cfg['Servers'][$i]['extension'] = ‘mysql’;  -The bold section is what you just changed. Make sure you saved this. What is going to happen now is when you load phpMyAdmin, these configurations that you just did will now allow you to access this program via your web browser. It is more then likely that you will need to do 1 more thing here.

Now that you have done this, it is more then likely that you are going to get the #2002 Error again but this time the logging screen will not be there. Sorry, no screen shot on this one!! Here is what you are going to have to do now:

1. Go to Applications=>Utilities=>Terminal and open up terminal
2. You are now going to be configuring your computer to accept phpMyAdmin and this will be very easy and fast.
3. With the terminal open you should have your username showing with a ~ in front of it like ~username. What you are going to do is either type in or cut and paste in the following command:   sudo mkdir /var/mysql (then hit return) If you type this in please make sure that you pay attention to the spacing. You should have gotten a password prompt which you use your password that you have set up with the Mac.
4. Next you are going to want to type or paste in the following: sudo ls -s /tmp/mysql.sock /var/mysql/mysql.sock (hit return) What you have basically done here is set up a directory in mysql and then linked in a file “mysql.sock” which from what I understand is the socket which correlates to the error you got when trying to get in to phpMyAdmin on your local host.
5. Now go to you web browser and refresh the page.
   

If this is still not working there are several possibilities for this but I would suggest that you go back and redo my steps. Personally I did this about 5 times to make sure that it worked. There are other things to consider with this so I would suggest that if you went through my directions and it is still not working, let me know, these directions assume that you have done the instillation of the other programs I mentioned earlier.