db_common.inc.php

Go to the documentation of this file.

<?php

// ------------------------------------------------------------ DATABASE-SPECIFIC
// You may define APPPREFIX and DB_DATABASE in the application
// APPPREFIX can be used to separate applications within one database
// allowed chars are A_Z,a-z,_  ( e.g.: "myAPP_" )
// for direct SQL use raw_db_query, keep in mind to handle APPPREFIX in your SQL
// for tablenames and possible prefixes to fieldnames in returned records
define("DB_WRAPVERSION", "db_v2b");
 
if (!defined("APPPREFIX")) define("APPPREFIX", "");
if (!defined("DB_DATABASE")) define("DB_DATABASE", "SQLite");
 
$_mainpath = pathinfo(__FILE__);
$_mainpath = $_mainpath["dirname"] . "/";
 
include $_mainpath . "db_" . strtolower(DB_DATABASE) . ".inc.php";

function db_get_info($info = "")
{
  switch (strtolower($info)) {
    case "version":
      return DB_DATABASE . " " . raw_get_db_version() . " with Database Wrapper " . DB_WRAPVERSION;
 
    case "dbtype":
      return DB_DATABASE;
 
    case "dbversion":
      return raw_get_db_version();
 
    case "wrapversion":
      return DB_WRAPVERSION;
  }
}
// -----------------------wrapper for APPPREFIX----- DATABASE FUNCTIONS
function db_open_database($database, $server = "", $username = "", $password = "")
{
  return raw_db_open_database($database, $server, $username , $password);
}

function db_create_database($database, $server = "", $username = "", $password = "")
{
  return raw_db_create_database($database, $server, $username , $password);
}

function db_list_database_tables()
{
  $tables = raw_db_list_database_tables();
  if (!$tables) {
    return false;
  }
  $tmp_tabs = "";
  $prefixlen = strlen(trim(APPPREFIX));
  if ($prefixlen > 0) {
    foreach($tables as $table) {
      if (!(stristr(substr($table, 0, $prefixlen), APPPREFIX) === false)) {
        $tmp_tabs[] = substr($table, strlen(APPPREFIX));
      }
    }
    if ($tmp_tabs == "") {
      return false;
    }
    return $tmp_tabs;
  } else return $tables;
}

function db_close_database()
{
  return raw_db_close_database();
}
// -------------------------------------------------------------- TABLE FUNCTIONS
function db_table_exists($tablename)
{
  global $g_lasttable;
 
  if (!$tablename)
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
  return raw_db_table_exists(APPPREFIX . $tablename);
}

function db_create_table($tablename, $fieldnames, $fieldattrib, $idfield = "id", $valarray = null)
{
  global $g_lasttable;
 
  if ($tablename == null || $tablename == "")
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
 
  if (db_table_exists($tablename))
    return false;
 
  if (is_string($fieldnames))
    $fieldnames = preg_split("/[\r\n,]/", $fieldnames);
  if (is_string($fieldattrib))
    $fieldattrib = preg_split("/[\r\n,]/", $fieldattrib);
  $attribs = count($fieldattrib);
  if (count($fieldnames) != $attribs) {
    trigger_error(__FUNCTION__ . ": both arrays must be same length.");
    return false;
  }
  $sql = "CREATE TABLE " . APPPREFIX . "$tablename (";
  $sql .= "$idfield int(11) NOT NULL PRIMARY KEY ";
  if ($attribs != 0) {
    $sql .= ", ";
 
    for($i = 0; $i < $attribs; $i++)
    $sql .= $fieldnames[$i] . " " . $fieldattrib[$i] . ($i < $attribs - 1 ? ", " : "");
  }
  $sql .= ")";
  // Send the sql command
  $result = raw_db_query($sql);
  if (!$result) {
    trigger_error(__FUNCTION__ . ": could not create table $tablename.");
    return false;
  }
 
  if ($valarray)
    foreach($valarray as $values) {
    $result = db_create_record($tablename, $fieldnames, $values, $idfield);
    if ($result === false) {
      return false;
    }
  }
  return $result;
}

function db_delete_table($tablename)
{
  global $g_lasttable;
 
  if (!$tablename)
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
 
  if ($tablename == null || $tablename == "")
    return false;
  if (db_table_exists($tablename))
    $result = raw_db_query("DROP table " . APPPREFIX . $tablename);
  return $result;
}

function db_rename_table($tablename, $newname)
{
  return raw_db_rename_table(APPPREFIX . $tablename, APPPREFIX . $newname);
}

function db_list_table_fields($tablename, $type = false)
{
  global $g_lasttable;
 
  if (!$tablename)
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
 
  $result = raw_db_list_table_fields_def(APPPREFIX . $tablename, $type);
  return $result;
}
// -------------------------------------------------------------- FIELD FUNCTIONS
function db_create_field($tablename, $field, $type)
{
  return raw_db_create_field(APPPREFIX . $tablename, $field, $type);
}

function db_delete_field($tablename, $field)
{
  return raw_db_delete_field(APPPREFIX . $tablename, $field);
}

