2 /************************************************************************
3 * Mailer v0.2.1-FINAL Start: 10/23/2009 *
4 * =================== Last change: 10/23/2009 *
6 * -------------------------------------------------------------------- *
7 * File : sql-functions.php *
8 * -------------------------------------------------------------------- *
9 * Short description : SQL functions to handle queries *
10 * -------------------------------------------------------------------- *
11 * Kurzbeschreibung : SQL-Funktionen fuer Queries *
12 * -------------------------------------------------------------------- *
15 * $Tag:: 0.2.1-FINAL $ *
17 * -------------------------------------------------------------------- *
18 * Copyright (c) 2003 - 2009 by Roland Haeder *
19 * Copyright (c) 2009 - 2012 by Mailer Developer Team *
20 * For more information visit: http://mxchange.org *
22 * This program is free software; you can redistribute it and/or modify *
23 * it under the terms of the GNU General Public License as published by *
24 * the Free Software Foundation; either version 2 of the License, or *
25 * (at your option) any later version. *
27 * This program is distributed in the hope that it will be useful, *
28 * but WITHOUT ANY WARRANTY; without even the implied warranty of *
29 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the *
30 * GNU General Public License for more details. *
32 * You should have received a copy of the GNU General Public License *
33 * along with this program; if not, write to the Free Software *
34 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, *
36 ************************************************************************/
38 // Some security stuff...
39 if (!defined('__SECURITY')) {
44 function initSqls () {
46 setSqlsArray(array('generic' => array()));
49 // Checks whether the sqls array is initialized
50 function isSqlsInitialized () {
51 return ((isset($GLOBALS['sqls'])) && (is_array($GLOBALS['sqls'])));
54 // Setter for SQLs array
55 function setSqlsArray ($SQLs) {
56 //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'count()='.count($SQLs));
57 $GLOBALS['sqls'] = (array) $SQLs;
60 // Remover for SQLs array
61 function unsetSqls () {
62 unset($GLOBALS['sqls']);
65 // Getter for SQLs array
67 return $GLOBALS['sqls'];
70 // Add an SQL to the list
71 function addSql ($sql) {
72 //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, sprintf("sql=%s, count=%d", $sql, countSqls()));
73 array_push($GLOBALS['sqls']['generic'], $sql);
76 // Merge SQLs together
77 function mergeSqls ($SQLs, $type = '') {
78 // Should we merge full array or partial?
80 // Merge full array (may kill entries)
81 setSqlsArray(merge_array(getSqls(), $SQLs));
83 // Merge sub array, so get it
86 // Is the sub array there?
87 if (isset($array[$type])) {
88 // Then get it and merge it with the new one
89 $array[$type] = merge_array($array[$type], $SQLs);
92 $array[$type] = $SQLs;
100 // Counter for SQLs array
101 function countSqls () {
105 // Is the array there?
106 if (isSqlsInitialized()) {
108 $count = count($GLOBALS['sqls']);
109 //* DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, sprintf("count=%d", $count));
116 // Checks whether the SQLs array is filled
117 function isSqlsValid () {
118 //* DEBUG: */ debugOutput(__FUNCTION__ . ':' . intval(isSqlsInitialized()) . '/' . countSqls() . '/' . getCurrentExtensionName());
128 // Generates an updating SQL query from given array
129 function getUpdateSqlFromArray ($array, $tableName, $whereColumn, $whereData, $excludedFields, $multiDimId = NULL) {
131 $SQL = 'UPDATE `{?_MYSQL_PREFIX?}_' . $tableName . '` SET ';
134 foreach ($array as $entry => $value) {
135 // Skip login/id entry
136 if (in_array($entry, $excludedFields)) {
140 // Is there a non-string (e.g. number, NULL, SQL function or back-tick at the beginning?
141 if (is_null($multiDimId)) {
142 // Handle one-dimensional data
143 if (is_null($value)) {
145 $SQL .= '`' . $entry . '`=NULL,';
146 } elseif ((substr($value, -2, 2) == '()') || (substr($value, 0, 1) == '`')) {
147 // SQL function needs no ticks (')
148 $SQL .= '`' . $entry . '`=' . SQL_ESCAPE($value) . ',';
149 } elseif ('' . bigintval($value, TRUE, FALSE) . '' == '' . $value . '') {
150 // No need for ticks (')
151 $SQL .= '`' . $entry . '`=' . $value . ',';
153 // Strings need ticks (') around them
154 $SQL .= '`' . $entry . "`='" . SQL_ESCAPE($value) . "',";
157 // Handle multi-dimensional data
158 if (is_null($value[$multiDimId])) {
160 $SQL .= '`' . $entry . '`=NULL,';
161 } elseif ((substr($value[$multiDimId], -2, 2) == '()') || (substr($value[$multiDimId], 0, 1) == '`')) {
162 // SQL function needs no ticks (')
163 $SQL .= '`' . $entry . '`=' . SQL_ESCAPE($value[$multiDimId]) . ',';
164 } elseif (('' . bigintval($value[$multiDimId], TRUE, FALSE) . '' == '' . $value[$multiDimId] . '')) {
165 // No need for ticks (')
166 $SQL .= '`' . $entry . '`=' . $value[$multiDimId] . ',';
168 // Strings need ticks (') around them
169 $SQL .= '`' . $entry . "`='" . SQL_ESCAPE($value[$multiDimId]) . "',";
174 // Remove last 2 chars and finish query
175 $SQL = substr($SQL, 0, -1) . ' WHERE `' . $whereColumn . '`=' . $whereData . ' LIMIT 1';
181 // "Getter" for an "INSERT INTO" SQL query
182 function getInsertSqlFromArray ($array, $tableName) {
185 `{?_MYSQL_PREFIX?}_' . $tableName . '`
187 `' . implode('`, `', array_keys(postRequestArray())) . '`
190 // Walk through all entries
191 foreach (postRequestArray() as $key => $value) {
193 //* NOISY-DEBUG: */ logDebugMessage(__FUNCTION__, __LINE__, 'tableName=' . $tableName . ',key=' . $key . ',value=' . $value);
196 if (is_null($value)) {
199 } elseif (substr($value, -2, 2) == '()') {
200 // SQL function needs no ticks (')
201 $SQL .= SQL_ESCAPE($value) . ',';
202 } elseif ('' . bigintval($value, TRUE, FALSE) . '' == '' . $value . '') {
203 // Number detected, no need for ticks (')
204 $SQL .= bigintval($value) . ',';
205 } elseif ('' . (float) $value . '' == '' . $value . '') {
206 // Float number detected
207 $SQL .= sprintf('%01.5f', $value);
209 // Everything else might be a string, so add ticks around it
210 $SQL .= chr(39) . SQL_ESCAPE($value) . chr(39) . ',';
215 $SQL = substr($SQL, 0, -1) . ')';