how to insert image(like photo) in one particular table (mysql)
help me..
thanks
barrathi
9 65467 Atli 5,058
Expert 4TB
Hi.
MySQL can store binary data in BLOB fields.
So to store images you would have to set up a table somwhat like this: -
CREATE TABLE Image
-
(
-
ImgID SERIAL,
-
ImgTitle VARCHAR(255) NOT NULL Default 'Untitled',
-
ImgType VARCHAR(255) NOT NULL Default 'png',
-
ImgData MEDIUMBLOB NOT NULL,
-
Primary Key(ImgID)
-
)
-
You would have to provide a script that reads the image as binary data and inserts it into the ImgData field of the table.
With php this could be done like this: -
// Read the image bytes into the $data variable
-
$fh = fopen("MyImg.jpg", "r");
-
$data = addslashes(fread($fh, filesize("MyImg.jpg")));
-
fclose($fh);
-
-
// Create the query
-
$SQL = "
-
INSERT INTO Image(ImgTitle, ImgType, ImgData)
-
VALUES('My Image', 'jpg', '$data')";
-
-
// Execute the query
-
$RESULT = mysql_query($SQL) or die("Couldn't insert image");
-
To get the image from the database another script is needed. This script will have to output a file of the same type stored in our ImgType field that contains the binary data in our BLOB field.
Again, this can be accomplished with PHP like this: -
// Fetch the latest image.
-
$SQL = "SELECT ImgData, ImgType FROM Image ORDER BY ImgID DESC LIMIT 1";
-
$RESULT = @mysql_query($SQL) or die("Query failed");
-
-
// Read the results
-
if($ROW = mysql_fetch_assoc($RESULT)) {
-
// Set the content type and print the data.
-
header("Content-type: img/". $ROW['ImgType']);
-
echo $ROW['ImgData'];
-
}
-
else {
-
// No images were found, print error
-
echo "No images were found";
-
}
-
Good luck!
P.S.
I changed the thread title.
MODERATOR
Hi I am happy to see that there is some procedure insert an image into mysql table because i have been tring this since 4 months but i dint find any where but the problem here is the above given code is in php and iwant this in vb.net
someone plzz look in to this
but i dint find any where but the problem here is the above given code is in php and iwant this in vb.net
someone plzz look in to this
Try creating a thread in the VB forum with a link to this thread as an example.
Alti, I notice you're using three steps to read the image file: -
// Read the image bytes into the $data variable
-
$fh = fopen("MyImg.jpg", "r");
-
$data = addslashes(fread($fh, filesize("MyImg.jpg")));
-
fclose($fh);
Wouldn't it be faster just to do this: -
$data = addslashes(file_get_contents('MyImg.jpg'));
-
Hey all,
I am coding a website in in DreamWeaver php and using a MySQL database. The problem that I have is that I want to dynamically load an image for every record in the db.
At this stage I am considering using a blob and simply dragging the field into the page. I am not sure if this will work though.
Oh and are there any free tools to add a blob into a db on your webserver from your local pc?
Any help will be appreciated.
Mission316
Hi.
MySQL can store binary data in BLOB fields.
So to store images you would have to set up a table somwhat like this: -
CREATE TABLE Image
-
(
-
ImgID SERIAL,
-
ImgTitle VARCHAR(255) NOT NULL Default 'Untitled',
-
ImgType VARCHAR(255) NOT NULL Default 'png',
-
ImgData MEDIUMBLOB NOT NULL,
-
Primary Key(ImgID)
-
)
-
You would have to provide a script that reads the image as binary data and inserts it into the ImgData field of the table.
With php this could be done like this: -
// Read the image bytes into the $data variable
-
$fh = fopen("MyImg.jpg", "r");
-
$data = addslashes(fread($fh, filesize("MyImg.jpg")));
-
fclose($fh);
-
-
// Create the query
-
$SQL = "
-
INSERT INTO Image(ImgTitle, ImgType, ImgData)
-
VALUES('My Image', 'jpg', '$data')";
-
-
// Execute the query
-
$RESULT = mysql_query($SQL) or die("Couldn't insert image");
-
To get the image from the database another script is needed. This script will have to output a file of the same type stored in our ImgType field that contains the binary data in our BLOB field.
Again, this can be accomplished with PHP like this: -
// Fetch the latest image.
-
$SQL = "SELECT ImgData, ImgType FROM Image ORDER BY ImgID DESC LIMIT 1";
-
$RESULT = @mysql_query($SQL) or die("Query failed");
-
-
// Read the results
-
if($ROW = mysql_fetch_assoc($RESULT)) {
-
// Set the content type and print the data.
-
header("Content-type: img/". $ROW['ImgType']);
-
echo $ROW['ImgData'];
-
}
-
else {
-
// No images were found, print error
-
echo "No images were found";
-
}
-
Good luck!
P.S.
I changed the thread title.
MODERATOR
I am trying to incorporate an image file within a BLOB field of a 'latest news' table, so that it can be pulled dynamically with the rest of the news text. So far I have succeeded in displaying this once the image is already in the database (using phpmyadmin).
I have been less successful in uploading the image into the database from a php newsupdate page using a form.
The relevant code snippets are shown below: -
<form action="newsupdate.php" method="POST" enctype="multipart/form-data" name="frm_insertnews" id="frm_insertnews">
-
.
-
.
-
.
-
-
<input type="file" name="file" id="file" />
-
<input type="submit" name="submit" id="submit" value="Insert" />
-
-
</form>
-
And for the database: -
$file = fopen($_FILES['file'], "r");
-
$image = addslashes(fread($file, filesize($_FILES['file'])));
-
fclose($file);
-
-
-
$insertSQL = "INSERT INTO tbl_news (date_added, title, description, picture, author) VALUES (CURDATE(), '".$_POST['title']."', '".$_POST['text']."', '$image', '".$_POST['author']."')";
-
When the form is submitted, the other fields are updated, but the blob is only a few bytes....
Any help / suggestions would be greatly appreciated.
figured it out - needed to append ['tmp_name'] to $_FILES['file']...
Heya, erp.
Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)
I'm having difficulty retrieving the images. This is what I've used to retrieve multiple records, each with an image. -
display_users.php
-
========================================================
-
<?php
-
ini_set ("display_errors", "1");
-
error_reporting(E_ALL);
-
require ("connect.php");
-
-
// select our database
-
mysql_select_db("basil_bookmark") or die(mysql_error());
-
$result = mysql_query("SELECT * FROM user ORDER BY id DESC");
-
-
//Display Table
-
//header("Content-type: img/". $ROW['image_type']);
-
if (!$result)
-
{
-
die('<p>Error performing query: ' . mysql_error() . '</p>');
-
}
-
-
echo "<table border='1' width='80%'cellpadding='3' cellspacing='0'>";
-
echo "<tr>";
-
echo "<td align='center' bgcolor='#FF3399'><strong>Username</strong></td>";
-
echo "<td align='center' bgcolor='#FF3399'><strong>First Name</strong></td>";
-
echo "<td align='center' bgcolor='#FF3399'><strong>Last Name</strong></td>";
-
echo "<td align='center' bgcolor='#FF3399'><strong>Favicon</strong></td>";
-
-
echo "</tr>";
-
-
-
while ($row = mysql_fetch_array($result)) {
-
-
echo "<tr>";
-
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["username"] ."</strong></td>";
-
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["first_name"] ."</strong></td>";
-
echo "<td bgcolor='#FFccff' align='center'><strong>". $row["last_name"] ."</strong></td>";
-
-
-
//echo "<td bgcolor='#FFccff' align='center'>".$row["image_data"]. "</td>";
-
echo "<td bgcolor='#FFccff' align='center'><img src='{$row['image_data']}' /></td>";
-
echo "</tr>";
-
-
}
-
-
echo ("\n</table>\n");
-
print "There are " . mysql_num_rows($result) . " rows in the Bookmarks database\n";
-
-
?>
-
I've messed with this for days, trying to retrieve an image. The best I've got so far is jumbled data.
I'd appreciate your ideas.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: SOAP |
last post by:
how to save binary data to database by using CMP2.0?
I would like to save a save image to database through EJB. but I don't know
which data type shoud I use.
any examples?
thanks a lot
|
by: christopher_board |
last post by:
Hi all. I am trying to connect to a MySQL Database using PHP on a
local machine using locahost. I am using the following code
<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass =...
|
by: =?Utf-8?B?SHVzYW0=?= |
last post by:
Hi EveryBody:
I have probelm that I hung with for three weeks, My problem is I want to
read Binary data from database. I will told you what I did :
first I declear an array byte and I cast the...
|
by: =?Utf-8?B?SHVzYW0=?= |
last post by:
Hi EveryBody:
I have probelm that I hung with for three weeks, My problem is I want to
read Binary data from database. I will told you what I did :
first I declear an array byte and I cast the...
|
by: vinayaknkadam |
last post by:
plz send me code to store video in mysql using php..
|
by: maheshonmail |
last post by:
i want to store pictures in mysql database using php code/ so please send me the most efficient code for doing this.
|
by: twinkle2010 |
last post by:
Hi I am a new web programmer.I got an issue that reads the excel sheet data and store it to MYSQL database using php. I dont have any idea on that. can you help me?
|
by: Amit yadav2010 |
last post by:
I am using java and mysql database and i want to know how to store video in mysql?
If anyone know the solution of it. Please let me know it.
|
by: santhanalakshmi |
last post by:
Hi,
I wrote some coding, to import excel data in mysql database using PHP Script? But my script, its not all inserting the excel data in mysql database. I don't know, what mistake did i made?...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |