Page 1 of 1

Uploading image files into MySQL using PHP

Posted: Fri Apr 02, 2004 2:48 am
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:

Posted: Fri Apr 02, 2004 3:03 am
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

Posted: Fri Apr 02, 2004 3:50 am
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:

Posted: Fri Apr 02, 2004 4:20 am
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

Posted: Fri Apr 02, 2004 4:46 am
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 .

Posted: Fri Apr 02, 2004 5:00 am
by twigletmac

Code: Select all

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

Mac

Posted: Fri Apr 02, 2004 5:17 am
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

Posted: Fri Apr 02, 2004 5:22 am
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

Posted: Fri Apr 02, 2004 7:03 am
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?

Posted: Fri Apr 02, 2004 7:18 am
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

Posted: Fri Apr 02, 2004 9:27 am
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.

Posted: Fri Apr 02, 2004 9:31 am
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.

Posted: Fri Apr 02, 2004 9:39 am
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?????????????????