Uploading image files into MySQL using PHP

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
idnoble
Forum Commoner
Posts: 28
Joined: Wed Mar 31, 2004 4:09 am

Uploading image files into MySQL using PHP

Post by idnoble »

Hello
I am trying to update a field in my Mysql database that has a blob datatype and I've written a PHP script and an HTML code to help me achieve this, but each time i fill and the HTML form and got passed to PHP for processing I always have this error message:

Error performing INSERT: You have an error in your SQL syntax near 'came Polly, Genre, Synopsis, Picture, filename, filesize, filetype) VALUES ('Alo' at line 1

The funny thing is that the syntax is messed up in MySQL, looking at the error message it says I have an error message near my syntax 'came Polly,
the picture I'm trying to upload is Along came Polly, and also the name of the HTML fields are also being passed to Mysql instead of the variables it holds,

The Mysql table schema, PHP script and HTML form are below. PLEASE HELP BEFORE I GO JUMP DOWN A BRIDGE :cry:

Table schema:
Itemcode int(10) PK auto_increment
Title varchar(70) PK
Genre varchar(20)
Synopsis varchar(100)
Picture tinyblob
filename varchar(30) NULL ALLOWED
filesize varchar(30) NULL ALLOWED
filetype varchar(30) NULL ALLOWED
Price float PK

Here's my PHP script

Code: Select all

<html>
<head>
<title>Update Movies GUI</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1"></head>
<body background="../Graphics/backgd.gif">
<h1>Database Movies Update Result</h1></body>
<?php
//Create short variable names. (REGISTER_GLOBALS = OFF)
$Title = $HTTP_POST_VARS['Title'];
$Genre = $HTTP_POST_VARS['Genre'];
$Synopsis = $HTTP_POST_VARS['Synopsis'];
$Picture = $HTTP_POST_VARS['Picture'];
$filename = $HTTP_POST_VARS['Picture_name'];
$filesize = $HTTP_POST_VARS['Picture_size'];
$filetype = $HTTP_POST_VARS['Picture_type'];
$Price = $HTTP_POST_VARS['Price'];
$Itemcode = $HTTP_POST_VARS['Itemcode'];


mysql_select_db("xplosive");
$query = "insert into movies ($Title, Genre, Synopsis, Picture, filename, filesize, filetype) " .
         "VALUES ('".$Title."', '".$Genre."', '".$Synopsis."', '".$Picture."', '".$Picture_name."', '".$Picture_size."', 
		          '".$Picture_type."', '".$Price."')";
$result = mysql_query($query);
if (!$result)
{
  echo ( "<p>Error performing INSERT: ".mysql_error(). "</p>" );
}
  
else
{  
    $Itemcode = mysql_insert_id();
	print '<h2>The itemcode for this movie is: <B>$Itemcode.</B></h2>';
}
?>
</body>
</html>
and here's my HTML code:

Code: Select all

&lt;HTML&gt;
&lt;HEAD&gt; 
&lt;TITLE&gt;STORE MOVIE&lt;/TITLE&gt;
&lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"&gt;&lt;/HEAD&gt;
&lt;BODY bgcolor="#6699CC" background="../Graphics/backgd.gif"&gt;
&lt;div align="center"&gt; 
  &lt;p align="left"&gt;&lt;img src="../Graphics/Admin/Xlogo.png" width="252" height="152"&gt;&lt;/p&gt;
  &lt;p&gt;&lt;strong&gt;&lt;font color="#FF0000" size="4"&gt;IMPORTANT: Only use this interface if you 
    want update the MOVIE table in the database&lt;/font&gt;&lt;/strong&gt;&lt;/p&gt;
  &lt;/div&gt;
&lt;FORM method="post" action="store.php" enctype="multipart/form-data"&gt;
&lt;P style= "font-size: 15pt; color:black;font-style: sylfaen"&gt;Title: &lt;BR&gt;
&lt;input type="text" name="Title" size="70"&gt; &lt;/p&gt;
&lt;P style= "font-size: 15pt; color:black;font-style: sylfaen"&gt;Genre: &lt;BR&gt;
&lt;input type="text" name="Genre" size="25"&gt; &lt;/p&gt;
  &lt;P style= "font-size: 15pt; color:black;font-style: sylfaen"&gt;Synopsis: &lt;BR&gt;
    &lt;textarea name = "Synopsis" rows = "6" cols = "n" size="250"&gt;
    &lt;/textarea&gt;
  &lt;/p&gt;
&lt;P style= "font-size: 15pt; color:black;font-style: sylfaen"&gt;Picture:&lt;BR&gt;
&lt;input type="file" name="Picture" size="45"&gt;
&lt;input type="hidden" name="max_file_size" value="1073741824"&gt;
&lt;/p&gt;
&lt;P style= "font-size: 15pt; color:black;font-style: sylfaen"&gt;Price GBP:&lt;BR&gt;
&lt;input type="text" name="Price" size="20"&gt;
&lt;/p&gt;
&lt;input type="submit" name="submit" value="Submit"&gt;
&lt;input type ="reset" value ="Clear"/&gt;
&lt;/FORM&gt;
&lt;/BODY&gt;
&lt;/HTML&gt;
Also is there any configuration in any of the servers i need to make to make this work although I've increased MAX_FILE_SIZE in PHP and MAX_PACKET_SIZE in MySQL so is there more to configure?

