INSERT OR UPDATE?

[smorrey]

Hello all,

I am writing an app in PHP that uses a PostGres database.
One thing i have noticed is that what should/could be a single line of
SQL code takes about 6 lines of PHP. This seem wasteful and redundant
to me.

Here is a sample of what I'm talking about ($db is a PDO already
defined and created).

$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
somecondition";
$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
if(!$db->query($query[1])){
$db->query($query[2]);
}

What I'm curious to know is if there is some way to simplify this,
either buy some PHP builtin or extension, or possibly something in SQL
I am missing. It seems to me that "UPDATE OR INSERT", should be valid,
but I can't seem to find anything relevant at all about it.

Anyways I hope you don't mind, but I'm crossposting this to
pgsql.general and comp.lang.php to see if I can get some information on
the subject.

[andrew]

I think is almost the same that in many other languages, and like in
many other with the time you can have function's libraries, or more
likely class libraries with the usefull stuff.

In desktop programming environments you have components, here you have
classes that are the same thing using it in another way. :) Watch out
the new auto-includes from PHP5 classes.

There are scripts with differents approaches, you can select one or
build your own for multiple proyects.
Personally I preffer not to build the SQL code in PHP. I allways have
the queries in XML files with an specific format I chose, to describe a
SQL Query with parameters. A class to handle the query can "tell me"
about the parameters or simply "receive" the parameters, build and
execute the query, and return the results or making it browseable
(recordset).

A very usual and smart approach is to use clases in PEAR::DB.

[Gordon Burditt]

>I am writing an app in PHP that uses a PostGres database.

One thing i have noticed is that what should/could be a single line of
SQL code takes about 6 lines of PHP. This seem wasteful and redundant
to me.

Here is a sample of what I'm talking about ($db is a PDO already
defined and created).

$query[1] = "UPDATE my.table, SET somefield = '$someval' WHERE
somecondition";
$query[2] = "INSERT INTO my.table (somefield) VALUES ('$someval')";
if(!$db->query($query[1])){
$db->query($query[2]);
}

What I'm curious to know is if there is some way to simplify this,
either buy some PHP builtin or extension, or possibly something in SQL
I am missing. It seems to me that "UPDATE OR INSERT", should be valid,
but I can't seem to find anything relevant at all about it.

MySQL permits (but it's not standard, and available in MySQL 4.1.0
and later):

INSERT INTO my.table (somefield) VALUES ('$someval') ON DUPLICATE
KEY UPDATE somefield = '$someval';

This is very useful for times when you want to count something (e.g.
SPAM), and if a record doesn't exist, make one with a count of 1.

I don't know whether something similar is available in PostGres.

Gordon L. Burditt

[shakahshakah]

Gordon Burditt wrote:

[...stuff snipped...]

MySQL permits (but it's not standard, and available in MySQL 4.1.0
and later):

INSERT INTO my.table (somefield) VALUES ('$someval') ON DUPLICATE
KEY UPDATE somefield = '$someval';

This is very useful for times when you want to count something (e.g.
SPAM), and if a record doesn't exist, make one with a count of 1.

I don't know whether something similar is available in PostGres.

Gordon L. Burditt

In Postgres you'd probably create a server-side function to get that
effect.

[Jerry Sievers]

sm*****@gmail.com writes:

Hello all,

I am writing an app in PHP that uses a PostGres database.
One thing i have noticed is that what should/could be a single line of
SQL code takes about 6 lines of PHP. This seem wasteful and redundant
to me.

Here is a sample of what I'm talking about ($db is a PDO already
defined and created).

Well, at least you're using a very good DB!

This is easy to solve in Postgres.

Study up on "the rule system" and you will find the solution. A
BEFORE INSERT trigger could be used here as well.

create table foo (a int not mull primary key, b text);

create rule maybe_update as on insert to foo where exists (select 1
from foo where a = new.a) do instead update foo set b = new.b where a
= new.a;

Untested example above... but have done this sort of thing a lot. Can
be difficult to grasp at first.

A before insert trigger would test if the record exists already and if
so, do an update inside the trigger function and return null else
return new and the outter query proceed doing the insert.

HTH
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/

[Jerry Sievers]

sm*****@gmail.com writes:

Hello all,

I am writing an app in PHP that uses a PostGres database.
One thing i have noticed is that what should/could be a single line of
SQL code takes about 6 lines of PHP. This seem wasteful and redundant
to me.

Here ya go!...

create temp table foo (
id int primary key,
data text
);

create rule foo
as on insert to foo
where exists (
select 1
from foo
where id = new.id
)
do instead
update foo
set data = new.data
where id = new.id
;

copy foo from stdin using delimiters ',';
1,hello
2,hello
\.

select * from foo order by id;

insert into foo values (
1,'it works!'
);

select * from foo order by id;

Outout...

CREATE TABLE
CREATE RULE
id | data
----+-------
1 | hello
2 | hello
(2 rows)

INSERT 0 0
id | data
----+-----------
1 | it works!
2 | hello
(2 rows)

HTH
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/

[smorrey]

That works great, thanks for the info!

[Oliver Saunders]

> create rule foo

as on insert to foo
where exists (
select 1
from foo
where id = new.id
)
do instead
update foo
set data = new.data
where id = new.id
;

wow that is a good database
can you do anything like that in MySQL?

[Mladen Gogala]

On Fri, 14 Oct 2005 09:09:30 +0100, Oliver Saunders wrote:

wow that is a good database
can you do anything like that in MySQL?

It will be possible when MySQL starts supporting PL/SQL.

--
http://www.mgogala.com

[Jerry Stuckle]

Oliver Saunders wrote:

create rule foo
as on insert to foo
where exists (
select 1
from foo
where id = new.id
)
do instead
update foo
set data = new.data
where id = new.id
;

wow that is a good database
can you do anything like that in MySQL?

It's possible in any of the commercial databases. SQL Server, Oracle
and DB2 have similar option, for example. But these can cost thousands
of dollars.

MySQL is good for a free database, and they are adding more features to
it every day. But it got a much later start than most other databases,
and being basically free, it doesn't get new features as quickly. But I
think the developers are doing a great job in making it a better database.

Postgres is also a super database - in some ways ahead of MySQL. But
it's not as common with the shared hosting companies, so people don't
know as much about it. If you can choose your database, it's worth a
check, also.

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

[Drazen Gemic]

Jerry Stuckle wrote:

Oliver Saunders wrote:
Postgres is also a super database - in some ways ahead of MySQL. But
it's not as common with the shared hosting companies, so people don't
know as much about it. If you can choose your database, it's worth a
check, also.

Just for the record, Postgres supports "CREATE RULE". One does not have
to spend thousands of dolars for that.

DG