Custom Search
 


How to export Northwind Access database to MySQL


Inside This Article
1Export Northwind Access database to MySQL via ODBC
2Make northwind a true relational database in MySQL
3Restore northwind database from SQL dump
   
4Northwind database schema
5. Implement file directory based image management

Implement file directory based image management



Unlike Northwind in Access database where photos are stored in database tables, our MySQL northwind database will store employees photos and categories pictures in file directories. Only image file names (eg. seafood.gif) are saved in the Photo column. The actual image files are stored in file system.

Photos in Employees table The table below lists data of a few columns in employees table. It's a very simple exercises of PHP with MySQL but it demonstrates how easy it is to code when we only need to manage image file names in database and save the actual images in file directories.

This is how to do it:

  1. Create .gif files for category pictures and .jpg files for employee photos from Access Northwind database. I used Print Screen key (the key on your keyboard), Microsoft Paint, and PhotoShop's Save for Web.

  2. Save the image files to a directory on your computer or in a directory on your site.

    Click here to download all the images.

  3. Add image file names in Northwind database.

  4. Write a simple PHP loop to concatenate each employee data in a HTML table row and after the loop we display the table by simply embedding the concatenated $tr variable inside the HTML table. See code below:
<?
// Assume you have define your mysql database parameters

// Connect to mysql server
$link mysql_connect($host$user$pass) or die("Could not connect to database"); 

// Select the database
mysql_select_db($db_name$link) or die("Could not select database"); 

// Select employees
$sql "select EmployeeID, FirstName, LastName, Photo from employees";

// Fetch the result set
$result mysql_query($sql$link);

// Loop thru each employee in the result set and build html table row
while($row mysql_fetch_array($resultMYSQL_ASSOC))
{
    
$emp_id $row["EmployeeID"];
    
$fname $row["FirstName"];
    
$lname $row["LastName"];
    
$photo_filename $row["Photo"];
    
     
// Concatenate to create html table row
    
$tr .= "<tr><td align=middle valign=top>"$emp_id"</td>".
           
"<td valign=top>"$fname"</td>".
           
"<td valign=top>"$lname"</td>".
           
"<td valign=top>"$photo"</td>".
           
"<td valign=top><img src='/img/article/northwind/"$photo_filename"'></td></tr>";
}
?>

<br>
<table cellpadding=5 cellspacing=0 border=1>
<tr>
    <th>EmployeeID</th>
    <th>FirstName</th>
    <th>LastName</th>
    <th>Image file name</th>
    <th>Photo</th>
</tr>
<?=$tr?>
</table>

Here is what the code produced:

EmployeeID FirstName LastName Image file name Photo
1NancyDavolionancy.jpg
2AndrewFullerandrew.jpg
3JanetLeverlingjanet.jpg
4MargaretPeacockmargaret.jpg
5StevenBuchanansteven.jpg
6MichaelSuyamamichael.jpg
7RobertKingrobert.jpg
8LauraCallahanlaura.jpg
9AnneDodsworthanne.jpg

Photos in Categories table: By using a similar code, we can display pictures for product categories.

<?
// Display categoris pictures
$sql "select CategoryID, CategoryName, Picture from "DB_NAME_NORTHWIND".categories";
$result mysql_query($sql$link);

while(
$cat_row mysql_fetch_array($resultMYSQL_ASSOC))
{
    
$cat_id $cat_row["CategoryID"];
    
$cat_name $cat_row["CategoryName"];
    
$photo $cat_row["Picture"];
    
    
$cat_tr .= "<tr><td align=middle valign=top>"$cat_id"</td>".
            
"<td valign=top>"$cat_name"</td>".
            
"<td valign=top>"$photo"</td>".
            
"<td valign=top><img src='/img/article/northwind/"$photo"'></td>".
            
"</tr>";
}
?>

<p>
<table cellpadding=5 cellspacing=0 border=1>
<tr>
    <th>CategoryID</th>
    <th>CatgoryName</th>
    <th>Image file name</th>
    <th>Photo</th>
</tr>
<?=$cat_tr?>
</table>

CategoryID CatgoryName Image file name Photo
1Beveragesbeverages.gif
2Condimentscondiments.gif
3Confectionsconfections.gif
4Dairy Productsdiary.gif
5Grains/Cerealscereals.gif
6Meat/Poultrymeat.gif
7Produceproduce.gif
8Seafoodseafood.gif

Here ends this article series. If you think I have missed anything that is better to be included, please let me know.

Happy Coding!


Copyright© GeeksEngine.com




Inside This Article
1Export Northwind Access database to MySQL via ODBC
2Make northwind a true relational database in MySQL
3Restore northwind database from SQL dump
   
4Northwind database schema
5. Implement file directory based image management
Related Articles:

1.Steps to install PHP 5.x on Windows as a development machine
2.How to install Apache 2.x web server on Windows
3.How to connect two different versions of MySQL server on the same computer
4.How to configure MySQL server 5.1 on Windows
5.How to install MySQL server 5.1 on Windows with screenshots
6.Five ways to create include path for PHP
7.How to use Date and Time data as integer value in PHP and MySQL
8.How to use Apache Virtual Host to run multiple local websites on Windows
9.Install all PEAR packages by yourself
10.How to install PEAR on Windows
11.How to use PHP and Microsoft SMTP Virtual Server to send emails
12.How to install PHP server-side scripting language on Windows
13.How to install Apache 1.3 web server on Windows
14.How to install two different versions of MySQL server on the same PC
15.How to configure MySQL server 4.1 on Windows
16.How to install MySQL server 4.1 on Windows with screenshots
17.Export Northwind Access database to MySQL via ODBC


Other Recent Articles from the Database SQL category:

1.Get single records when duplicate records exist
2.Find duplicate values or non-duplicate values in a table
3.How to get Top 1 record from Oracle by using PL/SQL
4.How to get Top N rows from Oracle by using SQL
5.How the data types in Access Northwind are converted to Oracle
6.How to do cross table update in Oracle
7.Export Northwind Access database to MySQL via ODBC

Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy