Interesting reads (PHP & more) and tools (Twitter & more)

More on PHP Performance
http://php100.wordpress.com/2009/07/13/php-performance/

Benchmarking PHP
http://blueprint.intereactive.net/benchmarking-our-php/

PHP Abstract Podcast Episode 42: Keith Casey & Web2Project
http://devzone.zend.com/article/4831-PHP-Abstract-Podcast-Episode-42-Keith-Casey-Web2Project

3 Part Series on namespaces in PHP 5.3
http://www.sitepoint.com/blogs/2009/07/13/php-53-namespaces-basics/
http://www.sitepoint.com/blogs/2009/07/14/php-namespaces-import-alias-resolution/
http://www.sitepoint.com/blogs/2009/07/15/how-to-use-php-namespaces-part-3-keywords-and-autoloading/

50 useful PHP Tools
http://www.smashingmagazine.com/2009/01/20/50-extremely-useful-php-tools/

List of “White Label” or “Private Label” (Applications you can Rebrand) Social Networking Platforms, Community Platforms
http://www.web-strategist.com/blog/2007/02/12/list-of-white-label-social-networking-platforms/

Freitags Open House für Freiberufler, Ideen und Projekte
http://hallenprojekt.de/mindmatters

The “AddToAny”-widgets
http://www.addtoany.com/

Twitter Tool for Team Tweeting
http://www.tweetfunnel.com/

Twitter toolbox
http://hootsuite.com/

Widgets galore
http://www.widgetbox.com/

Command line access to the web
http://yubnub.org/

Online wordprocessor
http://writer.zoho.com/

“Test everything” – nice idea to generate links to various services
http://tester.jonasjohn.de/

UIzard – in-browser web appication development tool – want to look more closely at this
http://www.uizard.org/

Multiple Inserts in Oracle (example using PHP)

Everybody knows how to make multiple inserts into MySQL:

INSERT INTO [table_name] ([col_1], [col_2])
  VALUES
    ([val_1_1], [val_1_2]),
    ([val_2_1], [val_2_2]),
    ([val_3_1], [val_3_2])

That´s easy! But multiple inserts to an Oracle DB don´t work like that… here are two ways of doing the same job with Oracle:

insert all
  into [table_name] ([col1], [col2]) values ([val1_1], [val1_2])
  into [table_name] ([col1], [col2]) values ([val2_1], [val2_2])
  into [table_name] ([col1], [col2]) values ([val3_1], [val3_2])
  select 1 from dual

or alternatively

insert into [table_name] ([col1], [col2])
  select [val1_1], [val1_2] from dual
  union all
    select [val2_1], [val2_2] from dual
  union all
    select [val3_1], [val3_2] from dual

This is useful if you need to insert a lot of rows into a table – which usually occurs in a job like data import from other sources rather than as a result of user interaction. Running a PHP script for example to insert 50.000 rows into a table would fire 50.000 inserts if you don´t use multiple inserts. This is slow and can also cause you to run into memory problems.

Here is a simple example of how to do this in PHP:

class MyImportTest extends MyDatabaseModel
{
 protected static $_parallelImports = 50;
 protected static $_insertCache = array();

 public static function insertImportDataIntoDb($inputData)
 {
  $added = 0;
  foreach ($inputdata as $inputArray) {
    $added += self::_addCaseToInsertCache($inputArray['col1'], $inputArray['col2']);
    if ($added % self::$_parallelInserts == 0) {
      self::_flushInsertCache();
    }
  }
	if ($added % self::$_parallelInserts != 0) {
    self::_flushInsertCache();
  }
 }

 function addCaseToInsertCache($col1, $col2)
 {
  // maybe do validity checks here and return 0 if an element is not added
  self::$_insertCache[] = array($col1, $col2);
  return 1;
 }

 function flushInsertCache()
 {
  $db = self::getDb();
  $sql = 'INSERT INTO "TABLE_NAME" ("COL1", "COL2")';
  $values = '';
  foreach (self::$_insertCache as $insert) {
            $values .= (empty($values)) ? '' : ' UNION ALL ';
            $values .= 'SELECT ' .
                $insert[0] . ', ' .
                $insert[1] . ', ' .
                $insert[5] . ' FROM DUAL';
        } // if using string values, then don't forget to use quotes!
        $db->query($sql . $values);
        self::$_insertCache = array();
    }
 }
}

Easy way to delete duplicate rows in Oracle

DELETE FROM [tablename] A WHERE A.ROWID > ANY (
  SELECT B.ROWID FROM [tablename] B
  WHERE A.[uniquecolumn] = B.[uniquecolumn]
  [ AND A.[anothercolumn] = B.[anothercolumn] ]
)

Autoincrement in Oracle DB mittels Sequence und Trigger

CREATE SEQUENCE [tablename]_SEQ

START WITH 1
MAXVALUE 9999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

CREATE OR REPLACE TRIGGER [tablename]_trigger

BEFORE INSERT ON [tablename]

FOR EACH ROW

BEGIN

SELECT [tablename]_SEQ.NEXTVAL INTO :NEW.[primarykey] FROM DUAL;

END;