:cry: :cry: :cry: :cry: :cry:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Code: Select all

insert into movies ($Title,
should be:

Code: Select all

"insert into movies (Title,
it's trying to put the $Title variable in, not the field name Title.

Mac
idnoble
Forum Commoner
Posts: 28
Joined: Wed Mar 31, 2004 4:09 am

Post by idnoble »

Thanx for that, stupid mistake of mine, I've corrected it but the nasty thing gave me something else:

Error performing INSERT: No Database Selected

And i explicitly typed

Code: Select all

mysql_select_db("xplosive");
on line 20 of the php script, and I've checked my code again can't seen anymore errors, HELLLLLLLLLLLLLLLLLLLLLLP :cry:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Have you connected to the database? To check on any MySQL errors make sure you have some error handling on mysql_connect() and mysql_select_db(), e.g.

Code: Select all

mysql_select_db("xplosive") or die(mysql_error());
Mac
idnoble
Forum Commoner
Posts: 28
Joined: Wed Mar 31, 2004 4:09 am

Post by idnoble »

Yeah I connected to Mysql and my connection is ok cos I made some adjustments to th script and I got this error message from Mysql:

Error performing INSERT: You have an error in your SQL syntax near '. VALUES ('Along came Polly', 'Comedy', 'NOT AVAILABLE ', '', '', ''' at line 1

seems PHP is not passing the blob to mysql or mysql is just wacko.

here's the updated script and html form

Code: Select all

<html>
<head>
<title>Update Movies GUI</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1"></head>
<body background="../Graphics/backgd.gif">
<h1>Database Movies Update Result</h1></body>
<?php
//Create short variable names. (REGISTER_GLOBALS = OFF)
$Title = $HTTP_POST_VARS['Title'];
$Genre = $HTTP_POST_VARS['Genre'];
$Synopsis = $HTTP_POST_VARS['Synopsis'];
$Picture = $HTTP_POST_VARS['Picture'];
$filename = $HTTP_POST_VARS['Picture_name'];
$filesize = $HTTP_POST_VARS['Picture_size'];
$filetype = $HTTP_POST_VARS['Picture_type'];
$Price = $HTTP_POST_VARS['Price'];
$Itemcode = $HTTP_POST_VARS['Itemcode'];


mysql_select_db("xplosive");
$query = "insert into movies (Title, Genre, Synopsis, Picture, filename, filesize, filetype, Price)  .
          VALUES ('".$Title."', '".$Genre."', '".$Synopsis."', '".$Picture."', '".$Picture_name."', '".$Picture_size."', 
		          '".$Picture_type."', '".$Price."')";
$result = mysql_query($query);
if (!$result)
{
  echo ( "<p>Error performing INSERT: ".mysql_error(). "</p>" );
}
  
else
{  
    $Itemcode = mysql_insert_id();
	print '<h2>The itemcode for this movie is: <B>$Itemcode.</B></h2>';
}
?>
</body>
</html>
and my HTML

<HTML>
<HEAD>
<TITLE>STORE MOVIE</TITLE>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></HEAD>
<BODY bgcolor="#6699CC" background="../Graphics/backgd.gif">
<div align="center">
<p align="left"><img src="../Graphics/Admin/Xlogo.png" width="252" height="152"></p>
<p><strong><font color="#FF0000" size="4">IMPORTANT: Only use this interface if you
want update the MOVIE table in the database</font></strong></p>
</div>
<FORM method="post" action="store.php" enctype="multipart/form-data">
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Title: <BR>
<input type="text" name="Title" size="70"> </p>
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Genre: <BR>
<input type="text" name="Genre" size="25"> </p>
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Synopsis: <BR>
<textarea name = "Synopsis" rows = "6" cols = "n" size="250">
</textarea>
</p>
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Picture:<BR>
<input type="file" name="Picture" size="45">
<input type="hidden" name="max_file_size" value="1073741824">
</p>
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Price GBP:<BR>
<input type="text" name="Price" size="20">
</p>
<input type="submit" name="submit" value="Submit">
<input type ="reset" value ="Clear"/>
</FORM>
</BODY>
</HTML>

I'm now buggered .
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Code: Select all

Price)  .
          VALUES
That period (.) shouldn't be there.

Mac
idnoble
Forum Commoner
Posts: 28
Joined: Wed Mar 31, 2004 4:09 am

Post by idnoble »

I removed the full stop and it goes back to:

Error performing INSERT: No Database Selected

if i leave the full stop there it shows a Mysql error that goes like this:

