我正在努力创建一个可容纳大约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|
+-----------------+
这可能是TOO规范化,但这是我正在使用的;)
让我展示代码:
形成
make:
model:
type:
caliber:
cost:
desc.:
Image:
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("
Database Error: $error_message
");exit();
}
}
else
{
print('Error : could not add item to database');
}
因此,当我使用上面的代码添加项目时,一切正常,但是当我使用相同的制造商名称添加另一个项目时,它将复制它.我只是想让它意识到它已经存在而不是重复它.
我正在考虑进行某种类型的检查以查看该数据是否已经存在,如果确实存在,则不输入数据,但获取id并在需要的其他表中输入该ID.
我想到的另一件事是为最有可能重复的数据创建一个下拉列表并将值指定为id.但是,我简单的头脑无法找到最好的方法:(希望所有这些都有意义,如果不是,我会尝试详细说明.