添加包含详细信息(名称，数量，多张图片)的产品，然后在另一页中检索它们 [英] Add a product with details (name,quantity,multiple images) and retrieve them in another page
本文介绍了添加包含详细信息(名称，数量，多张图片)的产品，然后在另一页中检索它们的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
假设我有一个包含属性ID，名称，数量，图像，描述的产品表.而且我还有另一个具有属性id，productId，imagepath的表格图像.
Suppose I have a product table with attributes id, name, quantity, image, description. and I have another table image with attributes id, productId, imagepath.
我有这样的表格
<form method="post" action="">
< input type="text" name="name" />
< input type="text" name="quantity" />
< input type="file" name="images[]" multiple/>
< input type="text" name="description" />
</form>
我刚刚开始学习PHP，但我不太熟练使用php和mysql，但我真的很想知道如何向我的数据库中添加一个包含详细信息(名称，数量，描述)和两个或更多图像的产品.在另一页上检索同一产品，其中包含详细信息以及属于该特定产品的两个或多个图像.
I just started learning PHP and I am not very proficient in using php and mysql but I really wish to know how can I add a product with details(name, quantity, description) and two or more images to my database and Later on in another page retrieve the same product with details and the two or more images that belongs to this particular product.
我真的不知道创建产品和图像表(图像表将获取productId并为每次上传存储图像路径)是否是解决问题的最佳方法. 因此，我希望找到一种简单或可靠的方法来进行此操作，例如，如果您可以举例说明，这将对我有很大的帮助. 预先感谢.
I don't really know if creating a product and an image table (the image table will get the productId and store imagepath for each upload) is the best way to solve it. So I wish to find an easy or reliable way to do it and If you can, for example, illustrate with an example it will be of great help to me. Thanks in advance.
推荐答案
是的，这是您应该这样做的方式:"products"表和"products_images"表.后者应具有外键集:"product_id"列应引用"products"表中的"id".并且两个表都必须将"id"列作为主键.
Yes, this is how you should do it: a "products" table and a "products_images" table. The later should have a foreign key set: "product_id" column should reference the "id" in "products" table. And both tables must have the "id" columns as primary keys.
希望您能理解代码:
- addProduct.php包含表单并添加产品. getProduct.php显示所选产品的详细信息.
- 成功添加产品后(在addProduct.php中)，将出现一个链接，以便可以在getProduct.php中显示其详细信息.
- 将在config.php文件中提供的路径下自动创建目录.我将目录路径设置为上载".上传后，此路径将添加到图像文件名的前面，并保存在"products_images"中相应的文件名"列中.
- 上传多个文件.
- 当您提交带有提供的产品详细信息的表单时，将验证用户输入的值，并且相应的错误消息将显示在表单上方.
- 除了addProduct.php和getProduct.php之外，还有两个辅助文件:config.php包含一些有关上载的常量； connection.php包含数据库连接实例及其所需的常量.
- 使用PDO/MySQLi和所谓的声明.
- addProduct.php contains the form and adds a product. getProduct.php displays the details of a selected product.
- A link will appear after successfully adding a product (in addProduct.php), in order to have a way to display its details in getProduct.php.
- A directory will be automatically created at the path provided in the config.php file. I set the directory path to "uploads". This path will be prepended to an image filename upon upload and saved in the corresponding column "filename" in "products_images".
- Multiple file uploads.
- When you submit the form with the provided product details, the user input values will be validated and corresponding error messages will appear over the form.
- Beside addProduct.php and getProduct.php, there are two auxiliary files: config.php holds some constants regarding upload; connection.php holds the database connection instance and the constants needed for it.
- The database access operations are performed using PDO/MySQLi and the so called prepared statements.
首先，只需按原样测试代码，以便您了解它的作用.当然，像我一样创建表之后(请参见下面的创建表语法).
Just test the code as it is, first, so that you see what it does. Of course, after you create the tables as I did (see the create table syntaxes below).
祝你好运.
CREATE TABLE `products` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`description` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `products_images` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(11) unsigned DEFAULT NULL,
`filename` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `product_id` (`product_id`),
CONSTRAINT `products_images_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
config.php
<?php
// Upload configs.
define('UPLOAD_DIR', 'uploads');
define('UPLOAD_MAX_FILE_SIZE', 10485760); // 10MB.
//@changed_2018-02-17_14.28
define('UPLOAD_ALLOWED_MIME_TYPES', 'image/jpeg,image/png,image/gif');
PDO解决方案
您已经拥有它...
PDO solution
You already have it...
<?php
// Db configs.
define('HOST', 'localhost');
define('PORT', 3306);
define('DATABASE', 'tests');
define('USERNAME', 'root');
define('PASSWORD', 'root');
define('CHARSET', 'utf8');
/*
* Enable internal report functions. This enables the exception handling,
* e.g. mysqli will not throw PHP warnings anymore, but mysqli exceptions
* (mysqli_sql_exception).
*
* MYSQLI_REPORT_ERROR: Report errors from mysqli function calls.
* MYSQLI_REPORT_STRICT: Throw a mysqli_sql_exception for errors instead of warnings.
*
* @link http://php.net/manual/en/class.mysqli-driver.php
* @link http://php.net/manual/en/mysqli-driver.report-mode.php
* @link http://php.net/manual/en/mysqli.constants.php
*/
$mysqliDriver = new mysqli_driver();
$mysqliDriver->report_mode = (MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
/*
* Create a new db connection.
*
* @see http://php.net/manual/en/mysqli.construct.php
*/
$connection = new mysqli(HOST, USERNAME, PASSWORD, DATABASE, PORT);
addProduct.php
<?php
include 'config.php';
include 'connection.php';
$productSaved = FALSE;
if (isset($_POST['submit'])) {
/*
* Read posted values.
*/
$productName = isset($_POST['name']) ? $_POST['name'] : '';
$productQuantity = isset($_POST['quantity']) ? $_POST['quantity'] : 0;
$productDescription = isset($_POST['description']) ? $_POST['description'] : '';
/*
* Validate posted values.
*/
if (empty($productName)) {
$errors[] = 'Please provide a product name.';
}
if ($productQuantity == 0) {
$errors[] = 'Please provide the quantity.';
}
if (empty($productDescription)) {
$errors[] = 'Please provide a description.';
}
/*
* Create "uploads" directory if it doesn't exist.
*/
if (!is_dir(UPLOAD_DIR)) {
mkdir(UPLOAD_DIR, 0777, true);
}
/*
* List of file names to be filled in by the upload script
* below and to be saved in the db table "products_images" afterwards.
*/
$filenamesToSave = [];
$allowedMimeTypes = explode(',', UPLOAD_ALLOWED_MIME_TYPES);
/*
* Upload files.
*/
if (!empty($_FILES)) {
if (isset($_FILES['file']['error'])) {
foreach ($_FILES['file']['error'] as $uploadedFileKey => $uploadedFileError) {
if ($uploadedFileError === UPLOAD_ERR_NO_FILE) {
$errors[] = 'You did not provide any files.';
} elseif ($uploadedFileError === UPLOAD_ERR_OK) {
$uploadedFileName = basename($_FILES['file']['name'][$uploadedFileKey]);
if ($_FILES['file']['size'][$uploadedFileKey] <= UPLOAD_MAX_FILE_SIZE) {
$uploadedFileType = $_FILES['file']['type'][$uploadedFileKey];
$uploadedFileTempName = $_FILES['file']['tmp_name'][$uploadedFileKey];
$uploadedFilePath = rtrim(UPLOAD_DIR, '/') . '/' . $uploadedFileName;
if (in_array($uploadedFileType, $allowedMimeTypes)) {
if (!move_uploaded_file($uploadedFileTempName, $uploadedFilePath)) {
$errors[] = 'The file "' . $uploadedFileName . '" could not be uploaded.';
} else {
$filenamesToSave[] = $uploadedFilePath;
}
} else {
$errors[] = 'The extension of the file "' . $uploadedFileName . '" is not valid. Allowed extensions: JPG, JPEG, PNG, or GIF.';
}
} else {
$errors[] = 'The size of the file "' . $uploadedFileName . '" must be of max. ' . (UPLOAD_MAX_FILE_SIZE / 1024) . ' KB';
}
}
}
}
}
/*
* Save product and images.
*/
if (!isset($errors)) {
/*
* The SQL statement to be prepared. Notice the so-called markers,
* e.g. the "?" signs. They will be replaced later with the
* corresponding values when using mysqli_stmt::bind_param.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$sql = 'INSERT INTO products (
name,
quantity,
description
) VALUES (
?, ?, ?
)';
/*
* Prepare the SQL statement for execution - ONLY ONCE.
*
* @link http://php.net/manual/en/mysqli.prepare.php
*/
$statement = $connection->prepare($sql);
/*
* Bind variables for the parameter markers (?) in the
* SQL statement that was passed to prepare(). The first
* argument of bind_param() is a string that contains one
* or more characters which specify the types for the
* corresponding bind variables.
*
* @link http://php.net/manual/en/mysqli-stmt.bind-param.php
*/
$statement->bind_param('sis', $productName, $productQuantity, $productDescription);
/*
* Execute the prepared SQL statement.
* When executed any parameter markers which exist will
* automatically be replaced with the appropriate data.
*
* @link http://php.net/manual/en/mysqli-stmt.execute.php
*/
$statement->execute();
// Read the id of the inserted product.
$lastInsertId = $connection->insert_id;
/*
* Close the prepared statement. It also deallocates the statement handle.
* If the statement has pending or unread results, it cancels them
* so that the next query can be executed.
*
* @link http://php.net/manual/en/mysqli-stmt.close.php
*/
$statement->close();
/*
* Save a record for each uploaded file.
*/
foreach ($filenamesToSave as $filename) {
$sql = 'INSERT INTO products_images (
product_id,
filename
) VALUES (
?, ?
)';
$statement = $connection->prepare($sql);
$statement->bind_param('is', $lastInsertId, $filename);
$statement->execute();
$statement->close();
}
/*
* Close the previously opened database connection.
*
* @link http://php.net/manual/en/mysqli.close.php
*/
$connection->close();
$productSaved = TRUE;
/*
* Reset the posted values, so that the default ones are now showed in the form.
* See the "value" attribute of each html input.
*/
$productName = $productQuantity = $productDescription = NULL;
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
<meta charset="UTF-8" />
<!-- The above 3 meta tags must come first in the head -->
<title>Save product details</title>
<script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>
<style type="text/css">
body {
padding: 30px;
}
.form-container {
margin-left: 80px;
}
.form-container .messages {
margin-bottom: 15px;
}
.form-container input[type="text"],
.form-container input[type="number"] {
display: block;
margin-bottom: 15px;
width: 150px;
}
.form-container input[type="file"] {
margin-bottom: 15px;
}
.form-container label {
display: inline-block;
float: left;
width: 100px;
}
.form-container button {
display: block;
padding: 5px 10px;
background-color: #8daf15;
color: #fff;
border: none;
}
.form-container .link-to-product-details {
margin-top: 20px;
display: inline-block;
}
</style>
</head>
<body>
<div class="form-container">
<h2>Add a product</h2>
<div class="messages">
<?php
if (isset($errors)) {
echo implode('<br/>', $errors);
} elseif ($productSaved) {
echo 'The product details were successfully saved.';
}
?>
</div>
<form action="addProduct.php" method="post" enctype="multipart/form-data">
<label for="name">Name</label>
<input type="text" id="name" name="name" value="<?php echo isset($productName) ? $productName : ''; ?>">
<label for="quantity">Quantity</label>
<input type="number" id="quantity" name="quantity" min="0" value="<?php echo isset($productQuantity) ? $productQuantity : '0'; ?>">
<label for="description">Description</label>
<input type="text" id="description" name="description" value="<?php echo isset($productDescription) ? $productDescription : ''; ?>">
<label for="file">Images</label>
<input type="file" id="file" name="file[]" multiple>
<button type="submit" id="submit" name="submit" class="button">
Submit
</button>
</form>
<?php
if ($productSaved) {
?>
<a href="getProduct.php?id=<?php echo $lastInsertId; ?>" class="link-to-product-details">
Click me to see the saved product details in <b>getProduct.php</b> (product id: <b><?php echo $lastInsertId; ?></b>)
</a>
<?php
}
?>
</div>
</body>
</html>
getProduct.php
<?php
include 'config.php';
include 'connection.php';
if (!isset($_GET['id']) || empty($_GET['id']) || !is_numeric($_GET['id'])) {
$errors[] = 'You must select a product in order to see its details!';
} else {
$productId = $_GET['id'];
/*
* Get the product details.
*/
$sql = 'SELECT *
FROM products
WHERE id = ?
LIMIT 1';
$statement = $connection->prepare($sql);
$statement->bind_param('i', $productId);
$statement->execute();
/*
* Get the result set from the prepared statement.
*
* NOTA BENE:
* Available only with mysqlnd ("MySQL Native Driver")! If this
* is not installed, then uncomment "extension=php_mysqli_mysqlnd.dll" in
* PHP config file (php.ini) and restart web server (I assume Apache) and
* mysql service. Or use the following functions instead:
* mysqli_stmt::store_result + mysqli_stmt::bind_result + mysqli_stmt::fetch.
*
* @link http://php.net/manual/en/mysqli-stmt.get-result.php
* @link https://stackoverflow.com/questions/8321096/call-to-undefined-method-mysqli-stmtget-result
*/
$result = $statement->get_result();
/*
* Fetch data (all at once) and save it into an array.
*
* @link http://php.net/manual/en/mysqli-result.fetch-all.php
*/
$products = $result->fetch_all(MYSQLI_ASSOC);
/*
* Free the memory associated with the result. You should
* always free your result when it is not needed anymore.
*
* @link http://php.net/manual/en/mysqli-result.free.php
*/
$result->close();
$statement->close();
if (!$products) {
$errors[] = 'No product found.';
} else {
$product = $products[0];
$productName = $product['name'];
$productQuantity = $product['quantity'];
$productDescription = $product['description'];
/*
* Get the images list for the provided product.
*/
$sql = 'SELECT *
FROM products_images
WHERE product_id = ?';
$statement = $connection->prepare($sql);
$statement->bind_param('i', $productId);
$statement->execute();
$result = $statement->get_result();
$images = $result->fetch_all(MYSQLI_ASSOC);
$result->close();
$statement->close();
$connection->close();
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
<meta charset="UTF-8" />
<!-- The above 3 meta tags must come first in the head -->
<title>Product details</title>
<script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript"></script>
<style type="text/css">
body {
padding: 30px;
}
.product-details tr td {
padding: 5px;
}
.product-details .label {
font-weight: 700;
}
.product-images {
margin-top: 30px;
}
.product-images tr td {
padding: 10px;
font-weight: 700;
background-color: #eee;
}
.product-images .label {
color: #fff;
font-weight: 700;
background-color: #8daf15;
}
.product-images img {
max-width: 400px;
display: inline-block;
float: left;
}
</style>
</head>
<body>
<div class="page-container">
<h2>Product details</h2>
<?php
if (isset($errors)) {
echo implode('<br/>', $errors);
exit();
}
?>
<table class="product-details">
<tr>
<td class="label">Name</td>
<td><?php echo $productName; ?></td>
</tr>
<tr>
<td class="label">Quantity</td>
<td><?php echo $productQuantity; ?></td>
</tr>
<tr>
<td class="label">Description</td>
<td><?php echo $productDescription; ?></td>
</tr>
</table>
<table class="product-images">
<tr>
<td class="label">Images</td>
</tr>
<?php
foreach ($images as $image) {
$imageId = $image['id'];
$imageFilename = $image['filename'];
?>
<tr>
<td>
<img src="<?php echo $imageFilename; ?>" alt="" />
</td>
</tr>
<?php
}
?>
</table>
</div>
</body>
</html>
这篇关于添加包含详细信息(名称，数量，多张图片)的产品，然后在另一页中检索它们的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
