如何使用多个表而不是获取重复数据？ （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:

我正在努力创建一个可容纳大约1000支枪的火器站点。这不是很多数据库条目,但我试图尽可能保持数据库的轻量级。我已经创建了五个表,记住了规范化,并且在将数据放入一个查询中的所有五个表时遇到了问题。我的数据库结构如下:

+-----------------+ +-----------------+ +-----------------+ +-----------------+ 
|       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 ;)

这可能是TOO规范化,但这是我正在使用的;)

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.

我正在考虑进行某种类型的检查以查看该数据是否已经存在,如果确实存在,则不输入数据,但获取id并在需要的其他表中输入该ID。

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.

我想到的另一件事是为最有可能重复的数据创建一个下拉列表并将值指定为id。但是,我简单的头脑无法找到最好的方法:(希望所有这些都有意义,如果不是,我会尝试详细说明。

2 个解决方案

#1

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.

如果你有的领域只有一组极限数据(口径肯定是一个,我怀疑manfacturer也会工作)你可以预先填充数据库中的表并将其转换为查找字段。

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.

在HTML表单上,您可以打印出一个选择框,而不是文本输入字段。 select的值是查找字段中的ID - 您无需担心向数据库中的查找字段添加任何内容。

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.

当我过去必须这样做的时候,我已经编写了一个函数来进行数据插入;它检查值是否在表中。如果是,则返回该字段的索引;否则,它将其添加为新条目,并返回新条目的ID。它不是最优雅的解决方案,但效果很好。

#2

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

然后,您需要为那些强制唯一性的表创建唯一索引。请参阅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

例如,您可以使用make_id,model_name和caliber_id来唯一标识模型。你需要

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

然后,您可以使用INSERT ON DUPLICATE KEY UPDATE来填充表。请参见此处: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.

为了使所有这些正常工作,在尝试填充每个模型行之前,您必须确保存在适当的类型,口径和make行:您需要前三个表中的id来填充第四个。