update mysql blob with another blob using php

[sime]

Hi,

I have a blob field in a mysql database table. I want to copy a blob
from one record to another. I am having trouble transferring the data
via a php variable. Maybe I need to addslashes or convert to Hex or
something. I've tried a few things but can't quite get it. Here is
simplified code.

mysql_select_db($dbname, $connection);

$query = "SELECT blobthing
FROM mytable
WHERE id = 1;";

$results = mysql_query ($query, $connection);
$row = (mysql_fetch_assoc($result));
$varblobthing = $row['blobthing']; //put the blob in a variable

$query = "UPDATE mytable
SET blobthing = ".$varblobthing.",
WHERE id = 2;";

mysql_query ($query, $connection);
I would love any advice I can get. Thanks in advance.
Simon

[Oli Filth]

sime said the following on 10/09/2005 18:36:

Hi,

I have a blob field in a mysql database table. I want to copy a blob
from one record to another. I am having trouble transferring the data
via a php variable. Maybe I need to addslashes or convert to Hex or
something. I've tried a few things but can't quite get it. Here is
simplified code.

mysql_select_db($dbname, $connection);

$query = "SELECT blobthing
FROM mytable
WHERE id = 1;";

$results = mysql_query ($query, $connection);
$row = (mysql_fetch_assoc($result));
$varblobthing = $row['blobthing']; //put the blob in a variable

$query = "UPDATE mytable
SET blobthing = ".$varblobthing.",
WHERE id = 2;";

^
^
You forgot quotes
--
Oli

[Pertti Kosunen]

sime wrote:

mysql_select_db($dbname, $connection);
mysql_select_db($dbname, $connection)
or die ( 'Unable to select database.' );
$results = mysql_query ($query, $connection);
$results = mysql_query ($query, $connection)
or die ( 'Unable to execute query.' );
$row = (mysql_fetch_assoc($result));
$varblobthing = $row['blobthing']; //put the blob in a variable

$varblobthing = $row['blobthing']; //put the blob in a variable
mysql_free_result($result);
....

[Jerry Stuckle]

sime wrote:

Hi,

I have a blob field in a mysql database table. I want to copy a blob
from one record to another. I am having trouble transferring the data
via a php variable. Maybe I need to addslashes or convert to Hex or
something. I've tried a few things but can't quite get it. Here is
simplified code.

mysql_select_db($dbname, $connection);

$query = "SELECT blobthing
FROM mytable
WHERE id = 1;";

$results = mysql_query ($query, $connection);
$row = (mysql_fetch_assoc($result));
$varblobthing = $row['blobthing']; //put the blob in a variable

$query = "UPDATE mytable
SET blobthing = ".$varblobthing.",
WHERE id = 2;";

mysql_query ($query, $connection);
I would love any advice I can get. Thanks in advance.
Simon

Sime,

Three problems here. First of all, since $varblobthing is non-numeric,
you need to enclose it with "'" characters. Also, a blob can, by
definition, contain any character - including "special" ones like "'".
You need to use mysql_escape_string (or mysql_real_escape_string,
depending on your version of PHP and MySQL) to escape it.

Finally, get rid of the trailing ';' after the 2. You don't need a ';'
at the end of the SQL statement.

Try something like:

$query = "UPDATE mytable SET blobthing = '" .
mysql_escape_string($varblobthing) .
"' WHERE id=2";

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Jerry Stuckle]

Jerry Stuckle wrote:

sime wrote:

Hi,

I have a blob field in a mysql database table. I want to copy a blob
from one record to another. I am having trouble transferring the data
via a php variable. Maybe I need to addslashes or convert to Hex or
something. I've tried a few things but can't quite get it. Here is
simplified code.

mysql_select_db($dbname, $connection);

$query = "SELECT blobthing
FROM mytable
WHERE id = 1;";

$results = mysql_query ($query, $connection);
$row = (mysql_fetch_assoc($result));
$varblobthing = $row['blobthing']; //put the blob in a variable

$query = "UPDATE mytable
SET blobthing = ".$varblobthing.",
WHERE id = 2;";

mysql_query ($query, $connection);
I would love any advice I can get. Thanks in advance.
Simon

Sime,

Three problems here. First of all, since $varblobthing is non-numeric,
you need to enclose it with "'" characters. Also, a blob can, by
definition, contain any character - including "special" ones like "'".
You need to use mysql_escape_string (or mysql_real_escape_string,
depending on your version of PHP and MySQL) to escape it.

Finally, get rid of the trailing ';' after the 2. You don't need a ';'
at the end of the SQL statement.

Try something like:

$query = "UPDATE mytable SET blobthing = '" .
mysql_escape_string($varblobthing) .
"' WHERE id=2";

P.S. You should check the result of each mysql operation per the manual.
In this case I suspect the mysql_query is returning false and
checking the error with mysql_error() would give you information on why
it didn't work.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

[Andy Hassall]

On 10 Sep 2005 10:36:06 -0700, "sime" <ge*************@hotmail.com> wrote:

I have a blob field in a mysql database table. I want to copy a blob
from one record to another. I am having trouble transferring the data
via a php variable. Maybe I need to addslashes or convert to Hex or
something. I've tried a few things but can't quite get it. Here is
simplified code.

mysql_select_db($dbname, $connection);
You've said you are "having trouble", yet you have not described what
"trouble" you're having, you've only posted some code.
mysql_query ($query, $connection);

I would love any advice I can get.
MySQL and PHP can give you advice for starters; make sure error_reporting is
set to E_ALL, and check the result code of every mysql_* call; if it returns
false, use mysql_error() to print a full error message.
$query = "UPDATE mytable
SET blobthing = ".$varblobthing.",
WHERE id = 2;";

You're missing quotes around the value, escaping using mysql_escape_string,
and what's that comma at the end of the second line for?
Or, an alternative approach:
(http://dev.mysql.com/doc/mysql/en/ex...variables.html)

mysql> create table t (id int, b blob);
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t values (1, 'one');
Query OK, 1 row affected (0.06 sec)

mysql> select * from t;
+------+------+
| id | b |
+------+------+
| 1 | one |
| 2 | two |
+------+------+
2 rows in set (0.00 sec)

mysql> insert into t values (2, 'two');
Query OK, 1 row affected (0.00 sec)

mysql> select @b:=b from t where id=1;
+-------+
| @b:=b |
+-------+
| one |
+-------+
1 row in set (0.08 sec)

mysql> update t set b=@b where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t;
+------+------+
| id | b |
+------+------+
| 1 | one |
| 2 | one |
+------+------+
2 rows in set (0.00 sec)

--
Andy Hassall :: an**@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[sime]

Jerry, you set me straight. mysql_escape_string() did the trick. I
had read about it at the mysql site but couldn't fathom it - especially
when they start talking about magic quotes.

Can I please apologise to you and other polite posters for the other
flaws in my sample. This is the poor form of a php newbie.

Many Thanks, Simon

[Jerry Stuckle]

sime wrote:

Jerry, you set me straight. mysql_escape_string() did the trick. I
had read about it at the mysql site but couldn't fathom it - especially
when they start talking about magic quotes.

Can I please apologise to you and other polite posters for the other
flaws in my sample. This is the poor form of a php newbie.

Many Thanks, Simon

No problem, Glad to help.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================