function db_rename_field($tablename, $field, $newname, $type)
{
  return raw_db_rename_field(APPPREFIX . $tablename, $field, $newname, $type);
}

function db_edit_field($tablename, $field, $type)
{
  return raw_db_edit_field(APPPREFIX . $tablename, $field, $type);
}
// ------------------------------------------------------------- RECORD FUNCTIONS
function db_create_record($tablename, $fieldnames = null, $fieldvalues = null, $idfield = "id")
{
  global $g_lasttable;
 
  if (!$tablename)
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
 
    if (!$fieldnames) {
      $fieldnames = db_list_table_fields($tablename);
      array_shift($fieldnames);
    }
    if (!$fieldvalues) {
      $fieldvalues = array_fill(0, count($fieldnames), 0);
 
    }
  // Get next available index
  $sql = "SELECT max($idfield) FROM " . APPPREFIX . $tablename;
  $result = raw_db_query($sql);
  if ($result === false) {
    return false;
  }
  $newid = (db_fetch_array($result, FETCH_NUM)) ;
  $newid = $newid[0] + 1;
  // Build the two arrays
  $names = is_string($fieldnames) ? preg_split("/[\r\n]/", $fieldnames) : $fieldnames;
  $values = is_string($fieldvalues) ? preg_split("/[\r\n]/", $fieldvalues) : $fieldvalues;
  if (count($names) != count($values)) {
    trigger_error(__FUNCTION__ . ": both arrays must be same length.\n");
    return false;
  }
  // Build the SQL query
  $nfields = count($names);
  $fieldnames = $names;
  $fieldvalues = $values;
  for($i = 0, $names = ""; $i < $nfields; $i++)
  $names .= $fieldnames[$i] . ($i < $nfields - 1 ? ", " : "");
  for($i = 0, $values = ""; $i < $nfields; $i++)
  $values .= "'" . db_escape_string($fieldvalues[$i]) . "'" . ($i < $nfields - 1 ? ", " : "");
 
  $sql = "INSERT INTO " . APPPREFIX . $tablename . " ($idfield, $names) VALUES ($newid, $values)";
 
  $result = raw_db_query($sql);
  if (!$result) {
    trigger_error(__FUNCTION__ . ": could not create new record in table $tablename.");
    return false;
  }
  return $newid;
}

function db_delete_records($tablename, $idarray, $idfield = "id")
{
  global $g_lasttable;
 
  if ($idarray == null || $idarray <= 0)
    return false;
  if (!is_array($idarray))
    $idarray = array($idarray);
 
  if (!$tablename)
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
 
  foreach($idarray as $item) {
    // Send the SQL command
    $sql = "DELETE FROM " . APPPREFIX . $tablename . " WHERE $idfield = " . $item;
    $result = raw_db_query($sql);
    if (!$result) {
      trigger_error(__FUNCTION__ . ": could not delete record $id in table $tablename.");
      return false;
    }
  }
  return true;
}

function db_edit_record($tablename, $id = 0, $fieldnames = null, $fieldvalues = null, $idfield = "id")
{
  global $g_lasttable;
 
  if ($id == null || $id <= 0) { // Create a new record
    return db_create_record($tablename, $fieldnames, $fieldvalues, $idfield);
  } else { // Edit existing record
    if (!$tablename)
      $tablename = $g_lasttable;
    $g_lasttable = $tablename;
    // Build the two arrays
    if (!$fieldnames) {
      $fieldnames = db_list_table_fields($tablename);
      array_shift($fieldnames);
    }
    if (!$fieldvalues) {
      $fieldvalues = array_fill(0, count($fieldnames), 0);
 
    }
 
    $names = is_string($fieldnames) ? preg_split("/[\r\n]/", $fieldnames) : $fieldnames;
    $values = is_string($fieldvalues) ? preg_split("/[\r\n]/", $fieldvalues) : $fieldvalues;
 
    if (count($names) != count($values)) {
      trigger_error(__FUNCTION__ . ": both arrays must be same length.\n");
      return false;
    }
    // Build the SQL query
    $nfields = count($names);
    for($i = 0, $str = ""; $i < $nfields; $i++) {
      $str .= $names[$i] . "='" . db_escape_string($values[$i]) . "'" .
      ($i < $nfields - 1 ? ", " : "");
    }
 
    $sql = "UPDATE " . APPPREFIX . "$tablename SET $str WHERE $idfield=$id";
    // Send the SQL command
    $result = raw_db_query($sql);
    if (!$result) {
      trigger_error(__FUNCTION__ . ": could not edit record $id in table $tablename.");
      return false;
    }
    return $id;
  }
}

