In my PHP page i upload pictures into database. For that i wrote a simple function which control which user is logged and will upload his picture.
here is the function :
CREATE OR REPLACE FUNCTION sp_a_006("login" character varying, photo bytea)
RETURNS boolean AS
$BODY$
DECLARE
my_id INTEGER :=0;
BEGIN
select into my_id
account_id from accounts where account_login = $1;
IF (my_id != 0) THEN
UPDATE users
SET user_photo = $2
WHERE user_account_id = my_id;
RETURN (TRUE);
ELSE
RETURN (FALSE);
end if;
END;
when i call this function in PHP, i do the following :
$my_query = "select * from immense.sp_a_006('".$_SESSION["username"]."','{$escaped}')";
$res_pic = pg_query(my_query);
where $escaped = pg_escape_bytea($data);
this inserts the picture only if i add E in front of '{$escaped}' and becomes E'{$escaped}').
why ?
on another website i do not use function but a simple SQL query as following and it works :
pg_query("INSERT INTO photo (photo_id,document_orientation_id, photo_date, photo_image)
VALUES (nextval('photo_photo_id_seq'),".$orientation_id[0].",NOW(), '{$escaped}')");
thanks a lot,
--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5
