469,360 Members | 1,774 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

update mysql blob with another blob using php

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

Sep 10 '05 #1
7 10865
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
Sep 10 '05 #2
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);
....
Sep 10 '05 #3
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
==================
Sep 10 '05 #4
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
==================
Sep 10 '05 #5
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
Sep 10 '05 #6
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

Sep 11 '05 #7
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
==================
Sep 11 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by James E Koehler | last post: by
4 posts views Thread by geof | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.