classes/database.inc

<?php

/**
 * *****************************************************************************
 * Copyright (c) 2017 Eclipse Foundation and others.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 * *****************************************************************************
 */

/**
 * This function provides a means of more-or-less completely abstracting the
 * database when executing a query (callers are still required to provide SQL
 * syntax in a form that the database provider understands).
 *
 * This function will execute the provided SQL as a query and then evaluate
 * the provided function with each of the rows that are returned from that
 * query. Rows are provided as an associative array with the column name as the
 * key.
 *
 * An array of substitutions can be provided. All values are escaped using
 * the database before they are substituted.
 *
 * All resources that are allocated by the function are closed before the
 * function exits.
 *
 * @param string $database
 *        The name of the database.
 * @param string $sql
 *        The query.
 * @param mixed $variables
 *        An associative array of variable substitutions.
 * @param callable $each
 *        A function that takes a single row as its only parameter.
 * @return void
 */
function query($database, $sql, $variables, $each) {
  global $App;
  if (function_exists('mysql_connect')) {
    $substitutions = array();
    if ($variables) {
     // Database connections are created lazily, so it's
     // possible that we may not have an actual database
     // connection when we try to call mysql_real_escape_string.
     // Call App::database to ensure that a connection exists.
     $App->database($database,'');
     foreach ($variables as $key => $value) {
        $substitutions[$key] = mysql_real_escape_string($value);
      }
    }

    if ($result = $App->sql(strtr($sql, $substitutions), $database)) {
      while ($row = mysql_fetch_assoc($result)) {
        $each($row);
      }
    }
    return;
  }

  // TODO this will only work in the development environment.
  $mysqli = new mysqli('localhost', 'dashboard', 'draobhsad', "{$database}_demo");
  if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
  }

  mysqli_set_charset ($mysqli , "utf8");

  $substitutions = array();
  if ($variables) {
    foreach ($variables as $key => $value) {
      $substitutions[$key] = $mysqli->escape_string($value);
    }
  }

  $statement = strtr($sql, $substitutions);
  if ($result = $mysqli->query($statement)) {
    while ($row = $result->fetch_assoc()) {
      $each($row);
    }
    $result->free();
  }
  else {
    printf("Query failed: %s\n", $mysqli->error);
  }

  $mysqli->close();
}
?>