Home » Database » DB_Table » Bug #8760

[2006-09-21 16:56 UTC] benjaminhill at gmail dot com

Description:
------------
I find myself re-using a similar function often in seed scripts:

/**
* Simple method to insert a row into a table, "smart" version of MDB2 Extended autoExecute
* returns: 0 if no insert, PK of new row or "1" if no pk.
* $values: Associative array of column values
* $only_if_new: Checks to see if the values already exist
* $has_pk: Automatically uses nextId(), if a new row is inserted
* $match_values: optional Associative array, if the values to match on aren't the same as the values to conditionally insert
*/
insert_row($table_name, $values, $only_if_new = false, $has_pk = true, $match_values=null) {
...

In an ideal world, one call to this function could insert across many tables based on foreign keys, but that is VERY blue sky.

[2006-09-21 17:05 UTC] benjaminhill at gmail dot com

Ok, this is VERY ugly code. But it gets the idea across.

function insert_row($table, $values, $only_if_new = false, $has_pk = true) {
$db = MDB2 :: singleton();
if ($only_if_new) {
$sql = "select ";
$sql .= ($has_pk) ? $table . "_id" : "count(1)";
$sql .= " from $table where ";

$sqlJoin = array();
foreach ($values as $_key => $_val) {
$sqlJoin[] = $_key . " = " . $db->quote($_val);
}
$sql .= implode(' and ', $sqlJoin);

if (isset ($_SESSION['debug']))
echo $sql . '<hr/>\n';

$id = $db->queryOne($sql);
if (PEAR :: isError($id)) {
throw new Exception('MDB2 Error on check for existing row:' . $id->getMessage());
}
if ($id)
return $id;
}

if ($has_pk)
$values[$table . "_id"] = $db->nextId($table);
if (isset ($_SESSION['debug']))
print_r($values);

$result = $db->autoExecute($table, $values, MDB2_AUTOQUERY_INSERT);
if (PEAR :: isError($result)) {
throw new Exception('MDB2 Error on insert into table:' . $table . ':' . print_r($values,true) . ':' . print_r($result, true));
}

return ($has_pk) ? $values[$table . "_id"] : 1;
}