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_as soc($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]

Re: update mysql blob with another blob using php

sime said the following on 10/09/2005 18:36:[color=blue]
> 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_as soc($result));
> $varblobthing = $row['blobthing']; //put the blob in a variable
>
> $query = "UPDATE mytable
> SET blobthing = ".$varblobthing .",
> WHERE id = 2;";[/color]
^
^
You forgot quotes


--
Oli

[Pertti Kosunen]

Re: update mysql blob with another blob using php

sime wrote:[color=blue]
> mysql_select_db ($dbname, $connection);[/color]

mysql_select_db ($dbname, $connection)
or die ( 'Unable to select database.' );
[color=blue]
> $results = mysql_query ($query, $connection);[/color]

$results = mysql_query ($query, $connection)
or die ( 'Unable to execute query.' );
[color=blue]
> $row = (mysql_fetch_as soc($result));
> $varblobthing = $row['blobthing']; //put the blob in a variable[/color]

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

[Jerry Stuckle]

Re: update mysql blob with another blob using php

sime wrote:[color=blue]
> 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_as soc($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
>[/color]

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_st ring (or mysql_real_esca pe_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_st ring($varblobth ing) .
"' WHERE id=2";

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: update mysql blob with another blob using php

Jerry Stuckle wrote:[color=blue]
> sime wrote:
>[color=green]
>> 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_as soc($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
>>[/color]
>
> 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_st ring (or mysql_real_esca pe_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_st ring($varblobth ing) .
> "' WHERE id=2";
>[/color]

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.
jstucklex@attgl obal.net
=============== ===

[Andy Hassall]

Re: update mysql blob with another blob using php

On 10 Sep 2005 10:36:06 -0700, "sime" <get_simon_hobb s@hotmail.com> wrote:
[color=blue]
>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);[/color]

You've said you are "having trouble", yet you have not described what
"trouble" you're having, you've only posted some code.
[color=blue]
> mysql_query ($query, $connection);
>
>I would love any advice I can get.[/color]

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.
[color=blue]
> $query = "UPDATE mytable
> SET blobthing = ".$varblobthing .",
> WHERE id = 2;";[/color]

You're missing quotes around the value, escaping using mysql_escape_st ring,
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 :: andy@andyh.co.u k :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

[sime]

Re: update mysql blob with another blob using php

Jerry, you set me straight. mysql_escape_st ring() 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]

Re: update mysql blob with another blob using php

sime wrote:[color=blue]
> Jerry, you set me straight. mysql_escape_st ring() 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
>[/color]

No problem, Glad to help.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===