function db_swap_records($tablename, $id1, $id2, $idfield = "id", $xchangeid = true)
{
  global $g_lasttable;
  // Table name
  if (!$tablename)
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
  $table = APPPREFIX . "$tablename";
  // Build SQL strings
  $result = raw_db_query("SELECT * FROM $table WHERE $idfield = $id1");
  if (!$result) {
    trigger_error(__FUNCTION__ . ": could not read record $id1 in table $tablename.");
    return false;
  }
  $a = db_fetch_array($result, FETCH_ASSOC);
  $fieldvalues1 = array_values($a);
  $fieldnames1 = array_keys($a);
  array_shift($fieldvalues1);
  array_shift($fieldnames1);
 
  $result = raw_db_query("SELECT * FROM $table WHERE $idfield = $id2");
  if (!$result) {
    trigger_error(__FUNCTION__ . ": could not read record $id2 in table $tablename.");
    return false;
  }
  $a = db_fetch_array($result, FETCH_ASSOC);
  $fieldvalues2 = array_values($a);
  $fieldnames2 = array_keys($a);
  array_shift($fieldvalues2);
  array_shift($fieldnames2);
  // Exchange values
  if (db_edit_record($tablename, $id1, $fieldnames2, $fieldvalues2, $idfield) === false) return false;
  if (db_edit_record($tablename, $id2, $fieldnames1, $fieldvalues1, $idfield) === false) return false;
  // Exchange id's
  if ($xchangeid) {
    $unique = db_get_next_free_id($tablename);
    if (db_edit_record($tablename, $id1, array($idfield), array($unique), $idfield) === false) return false;
    if (db_edit_record($tablename, $id2, array($idfield), array($id1), $idfield) === false) return false;
    if (db_edit_record($tablename, $unique, array($idfield), array($id2), $idfield) === false) return false;
  }
  return true;
}

function db_get_data($tablename, $id = null, $col = null, $where = "", $result_type = FETCH_NUM, $idfield = "id", $orderby = "")
{
  global $g_lasttable;
 
  if (!$tablename)
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
 
  if (is_array($col))
    $col = implode(",", $col);
  if ($col === null || $col === "")
    $col = "*";
  // Build the WHERE clause
  if ($id !== null) {
    if (is_string($id) && strstr($id, ",")) {
      $id = explode(",", $id);
    }
    if (is_array($id)) {
      $idcond = "";
      for($i = 0; $i < count($id); $i++)
      $idcond .= "$idfield = '{$id[$i]}'" . ($i < count($id) - 1 ? " OR " : "");
    } else
      $idcond = "$idfield = '$id'";
 
    $condition = $where ? " WHERE ($where) AND ($idcond)" : " WHERE ($idcond)";
  } else
    $condition = $where ? " WHERE ($where)" : "";
 
  $orderby = $orderby ? " ORDER BY $orderby" : "";
  // Do the query
  $sql = "SELECT $col FROM " . APPPREFIX . $tablename . $condition . $orderby;
 
  $result = raw_db_query($sql);
  if (!$result)
    return false;
  // Loop to build the return array
  $array = array();
  while ($row = db_fetch_array($result, $result_type)) {
    if (count($row) == 1)
      $row = array_shift($row);
    $array[] = $row;
  }
  if (db_free_result($result) === false) return false;
  // Return the result
  if (!is_array($array))
    return $array;
 
  switch (count($array)) {
    case 0:
      return false;
 
    case 1:
 
      $test = $array; // Copy array
      $elem = array_shift($test); // 1st element of array...
      if (is_null($elem)) // ...is it null?
        return false; // Yes: return null
      if (is_scalar($elem)) // ...is it a scalar?
        return $elem; // Yes: return the element alone
      else
        return $array; // No: return the whole array
    default:
      return $array;
  }
}

 
function db_get_index($tablename, $id, $idfield = "id")
{
  $data = db_get_data($tablename, null, $idfield);
  return array_search($id, $data);
}

 
function db_get_id($tablename, $index, $idfield = "id")
{
  global $g_lasttable;
 
  if (!is_scalar($index)) {
    trigger_error(__FUNCTION__ . ": index must be an integer");
    return false;
  } else
    $index = (int)$index;
 
  if (!$tablename)
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
 
  // Do the query
  $sql = "SELECT $idfield FROM " . APPPREFIX . $tablename . " LIMIT 1 OFFSET $index";
 
  $result = raw_db_query($sql);
  if (!$result)
    return false;
 
  $ret = db_fetch_array($result, FETCH_NUM);
 
  if (db_free_result($result) === false)
    return false;
 
  return $ret[0];
}

function db_get_next_free_id($tablename, $idfield = "id")
{
  global $g_current_db;
  global $g_lasttable;
 
  if (!$tablename)
    $tablename = $g_lasttable;
  $g_lasttable = $tablename;
 
  $sql = "SELECT max($idfield) FROM " . APPPREFIX . $tablename;
  $result = raw_db_query($sql);
  if (!$result) {
    return false;
  }
  $maxid = (db_fetch_array($result, FETCH_NUM)) ;
 
  return $maxid[0] + 1;
}
// ---------------------------------------------------------------- SQL FUNCTIONS
 
function db_query($query)
{
  return raw_db_query($query);
}

function db_fetch_array($result, $type = FETCH_NUM)
{
 
  return raw_db_fetch_array($result, $type);
}

function db_free_result($result)
{
  return raw_db_free_result($result);
}

function db_escape_string($str)
{
 
  return raw_db_escape_string($str);
}
// ------------------------------------------------------------------ END OF FILE
 
?>