Обсуждение: INSERT OR UPDATE?
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.
>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
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 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.
Try (for simple cases): DELETE FROM my.table WHERE somecondition; INSERT INTO my.table (somefield) VALUES ('$someval'); In complex cases it may be necessary to INSERT the values into a temporary table, which is then used to condition the DELETE before INSERTing the temporary table into your permanent table. One advantage of the above is that it works for multiple rows, whereas your technique does not. -- Dean On 2005-10-08 23:15, smorrey@gmail.com wrote: >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. > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > >
On 09.10.2005 08:48, andrew wrote: > A very usual and smart approach is to use clases in PEAR::DB. Well, IMHO PEAR::DB is one of the worst classes of PEAR. Besides its ugly code it's worth nothing. This is some incomplete "abstraction layer" for kiddies, to make it easy for people coming from stupid storage engines like MySQL or SqLite, to _store_ their data in another DBMS. I never really understood, what such a thing should be good for. SQL implementations are not portable, nor are stored procedures, functions, rules, triggers, etc. Every DBMS implements them in it's own way and almost all professional devs will implement most, or at least the critical parts, of the business logic directly inside DBMS and not at application level. Thus making the application basically just a frontend, calling stored procedures and writing to views, controlled by triggers and strict permissions. The only thing PEAR::DB might be usefull for is, writing those famous cross-dbms-apps, which basically use the DBMS highly inefficient as a better plaintext file (not even using foreign keys in most cases, so it will work with MySql 3.x as well). And if you don't what to write such bizarre cross-dbms-apps, what is the point in using PEAR::DB at all? Those few incomplete methods wrapping the pg_* functions in some highly debatable way? Do yourself a favor and write your own slime and efficient set of classes around the native API-functions of your DBMS. PEAR::DB and others prevent you from using an advanced DBMS the way you should. -- Regards, Hannes Dorbath