😅MySQL BLOB using PHP FULL (ok)
https://phppot.com/php/mysql-blob-using-php/
Last updated
https://phppot.com/php/mysql-blob-using-php/
Last updated
C:\xampp71\htdocs\testpro\index.php
<?php
require_once __DIR__ . '/db.php';
if (count($_FILES) > 0) {
if (is_uploaded_file($_FILES['userImage']['tmp_name'])) {
$imgData = file_get_contents($_FILES['userImage']['tmp_name']);
$imgType = $_FILES['userImage']['type'];
$sql = 'INSERT INTO tbl_image(imageType ,imageData) VALUES(?, ?)';
$statement = $conn->prepare($sql);
$statement->bind_param('ss', $imgType, $imgData);
$current_id = $statement->execute() or die('<b>Error:</b> Problem on Image Insert<br/>' . mysqli_connect_error());
}
}
?>
<HTML>
<HEAD>
<TITLE>Upload Image to MySQL BLOB</TITLE>
<link href='css/form.css' rel='stylesheet' type='text/css' />
<link href='css/style.css' rel='stylesheet' type='text/css' />
<style>
.image-gallery {
text-align: center;
}
.image-gallery img {
padding: 3px;
box-shadow: 0 1px 3px rgba(0, 0, 0, 0.5);
border: 1px solid #FFFFFF;
border-radius: 4px;
margin: 20px;
}
</style>
</HEAD>
<BODY>
<form name='frmImage' enctype='multipart/form-data' action='' method='post'>
<div class='phppot-container tile-container'>
<label>Upload Image File:</label>
<div class='row'>
<input name='userImage' type='file' class='full-width' />
</div>
<div class='row'>
<input type='submit' value='Submit' />
</div>
</div>
<div class='image-gallery'>
<?php require_once __DIR__ . '/listImages.php';
?>
</div>
</form>
</BODY>
</HTML>
C:\xampp71\htdocs\testpro\listImages.php
<?php
$sql = 'SELECT imageId FROM tbl_image ORDER BY imageId DESC';
$stmt = $conn->prepare( $sql );
$stmt->execute();
$result = $stmt->get_result();
?>
<?php
if ( $result->num_rows > 0 ) {
while ( $row = $result->fetch_assoc() ) {
?>
<?php ?>
<img src = "imageView.php?image_id=<?php echo $row["imageId'];?>'>
<?php
}
}
?>
C:\xampp71\htdocs\testpro\db.php
<?php
$conn = mysqli_connect( 'localhost', 'root', '', 'db_add_blob' );
?>
C:\xampp71\htdocs\testpro\structure.sql
CREATE TABLE `tbl_image` (
`imageId` int(11) NOT NULL,
`imageType` varchar(255) NOT NULL,
`imageData` longblob NOT NULL
);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_image`
--
ALTER TABLE `tbl_image`
ADD PRIMARY KEY (`imageId`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_image`
--
ALTER TABLE `tbl_image`
MODIFY `imageId` int(11) NOT NULL AUTO_INCREMENT;
C:\xampp71\htdocs\testpro\css\form.css
input, textarea, select {
box-sizing: border-box;
width: 200px;
height: initial;
padding: 8px 5px;
border: 1px solid #9a9a9a;
border-radius: 4px;
}
input[type="checkbox"] {
width: auto;
vertical-align: text-bottom;
}
textarea {
width: 300px;
}
select {
display: initial;
height: 30px;
padding: 2px 5px;
}
button, input[type=submit], input[type=button] {
padding: 8px 20px;
font-size: 1em;
cursor: pointer;
border-radius: 25px;
color: #000000;
background-color: #ffc72c;
border-color: #ffd98e #ffbe3d #de9300;
}
input[type=submit]:hover {
background-color: #f7c027;
}
::placeholder {
color: #bdbfc4;
}
label {
display: block;
color: #565656;
}
C:\xampp71\htdocs\testpro\css\style.css
body {
font-family: -apple-system, BlinkMacSystemFont, Roboto, Segoe UI,
Helvetica Neue, Helvetica, Arial, sans-serif;
margin: 0 auto;
-webkit-font-smoothing: antialiased;
box-sizing: border-box;
color: #2f2f2f;
line-height: 1.5;
}
a {
text-decoration: none;
color: #2f20d1;
}
a:hover {
text-decoration: underline;
}
img {
height: auto;
max-width: 100%;
vertical-align: middle;
}
.phppot-container {
width: 740px;
margin: 20 auto;
padding: 0px 20px 0px 20px;
}
.row {
padding: 6px 0 6px 0;
}
.label {
color: #565656;
margin-bottom: 2px;
}
.card {
padding: 1rem 2rem;
border-radius: 4px;
box-shadow: 0 1px 3px #747681;
}
.message {
padding: 6px 20px;
font-size: 1em;
color: rgb(40, 40, 40);
box-sizing: border-box;
margin: 0px;
border-radius: 3px;
width: 100%;
overflow: auto;
}
.error {
background-color: #fb817c;
border: 1px solid #e46b66;
}
.success {
background-color: #48e0a4;
border: #40cc94 1px solid;
}
.validation-message {
color: #e20900;
}
.font-bold {
font-weight: bold;
}
.display-none {
display: none;
}
.inline-block {
display: inline-block;
}
.float-right {
float: right;
}
.float-left {
float: left;
}
.text-center {
text-align: center;
}
.text-left {
text-align: left;
}
.text-right {
text-align: right;
}
.full-width {
width: 100%;
}
.cursor-pointer {
cursor: pointer;
}
.mr-20 {
margin-right: 20px;
}
.m-20 {
margin: 20px;
}
.tile-container {
width: 240px;
border: #9a9a9a 1px solid;
border-radius: 3px;
padding: 10px 20px 10px 20px;
}
@media all and (max-width: 780px) {
.phppot-container {
width: auto;
}
}
@media all and (max-width: 400px) {
.phppot-container {
padding: 0px 20px;
}
.tile-container {
width: auto;
}
input, textarea, select {
width: 100%;
}
}
CREATE TABLE `products` (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE, product_image BLOB) ENGINE = InnoDB;C:\xampp71\htdocs\testpro\config.php
<?php
define('DB_NAME', 'db_add_blob');
define('DB_USER', 'root');
define('DB_PASSWORD', '');
define('DB_HOST', 'localhost');
$pdo = new PDO("mysql:host=" . DB_HOST . "; dbname=" . DB_NAME, DB_USER, DB_PASSWORD);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
C:\xampp71\htdocs\testpro\insert_products.php
<?php
require_once 'config.php';
$products = [];
$products[] = [
'product_name' => 'VIRTUAL SERVERS',
'price' => 5,
'product_image' => file_get_contents('https://i.imgur.com/VEIKbp0.png')
];
$products[] = [
'product_name' => 'MANAGED KUBERNETES',
'price' => 30,
'product_image' => file_get_contents('https://i.imgur.com/cCc9Gw9.png')
];
$products[] = [
'product_name' => 'MySQL DATABASES',
'price' => 15,
'product_image' => file_get_contents('https://i.imgur.com/UYcHkKD.png')
];
$sql = 'INSERT INTO products(product_name, price, product_image) VALUES (:product_name, :price, :product_image)';
foreach ($products as $product) {
$stmt = $pdo->prepare($sql);
$stmt->execute($product);
}
echo 'Records inserted successfully';
C:\xampp71\htdocs\testpro\display_products.php
<html>
<title>Using BLOB and MySQL</title>
<body>
<?php
require_once 'config.php';
$sql = "SELECT * FROM products";
$stmt = $pdo->prepare($sql);
$stmt->execute();
?>
<table border='1' align='center'>
<caption>Products Database</caption>
<tr>
<th>Product Id</th>
<th>Product Name</th>
<th>Price</th>
<th>Product Image</th>
</tr>
<?php
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo '<tr>';
echo '<td>' . $row['product_id'] . '</td>';
echo '<td>' . $row['product_name'] . '</td>';
echo '<td>' . $row['price'] . '</td>';
echo '<td>' .
'<img src = "data:image/png;base64,' . base64_encode($row['product_image']) . '" width = "50px" height = "50px"/>'
. '</td>';
echo '</tr>';
}
?>
</table>
</body>
</html>
