[PHP + MySQL] Making a basic product database - Part 1

Firstly you need a server which supports PHP and Mysql. If you haven't got one, you can set up your computer as a local web server. If you are on Windows, you can follow this guide to help you Set up your PC as a test environment using Apache, PHP and MySQL.

Create the database and tables

Open up your database management tool (PHPMyAdmin or command prompt or MySQL Query Browser) and create a new database, I will call mine product_db.

Then we want to create 3 tables. Product, companies and ownership.

The products table will contain 3 fields:

pid - A unique ID that is automatically generated for us.
name - The name of the product.
weight - The weight of the product.

This is the SQL for the table I am creating:

CREATE TABLE products (
pid INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 100 ) NOT NULL ,
weight FLOAT NOT NULL
)

Next up is the companies table which will contain 3 fields.

cid - A unique ID that is automatically generated for us.
name - The name of the company.
email - A contact email address for the company.

This is the SQL for the table I am creating:

CREATE TABLE companies (
cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR( 100 ) NOT NULL ,
email VARCHAR(100) NOT NULL
)

The 3rd table is the ownership table which will link up products to companies.

This will contain 2 fields, pid and cid which relate to the automatic ID of the 2 other tables.

This is the SQL for the table I am creating:

CREATE TABLE ownership (
cid INT NOT NULL,
pid INT NOT NULL
)

Now that we have the tables set up we can write the PHP to interact with the tables.

Connecting to the database using PHP

In your web folder, create a file and call it db_config.php.

In this file we will make the connection to the database.

<?php
$db_host = 'localhost';
$db_user = 'root';
$db_pass = 'password';
$db_name = 'product_db';
mysql_connect($db_host,$db_user,$db_pass) or die(mysql_error());
mysql_select_db($db_name) or die(mysql_error());
?>

Change the $db_xxxx variables to your database settings.

Save the file and load it up in your browser. If you get a blank white screen, everything is OK.

Possible Errors

Some possible errors you may get are...

Unknown database 'product1_db'

This will be because $db_name is wrong.

Warning: mysql_connect() [function.mysql-connect]: Unknown MySQL server host 'loc1alhost' (11001) in D:\xampp\htdocs\product\db_config.php on line 3
Unknown MySQL server host 'loc1alhost' (11001)

This is because the $db_host is wrong.

Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'ro1ot'@'localhost' (using password: YES) in D:\xampp\htdocs\product\db_config.php on line 3
Access denied for user 'ro1ot'@'localhost' (using password: YES)

This could be because $db_user is wrong or $db_pass is wrong.

Inserting new products in to the database

Create a new file and call it index.php and put it in the same location as the db_config.php file and paste the following code in it:

<?php require_once 'db_config.php'; ?>

Previewing the page should result in a blank white page. This means that everything is OK.

Now we will create a function which will used for all our modifying database interactions such as inserting data, updating data and deleting data.

function db_action($data = array()) {
	switch ($data['action']) {
		case 'insert':
			$keys = array(); $values = array();
			foreach ($data['data'] as $key => $value) {
				$keys[$key] = $key;
				$values[$key] = "'".$value."'";
			}
			$keys = implode(', ', $keys);
			$values = implode(', ', $values);
			mysql_query("INSERT INTO ".$data['table']." (".$keys.") VALUES (".$values.")");
			break;
		case 'update':
			mysql_query("UPDATE ".$data['table']." SET ".$values." WHERE ".$key." = ".$id);
			break;
		case 'delete':
			mysql_query("DELETE FROM ".$data['table']." WHERE ".$key." = ".$id);
			break;
	}
	return;
}

This is set up for inserting data only at the moment, both update and delete would fail at the moment, unless the data was passed on properly.

To insert data into the database we will pass all the information the db_function needs in an array like the one below.

$array = array(
  'action' => 'insert',
  'table' => 'products',
  'data' => array(
    'name' => 'Product Name',
    'weight' => '12.5',
  ),
);

By looking at the db_action function you can see that the action part of the array defines what we want to do in the database (insert, update or delete), then we have the table name (products, companies, ownership) and a data array which is in the format of the key (column name in the database) and the value we want to put in to the database.

We draw this data out the array in to a workable format using the following code:

$keys = array(); $values = array();
foreach ($data['data'] as $key => $value) {
  $keys[$key] = $key;
  $values[$key] = "'".$value."'";
}

