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.
I hope you will understand the code:
- 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).
Good luck.
Create table syntaxes
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 solution
You already have it...
MySQLi solution (object-oriented style)
connection.php
<?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);
} e
