我有两个数据库表,即photos
、albums
。我可以上传照片并同时将表单数据插入两个表中,这没关系。
我遇到的问题是,
例如,当我上传 5 张照片时,每个表中会创建 5 行——每张照片的每一行。
我想要的是,
应在photos
表中创建 5 行,但只有一行应进入albums
表。photos
表具有foreign key
到albums
表。
这是我下面的代码:
扩展UploadHandler
处理程序的index.php
具有以下代码:
<?php
$options = array(
'delete_type' => 'POST',
'db_host' => 'localhost',
'db_user' => 'username',
'db_pass' => 'password',
'db_name' => 'test',
'db_table' => 'photos'
);
error_reporting(E_ALL | E_STRICT);
require('UploadHandler.php');
class CustomUploadHandler extends UploadHandler {
protected function initialize() {
$this->db = new mysqli(
$this->options['db_host'],
$this->options['db_user'],
$this->options['db_pass'],
$this->options['db_name']
);
parent::initialize();
$this->db->close();
}
protected function handle_form_data($file, $index) {
$file->title = @$_REQUEST['title'][$index];
$file->description = @$_REQUEST['description'][$index];
}
protected function handle_file_upload($uploaded_file, $name, $size, $type, $error,
$index = null, $content_range = null) {
$file = parent::handle_file_upload(
$uploaded_file, $name, $size, $type, $error, $index, $content_range
);
if (empty($file->error)) {
$sql = 'INSERT INTO `'.$this->options['db_table']
.'` (`name`, `size`, `type`, `title`, `description`)'
.' VALUES (?,?, ?, ?, ?)';
$query = $this->db->prepare($sql);
$query->bind_param(
'sisss',
$file->name,
$file->size,
$file->type,
$file->title,
$file->description,
);
$query->execute();
$file->id = $this->db->insert_id;
//LABEL: PROBLEM BLOCK BEGINS
/*Here, I am attempting to insert only row in the albums table
for each batch of photos I upload. So even if I upload 5 photos
into the photos table, only one row should be created in the albums table*/
$sql2 = 'INSERT INTO `albums` (`album_title`, `album_description`)'
.' VALUES (?,?)';
$query2 = $this->db->prepare($sql2);
$query2->bind_param(
'ss',
$file->title,
$file->description,
);
$query2->execute();
$file->id = $this->db->insert_id;
//LABEL: PROBLEM BLOCK ENDS
}
return $file;
}
}
$upload_handler = new CustomUploadHandler($options);
?>
在上面的代码中,我将代码块注释为
//LABEL: PROBLEM BLOCK BEGINS
...
//LABEL: PROBLEM BLOCK ENDS.
上面的代码用于在表和albums
表中插入相同数量的行photos
。我需要有关PROBLEM BLOCK
的帮助,以便albums
照片表中上传的每批photos
仅在表中创建一行。
我自己想出了解决方案。我在album
表中添加了unique id
列,并将unique id
的值添加为上传表单上的hidden
字段,因此它变成了这样
相册表
CREATE TABLE album (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
album_title varchar(255) DEFAULT NULL,
album_description text,
special_album_id varchar(255) NOT NULL,
PRIMARY KEY (id),
UNIQUE (special_album_id)
)
在上传表单中,我添加了
<input type = "hidden" name = "special_album_id[]" value= "<?php echo $variable-data; ?>">
您需要创建自己的函数来获取变量$variable-data
对于每个新照片上传都会有所不同。
这样,CustomUploadHanler.php
将发送查询以在album
表中插入多行,但对于上传的每批照片,该表将只接受一行。也许有更好的解决方案,但这对我有用。
还要对 OP 中的代码进行以下更改。
更改:
protected function handle_form_data($file, $index) {
$file->title = @$_REQUEST['title'][$index];
$file->description = @$_REQUEST['description'][$index];
}
自
protected function handle_form_data($file, $index) {
$file->title = @$_REQUEST['title'][$index];
$file->description = @$_REQUEST['description'][$index];
$file->special_album_id = @$_REQUEST['special_album_id'][$index];
}
然后在问题块中,更改:
$sql2 = 'INSERT INTO `albums` (`album_title`, `album_description`)'
.' VALUES (?,?)';
$query2 = $this->db->prepare($sql2);
$query2->bind_param(
'ss',
$file->title,
$file->description,
);
自
$sql2 = 'INSERT INTO `albums` (`album_title`, `album_description`, `special_album_id`)'
.' VALUES (?,?,?)';
$query2 = $this->db->prepare($sql2);
$query2->bind_param(
'sss',
$file->title,
$file->description,
$file->special_album_id
);
就是这样;我们完成了。希望这对某人有所帮助