Error performing INSERT: You have an error in your SQL syntax near '. VALUES ('Along came Polly', 'Comedy', 'NOT AVAILABLE ', '', '', ' at line 1
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Have you added the error handling to the mysql_select_db() call? The error you are getting is fairly explicit - something is going wrong with the database connection or selection.

Mac
idnoble
Forum Commoner
Posts: 28
Joined: Wed Mar 31, 2004 4:09 am

Post by idnoble »

Hey thanx,

I finally got it done, I forgot to add the connection script

Code: Select all

$session = mysql_connect ("localhost", "username", "password");
if (!$session)
{
echo 'Error: Could not connect to the database.' .mysql_error();
exit;
}
I think i should change my handle from idnoble to forgetfull jones or stupid idiot, if you have a better handle on those lines i don't mind you suggesting it.

Although the I can use the HTML form and PHP script to populate the table, but i tried viewing the table in MySQL CC and the Picture, filename, filesize and filetype are empty so the blob field ain't getting updated. Any ideas for my pea sized brain?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

lol, don't be so down on yourself, it's really easy to miss things when you've been looking at the same code for ages...

Do you get anything echoed from this line?:

Code: Select all

print '<h2>The itemcode for this movie is: <B>$Itemcode.</B></h2>';
Mac
idnoble
Forum Commoner
Posts: 28
Joined: Wed Mar 31, 2004 4:09 am

Post by idnoble »

Yeah I got the message I'm supposed to get:

The itemcode for this movie is: 5

This is automatically gets incremented as I used auto_increment in the Itemcode field, but the fields that holds information about the blob are just blank.
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Hmmm, well with:

print '<h2>The itemcode for this movie is: <B>$Itemcode.</B></h2>';

$itemcode wont be replaced with its real value, as single quotes print as is and doesnt print var values.
idnoble
Forum Commoner
Posts: 28
Joined: Wed Mar 31, 2004 4:09 am

Post by idnoble »

Hello,
The itemcode is ok no problem its just that the fields that are supossed to hold information about the picture which is tinyblob are not being uploaded into the table, everything else are being loaded.

the PHP script is:

Code: Select all

<html>
<head>
<title>Update Movies GUI</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1"></head>
<body background="../Graphics/backgd.gif">
<h1>Database Movies Update Result</h1></body>
<?php
//Create short variable names. (REGISTER_GLOBALS = OFF)
$Title = $HTTP_POST_VARS['Title'];
$Genre = $HTTP_POST_VARS['Genre'];
$Synopsis = $HTTP_POST_VARS['Synopsis'];
$Picture = $HTTP_POST_VARS['Picture'];
$filename = $HTTP_POST_VARS['Picture_name'];
$filesize = $HTTP_POST_VARS['Picture_size'];
$filetype = $HTTP_POST_VARS['Picture_type'];
$Price = $HTTP_POST_VARS['Price'];
$Itemcode = $HTTP_POST_VARS['Itemcode'];

$session = mysql_connect ("localhost", "root", "platinum");
if (!$session)
{
echo 'Error: Could not connect to the database.' .mysql_error();
exit;
}

mysql_select_db("xplosive");
$query = "insert into movies (Title, Genre, Synopsis, Picture, filename, filesize, filetype, Price)
          VALUES ('".$Title."', '".$Genre."', '".$Synopsis."', '".$Picture."', '".$Picture_name."', '".$Picture_size."', 
		          '".$Picture_type."', '".$Price."')";
				  
$result = mysql_query($query);
if (!$result)
{
  echo ( "<p>Error performing INSERT: ".mysql_error(). "</p>" );
}
  
else
{  
    $Itemcode = mysql_insert_id();
	print "<h2>The itemcode for this movie is: <B>$Itemcode</B></h2>";
}
?>
</body>
</html>
and the html code:

<HTML>
<HEAD>
<TITLE>STORE MOVIE</TITLE>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></HEAD>
<BODY bgcolor="#6699CC" background="../Graphics/backgd.gif">
<div align="center">
<p align="left"><img src="../Graphics/Admin/Xlogo.png" width="252" height="152"></p>
<p><strong><font color="#FF0000" size="4">IMPORTANT: Only use this interface if you
want update the MOVIE table in the database</font></strong></p>
</div>
<FORM method="post" action="storemovies.php" enctype="multipart/form-data">
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Title: <BR>
<input type="text" name="Title" size="70"> </p>
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Genre: <BR>
<input type="text" name="Genre" size="25"> </p>
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Synopsis: <BR>
<textarea name = "Synopsis" rows = "6" cols = "n" size="250">
</textarea>
</p>
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Picture:<BR>
<input type="file" name="Picture" size="45">
<input type="hidden" name="max_file_size" value="1073741824">
</p>
<P style= "font-size: 15pt; color:black;font-style: sylfaen">Price GBP:<BR>
<input type="text" name="Price" size="20">
</p>
<input type="submit" name="submit" value="Submit">
<input type ="reset" value ="Clear"/> <br>
Click here <a href="upload movies.html"> to add a trailer to this movie.
</FORM>
</BODY>
</HTML>

I don't know why?????????????????
Post Reply