How to manage photos in employees and categiries table in Northwind MySQL database

Inside This Article

1.	Export Northwind Access database to MySQL via ODBC
2.	Make northwind a true relational database in MySQL
3.	Restore northwind database from SQL dump

4.	Northwind 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:

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.
Save the image files to a directory on your computer or in a directory on your site.

Click here to download all the images.
Add image file names in Northwind database.
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($result, MYSQL_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
1	Nancy	Davolio	nancy.jpg
2	Andrew	Fuller	andrew.jpg
3	Janet	Leverling	janet.jpg
4	Margaret	Peacock	margaret.jpg
5	Steven	Buchanan	steven.jpg
6	Michael	Suyama	michael.jpg
7	Robert	King	robert.jpg
8	Laura	Callahan	laura.jpg
9	Anne	Dodsworth	anne.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($result, MYSQL_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
1	Beverages	beverages.gif
2	Condiments	condiments.gif
3	Confections	confections.gif
4	Dairy Products	diary.gif
5	Grains/Cereals	cereals.gif
6	Meat/Poultry	meat.gif
7	Produce	produce.gif
8	Seafood	seafood.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

1.	Export Northwind Access database to MySQL via ODBC
2.	Make northwind a true relational database in MySQL
3.	Restore northwind database from SQL dump

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

	Custom Search