Updating tables with blob datatypes in MySQL using PHP

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: No Database Selected

I don't understand this as I explicitly selected a database using:

mysql_select_db("xplosive");

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

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

<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:

<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>

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?
🙁 🙁 🙁 🙁

QRator

Hi there

You need to create a connection to the database first like so:

$dbConn = mysql_connect("MYSQL_HOST","MYSQL_USERNAME","MYSQL_PASSWORD");
mysql_select_db("xplosive",$dbConn);

That should work great 🙂

idnoble

Yeah I connected to Mysql and my connection is ok cos I made some adjustments to the 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


<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

I'm now buggered .

QRator

I see your problem in the following line:

$query = "insert into movies (Title, Genre, Synopsis, Picture, filename, filesize, filetype, Price)  . VALUES ('".$Title."', '".$Genre."', '".$Synopsis."', '".$Picture."', '".$Picture_name."', '".$Picture_size."', '".$Picture_type."', '".$Price."')";

just before VALUES you have a fullstop (.)
Take the fullstop out 🙂

idnoble

I tried that but the error came back as no database selected, even though I have a connection which is working, when the full stop is there the query get passed to Mysql and the error comes back as

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

I think the error occurs because of the blob. Any ideas?