Skip to content

Commit

Permalink
feature:2359 add single_update and single_insert functions
Browse files Browse the repository at this point in the history 
git-svn-id: http://piwigo.org/svn/trunk@11485 68402e56-0260-453c-a942-63ccdbb3a9ee
  • Loading branch information
@mistic100
mistic100 committed Jun 22, 2011
1 parent 8d22af0 commit 0296882
Show file tree
Hide file tree
Showing 4 changed files with 423 additions and 34 deletions.
103 changes: 101 additions & 2 deletions include/dblayer/functions_mysql.inc.php
View file
Expand Up @@ -219,6 +219,7 @@ function mass_updates($tablename, $dbfields, $datas, $flags=0)
{
if (count($datas) == 0)
return;

// depending on the MySQL version, we use the multi table update or N update queries
if (count($datas) < 10)
{
Expand All @@ -232,13 +233,13 @@ function mass_updates($tablename, $dbfields, $datas, $flags=0)
{
$separator = $is_first ? '' : ",\n ";

if (isset($data[$key]) and $data[$key] !== '')
if (isset($data[$key]) and $data[$key] != '')
{
$query.= $separator.$key.' = \''.$data[$key].'\'';
}
else
{
if ($flags & MASS_UPDATES_SKIP_EMPTY )
if ( $flags & MASS_UPDATES_SKIP_EMPTY )
continue; // next field
$query.= "$separator$key = NULL";
}
Expand Down Expand Up @@ -345,6 +346,65 @@ function mass_updates($tablename, $dbfields, $datas, $flags=0)
}
}

/**
* updates one line in a table
*
* @param string table_name
* @param array dbfields
* @param array data
* @param int flags - if MASS_UPDATES_SKIP_EMPTY - empty values do not overwrite existing ones
* @return void
*/
function single_update($tablename, $dbfields, $data, $flags=0)
{
if (count($data) == 0)
return;

$query = '
UPDATE '.$tablename.'
SET ';
$is_first = true;
foreach ($dbfields['update'] as $key)
{
$separator = $is_first ? '' : ",\n ";

if (isset($data[$key]) and $data[$key] != '')
{
$query.= $separator.$key.' = \''.$data[$key].'\'';
}
else
{
if ( $flags & MASS_UPDATES_SKIP_EMPTY )
continue; // next field
$query.= "$separator$key = NULL";
}
$is_first = false;
}
if (!$is_first)
{// only if one field at least updated
$query.= '
WHERE ';
$is_first = true;
foreach ($dbfields['primary'] as $key)
{
if (!$is_first)
{
$query.= ' AND ';
}
if ( isset($data[$key]) )
{
$query.= $key.' = \''.$data[$key].'\'';
}
else
{
$query.= $key.' IS NULL';
}
$is_first = false;
}
pwg_query($query);
}
}


/**
* inserts multiple lines in a table
Expand Down Expand Up @@ -410,6 +470,45 @@ function mass_inserts($table_name, $dbfields, $datas)
}
}

/**
* inserts on line in a table
*
* @param string table_name
* @param array dbfields
* @param array insert
* @return void
*/
function single_insert($table_name, $dbfields, $insert)
{
if (count($insert) != 0)
{
$query = '
INSERT INTO '.$table_name.'
('.implode(',', $dbfields).')
VALUES';

$query .= '(';
foreach ($dbfields as $field_id => $dbfield)
{
if ($field_id > 0)
{
$query .= ',';
}
if (!isset($insert[$dbfield]) or $insert[$dbfield] === '')
{
$query .= 'NULL';
}
else
{
$query .= "'".$insert[$dbfield]."'";
}
}
$query .= ')';

pwg_query($query);
}
}

/**
* Do maintenance on all PWG tables
*
Expand Down
127 changes: 112 additions & 15 deletions include/dblayer/functions_pdo-sqlite.inc.php
View file
Expand Up @@ -278,13 +278,13 @@ function mass_updates($tablename, $dbfields, $datas, $flags=0)

if (isset($data[$key]) and $data[$key] != '')
{
$query.= $separator.$key.' = \''.$data[$key].'\'';
$query.= $separator.$key.' = \''.$data[$key].'\'';
}
else
{
if ($flags & MASS_UPDATES_SKIP_EMPTY )
continue; // next field
$query.= "$separator$key = NULL";
if ( $flags & MASS_UPDATES_SKIP_EMPTY )
continue; // next field
$query.= "$separator$key = NULL";
}
$is_first = false;
}
Expand All @@ -295,25 +295,84 @@ function mass_updates($tablename, $dbfields, $datas, $flags=0)
$is_first = true;
foreach ($dbfields['primary'] as $key)
{
if (!$is_first)
if (!$is_first)
{
$query.= ' AND ';
}
if ( isset($data[$key]) )
$query.= ' AND ';
}
if ( isset($data[$key]) )
{
$query.= $key.' = \''.$data[$key].'\'';
}
else
$query.= $key.' = \''.$data[$key].'\'';
}
else
{
$query.= $key.' IS NULL';
}
$is_first = false;
$query.= $key.' IS NULL';
}
$is_first = false;
}
pwg_query($query);
}
}
}

/**
* updates on line in a table
*
* @param string table_name
* @param array dbfields
* @param array data
* @param int flags - if MASS_UPDATES_SKIP_EMPTY - empty values do not overwrite existing ones
* @return void
*/
function single_update($tablename, $dbfields, $data, $flags=0)
{
if (count($data) == 0)
return;

$query = '
UPDATE '.$tablename.'
SET ';
$is_first = true;
foreach ($dbfields['update'] as $key)
{
$separator = $is_first ? '' : ",\n ";

if (isset($data[$key]) and $data[$key] != '')
{
$query.= $separator.$key.' = \''.$data[$key].'\'';
}
else
{
if ( $flags & MASS_UPDATES_SKIP_EMPTY )
continue; // next field
$query.= "$separator$key = NULL";
}
$is_first = false;
}
if (!$is_first)
{// only if one field at least updated
$query.= '
WHERE ';
$is_first = true;
foreach ($dbfields['primary'] as $key)
{
if (!$is_first)
{
$query.= ' AND ';
}
if ( isset($data[$key]) )
{
$query.= $key.' = \''.$data[$key].'\'';
}
else
{
$query.= $key.' IS NULL';
}
$is_first = false;
}
pwg_query($query);
}
}


/**
* inserts multiple lines in a table
Expand All @@ -323,7 +382,6 @@ function mass_updates($tablename, $dbfields, $datas, $flags=0)
* @param array inserts
* @return void
*/

function mass_inserts($table_name, $dbfields, $datas)
{
if (count($datas) != 0)
Expand Down Expand Up @@ -379,6 +437,45 @@ function mass_inserts($table_name, $dbfields, $datas)
}
}

/**
* inserts one line in a table
*
* @param string table_name
* @param array dbfields
* @param array insert
* @return void
*/
function single_insert($table_name, $dbfields, $insert)
{
if (count($insert) != 0)
{
$query = '
INSERT INTO '.$table_name.'
('.implode(',', $dbfields).')
VALUES';

$query .= '(';
foreach ($dbfields as $field_id => $dbfield)
{
if ($field_id > 0)
{
$query .= ',';
}
if (!isset($insert[$dbfield]) or $insert[$dbfield] === '')
{
$query .= 'NULL';
}
else
{
$query .= "'".$insert[$dbfield]."'";
}
}
$query .= ')';

pwg_query($query);
}
}

/**
* Do maintenance on all PWG tables
*
Expand Down

0 comments on commit 0296882

Please sign in to comment.