How to work with multiple tables and not get duplicate data? (MySQL/PDO)
I am trying to create a firearms site that will house about 1000 guns. This is not a lot of database entries, but I am trying to keep the database light as possible. I have created five tables, keeping normalization in mind, and I'm having problems putting data into all five tables in one query. My database is structured like so:
+-----------------+ +-----------------+ +-----------------+ +-----------------+
| make + | model | | image | | type |
+-----------------+ +-----------------+ +-----------------+ +-----------------+
| PK | make_id | | PK | model_id | | PK | model_id | | PK | type_id |
+-----------------+ +-----------------+ +-----------------+ +-----------------+
| | make_name | | | make_id | | | image_path | | | type_name |
+-----------------+ +-----------------+ +-----------------+ +-----------------+
| | type_id |
+-----------------+ +------------------+
| | caliber_id | | caliber |
+-----------------+ +------------------+
| | model_name | | PK | caliber_id |
+-----------------+ +------------------+
| | cost | | | caliber_name|
+-----------------+ +------------------+
| | description|
+-----------------+
This might be TOO normalized, but this is what I am working with ;)
Let me show the code:
form
<form action="post" method="addProduct.php" enctype="multipart/form-data">
make: <input type="text" name="make" />
model: <input type="text" name="model" />
type: <input type="text" name="type" />
caliber: <input type="text" name="caliber" />
cost: <input type="text" name="cost" />
desc.: <input type="text" name="description" />
Image: <input type="file" name="image" id="image" />
<input type="submit" name="submit" value="Add Item" />
</form>
addProduct.php
$make = $_POST['make'];
$model = $_POST['model'];
$type = $_POST['type'];
$caliber = $_POST['caliber'];
$cost = $_POST['cost'];
$description = $_POST['description'];
$image = basename($_FILES['image']['name']);
$uploadfile = 'pictures/temp/'.$image;
if(move_uploaded_file($_FILES['image']['tmp_name'],$uploadfile))
{
$makeSQL = "INSERT INTO make (make_id,make_name) VALUES ('',:make_name)";
$typeSQL = "INSERT INTO type (type_id,type_name) VALUES ('',:type_name)";
$modelSQL = "INSERT INTO model (model_id,make_id,type_id,caliber,model_name,cost,description,) VALUES ('',:make_id,:type_id,:caliber,:model_name,:cost,:description)";
$imageSQL = "INSERT INTO image (model_id,image_path) VALUES (:model_id,:image_path)";
try
{
/* db Connector */
$pdo = new PDO("mysql:host=localhost;dbname=gun",'root','');
/* insert make information */
$make = $pdo->prepare($makeSQL);
$make->bindParam(':make_name',$make);
$make->execute();
$make->closeCursor();
$makeLastId = $pdo->lastInsertId();
/* insert type information */
$type = $pdo->prepare($typeSQL);
$type->bindParam(':type_name',$type);
$type->execute();
$type->closeCursor();
$typeLastId = $pdo->lastInsertId();
/* insert model information */
$model = $pdo->prepare($modelSQL);
$model->bindParam(':make_id',$makeLastId);
$model->bindParam(':type_id',$typeLastId);
$model->bindParam(':caliber',$caliber);
$model->bindParam(':model_name',$model);
$model->bindParam(':cost',$cost);
$model->bindParam(':description',$description);
$model->execute();
$model->closeCursor();
$modelLastId = $pdo->lastInsertId();
/* insert image information */
$image = $pdo->prepare($imageSQL);
$image->bindParam(':model_id',$modelLastId);
$image->bindParam(':image_path',$image);
$image->execute();
$image->closeCursor();
print(ucwords($manu));
}
catch(PDOexception $e)
{
$error_message = $e->getMessage();
print("<p>Database Error: $error_message</p>");
exit();
}
}
else
{
print('Error : could not add item to database');
}
So when I add an item using the above code everything works fine, but when I add another item using the same manufacturer name it will duplicate it. I just want it to realize it already exists and not duplicate it.
I was thinking of putting some type of check to see if that data already exists and if it does then don't enter the data, but get the id and enter that in the other tables where required.
Another thing I thought of was to create a dropdown for the data that will most likely be duplicated and assign the value as the id. But, my simple mind can't figure out the best way to do it :( Hopefully all that makes sense, if not I will try to elaborate.
If you've got fields where there's only ever going to be a limit set of data (calibre is definitely one, and I suspect manfacturer will also work) you can pre-populate the tables in the database and turn it into a lookup field.
On your HTML form, instead of having a text input field, you can print out a select box instead. The value of the select is the ID in the lookup field - you don't need to worry about adding anything to the lookup fields in the database then.
When I've had to do this in the past, I've written a function to do the data insert; it checks to see if the value is in the table. If it is, it returns the index of the field; otherwise, it adds it as a new entry, and returns the ID for the new entry. It's not the most elegant solution, but it works well.
You need to work out what constitutes a unique (unduplicated) make, model, type, and caliber.
Then, you need to create unique indexes for those tables that enforce the uniqueness. See http://dev.mysql.com/doc/refman/5.0/en/create-index.html
For example you might use make_id, model_name, and caliber_id to uniquely identify a model. You'd need
CREATE UNIQUE INDEX UNIQUEMODEL ON MODEL(make_id, caliber_id, model_name)
to set up your unique index. Notice that a primary key index can be a unique index, but you can have other unique indexes as well.
You then can use INSERT ON DUPLICATE KEY UPDATE to populate your tables. See here: http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html
For all this to work correctly, you'll have to make sure appropriate type , caliber , and make rows exist before you try to populate each model row: you need the ids from those first three tables to populate the fourth.