1.
<?php
2.
3.
/*
4.
{{title}}
5.
6.
Class for table : {{table}}.
7.
8.
{{datetime}}
9.
10.
{{author}}
11.
12.
{{licence}}
13.
14.
Usage examples :
15.
16.
<?php
17.
18.
include_once("classdir/dbconnect.php);
19.
include_once("classdir/class_{{table}}.php);
20.
21.
// Class instance
22.
$my{{table}} = {{table}}( $db );
23.
24.
// Select all reccords :
25.
$results = $my{{table}}->selectAll();
26.
foreach($results as $row) {
27.
print_r($row);
28.
}
29.
30.
// Select reccord #12 :
31.
${{idkeyname}} = 12;
32.
$row = $my{{table}}->select(${{idkeyname}});
33.
print_r($row);
34.
35.
?>
36.
37.
*/
38.
class _{{table}} {
39.
40.
public $db;
41.
public $lasterror="";
42.
public $lastInsertId=-1;
43.
public $count = 0;
44.
45.
/*
46.
Initialise object with dbHanler.
47.
48.
*/
49.
function __construct( $db ) {
50.
$this->db = $db;
51.
}
52.
53.
/*
54.
Select one reccord from {{table}}.
55.
56.
parameters : $id (reccord id).
57.
return : associative array of reccord.
58.
*/
59.
function select(${{idkeyname}}) {
60.
61.
$this->lasterror="";
62.
63.
$sqlSt="
64.
{{tab}}{{tab}}select
65.
{{tab}}{{tab}}{{tab}}{{mainprefix}}.{{idkeyname}} as '{{mainprefix}}.{{idkeyname}}',
66.
%%{{tab}}{{tab}}{{tab}}{{mainprefix}}.{{field}} as '{{mainprefix}}.{{field}}'{{fieldselectsep}}
67.
%%
68.
%%ID:fkey {{tab}}{{tab}}{{tab}}{{fktableprefix}}.{{fkto}} as '{{fktableprefix}}.{{fkto}}'{{fksep}}
69.
%%
70.
{{tab}}{{tab}}from {{table}} {{mainprefix}}
71.
%%ID:fkjoin {{tab}}{{tab}}{{tab}}left outer join {{fktablename}} {{fkjointableprefix}} on {{fkjointableprefix}}.{{fkto}} = a.{{fkfrom}}
72.
%%
73.
{{tab}}{{tab}}where {{mainprefix}}.{{idkeyname}} = :{{idkeyname}};
74.
";
75.
76.
try {
77.
78.
$stmt = $this->db->prepare($sqlSt);
79.
$stmt->bindParam(':{{idkeyname}}', ${{idkeyname}}, PDO::PARAM_{{PHP_KEY_TYPE}});
80.
$stmt->execute();
81.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
82.
return $results;
83.
84.
} catch (Exception $e) {
85.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$idkeyname.')';
86.
return false;
87.
}
88.
}
89.
90.
function selectPrev(${{idkeyname}}) {
91.
92.
$this->lasterror="";
93.
94.
$sqlSt="
95.
{{tab}}{{tab}}select
96.
{{tab}}{{tab}}{{tab}}{{mainprefix}}.{{idkeyname}} as '{{mainprefix}}.{{idkeyname}}',
97.
%%{{tab}}{{tab}}{{tab}}{{mainprefix}}.{{field}} as '{{mainprefix}}.{{field}}'{{fieldselectsep}}
98.
%%
99.
%%ID:fkey {{tab}}{{tab}}{{tab}}{{fktableprefix}}.{{fkto}} as '{{fktableprefix}}.{{fkto}}'{{fksep}}
100.
%%
101.
{{tab}}{{tab}}from {{table}} {{mainprefix}}
102.
%%ID:fkjoin {{tab}}{{tab}}{{tab}}left outer join {{fktablename}} {{fkjointableprefix}} on {{fkjointableprefix}}.{{fkto}} = a.{{fkfrom}}
103.
%%
104.
{{tab}}{{tab}}where {{mainprefix}}.{{idkeyname}} = (SELECT {{idkeyname}} FROM {{table}} WHERE {{idkeyname}} < :{{idkeyname}} ORDER BY id DESC LIMIT 1) ;
105.
";
106.
107.
try {
108.
109.
$stmt = $this->db->prepare($sqlSt);
110.
$stmt->bindParam(':{{idkeyname}}', ${{idkeyname}}, PDO::PARAM_{{PHP_KEY_TYPE}});
111.
$stmt->execute();
112.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
113.
return $results;
114.
115.
} catch (Exception $e) {
116.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$idkeyname.')';
117.
return false;
118.
}
119.
}
120.
121.
122.
function selectNext(${{idkeyname}}) {
123.
124.
$this->lasterror="";
125.
126.
$sqlSt="
127.
{{tab}}{{tab}}select
128.
{{tab}}{{tab}}{{tab}}{{mainprefix}}.{{idkeyname}} as '{{mainprefix}}.{{idkeyname}}',
129.
%%{{tab}}{{tab}}{{tab}}{{mainprefix}}.{{field}} as '{{mainprefix}}.{{field}}'{{fieldselectsep}}
130.
%%
131.
%%ID:fkey {{tab}}{{tab}}{{tab}}{{fktableprefix}}.{{fkto}} as '{{fktableprefix}}.{{fkto}}'{{fksep}}
132.
%%
133.
{{tab}}{{tab}}from {{table}} {{mainprefix}}
134.
%%ID:fkjoin {{tab}}{{tab}}{{tab}}left outer join {{fktablename}} {{fkjointableprefix}} on {{fkjointableprefix}}.{{fkto}} = a.{{fkfrom}}
135.
%%
136.
{{tab}}{{tab}}where {{mainprefix}}.{{idkeyname}} = (SELECT {{idkeyname}} FROM {{table}} WHERE {{idkeyname}} > :{{idkeyname}} ORDER BY id ASC LIMIT 1) ;
137.
";
138.
139.
try {
140.
141.
$stmt = $this->db->prepare($sqlSt);
142.
$stmt->bindParam(':{{idkeyname}}', ${{idkeyname}}, PDO::PARAM_{{PHP_KEY_TYPE}});
143.
$stmt->execute();
144.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
145.
return $results;
146.
147.
} catch (Exception $e) {
148.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$idkeyname.')';
149.
return false;
150.
}
151.
}
152.
153.
/*
154.
Select all reccords from {{table}} (with default order).
155.
156.
parameters : None
157.
return : associative array of reccords.
158.
*/
159.
function selectAll() {
160.
161.
$this->lasterror="";
162.
163.
$sqlSt="
164.
{{tab}}{{tab}}select
165.
{{tab}}{{tab}}{{tab}}{{mainprefix}}.{{idkeyname}} as '{{mainprefix}}.{{idkeyname}}',
166.
%%{{tab}}{{tab}}{{tab}}{{mainprefix}}.{{field}} as '{{mainprefix}}.{{field}}'{{fieldselectsep}}
167.
%%
168.
%%ID:fkey {{tab}}{{tab}}{{tab}}{{fktableprefix}}.{{fkto}} as '{{fktableprefix}}.{{fkto}}'{{fksep}}
169.
%%
170.
{{tab}}{{tab}}from {{table}} {{mainprefix}}
171.
%%ID:fkjoin {{tab}}{{tab}}{{tab}}left outer join {{fktablename}} {{fkjointableprefix}} on {{fkjointableprefix}}.{{fkto}} = a.{{fkfrom}}
172.
%%
173.
{{tab}}{{tab}}{{orderby}};
174.
";
175.
176.
try {
177.
178.
$stmt = $this->db->prepare($sqlSt);
179.
$stmt->execute();
180.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
181.
return $results;
182.
183.
} catch (Exception $e) {
184.
$this->lasterror = $e->getMessage();
185.
$this->count = 0;
186.
return false;
187.
}
188.
}
189.
190.
/*
191.
Update {{table}}
192.
193.
parameters : ${{idkeyname}} (reccord id) and fields list values.
194.
return : True if no error.
195.
*/
196.
function update( ${{idkeyname}}, %%${{field}}{{fieldsep}}%% ) {
197.
198.
$this->lasterror="";
199.
200.
%%if (${{field}} == '') ${{field}} = null;
201.
%%
202.
203.
if ($this->checkValue( array(
204.
%%{{tab}}{{tab}}${{field}} => ''{{fieldsep}}
205.
%%
206.
) ) == false )
207.
{
208.
$this->lasterror="Check fields false";
209.
return false;
210.
}
211.
212.
$sqlSt="
213.
{{tab}}{{tab}}update {{table}} set
214.
%%{{tab}}{{tab}}{{tab}}{{field}} = :{{field}}{{fieldsep}}
215.
%%
216.
{{tab}}{{tab}}where {{idkeyname}} = :{{idkeyname}};
217.
";
218.
219.
try {
220.
221.
$stmt = $this->db->prepare($sqlSt);
222.
223.
$stmt->bindParam(':{{idkeyname}}', ${{idkeyname}}, PDO::PARAM_{{PHP_KEY_TYPE}});
224.
225.
%%{{tab}}{{tab}}{{tab}}$stmt->bindParam(':{{field}}', ${{field}}, PDO::PARAM_{{PHP_FIELD_TYPE}});
226.
%%
227.
$stmt->execute();
228.
229.
if ($stmt->rowCount() == 0) {
230.
$this->lasterror = 'Erreur Sql update '.' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."%%<b>{{field}}</b> = [${{field}}]{{fieldsep}}%%";
231.
232.
return false;
233.
}
234.
235.
return true;
236.
237.
} catch (Exception $e) {
238.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."%%<b>{{field}}</b> = [${{field}}]{{fieldsep}}%%";
239.
return false;
240.
}
241.
242.
}
243.
244.
/*
245.
Insert new reccord into {{table}}.
246.
247.
Parameters : fields values list.
248.
return : True if no error.
249.
*/
250.
function insert( %%${{field}}{{fieldsep}}%% ) {
251.
252.
$this->lasterror="";
253.
$this->lastInsertId = -1;
254.
255.
%%if (${{field}} == '') ${{field}} = null;
256.
%%
257.
258.
if ($this->checkValue( array(
259.
%%{{tab}}{{tab}}${{field}} => ''{{fieldsep}}
260.
%%
261.
) ) == false )
262.
{
263.
$this->lasterror="Check fields error";
264.
return false;
265.
}
266.
267.
$sqlSt="
268.
{{tab}}{{tab}}insert into {{table}} (
269.
%%{{tab}}{{tab}}{{field}}{{fieldsep}}
270.
%%
271.
{{tab}}{{tab}}{{tab}})
272.
{{tab}}{{tab}}values (
273.
%%{{tab}}{{tab}}:{{field}}{{fieldsep}}
274.
%%
275.
{{tab}}{{tab}}{{tab}});
276.
";
277.
278.
try {
279.
280.
$stmt = $this->db->prepare($sqlSt);
281.
282.
%%{{tab}}{{tab}}{{tab}}$stmt->bindParam(':{{field}}', ${{field}}, PDO::PARAM_{{PHP_FIELD_TYPE}});
283.
%%
284.
285.
$stmt->execute();
286.
$this->lastInsertId = $this->db->lastInsertId();
287.
288.
if ($stmt->rowCount() == 0) {
289.
$this->lasterror = 'Erreur Sql insert'.' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."%%<b>{{field}}</b> = [${{field}}]{{fieldsep}}%%";
290.
return false;
291.
}
292.
293.
return true;
294.
295.
} catch (Exception $e) {
296.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."%%<b>{{field}}</b> = [${{field}}]{{fieldsep}}%%";
297.
return false;
298.
}
299.
}
300.
301.
/*
302.
Delete one reccord from {{table}}.
303.
304.
parameters : $id (reccord id).
305.
return : True if no error.
306.
*/
307.
function delete(${{idkeyname}}) {
308.
309.
$this->lasterror="";
310.
311.
$sqlSt="
312.
{{tab}}{{tab}}delete from {{table}}
313.
{{tab}}{{tab}}where {{idkeyname}} = :{{idkeyname}};
314.
";
315.
316.
try {
317.
318.
$stmt = $this->db->prepare($sqlSt);
319.
$stmt->bindParam(':{{idkeyname}}', ${{idkeyname}}, PDO::PARAM_{{PHP_KEY_TYPE}});
320.
$stmt->execute();
321.
322.
if ($stmt->rowCount() == 0) {
323.
$this->lasterror = "Erreur Sql delete ".' (<b>Id</b> ='.${{idkeyname}}.')';
324.
return false;
325.
}
326.
327.
} catch (Exception $e) {
328.
$this->lasterror = $e->getMessage().' (<b>Id</b>='.$id.')';
329.
return false;
330.
}
331.
return true;
332.
}
333.
334.
/*
335.
Get id min, id max, record count from {{table}}
336.
337.
parameters : None
338.
return : count value or False in case of error
339.
*/
340.
function getCount() {
341.
342.
$this->lasterror="";
343.
344.
$sqlSt="
345.
{{tab}}{{tab}}select min({{idkeyname}}) as minid, max({{idkeyname}}) as maxid, count(*) as count
346.
{{tab}}{{tab}}from {{table}}
347.
";
348.
349.
try {
350.
351.
$stmt = $this->db->prepare($sqlSt);
352.
$stmt->execute();
353.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
354.
return $results[0];
355.
356.
} catch (Exception $e) {
357.
$this->lasterror = $e->getMessage();
358.
return array( 'minid' => -1, 'maxid' => -1, 'count' => -1);
359.
}
360.
361.
//return $this->count;
362.
return $results[0];
363.
}
364.
365.
366.
367.
368.
public $colmeta = [
369.
{{tab}}'{{mainprefix}}.{{idkeyname}}' => array (
370.
{{tab}}{{tab}}'metades' => "{{metades}}",
371.
{{tab}}{{tab}}'sortformat' => "{{sortformat}}",
372.
{{tab}}{{tab}}'listlen' => {{idlistlen}},
373.
{{tab}}{{tab}}'listlib' => '{{idkeyeditlib}}',
374.
{{tab}}{{tab}}'listlink' => '{{idkeylistlink}}',
375.
{{tab}}{{tab}}'selectlen' => {{idselectlen}},
376.
{{tab}}{{tab}}'selectlib' => '{{idkeyselectlib}}',
377.
{{tab}}{{tab}}'selectlink' => '{{idkeyselectlink}}',
378.
{{tab}}{{tab}}'selectdisplay' => '{{idselectdisplay}}',
379.
{{tab}}{{tab}}'selectdisplaywidth' => '{{idselectdisplaywidth}}',
380.
{{tab}}{{tab}}'selectdisplayheight' => '{{idselectdisplayheight}}',
381.
{{tab}}{{tab}}'listdisplay' => '{{idlistdisplay}}',
382.
{{tab}}{{tab}}'listdisplaywidth' => '{{iddisplaywidth}}',
383.
{{tab}}{{tab}}'listdisplayheight' => '{{iddisplayheight}}'
384.
385.
{{tab}}{{tab}}),
386.
%%{{tab}}'{{mainprefix}}.{{field}}' => array (
387.
{{tab}}{{tab}}'metades' => "{{metades}}",
388.
{{tab}}{{tab}}'sortformat' => "{{sortformat}}",
389.
{{tab}}{{tab}}'listlen' => {{fieldlistlen}},
390.
{{tab}}{{tab}}'listlib' => '{{listlib}}' ,
391.
{{tab}}{{tab}}'listlink' => '{{listlink}}',
392.
{{tab}}{{tab}}'selectlen' => {{selectlen}},
393.
{{tab}}{{tab}}'selectlib' => '{{selectlib}}' ,
394.
{{tab}}{{tab}}'selectlink' => '{{selectlink}}',
395.
{{tab}}{{tab}}'selectdisplay' => '{{selectdisplay}}',
396.
{{tab}}{{tab}}'selectdisplaywidth' => '{{selectdisplaywidth}}',
397.
{{tab}}{{tab}}'selectdisplayheight' => '{{selectdisplayheight}}',
398.
{{tab}}{{tab}}'listdisplay' => '{{listdisplay}}',
399.
{{tab}}{{tab}}'listdisplaywidth' => '{{listdisplaywidth}}',
400.
{{tab}}{{tab}}'listdisplayheight' => '{{listdisplayheight}}'
401.
){{fieldselectsep}}
402.
%%
403.
%%ID:fkey {{tab}}'{{fktableprefix}}.{{fkto}}' => array ( // {{fktablename}}
404.
{{tab}}{{tab}}'metades' => "{{metades}}",
405.
{{tab}}{{tab}}'sortformat' => "{{sortformat}}",
406.
{{tab}}{{tab}}'listlen' => {{fkfieldlistlen}},
407.
{{tab}}{{tab}}'listlib' => '{{fklistlib}}',
408.
{{tab}}{{tab}}'listlink' => '{{fklistlink}}' ,
409.
{{tab}}{{tab}}'selectlen' => {{fkfieldselectlen}},
410.
{{tab}}{{tab}}'selectlib' => '{{fkselectlib}}',
411.
{{tab}}{{tab}}'selectlink' => '{{fkselectlink}}',
412.
{{tab}}{{tab}}'selectdisplay' => '{{fkselectdisplay}}',
413.
{{tab}}{{tab}}'selectdisplaywidth' => '{{fkselectdisplaywidth}}',
414.
{{tab}}{{tab}}'selectdisplayheight' => '{{fkselectdisplayheight}}',
415.
{{tab}}{{tab}}'listdisplay' => '{{fklistdisplay}}',
416.
{{tab}}{{tab}}'listdisplaywidth' => '{{fklistdisplaywidth}}',
417.
{{tab}}{{tab}}'listdisplayheight' => '{{fklistdisplayheight}}'
418.
){{fksep}}
419.
%%
420.
{{tab}}];
421.
422.
/*
423.
Get field property value
424.
425.
Parameters : field name and property name.
426.
return : value of property.
427.
*/
428.
429.
function getMeta( $fieldkey , $metakey ) {
430.
return $this->colmeta [$fieldkey][$metakey];
431.
}
432.
433.
/*
434.
Get labels for list table header.
435.
436.
Parameters : None.
437.
return : String.
438.
*/
439.
440.
function getTableHeaderList() {
441.
442.
$labelsStr ='';
443.
444.
foreach ( $this->colmeta as $k => $v) {
445.
if ( $v['listlen'] > 0 )
446.
$labelsStr = $labelsStr.'<th title="'.$v['metades'].'" >'.$v['listlib'] .'</th>'."\n";
447.
}
448.
return $labelsStr;
449.
}
450.
451.
function getTableFieldLenList( $field ) {
452.
453.
return $this->colmeta[$field]['listlen'];
454.
}
455.
456.
function getTableFieldLinkList( $field ) {
457.
458.
return $this->colmeta[$field]['listlink'];
459.
}
460.
461.
/*
462.
Get labels for select table header.
463.
464.
Parameters : None.
465.
return : String.
466.
*/
467.
468.
function getTableHeaderSelect() {
469.
470.
$labelsStr ='';
471.
472.
foreach ( $this->colmeta as $k => $v) {
473.
if ( $v['selectlen'] > 0 )
474.
$labelsStr = $labelsStr.'<th title="'.$v['metades'].'" >'.$v['selectlib'] .'</th>'."\n";
475.
}
476.
return $labelsStr;
477.
}
478.
479.
function getTableFieldLenSelect( $field ) {
480.
481.
return $this->colmeta[$field]['selectlen'];
482.
}
483.
484.
function getTableFieldLinkSelect( $field ) {
485.
486.
return $this->colmeta[$field]['selectlink'];
487.
}
488.
489.
490.
/*
491.
Check user input values for {{table}}.
492.
493.
Parameters : array of fields with regexp paterns. ex: array( $nom =>'^(?!\s*$).+' , $icone => '')
494.
return : False if at last one regexp does not match.
495.
*/
496.
function checkValue( $arrayCheck ) {
497.
498.
$indexfield=1;
499.
foreach ( $arrayCheck as $value => $pattern) {
500.
501.
if ($pattern=='') continue;
502.
503.
$pattern='/'.$pattern.'/';
504.
505.
if (!preg_match($pattern, $value)) {
506.
$this->lasterror = "Erreur champ #".$indexfield. '. Valeur:['.$value.']';
507.
return false;
508.
}
509.
510.
$indexfield+=1;
511.
512.
}
513.
514.
return true;
515.
}
516.
517.
} // end class
518.
519.
?>
520.