This goes through the data array within our main array and spits out the $keys and $values that we will use for the database.

Once we have the $keys and $values arrays we need to organise these in a way that MySQL will know to insert correctly.

$keys = implode(', ', $keys);
$values = implode(', ', $values);

This sets the arrays in to strings with each field separated with a comma. Perfect for MySQL.

We then insert the data in to the database using the following code.

mysql_query("INSERT INTO ".$data['table']." (".$keys.") VALUES (".$values.")");

From the above array, the information sent to MySQL would be:

INSERT INTO products (name, weight) VALUES ('Product Name', '12.5')

Inserting new companies in to the database

By changing the array we can completely change the data that is going in to the database. Now lets modify the array to insert a new company.

$array = array(
  'action' => 'insert',
  'table' => 'companies',
  'data' => array(
    'name' => 'Something Enterprise',
    'email' => 'info@somethingenterprise.com',
  ),
);

So with the following code, by viewing the page we will insert data in to both tables.

<?php require_once 'db_config.php'; ?>
<?php
$action = $_POST['action'] ? $_POST['action'] : $_GET['action'];
$pid = $_POST['pid'] ? $_POST['pid'] : $_GET['pid'];
$cid = $_POST['cid'] ? $_POST['cid'] : $_GET['cid'];

function db_action($data = array()) {
    switch ($data['action']) {
        case 'insert':
            $keys = array(); $values = array();
            foreach ($data['data'] as $key => $value) {
                $keys[$key] = $key;
                $values[$key] = "'".$value."'";
            }
            $keys = implode(', ', $keys);
            $values = implode(', ', $values);
            mysql_query("INSERT INTO ".$data['table']." (".$keys.") VALUES (".$values.")");
            break;
        case 'update':
            mysql_query("UPDATE ".$data['table']." SET ".$values." WHERE ".$key." = ".$id);
            break;
        case 'delete':
            mysql_query("DELETE FROM ".$data['table']." WHERE ".$key." = ".$id);
            break;
    }
    return;
}

$product = array(
    'action' => 'insert',
    'table' => 'products',
    'data' => array(
        'name' => 'Product Name',
        'weight' => '12.5',
    ),
);

$company = array(
    'action' => 'insert',
    'table' => 'companies',
    'data' => array(
        'name' => 'Something Enterprise',
        'email' => 'info@somethingenterprise.com',
    ),
);

db_action($product);
db_action($company);
?>

Inserting new ownership in to the database

Now that we have both a company and a product we can try linking them together. Although we will automate this later, check your database, both the product and company should have an ID of 1.

$ownership = array(
	'action' => 'insert',
	'table' => 'ownership',
	'data' => array(
		'pid' => 1,
		'cid' => 1,
	),
);

Then by running

db_action($ownership);

We have the entry added to the ownership table.

Updating existing data

To update data we just need to modify our function a little to get the data in the right format and we can update with ease.

To do this we update the 'update' case in our switch statement to the following:

case 'update':
			$items = array();
			foreach ($data['data'] as $key => $value) {
				$items[] = $key .' = "'.$value.'"';
			}
			$values = implode(' , ', $items);
			$key = $data['key'];
			$id = $data['id'];
			mysql_query("UPDATE ".$data['table']." SET ".$values." WHERE ".$key." = ".$id);
			break;

We are using a similar foreach statement but have changed the 2 arrays into 1 array as the SQL format for UPDATE is different to INSERT.

For the update statement to work we need to pass on the values for the WHERE statement. After a minor change to our array we have the following:

$updateproduct = array(
	'action' => 'update',
	'table' => 'products',
	'data' => array(
		'name' => 'New Product Name',
	),
	'key' => 'pid',
	'id' => 1,
);

We have changed the action and added 2 new array keys, 1 called key which is the column name that we will use in our SQL statement and the id which is the cid or pid that needs to be updated.

Deleting data

The DELETE case in our function is even easier than our UPDATE as we don't need to much around with any data.

case 'delete':
			$key = $data['key'];
			$id = $data['id'];
			mysql_query("DELETE FROM ".$data['table']." WHERE ".$key." = ".$id);
			break;

Our DELETE array is almost identical to our UPDATE array.

$deleteproduct = array(
	'action' => 'delete',
	'table' => 'products',
	'key' => 'pid',
	'id' => 1,
);

All we need is the key and id as no data is needed to delete a row.

In part 2 we will create the HTML to do our functions.