queries tellen
hoe kan ik het beste queries tellen?
ik wil mijn online software sneller maken
dus wil ik dat op 2 manieren doen
1 mijn sql indexes optimaliseren
2 minder queries, want ik heb er heeeeeeeeel veel :P
nu wil ik dus een klein scriptje schrijven waarmee ik de queries kan tellen op een bepaalde pagina
ik heb al iets gemaakt met SHOW SESSION STATUS en dit werkt opzich wel maar alleen als ik enigste ben die op dat moment in het systeem is
wat bijna nooit het geval is
dus zoek ik een goede manier om alle queries te tellen op een pagina
ideeën zijn welkom
Gewijzigd op 28/08/2019 13:33:32 door - Ariën -
$db->sql_query(
dus ik zou dat best daarin kunnen doen ergens :)
even over nadenken
Als jij een ellendige join met temp-tables nodig hebt en 15x een union erin moet zetten, dan zou dat best wel eens heel traag kunnen worden.
Een query in een loop is vaak wel iets dat beter kan. Maar ga niet voor de 1 query, puur vanwege "minder is beter"
en ik heb bv ook veel queries die in de while weer 10 andere opvragen en dat dan bv X 100 of sommige paginas x 10.000
op dit soort plekken wil ik het gaan versnellen ik heb vanwege jarenlang aanpassen van mijn script ook veel dubbele dingen erin staan wat dus gewoon zonde is van snelheid
ik heb een best wel hele snelle server dus veel verschil zal je niet merken
maar als elke query die ik verminder 00.1 sec minder kan maken dan ken dit al gauw op sommige paginas 5/10 seconden of meer schelen en dat vind ik wel veel
Queries in een while() moet je zoveel mogelijk vermijden. Hoe meer loopjes je hebt, hoe meer het exponentieel toeneemt.
Ik weet dat je logging ook met SQL-commando's kunt regelen, maar softwarematige toggles in PHP zijn ook handig. Je kunt dan ook specifieke querybatches makkelijker isoleren, bijvoorbeeld dus alle queries die in een enkele pagina-aanroep plaatsvinden.
- Ariën - op 28/08/2019 14:54:44:
Queries in een while() moet je zoveel mogelijk vermijden. Hoe meer loopjes je hebt, hoe meer het exponentieel toeneemt.
ja daarom wil ik dit dus aanpassen :P
die loops wil ik in geval dat dit mogelijk is aanpassen naar een join
maar ik wil dit pagina voor pagina doen en doormiddel van dat tellen en loadtime kan ik zien of het heeft gewerkt
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
class Database extends mysqli {
public $totalQueries = 0;
public function query($query)
{
$result = parent::query($query);
$this->totalQueries = $this->totalQueries+1;
if($this->error) {
echo "Error:".mysqli_error($this)." op lijn: ".mysqli_errno($this));
}
return $result;
}
public function totalQueries(){
return $this->totalQueries;
}
}
?>
class Database extends mysqli {
public $totalQueries = 0;
public function query($query)
{
$result = parent::query($query);
$this->totalQueries = $this->totalQueries+1;
if($this->error) {
echo "Error:".mysqli_error($this)." op lijn: ".mysqli_errno($this));
}
return $result;
}
public function totalQueries(){
return $this->totalQueries;
}
}
?>
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?php
define("DB_HOST","localhost");
define("DB_USER","pietje");
define("DB_PASS","93h31m");
define("DB_NAME","blah");
include "/classes/database.inc.php";
// Je connectie die je globaal ergens start. let op dat de class hier Database heet.
$db = new Database(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// Voer een query uit
$db->query("SELECT dit, dat FROM iets...");
echo "Uitgevoerde queries: ".$db->totalQueries;
?>
define("DB_HOST","localhost");
define("DB_USER","pietje");
define("DB_PASS","93h31m");
define("DB_NAME","blah");
include "/classes/database.inc.php";
// Je connectie die je globaal ergens start. let op dat de class hier Database heet.
$db = new Database(DB_HOST, DB_USER, DB_PASS, DB_NAME);
// Voer een query uit
$db->query("SELECT dit, dat FROM iets...");
echo "Uitgevoerde queries: ".$db->totalQueries;
?>
Nog mooier is een exception in plaats van de echo.
Gewijzigd op 28/08/2019 16:58:38 door - Ariën -
Thnx!
Maak ofwel $(this->)totalQueries protected (wat mogelijk de betere oplossing is, dit is geen variabele die je van buitenaf in zou moeten kunnen stellen, deze is "read only") of verwijder de methode zou ik zeggen.
Is al best wat code van een aantal jaar terug.
haha
ik heb even uitgezocht en volgens mij moet ik eigelijk all een functie hebben maar ik krijg hem niet werkent
dus ik zal hieronder even wat dingen neerzetten mischien kunnen jullie deze noob even helpen :P
ik gebruik in mijn software de dbal.php die ooit gemaakt is voor phpbb
dat is deze file
dbal.php
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
<?php
/**
*
* @package dbal
* @version $Id: dbal.php 10041 2009-08-21 21:47:19Z terrafrost $
* @copyright (c) 2005 phpBB Group
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/
/**
* @ignore
/**
* Database Abstraction Layer
* @package dbal
*/
class dbal
{
var $db_connect_id;
var $query_result;
var $return_on_error = false;
var $transaction = false;
var $sql_time = 0;
var $num_queries = array();
var $open_queries = array();
var $curtime = 0;
var $query_hold = '';
var $html_hold = '';
var $sql_report = '';
var $persistency = false;
var $user = '';
var $server = '';
var $dbname = '';
// Set to true if error triggered
var $sql_error_triggered = false;
// Holding the last sql query on sql error
var $sql_error_sql = '';
// Holding the error information - only populated if sql_error_triggered is set
var $sql_error_returned = array();
// Holding transaction count
var $transactions = 0;
// Supports multi inserts?
var $multi_insert = false;
/**
* Current sql layer
*/
var $sql_layer = '';
/**
* Wildcards for matching any (%) or exactly one (_) character within LIKE expressions
*/
var $any_char;
var $one_char;
/**
* Exact version of the DBAL, directly queried
*/
var $sql_server_version = false;
/**
* Constructor
*/
function dbal()
{
$this->num_queries = array(
'cached' => 0,
'normal' => 0,
'total' => 0,
);
// Fill default sql layer based on the class being called.
// This can be changed by the specified layer itself later if needed.
$this->sql_layer = substr(get_class($this), 5);
// Do not change this please! This variable is used to easy the use of it - and is hardcoded.
$this->any_char = chr(0) . '%';
$this->one_char = chr(0) . '_';
}
/**
* return on error or display error message
*/
function sql_return_on_error($fail = false)
{
$this->sql_error_triggered = false;
$this->sql_error_sql = '';
$this->return_on_error = $fail;
}
/**
* Return number of sql queries and cached sql queries used
*/
function sql_num_queries($cached = false)
{
return ($cached) ? $this->num_queries['cached'] : $this->num_queries['normal'];
}
/**
* Add to query count
*/
function sql_add_num_queries($cached = false)
{
$this->num_queries['cached'] += ($cached !== false) ? 1 : 0;
$this->num_queries['normal'] += ($cached !== false) ? 0 : 1;
$this->num_queries['total'] += 1;
}
/**
* DBAL garbage collection, close sql connection
*/
function sql_close()
{
if (!$this->db_connect_id)
{
return false;
}
if ($this->transaction)
{
do
{
$this->sql_transaction('commit');
}
while ($this->transaction);
}
foreach ($this->open_queries as $query_id)
{
$this->sql_freeresult($query_id);
}
// Connection closed correctly. Set db_connect_id to false to prevent errors
if ($result = $this->_sql_close())
{
$this->db_connect_id = false;
}
return $result;
}
/**
* Build LIMIT query
* Doing some validation here.
*/
function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
{
if (empty($query))
{
return false;
}
// Never use a negative total or offset
$total = ($total < 0) ? 0 : $total;
$offset = ($offset < 0) ? 0 : $offset;
return $this->_sql_query_limit($query, $total, $offset, $cache_ttl);
}
/**
* Fetch all rows
*/
function sql_fetchrowset($query_id = false)
{
if ($query_id === false)
{
$query_id = $this->query_result;
}
if ($query_id !== false)
{
$result = array();
while ($row = $this->sql_fetchrow($query_id))
{
$result[] = $row;
}
return $result;
}
return false;
}
/**
* Fetch field
* if rownum is false, the current row is used, else it is pointing to the row (zero-based)
*/
function sql_fetchfield($field, $rownum = false, $query_id = false)
{
global $cache;
if ($query_id === false)
{
$query_id = $this->query_result;
}
if ($query_id !== false)
{
if ($rownum !== false)
{
$this->sql_rowseek($rownum, $query_id);
}
if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
{
return $cache->sql_fetchfield($query_id, $field);
}
$row = $this->sql_fetchrow($query_id);
return (isset($row[$field])) ? $row[$field] : false;
}
return false;
}
/**
* Correctly adjust LIKE expression for special characters
* Some DBMS are handling them in a different way
*
* @param string $expression The expression to use. Every wildcard is escaped, except $this->any_char and $this->one_char
* @return string LIKE expression including the keyword!
*/
function sql_like_expression($expression)
{
$expression = utf8_str_replace(array('_', '%'), array("\_", "\%"), $expression);
$expression = utf8_str_replace(array(chr(0) . "\_", chr(0) . "\%"), array('_', '%'), $expression);
return $this->_sql_like_expression('LIKE \'' . $this->sql_escape($expression) . '\'');
}
/**
* SQL Transaction
* @access private
*/
function sql_transaction($status = 'begin')
{
switch ($status)
{
case 'begin':
// If we are within a transaction we will not open another one, but enclose the current one to not loose data (prevening auto commit)
if ($this->transaction)
{
$this->transactions++;
return true;
}
$result = $this->_sql_transaction('begin');
if (!$result)
{
$this->sql_error();
}
$this->transaction = true;
break;
case 'commit':
// If there was a previously opened transaction we do not commit yet... but count back the number of inner transactions
if ($this->transaction && $this->transactions)
{
$this->transactions--;
return true;
}
// Check if there is a transaction (no transaction can happen if there was an error, with a combined rollback and error returning enabled)
// This implies we have transaction always set for autocommit db's
if (!$this->transaction)
{
return false;
}
$result = $this->_sql_transaction('commit');
if (!$result)
{
$this->sql_error();
}
$this->transaction = false;
$this->transactions = 0;
break;
case 'rollback':
$result = $this->_sql_transaction('rollback');
$this->transaction = false;
$this->transactions = 0;
break;
default:
$result = $this->_sql_transaction($status);
break;
}
return $result;
}
/**
* Build sql statement from array for insert/update/select statements
*
* Idea for this from Ikonboard
* Possible query values: INSERT, INSERT_SELECT, UPDATE, SELECT
*
*/
function sql_build_array($query, $assoc_ary = false)
{
if (!is_array($assoc_ary))
{
return false;
}
$fields = $values = array();
if ($query == 'INSERT' || $query == 'INSERT_SELECT')
{
foreach ($assoc_ary as $key => $var)
{
$fields[] = $key;
if (is_array($var) && is_string($var[0]))
{
// This is used for INSERT_SELECT(s)
$values[] = $var[0];
}
else
{
$values[] = $this->_sql_validate_value($var);
}
}
$query = ($query == 'INSERT') ? ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')' : ' (' . implode(', ', $fields) . ') SELECT ' . implode(', ', $values) . ' ';
}
else if ($query == 'MULTI_INSERT')
{
trigger_error('The MULTI_INSERT query value is no longer supported. Please use sql_multi_insert() instead.', E_USER_ERROR);
}
else if ($query == 'UPDATE' || $query == 'SELECT')
{
$values = array();
foreach ($assoc_ary as $key => $var)
{
$values[] = "$key = " . $this->_sql_validate_value($var);
}
$query = implode(($query == 'UPDATE') ? ', ' : ' AND ', $values);
}
return $query;
}
/**
* Build IN or NOT IN sql comparison string, uses <> or = on single element
* arrays to improve comparison speed
*
* @access public
* @param string $field name of the sql column that shall be compared
* @param array $array array of values that are allowed (IN) or not allowed (NOT IN)
* @param bool $negate true for NOT IN (), false for IN () (default)
* @param bool $allow_empty_set If true, allow $array to be empty, this function will return 1=1 or 1=0 then. Default to false.
*/
function sql_in_set($field, $array, $negate = false, $allow_empty_set = false)
{
if (!sizeof($array))
{
if (!$allow_empty_set)
{
// Print the backtrace to help identifying the location of the problematic code
$this->sql_error('No values specified for SQL IN comparison');
}
else
{
// NOT IN () actually means everything so use a tautology
if ($negate)
{
return '1=1';
}
// IN () actually means nothing so use a contradiction
else
{
return '1=0';
}
}
}
if (!is_array($array))
{
$array = array($array);
}
if (sizeof($array) == 1)
{
@reset($array);
$var = current($array);
return $field . ($negate ? ' <> ' : ' = ') . $this->_sql_validate_value($var);
}
else
{
return $field . ($negate ? ' NOT IN ' : ' IN ') . '(' . implode(', ', array_map(array($this, '_sql_validate_value'), $array)) . ')';
}
}
/**
* Run binary AND operator on DB column.
* Results in sql statement: "{$column_name} & (1 << {$bit}) {$compare}"
*
* @param string $column_name The column name to use
* @param int $bit The value to use for the AND operator, will be converted to (1 << $bit). Is used by options, using the number schema... 0, 1, 2...29
* @param string $compare Any custom SQL code after the check (for example "= 0")
*/
function sql_bit_and($column_name, $bit, $compare = '')
{
if (method_exists($this, '_sql_bit_and'))
{
return $this->_sql_bit_and($column_name, $bit, $compare);
}
return $column_name . ' & ' . (1 << $bit) . (($compare) ? ' ' . $compare : '');
}
/**
* Run more than one insert statement.
*
* @param string $table table name to run the statements on
* @param array &$sql_ary multi-dimensional array holding the statement data.
*
* @return bool false if no statements were executed.
* @access public
*/
function sql_multi_insert($table, &$sql_ary)
{
if (!sizeof($sql_ary))
{
return false;
}
if ($this->multi_insert)
{
$ary = array();
foreach ($sql_ary as $id => $_sql_ary)
{
// If by accident the sql array is only one-dimensional we build a normal insert statement
if (!is_array($_sql_ary))
{
return $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $sql_ary));
}
$values = array();
foreach ($_sql_ary as $key => $var)
{
$values[] = $this->_sql_validate_value($var);
}
$ary[] = '(' . implode(', ', $values) . ')';
}
return $this->sql_query('INSERT INTO ' . $table . ' ' . ' (' . implode(', ', array_keys($sql_ary[0])) . ') VALUES ' . implode(', ', $ary));
}
else
{
foreach ($sql_ary as $ary)
{
if (!is_array($ary))
{
return false;
}
$result = $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $ary));
if (!$result)
{
return false;
}
}
}
return true;
}
/**
* Function for validating values
* @access private
*/
function _sql_validate_value($var)
{
if (is_null($var))
{
return 'NULL';
}
else if (is_string($var))
{
return "'" . $this->sql_escape($var) . "'";
}
else
{
return (is_bool($var)) ? intval($var) : $var;
}
}
/**
* Build sql statement from array for select and select distinct statements
*
* Possible query values: SELECT, SELECT_DISTINCT
*/
function sql_build_query($query, $array)
{
$sql = '';
switch ($query)
{
case 'SELECT':
case 'SELECT_DISTINCT';
$sql = str_replace('_', ' ', $query) . ' ' . $array['SELECT'] . ' FROM ';
// Build table array. We also build an alias array for later checks.
$table_array = $aliases = array();
$used_multi_alias = false;
foreach ($array['FROM'] as $table_name => $alias)
{
if (is_array($alias))
{
$used_multi_alias = true;
foreach ($alias as $multi_alias)
{
$table_array[] = $table_name . ' ' . $multi_alias;
$aliases[] = $multi_alias;
}
}
else
{
$table_array[] = $table_name . ' ' . $alias;
$aliases[] = $alias;
}
}
// We run the following code to determine if we need to re-order the table array. ;)
// The reason for this is that for multi-aliased tables (two equal tables) in the FROM statement the last table need to match the first comparison.
// DBMS who rely on this: Oracle, PostgreSQL and MSSQL. For all other DBMS it makes absolutely no difference in which order the table is.
if (!empty($array['LEFT_JOIN']) && sizeof($array['FROM']) > 1 && $used_multi_alias !== false)
{
// Take first LEFT JOIN
$join = current($array['LEFT_JOIN']);
// Determine the table used there (even if there are more than one used, we only want to have one
preg_match('/(' . implode('|', $aliases) . ')\.[^\s]+/U', str_replace(array('(', ')', 'AND', 'OR', ' '), '', $join['ON']), $matches);
// If there is a first join match, we need to make sure the table order is correct
if (!empty($matches[1]))
{
$first_join_match = trim($matches[1]);
$table_array = $last = array();
foreach ($array['FROM'] as $table_name => $alias)
{
if (is_array($alias))
{
foreach ($alias as $multi_alias)
{
($multi_alias === $first_join_match) ? $last[] = $table_name . ' ' . $multi_alias : $table_array[] = $table_name . ' ' . $multi_alias;
}
}
else
{
($alias === $first_join_match) ? $last[] = $table_name . ' ' . $alias : $table_array[] = $table_name . ' ' . $alias;
}
}
$table_array = array_merge($table_array, $last);
}
}
$sql .= $this->_sql_custom_build('FROM', implode(', ', $table_array));
if (!empty($array['LEFT_JOIN']))
{
foreach ($array['LEFT_JOIN'] as $join)
{
$sql .= ' LEFT JOIN ' . key($join['FROM']) . ' ' . current($join['FROM']) . ' ON (' . $join['ON'] . ')';
}
}
if (!empty($array['WHERE']))
{
$sql .= ' WHERE ' . $this->_sql_custom_build('WHERE', $array['WHERE']);
}
if (!empty($array['GROUP_BY']))
{
$sql .= ' GROUP BY ' . $array['GROUP_BY'];
}
if (!empty($array['ORDER_BY']))
{
$sql .= ' ORDER BY ' . $array['ORDER_BY'];
}
break;
}
return $sql;
}
/**
* display sql error page
*/
function sql_error($sql = '')
{
global $auth, $user, $config;
// Set var to retrieve errored status
$this->sql_error_triggered = true;
$this->sql_error_sql = $sql;
$this->sql_error_returned = $this->_sql_error();
if ($this->transaction)
{
$this->sql_transaction('rollback');
}
return $this->sql_error_returned;
}
}
/**
* This variable holds the class name to use later
*/
$sql_db = (!empty($dbtype)) ? 'dbal_' . basename($dbtype) : 'dbal';
?>
/**
*
* @package dbal
* @version $Id: dbal.php 10041 2009-08-21 21:47:19Z terrafrost $
* @copyright (c) 2005 phpBB Group
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/
/**
* @ignore
/**
* Database Abstraction Layer
* @package dbal
*/
class dbal
{
var $db_connect_id;
var $query_result;
var $return_on_error = false;
var $transaction = false;
var $sql_time = 0;
var $num_queries = array();
var $open_queries = array();
var $curtime = 0;
var $query_hold = '';
var $html_hold = '';
var $sql_report = '';
var $persistency = false;
var $user = '';
var $server = '';
var $dbname = '';
// Set to true if error triggered
var $sql_error_triggered = false;
// Holding the last sql query on sql error
var $sql_error_sql = '';
// Holding the error information - only populated if sql_error_triggered is set
var $sql_error_returned = array();
// Holding transaction count
var $transactions = 0;
// Supports multi inserts?
var $multi_insert = false;
/**
* Current sql layer
*/
var $sql_layer = '';
/**
* Wildcards for matching any (%) or exactly one (_) character within LIKE expressions
*/
var $any_char;
var $one_char;
/**
* Exact version of the DBAL, directly queried
*/
var $sql_server_version = false;
/**
* Constructor
*/
function dbal()
{
$this->num_queries = array(
'cached' => 0,
'normal' => 0,
'total' => 0,
);
// Fill default sql layer based on the class being called.
// This can be changed by the specified layer itself later if needed.
$this->sql_layer = substr(get_class($this), 5);
// Do not change this please! This variable is used to easy the use of it - and is hardcoded.
$this->any_char = chr(0) . '%';
$this->one_char = chr(0) . '_';
}
/**
* return on error or display error message
*/
function sql_return_on_error($fail = false)
{
$this->sql_error_triggered = false;
$this->sql_error_sql = '';
$this->return_on_error = $fail;
}
/**
* Return number of sql queries and cached sql queries used
*/
function sql_num_queries($cached = false)
{
return ($cached) ? $this->num_queries['cached'] : $this->num_queries['normal'];
}
/**
* Add to query count
*/
function sql_add_num_queries($cached = false)
{
$this->num_queries['cached'] += ($cached !== false) ? 1 : 0;
$this->num_queries['normal'] += ($cached !== false) ? 0 : 1;
$this->num_queries['total'] += 1;
}
/**
* DBAL garbage collection, close sql connection
*/
function sql_close()
{
if (!$this->db_connect_id)
{
return false;
}
if ($this->transaction)
{
do
{
$this->sql_transaction('commit');
}
while ($this->transaction);
}
foreach ($this->open_queries as $query_id)
{
$this->sql_freeresult($query_id);
}
// Connection closed correctly. Set db_connect_id to false to prevent errors
if ($result = $this->_sql_close())
{
$this->db_connect_id = false;
}
return $result;
}
/**
* Build LIMIT query
* Doing some validation here.
*/
function sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
{
if (empty($query))
{
return false;
}
// Never use a negative total or offset
$total = ($total < 0) ? 0 : $total;
$offset = ($offset < 0) ? 0 : $offset;
return $this->_sql_query_limit($query, $total, $offset, $cache_ttl);
}
/**
* Fetch all rows
*/
function sql_fetchrowset($query_id = false)
{
if ($query_id === false)
{
$query_id = $this->query_result;
}
if ($query_id !== false)
{
$result = array();
while ($row = $this->sql_fetchrow($query_id))
{
$result[] = $row;
}
return $result;
}
return false;
}
/**
* Fetch field
* if rownum is false, the current row is used, else it is pointing to the row (zero-based)
*/
function sql_fetchfield($field, $rownum = false, $query_id = false)
{
global $cache;
if ($query_id === false)
{
$query_id = $this->query_result;
}
if ($query_id !== false)
{
if ($rownum !== false)
{
$this->sql_rowseek($rownum, $query_id);
}
if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
{
return $cache->sql_fetchfield($query_id, $field);
}
$row = $this->sql_fetchrow($query_id);
return (isset($row[$field])) ? $row[$field] : false;
}
return false;
}
/**
* Correctly adjust LIKE expression for special characters
* Some DBMS are handling them in a different way
*
* @param string $expression The expression to use. Every wildcard is escaped, except $this->any_char and $this->one_char
* @return string LIKE expression including the keyword!
*/
function sql_like_expression($expression)
{
$expression = utf8_str_replace(array('_', '%'), array("\_", "\%"), $expression);
$expression = utf8_str_replace(array(chr(0) . "\_", chr(0) . "\%"), array('_', '%'), $expression);
return $this->_sql_like_expression('LIKE \'' . $this->sql_escape($expression) . '\'');
}
/**
* SQL Transaction
* @access private
*/
function sql_transaction($status = 'begin')
{
switch ($status)
{
case 'begin':
// If we are within a transaction we will not open another one, but enclose the current one to not loose data (prevening auto commit)
if ($this->transaction)
{
$this->transactions++;
return true;
}
$result = $this->_sql_transaction('begin');
if (!$result)
{
$this->sql_error();
}
$this->transaction = true;
break;
case 'commit':
// If there was a previously opened transaction we do not commit yet... but count back the number of inner transactions
if ($this->transaction && $this->transactions)
{
$this->transactions--;
return true;
}
// Check if there is a transaction (no transaction can happen if there was an error, with a combined rollback and error returning enabled)
// This implies we have transaction always set for autocommit db's
if (!$this->transaction)
{
return false;
}
$result = $this->_sql_transaction('commit');
if (!$result)
{
$this->sql_error();
}
$this->transaction = false;
$this->transactions = 0;
break;
case 'rollback':
$result = $this->_sql_transaction('rollback');
$this->transaction = false;
$this->transactions = 0;
break;
default:
$result = $this->_sql_transaction($status);
break;
}
return $result;
}
/**
* Build sql statement from array for insert/update/select statements
*
* Idea for this from Ikonboard
* Possible query values: INSERT, INSERT_SELECT, UPDATE, SELECT
*
*/
function sql_build_array($query, $assoc_ary = false)
{
if (!is_array($assoc_ary))
{
return false;
}
$fields = $values = array();
if ($query == 'INSERT' || $query == 'INSERT_SELECT')
{
foreach ($assoc_ary as $key => $var)
{
$fields[] = $key;
if (is_array($var) && is_string($var[0]))
{
// This is used for INSERT_SELECT(s)
$values[] = $var[0];
}
else
{
$values[] = $this->_sql_validate_value($var);
}
}
$query = ($query == 'INSERT') ? ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')' : ' (' . implode(', ', $fields) . ') SELECT ' . implode(', ', $values) . ' ';
}
else if ($query == 'MULTI_INSERT')
{
trigger_error('The MULTI_INSERT query value is no longer supported. Please use sql_multi_insert() instead.', E_USER_ERROR);
}
else if ($query == 'UPDATE' || $query == 'SELECT')
{
$values = array();
foreach ($assoc_ary as $key => $var)
{
$values[] = "$key = " . $this->_sql_validate_value($var);
}
$query = implode(($query == 'UPDATE') ? ', ' : ' AND ', $values);
}
return $query;
}
/**
* Build IN or NOT IN sql comparison string, uses <> or = on single element
* arrays to improve comparison speed
*
* @access public
* @param string $field name of the sql column that shall be compared
* @param array $array array of values that are allowed (IN) or not allowed (NOT IN)
* @param bool $negate true for NOT IN (), false for IN () (default)
* @param bool $allow_empty_set If true, allow $array to be empty, this function will return 1=1 or 1=0 then. Default to false.
*/
function sql_in_set($field, $array, $negate = false, $allow_empty_set = false)
{
if (!sizeof($array))
{
if (!$allow_empty_set)
{
// Print the backtrace to help identifying the location of the problematic code
$this->sql_error('No values specified for SQL IN comparison');
}
else
{
// NOT IN () actually means everything so use a tautology
if ($negate)
{
return '1=1';
}
// IN () actually means nothing so use a contradiction
else
{
return '1=0';
}
}
}
if (!is_array($array))
{
$array = array($array);
}
if (sizeof($array) == 1)
{
@reset($array);
$var = current($array);
return $field . ($negate ? ' <> ' : ' = ') . $this->_sql_validate_value($var);
}
else
{
return $field . ($negate ? ' NOT IN ' : ' IN ') . '(' . implode(', ', array_map(array($this, '_sql_validate_value'), $array)) . ')';
}
}
/**
* Run binary AND operator on DB column.
* Results in sql statement: "{$column_name} & (1 << {$bit}) {$compare}"
*
* @param string $column_name The column name to use
* @param int $bit The value to use for the AND operator, will be converted to (1 << $bit). Is used by options, using the number schema... 0, 1, 2...29
* @param string $compare Any custom SQL code after the check (for example "= 0")
*/
function sql_bit_and($column_name, $bit, $compare = '')
{
if (method_exists($this, '_sql_bit_and'))
{
return $this->_sql_bit_and($column_name, $bit, $compare);
}
return $column_name . ' & ' . (1 << $bit) . (($compare) ? ' ' . $compare : '');
}
/**
* Run more than one insert statement.
*
* @param string $table table name to run the statements on
* @param array &$sql_ary multi-dimensional array holding the statement data.
*
* @return bool false if no statements were executed.
* @access public
*/
function sql_multi_insert($table, &$sql_ary)
{
if (!sizeof($sql_ary))
{
return false;
}
if ($this->multi_insert)
{
$ary = array();
foreach ($sql_ary as $id => $_sql_ary)
{
// If by accident the sql array is only one-dimensional we build a normal insert statement
if (!is_array($_sql_ary))
{
return $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $sql_ary));
}
$values = array();
foreach ($_sql_ary as $key => $var)
{
$values[] = $this->_sql_validate_value($var);
}
$ary[] = '(' . implode(', ', $values) . ')';
}
return $this->sql_query('INSERT INTO ' . $table . ' ' . ' (' . implode(', ', array_keys($sql_ary[0])) . ') VALUES ' . implode(', ', $ary));
}
else
{
foreach ($sql_ary as $ary)
{
if (!is_array($ary))
{
return false;
}
$result = $this->sql_query('INSERT INTO ' . $table . ' ' . $this->sql_build_array('INSERT', $ary));
if (!$result)
{
return false;
}
}
}
return true;
}
/**
* Function for validating values
* @access private
*/
function _sql_validate_value($var)
{
if (is_null($var))
{
return 'NULL';
}
else if (is_string($var))
{
return "'" . $this->sql_escape($var) . "'";
}
else
{
return (is_bool($var)) ? intval($var) : $var;
}
}
/**
* Build sql statement from array for select and select distinct statements
*
* Possible query values: SELECT, SELECT_DISTINCT
*/
function sql_build_query($query, $array)
{
$sql = '';
switch ($query)
{
case 'SELECT':
case 'SELECT_DISTINCT';
$sql = str_replace('_', ' ', $query) . ' ' . $array['SELECT'] . ' FROM ';
// Build table array. We also build an alias array for later checks.
$table_array = $aliases = array();
$used_multi_alias = false;
foreach ($array['FROM'] as $table_name => $alias)
{
if (is_array($alias))
{
$used_multi_alias = true;
foreach ($alias as $multi_alias)
{
$table_array[] = $table_name . ' ' . $multi_alias;
$aliases[] = $multi_alias;
}
}
else
{
$table_array[] = $table_name . ' ' . $alias;
$aliases[] = $alias;
}
}
// We run the following code to determine if we need to re-order the table array. ;)
// The reason for this is that for multi-aliased tables (two equal tables) in the FROM statement the last table need to match the first comparison.
// DBMS who rely on this: Oracle, PostgreSQL and MSSQL. For all other DBMS it makes absolutely no difference in which order the table is.
if (!empty($array['LEFT_JOIN']) && sizeof($array['FROM']) > 1 && $used_multi_alias !== false)
{
// Take first LEFT JOIN
$join = current($array['LEFT_JOIN']);
// Determine the table used there (even if there are more than one used, we only want to have one
preg_match('/(' . implode('|', $aliases) . ')\.[^\s]+/U', str_replace(array('(', ')', 'AND', 'OR', ' '), '', $join['ON']), $matches);
// If there is a first join match, we need to make sure the table order is correct
if (!empty($matches[1]))
{
$first_join_match = trim($matches[1]);
$table_array = $last = array();
foreach ($array['FROM'] as $table_name => $alias)
{
if (is_array($alias))
{
foreach ($alias as $multi_alias)
{
($multi_alias === $first_join_match) ? $last[] = $table_name . ' ' . $multi_alias : $table_array[] = $table_name . ' ' . $multi_alias;
}
}
else
{
($alias === $first_join_match) ? $last[] = $table_name . ' ' . $alias : $table_array[] = $table_name . ' ' . $alias;
}
}
$table_array = array_merge($table_array, $last);
}
}
$sql .= $this->_sql_custom_build('FROM', implode(', ', $table_array));
if (!empty($array['LEFT_JOIN']))
{
foreach ($array['LEFT_JOIN'] as $join)
{
$sql .= ' LEFT JOIN ' . key($join['FROM']) . ' ' . current($join['FROM']) . ' ON (' . $join['ON'] . ')';
}
}
if (!empty($array['WHERE']))
{
$sql .= ' WHERE ' . $this->_sql_custom_build('WHERE', $array['WHERE']);
}
if (!empty($array['GROUP_BY']))
{
$sql .= ' GROUP BY ' . $array['GROUP_BY'];
}
if (!empty($array['ORDER_BY']))
{
$sql .= ' ORDER BY ' . $array['ORDER_BY'];
}
break;
}
return $sql;
}
/**
* display sql error page
*/
function sql_error($sql = '')
{
global $auth, $user, $config;
// Set var to retrieve errored status
$this->sql_error_triggered = true;
$this->sql_error_sql = $sql;
$this->sql_error_returned = $this->_sql_error();
if ($this->transaction)
{
$this->sql_transaction('rollback');
}
return $this->sql_error_returned;
}
}
/**
* This variable holds the class name to use later
*/
$sql_db = (!empty($dbtype)) ? 'dbal_' . basename($dbtype) : 'dbal';
?>
mijn queries vraag ik normaal op met
deze functie staat in deze file
mysqli.php
regel 144
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
<?php
/**
*
* @package dbal
* @version $Id: mysqli.php 8814 2008-09-04 12:01:47Z acydburn $
* @copyright (c) 2005 phpBB Group
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/
include_once('dbal.php');
/**
* MySQLi Database Abstraction Layer
* mysqli-extension has to be compiled with:
* MySQL 4.1+ or MySQL 5.0+
* @package dbal
*/
class dbal_mysqli extends dbal
{
var $multi_insert = true;
/**
* Connect to server
*/
function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false , $new_link = false)
{
$this->persistency = $persistency;
$this->user = $sqluser;
$this->server = $sqlserver;
$this->dbname = $database;
$port = (!$port) ? NULL : $port;
// Persistant connections not supported by the mysqli extension?
$this->db_connect_id = @mysqli_connect($this->server, $this->user, $sqlpassword, $this->dbname, $port);
if ($this->db_connect_id && $this->dbname != '')
{
@mysqli_query($this->db_connect_id, "SET NAMES 'utf8'");
// enforce strict mode on databases that support it
if (version_compare($this->sql_server_info(true), '5.0.2', '>='))
{
$result = @mysqli_query($this->db_connect_id, 'SELECT @@session.sql_mode AS sql_mode');
$row = @mysqli_fetch_assoc($result);
@mysqli_free_result($result);
$modes = array_map('trim', explode(',', $row['sql_mode']));
// TRADITIONAL includes STRICT_ALL_TABLES and STRICT_TRANS_TABLES
if (!in_array('TRADITIONAL', $modes))
{
if (!in_array('STRICT_ALL_TABLES', $modes))
{
$modes[] = 'STRICT_ALL_TABLES';
}
if (!in_array('STRICT_TRANS_TABLES', $modes))
{
$modes[] = 'STRICT_TRANS_TABLES';
}
}
$mode = implode(',', $modes);
@mysqli_query($this->db_connect_id, "SET SESSION sql_mode='{$mode}'");
}
return $this->db_connect_id;
}
return $this->sql_error('');
}
/**
* Version information about used database
* @param bool $raw if true, only return the fetched sql_server_version
* @return string sql server version
*/
function sql_server_info($raw = false)
{
global $cache;
if (empty($cache) || ($this->sql_server_version = $cache->get('mysqli_version')) === false)
{
$result = @mysqli_query($this->db_connect_id, 'SELECT VERSION() AS version');
$row = @mysqli_fetch_assoc($result);
@mysqli_free_result($result);
$this->sql_server_version = $row['version'];
if (!empty($cache))
{
$cache->put('mysqli_version', $this->sql_server_version);
}
}
return ($raw) ? $this->sql_server_version : 'MySQL(i) ' . $this->sql_server_version;
}
/**
* SQL Transaction
* @access private
*/
function _sql_transaction($status = 'begin')
{
switch ($status)
{
case 'begin':
return @mysqli_autocommit($this->db_connect_id, false);
break;
case 'commit':
$result = @mysqli_commit($this->db_connect_id);
@mysqli_autocommit($this->db_connect_id, true);
return $result;
break;
case 'rollback':
$result = @mysqli_rollback($this->db_connect_id);
@mysqli_autocommit($this->db_connect_id, true);
return $result;
break;
}
return true;
}
/**
* Base query method
*
* @param string $query Contains the SQL query which shall be executed
* @param int $cache_ttl Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
* @return mixed When casted to bool the returned value returns true on success and false on failure
*
* @access public
*/
function sql_query($query = '', $cache_ttl = 0)
{
if ($query != '')
{
global $cache;
// EXPLAIN only in extra debug mode
if (defined('DEBUG_EXTRA'))
{
$this->sql_report('start', $query);
}
$this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
$this->sql_add_num_queries($this->query_result);
if ($this->query_result === false)
{
if (($this->query_result = @mysqli_query($this->db_connect_id, $query)) === false)
{
$this->sql_error($query);
}
if (defined('DEBUG_EXTRA'))
{
$this->sql_report('stop', $query);
}
if ($cache_ttl && method_exists($cache, 'sql_save'))
{
$cache->sql_save($query, $this->query_result, $cache_ttl);
}
}
else if (defined('DEBUG_EXTRA'))
{
$this->sql_report('fromcache', $query);
}
}
else
{
return false;
}
return $this->query_result;
}
/**
* Build LIMIT query
*/
function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
{
$this->query_result = false;
// if $total is set to 0 we do not want to limit the number of rows
if ($total == 0)
{
// MySQL 4.1+ no longer supports -1 in limit queries
$total = '18446744073709551615';
}
$query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
return $this->sql_query($query, $cache_ttl);
}
/**
* Return number of affected rows
*/
function sql_affectedrows()
{
return ($this->db_connect_id) ? @mysqli_affected_rows($this->db_connect_id) : false;
}
/**
* Fetch current row
*/
function sql_fetchrow($query_id = false)
{
global $cache;
if ($query_id === false)
{
$query_id = $this->query_result;
}
if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
{
return $cache->sql_fetchrow($query_id);
}
return ($query_id !== false) ? @mysqli_fetch_assoc($query_id) : false;
}
/**
* Seek to given row number
* rownum is zero-based
*/
function sql_rowseek($rownum, &$query_id)
{
global $cache;
if ($query_id === false)
{
$query_id = $this->query_result;
}
if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
{
return $cache->sql_rowseek($rownum, $query_id);
}
return ($query_id !== false) ? @mysqli_data_seek($query_id, $rownum) : false;
}
/**
* Get last inserted id after insert statement
*/
function sql_nextid()
{
return ($this->db_connect_id) ? @mysqli_insert_id($this->db_connect_id) : false;
}
/**
* Free sql result
*/
function sql_freeresult($query_id = false)
{
global $cache;
if ($query_id === false)
{
$query_id = $this->query_result;
}
if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
{
return $cache->sql_freeresult($query_id);
}
return @mysqli_free_result($query_id);
}
/**
* Escape string used in sql query
*/
function sql_escape($msg)
{
return @mysqli_real_escape_string($this->db_connect_id, $msg);
}
/**
* Build LIKE expression
* @access private
*/
function _sql_like_expression($expression)
{
return $expression;
}
/**
* Build db-specific query data
* @access private
*/
function _sql_custom_build($stage, $data)
{
switch ($stage)
{
case 'FROM':
$data = '(' . $data . ')';
break;
}
return $data;
}
/**
* return sql error array
* @access private
*/
function _sql_error()
{
if (!$this->db_connect_id)
{
return array(
'message' => @mysqli_connect_error(),
'code' => @mysqli_connect_errno()
);
}
return array(
'message' => @mysqli_error($this->db_connect_id),
'code' => @mysqli_errno($this->db_connect_id)
);
}
/**
* Close sql connection
* @access private
*/
function _sql_close()
{
return @mysqli_close($this->db_connect_id);
}
/**
* Build db-specific report
* @access private
*/
function _sql_report($mode, $query = '')
{
static $test_prof;
// current detection method, might just switch to see the existance of INFORMATION_SCHEMA.PROFILING
if ($test_prof === null)
{
$test_prof = false;
if (strpos(mysqli_get_server_info($this->db_connect_id), 'community') !== false)
{
$ver = mysqli_get_server_version($this->db_connect_id);
if ($ver >= 50037 && $ver < 50100)
{
$test_prof = true;
}
}
}
switch ($mode)
{
case 'start':
$explain_query = $query;
if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
{
$explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
}
else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
{
$explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
}
if (preg_match('/^SELECT/', $explain_query))
{
$html_table = false;
// begin profiling
if ($test_prof)
{
@mysqli_query($this->db_connect_id, 'SET profiling = 1;');
}
if ($result = @mysqli_query($this->db_connect_id, "EXPLAIN $explain_query"))
{
while ($row = @mysqli_fetch_assoc($result))
{
$html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
}
}
@mysqli_free_result($result);
if ($html_table)
{
$this->html_hold .= '</table>';
}
if ($test_prof)
{
$html_table = false;
// get the last profile
if ($result = @mysqli_query($this->db_connect_id, 'SHOW PROFILE ALL;'))
{
$this->html_hold .= '<br />';
while ($row = @mysqli_fetch_assoc($result))
{
// make <unknown> HTML safe
if (!empty($row['Source_function']))
{
$row['Source_function'] = str_replace(array('<', '>'), array('<', '>'), $row['Source_function']);
}
// remove unsupported features
foreach ($row as $key => $val)
{
if ($val === null)
{
unset($row[$key]);
}
}
$html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
}
}
@mysqli_free_result($result);
if ($html_table)
{
$this->html_hold .= '</table>';
}
@mysqli_query($this->db_connect_id, 'SET profiling = 0;');
}
}
break;
case 'fromcache':
$endtime = explode(' ', microtime());
$endtime = $endtime[0] + $endtime[1];
$result = @mysqli_query($this->db_connect_id, $query);
while ($void = @mysqli_fetch_assoc($result))
{
// Take the time spent on parsing rows into account
}
@mysqli_free_result($result);
$splittime = explode(' ', microtime());
$splittime = $splittime[0] + $splittime[1];
$this->sql_report('record_fromcache', $query, $endtime, $splittime);
break;
}
}
}
?>
/**
*
* @package dbal
* @version $Id: mysqli.php 8814 2008-09-04 12:01:47Z acydburn $
* @copyright (c) 2005 phpBB Group
* @license http://opensource.org/licenses/gpl-license.php GNU Public License
*
*/
include_once('dbal.php');
/**
* MySQLi Database Abstraction Layer
* mysqli-extension has to be compiled with:
* MySQL 4.1+ or MySQL 5.0+
* @package dbal
*/
class dbal_mysqli extends dbal
{
var $multi_insert = true;
/**
* Connect to server
*/
function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false , $new_link = false)
{
$this->persistency = $persistency;
$this->user = $sqluser;
$this->server = $sqlserver;
$this->dbname = $database;
$port = (!$port) ? NULL : $port;
// Persistant connections not supported by the mysqli extension?
$this->db_connect_id = @mysqli_connect($this->server, $this->user, $sqlpassword, $this->dbname, $port);
if ($this->db_connect_id && $this->dbname != '')
{
@mysqli_query($this->db_connect_id, "SET NAMES 'utf8'");
// enforce strict mode on databases that support it
if (version_compare($this->sql_server_info(true), '5.0.2', '>='))
{
$result = @mysqli_query($this->db_connect_id, 'SELECT @@session.sql_mode AS sql_mode');
$row = @mysqli_fetch_assoc($result);
@mysqli_free_result($result);
$modes = array_map('trim', explode(',', $row['sql_mode']));
// TRADITIONAL includes STRICT_ALL_TABLES and STRICT_TRANS_TABLES
if (!in_array('TRADITIONAL', $modes))
{
if (!in_array('STRICT_ALL_TABLES', $modes))
{
$modes[] = 'STRICT_ALL_TABLES';
}
if (!in_array('STRICT_TRANS_TABLES', $modes))
{
$modes[] = 'STRICT_TRANS_TABLES';
}
}
$mode = implode(',', $modes);
@mysqli_query($this->db_connect_id, "SET SESSION sql_mode='{$mode}'");
}
return $this->db_connect_id;
}
return $this->sql_error('');
}
/**
* Version information about used database
* @param bool $raw if true, only return the fetched sql_server_version
* @return string sql server version
*/
function sql_server_info($raw = false)
{
global $cache;
if (empty($cache) || ($this->sql_server_version = $cache->get('mysqli_version')) === false)
{
$result = @mysqli_query($this->db_connect_id, 'SELECT VERSION() AS version');
$row = @mysqli_fetch_assoc($result);
@mysqli_free_result($result);
$this->sql_server_version = $row['version'];
if (!empty($cache))
{
$cache->put('mysqli_version', $this->sql_server_version);
}
}
return ($raw) ? $this->sql_server_version : 'MySQL(i) ' . $this->sql_server_version;
}
/**
* SQL Transaction
* @access private
*/
function _sql_transaction($status = 'begin')
{
switch ($status)
{
case 'begin':
return @mysqli_autocommit($this->db_connect_id, false);
break;
case 'commit':
$result = @mysqli_commit($this->db_connect_id);
@mysqli_autocommit($this->db_connect_id, true);
return $result;
break;
case 'rollback':
$result = @mysqli_rollback($this->db_connect_id);
@mysqli_autocommit($this->db_connect_id, true);
return $result;
break;
}
return true;
}
/**
* Base query method
*
* @param string $query Contains the SQL query which shall be executed
* @param int $cache_ttl Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
* @return mixed When casted to bool the returned value returns true on success and false on failure
*
* @access public
*/
function sql_query($query = '', $cache_ttl = 0)
{
if ($query != '')
{
global $cache;
// EXPLAIN only in extra debug mode
if (defined('DEBUG_EXTRA'))
{
$this->sql_report('start', $query);
}
$this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
$this->sql_add_num_queries($this->query_result);
if ($this->query_result === false)
{
if (($this->query_result = @mysqli_query($this->db_connect_id, $query)) === false)
{
$this->sql_error($query);
}
if (defined('DEBUG_EXTRA'))
{
$this->sql_report('stop', $query);
}
if ($cache_ttl && method_exists($cache, 'sql_save'))
{
$cache->sql_save($query, $this->query_result, $cache_ttl);
}
}
else if (defined('DEBUG_EXTRA'))
{
$this->sql_report('fromcache', $query);
}
}
else
{
return false;
}
return $this->query_result;
}
/**
* Build LIMIT query
*/
function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
{
$this->query_result = false;
// if $total is set to 0 we do not want to limit the number of rows
if ($total == 0)
{
// MySQL 4.1+ no longer supports -1 in limit queries
$total = '18446744073709551615';
}
$query .= "\n LIMIT " . ((!empty($offset)) ? $offset . ', ' . $total : $total);
return $this->sql_query($query, $cache_ttl);
}
/**
* Return number of affected rows
*/
function sql_affectedrows()
{
return ($this->db_connect_id) ? @mysqli_affected_rows($this->db_connect_id) : false;
}
/**
* Fetch current row
*/
function sql_fetchrow($query_id = false)
{
global $cache;
if ($query_id === false)
{
$query_id = $this->query_result;
}
if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
{
return $cache->sql_fetchrow($query_id);
}
return ($query_id !== false) ? @mysqli_fetch_assoc($query_id) : false;
}
/**
* Seek to given row number
* rownum is zero-based
*/
function sql_rowseek($rownum, &$query_id)
{
global $cache;
if ($query_id === false)
{
$query_id = $this->query_result;
}
if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
{
return $cache->sql_rowseek($rownum, $query_id);
}
return ($query_id !== false) ? @mysqli_data_seek($query_id, $rownum) : false;
}
/**
* Get last inserted id after insert statement
*/
function sql_nextid()
{
return ($this->db_connect_id) ? @mysqli_insert_id($this->db_connect_id) : false;
}
/**
* Free sql result
*/
function sql_freeresult($query_id = false)
{
global $cache;
if ($query_id === false)
{
$query_id = $this->query_result;
}
if (!is_object($query_id) && isset($cache->sql_rowset[$query_id]))
{
return $cache->sql_freeresult($query_id);
}
return @mysqli_free_result($query_id);
}
/**
* Escape string used in sql query
*/
function sql_escape($msg)
{
return @mysqli_real_escape_string($this->db_connect_id, $msg);
}
/**
* Build LIKE expression
* @access private
*/
function _sql_like_expression($expression)
{
return $expression;
}
/**
* Build db-specific query data
* @access private
*/
function _sql_custom_build($stage, $data)
{
switch ($stage)
{
case 'FROM':
$data = '(' . $data . ')';
break;
}
return $data;
}
/**
* return sql error array
* @access private
*/
function _sql_error()
{
if (!$this->db_connect_id)
{
return array(
'message' => @mysqli_connect_error(),
'code' => @mysqli_connect_errno()
);
}
return array(
'message' => @mysqli_error($this->db_connect_id),
'code' => @mysqli_errno($this->db_connect_id)
);
}
/**
* Close sql connection
* @access private
*/
function _sql_close()
{
return @mysqli_close($this->db_connect_id);
}
/**
* Build db-specific report
* @access private
*/
function _sql_report($mode, $query = '')
{
static $test_prof;
// current detection method, might just switch to see the existance of INFORMATION_SCHEMA.PROFILING
if ($test_prof === null)
{
$test_prof = false;
if (strpos(mysqli_get_server_info($this->db_connect_id), 'community') !== false)
{
$ver = mysqli_get_server_version($this->db_connect_id);
if ($ver >= 50037 && $ver < 50100)
{
$test_prof = true;
}
}
}
switch ($mode)
{
case 'start':
$explain_query = $query;
if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
{
$explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
}
else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
{
$explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
}
if (preg_match('/^SELECT/', $explain_query))
{
$html_table = false;
// begin profiling
if ($test_prof)
{
@mysqli_query($this->db_connect_id, 'SET profiling = 1;');
}
if ($result = @mysqli_query($this->db_connect_id, "EXPLAIN $explain_query"))
{
while ($row = @mysqli_fetch_assoc($result))
{
$html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
}
}
@mysqli_free_result($result);
if ($html_table)
{
$this->html_hold .= '</table>';
}
if ($test_prof)
{
$html_table = false;
// get the last profile
if ($result = @mysqli_query($this->db_connect_id, 'SHOW PROFILE ALL;'))
{
$this->html_hold .= '<br />';
while ($row = @mysqli_fetch_assoc($result))
{
// make <unknown> HTML safe
if (!empty($row['Source_function']))
{
$row['Source_function'] = str_replace(array('<', '>'), array('<', '>'), $row['Source_function']);
}
// remove unsupported features
foreach ($row as $key => $val)
{
if ($val === null)
{
unset($row[$key]);
}
}
$html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
}
}
@mysqli_free_result($result);
if ($html_table)
{
$this->html_hold .= '</table>';
}
@mysqli_query($this->db_connect_id, 'SET profiling = 0;');
}
}
break;
case 'fromcache':
$endtime = explode(' ', microtime());
$endtime = $endtime[0] + $endtime[1];
$result = @mysqli_query($this->db_connect_id, $query);
while ($void = @mysqli_fetch_assoc($result))
{
// Take the time spent on parsing rows into account
}
@mysqli_free_result($result);
$splittime = explode(' ', microtime());
$splittime = $splittime[0] + $splittime[1];
$this->sql_report('record_fromcache', $query, $endtime, $splittime);
break;
}
}
}
?>
ja ik weet het is een lap code maar ik denk ik kan beter de hele file neerzetten mischien zien jullie dingen die ik niet snap :P
Anyway:
- initialiseer een counter, en zet deze op 0
- Na elke uitgevoerde query, verhoog dit met 1, en sla het op als $this->totalQueries
- Maak een method (functie) die $this->totalQueries returned.
- Roep deze aan.
Dit zou een toegevoegde waarde kunnen hebben, maar als MySQL de enige database (het enige database-type) is die je gebruikt dan is dat in principe niet verplicht. Of je zou zelfs kunnen zeggen overbodig of onnodig complex of abstract.
Dan is deze code 10-11 jaar oud. In termen van programmacode is dat toch een beetje bejaard.
In de gauwigheid zie ik trouwens al één ding wat niet klopt. Op regel 47 van het tweede fragment staat:
Wat je hiermee doet is dat je je database-connectie vertelt dat alles wat je doet UTF-8 is of naar UTF-8 vertaald zou moeten worden. Maar PHP weet dit verder niet, omdat dit een afspraak is tussen de connectie en de database, maar niet tussen PHP en de database. Dit is ook de verkeerde manier om dit te doen want deze afspraak zou tot stand moeten komen via set_charset(). De manier waarop dit op dit moment in elkaar zit kan tot gevolg hebben dat escaping-functionaliteit via real_escape_string() (de methode sql_escape()) mogelijk niet goed werkt. En dat lijkt mij nogal gevaarlijk.
Mijn advies is dan ook, gebruik de bovenstaande code niet, maar gebruik in eerste instantie wat simpelers, zoals de (minimale) wrapper van @Ariën.
Slow Query Log in te schakelen. Dan zie je concreet waar de vertragingen precies zitten, zodat je de grootste problemen het eerst kunt aanpakken.
Hoewel het de boel vertraagt, zou je kunnen overwegen om tijdelijk de ja je hebt helemaal gelijk echter ben ik 10 jaar geleden begonnen met een ideetje van een online software
ik heb toen als basis inlog/registratie script userpie gebruikt
en in userpie zat dit alles in
aangezien alles redelijk goed draait en ik ook druk ben met andere dingen heb ik het eigelijk zo gelaten
nu wil ik eerst alles versnellen zodat mijn klanten tevreden zijn
en over een tijd langzaamaan een V3 maken van mijn software en dan gaan all die dingen die eigelijk niet nodig zijn er allemaal uit
probleem is dus dat ik opzich best wel goed ben in ouderwetse php maar het gedoe met classen en functies niet
:P
ik heb soms al moeite met een simpele foreach
ik heb alles op php gebied helemaal mezelf aangeleerd
ik ben echt nog van de meeste noobie manier, if dit dan dat maar if anders dan anders enz enz
ik denk dat ik bv wel een paar pagina's heb waar wel 30x een elseif instaat
en ik heb bv dus best veel while enzo in mijn script want heel vroeger ging dat allemaal op die manier
dus veel wat vaak voorgesteld wordt als oplossing is voor mij chinees en duurt soms even voordat ik het allemaal snap :P
ik denk dat ik die tel functie hierin moet zetten ergens
function sql_query(
want dat is mijn query deel
die bij elke query wordt aangeroepen
maar hoe precies moet ik dus nog even mijn hersenen voor laten kraken
en waarschijnlijk is dat iets heel simpels
:)
De werkwijze heb ik al min of meer uitgelegd. En op deze site staat ook een mooie tutorial over classes en methods.
ik heb nu in mijn mysqli.php
in de class dbal_mysqli extends dbal
{
bovenaan
public $totalQueries = 0;
neergezet
vervolgens heb ik
$this->totalQueries = $this->totalQueries+1;
in de function sql_query(
gezet
en dan een nieuwe functie onder deze gemaakt met
public function totalQueries(){
return $this->totalQueries;
}
en dit blijkt volgens mij te werken