The Tables for Browsing and Searching

The Tables for Browsing and Searching

Introduction. Now we are actually going to start developing our template. The first capability we will develop relates to browsing and searching firefox departments and products. The first thing you need to do is start developing the directory structure for the site on your client and on the web server.

The following is a diagram that displays the desired structure.

So you need to create a firefox directory on your account to be the root for the project files. Then you need to create a subdirectory/subfolder with each of the names shown above. It is very important that the spelling matches exactly, including the case of the characters!

Creating the Tables. There are two main ways you can create the tables in MySQL on a typical PHP site.

use SQL code submitted within PHP

use the MySQL interface

Where we are hosting our reseller accounts, battcave.com, has the MySQL interface which you should work with. But, particularly when creating the tables you need to be very careful about your typing. So we will use SQL code within PHPs to create the tables. But rather than using this same approach to initialize the tables, I expect you to use the MySQL interface to do it. Fortunately, it is much easier to modify mistakes when entering data as opposed to initially creating the tables.

So I am about to present six different PHPs that you should copy into your editor, modify slightly for your account and then upload and execute.

You can verify the creation of the tables by using your control panel within your web and entering the MySQL interface. I will create a short webpage relating to this and we will go over this in class.

We know from a previous page that the overall structure of our department table should look like the following.

department
Key	Field	Data Type	Allow Nulls
	id_department	int	no
	department_name	varchar	yes
	department_description	varchar	yes
	department_image	varchar	yes

The first PHP should be called create_department.php. It should be uploaded to your firefox directory and executed just once.

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "yourpassword";
$db_name = "yourlastnameweb";
$table_name = "department";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_department MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(50),
department_description VARCHAR(100),
department_image VARCHAR(50))";

if (mysql_db_query($db_name, $query_string, $link))
{

print ("The query was successfully created ");

}
else
{

print ("The query could not be created ");

}

mysql_close($link);
?>
</body>
</html>

Now we want to create the products table which should have a structure like the following.

products
Key	Field	Data Type	Allow Nulls
	id_product	int	no
	product_name	varchar	yes
	product_description	varchar	yes
	product_image	varchar	yes
	weight	float	yes
	price	float	yes

This PHP should be called create_products.php. It should be uploaded to your firefox directory and executed just once.

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "your_password";
$db_name = "yourlastnameweb";
$table_name = "products";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_product MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(50),
product_description VARCHAR(150),
product_image VARCHAR(50),
weight FLOAT,
price FLOAT)";

if (mysql_db_query($db_name, $query_string, $link))
{

print ("The query was successfully created ");

}
else
{

print ("The query could not be created ");

}

mysql_close($link);
?>
</body>
</html>

Now we want to create the department_products table which should have a structure like the following.

department_products
Key	Field	Data Type	Allow Nulls
	id_department_product	int	no
	id_department	int	yes
	id_product	int	yes

This PHP should be called create_department_products.php. It should be uploaded to your firefox directory and executed just once.

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "your_password";
$db_name = "yourlastnameweb";
$table_name = "department_products";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_department_product MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_department MEDIUMINT UNSIGNED,
id_product MEDIUMINT UNSIGNED)";

if (mysql_db_query($db_name, $query_string, $link))
{

print ("The query was successfully created ");

}
else
{

print ("The query could not be created ");

}

mysql_close($link);
?>
</body>
</html>

Now we want to create the attribute_category table which should have a structure like the following.

attribute_category
Key	Field	Data Type	Allow Nulls
	id_attribute_category	int	no
	category_name	varchar	yes

This PHP should be called create_attribute_category.php. It should be uploaded to your firefox directory and executed just once.

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "your_password";
$db_name = "yourlastnameweb";
$table_name = "attribute_category";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_attribute_category MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50))";

if (mysql_db_query($db_name, $query_string, $link))
{

print ("The query was successfully created ");

}
else
{

print ("The query could not be created ");

}

mysql_close($link);
?>
</body>
</html>

Now we want to create the attribute table which should have a structure like the following.

attribute
Key	Field	Data Type	Allow Nulls
	id_attribute	int	no
	attribute_name	varchar	yes
	id_attribute_category	int	yes

This PHP should be called create_attribute.php. It should be uploaded to your firefox directory and executed just once.

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "your_password";
$db_name = "yourlastnameweb";
$table_name = "attribute";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_attribute MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
attribute_name VARCHAR(50),
id_attribute_category MEDIUMINT UNSIGNED)";

if (mysql_db_query($db_name, $query_string, $link))
{

print ("The query was successfully created ");

}
else
{

print ("The query could not be created ");

}

mysql_close($link);
?>
</body>
</html>

Now we want to create the product_attribute table which should have a structure like the following.

product_attribute
Key	Field	Data Type	Allow Nulls
	id_product_attribute	int	no
	id_attribute	int	yes
	id_product	int	yes

This PHP should be called create_product_attribute.php. It should be uploaded to your firefox directory and executed just once.

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "your_password";
$db_name = "yourlastnameweb";
$table_name = "product_attribute";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_product_attribute MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_attribute MEDIUMINT UNSIGNED,
id_product MEDIUMINT UNSIGNED)";

if (mysql_db_query($db_name, $query_string, $link))
{

print ("The query was successfully created ");

}
else
{

print ("The query could not be created ");

}

mysql_close($link);
?>
</body>
</html>

Now we want to create the product_attribute_category table which should have a structure like the following.

product_attribute_category
Key	Field	Data Type	Allow Nulls
	id_prod_attribute_category	int	no
	id_product	int	yes
	id_attribute_category	int	yes

This PHP should be called create_prod_attribute_category.php. It should be uploaded to your firefox directory and executed just once.

<html>
<head>
<title>Creating a Table in MySQL Using PHP</title>
</head>

<body>
<?php
// assign the values for database access
$host = "localhost";
$user = "yourlastnameweb";
$password = "your_password";
$db_name = "yourlastnameweb";
$table_name = "product_attribute_category";

// connecting to the database on battcave.com
$link = mysql_connect($host, $user, $password);
// constructing the query string
$query_string = "CREATE TABLE $table_name
(id_prod_attribute_category MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
id_product MEDIUMINT UNSIGNED,
id_attribute_category MEDIUMINT UNSIGNED)";

if (mysql_db_query($db_name, $query_string, $link))
{

print ("The query was successfully created ");

}
else
{

print ("The query could not be created ");

}

mysql_close($link);
?>
</body>
</html>

After you have determined that these tables were all created successfully, you should delete these create_some_table.php files from your firefox directory. You won't need them anymore.

In order to initialize these tables we could use either MySQL within PHP or use the MySQL Admin interface directly. So that you will learn more about how to use the MySQL Admin interface, we will work with this in the next page and then use it to initialize the tables.