1.
<?php
2.
3.
/*
4.
Généalogik
5.
6.
Class for table : metadata.
7.
8.
07/03/2021 10:45
9.
10.
lvardon@laposte.net - 2021
11.
12.
Licence libre
13.
14.
Usage examples :
15.
16.
<?php
17.
18.
include_once("classdir/dbconnect.php);
19.
include_once("classdir/class_metadata.php);
20.
21.
// Class instance
22.
$mymetadata = metadata( $db );
23.
24.
// Select all reccords :
25.
$results = $mymetadata->selectAll();
26.
foreach($results as $row) {
27.
print_r($row);
28.
}
29.
30.
// Select reccord #12 :
31.
$id = 12;
32.
$row = $mymetadata->select($id);
33.
print_r($row);
34.
35.
?>
36.
37.
*/
38.
class _metadata {
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 metadata.
55.
56.
parameters : $id (reccord id).
57.
return : associative array of reccord.
58.
*/
59.
function select($id) {
60.
61.
$this->lasterror="";
62.
63.
$sqlSt="
64.
select
65.
a.id as 'a.id',
66.
a.name as 'a.name',
67.
a.code as 'a.code',
68.
a.value as 'a.value',
69.
a.type as 'a.type',
70.
a.description as 'a.description'
71.
72.
73.
from metadata a
74.
75.
where a.id = :id;
76.
";
77.
78.
try {
79.
80.
$stmt = $this->db->prepare($sqlSt);
81.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
82.
$stmt->execute();
83.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
84.
return $results;
85.
86.
} catch (Exception $e) {
87.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$idkeyname.')';
88.
return false;
89.
}
90.
}
91.
92.
function selectPrev($id) {
93.
94.
$this->lasterror="";
95.
96.
$sqlSt="
97.
select
98.
a.id as 'a.id',
99.
a.name as 'a.name',
100.
a.code as 'a.code',
101.
a.value as 'a.value',
102.
a.type as 'a.type',
103.
a.description as 'a.description'
104.
105.
106.
from metadata a
107.
108.
where a.id = (SELECT id FROM metadata WHERE id < :id ORDER BY id DESC LIMIT 1) ;
109.
";
110.
111.
try {
112.
113.
$stmt = $this->db->prepare($sqlSt);
114.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
115.
$stmt->execute();
116.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
117.
return $results;
118.
119.
} catch (Exception $e) {
120.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$idkeyname.')';
121.
return false;
122.
}
123.
}
124.
125.
126.
function selectNext($id) {
127.
128.
$this->lasterror="";
129.
130.
$sqlSt="
131.
select
132.
a.id as 'a.id',
133.
a.name as 'a.name',
134.
a.code as 'a.code',
135.
a.value as 'a.value',
136.
a.type as 'a.type',
137.
a.description as 'a.description'
138.
139.
140.
from metadata a
141.
142.
where a.id = (SELECT id FROM metadata WHERE id > :id ORDER BY id ASC LIMIT 1) ;
143.
";
144.
145.
try {
146.
147.
$stmt = $this->db->prepare($sqlSt);
148.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
149.
$stmt->execute();
150.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
151.
return $results;
152.
153.
} catch (Exception $e) {
154.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$idkeyname.')';
155.
return false;
156.
}
157.
}
158.
159.
/*
160.
Select all reccords from metadata (with default order).
161.
162.
parameters : None
163.
return : associative array of reccords.
164.
*/
165.
function selectAll() {
166.
167.
$this->lasterror="";
168.
169.
$sqlSt="
170.
select
171.
a.id as 'a.id',
172.
a.name as 'a.name',
173.
a.code as 'a.code',
174.
a.value as 'a.value',
175.
a.type as 'a.type',
176.
a.description as 'a.description'
177.
178.
179.
from metadata a
180.
181.
;
182.
";
183.
184.
try {
185.
186.
$stmt = $this->db->prepare($sqlSt);
187.
$stmt->execute();
188.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
189.
return $results;
190.
191.
} catch (Exception $e) {
192.
$this->lasterror = $e->getMessage();
193.
$this->count = 0;
194.
return false;
195.
}
196.
}
197.
198.
/*
199.
Update metadata
200.
201.
parameters : $id (reccord id) and fields list values.
202.
return : True if no error.
203.
*/
204.
function update( $id, $name,$code,$value,$type,$description ) {
205.
206.
$this->lasterror="";
207.
208.
if ($name == '') $name = null;
209.
if ($code == '') $code = null;
210.
if ($value == '') $value = null;
211.
if ($type == '') $type = null;
212.
if ($description == '') $description = null;
213.
214.
215.
if ($this->checkValue( array(
216.
$name => '',
217.
$code => '',
218.
$value => '',
219.
$type => '',
220.
$description => ''
221.
222.
) ) == false )
223.
{
224.
$this->lasterror="Check fields false";
225.
return false;
226.
}
227.
228.
$sqlSt="
229.
update metadata set
230.
name = :name,
231.
code = :code,
232.
value = :value,
233.
type = :type,
234.
description = :description
235.
236.
where id = :id;
237.
";
238.
239.
try {
240.
241.
$stmt = $this->db->prepare($sqlSt);
242.
243.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
244.
245.
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
246.
$stmt->bindParam(':code', $code, PDO::PARAM_STR);
247.
$stmt->bindParam(':value', $value, PDO::PARAM_STR);
248.
$stmt->bindParam(':type', $type, PDO::PARAM_STR);
249.
$stmt->bindParam(':description', $description, PDO::PARAM_STR);
250.
251.
$stmt->execute();
252.
253.
if ($stmt->rowCount() == 0) {
254.
$this->lasterror = 'Erreur Sql update '.' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."<b>name</b> = [$name],<b>code</b> = [$code],<b>value</b> = [$value],<b>type</b> = [$type],<b>description</b> = [$description]";
255.
256.
return false;
257.
}
258.
259.
return true;
260.
261.
} catch (Exception $e) {
262.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."<b>name</b> = [$name],<b>code</b> = [$code],<b>value</b> = [$value],<b>type</b> = [$type],<b>description</b> = [$description]";
263.
return false;
264.
}
265.
266.
}
267.
268.
/*
269.
Insert new reccord into metadata.
270.
271.
Parameters : fields values list.
272.
return : True if no error.
273.
*/
274.
function insert( $name,$code,$value,$type,$description ) {
275.
276.
$this->lasterror="";
277.
$this->lastInsertId = -1;
278.
279.
if ($name == '') $name = null;
280.
if ($code == '') $code = null;
281.
if ($value == '') $value = null;
282.
if ($type == '') $type = null;
283.
if ($description == '') $description = null;
284.
285.
286.
if ($this->checkValue( array(
287.
$name => '',
288.
$code => '',
289.
$value => '',
290.
$type => '',
291.
$description => ''
292.
293.
) ) == false )
294.
{
295.
$this->lasterror="Check fields error";
296.
return false;
297.
}
298.
299.
$sqlSt="
300.
insert into metadata (
301.
name,
302.
code,
303.
value,
304.
type,
305.
description
306.
307.
)
308.
values (
309.
:name,
310.
:code,
311.
:value,
312.
:type,
313.
:description
314.
315.
);
316.
";
317.
318.
try {
319.
320.
$stmt = $this->db->prepare($sqlSt);
321.
322.
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
323.
$stmt->bindParam(':code', $code, PDO::PARAM_STR);
324.
$stmt->bindParam(':value', $value, PDO::PARAM_STR);
325.
$stmt->bindParam(':type', $type, PDO::PARAM_STR);
326.
$stmt->bindParam(':description', $description, PDO::PARAM_STR);
327.
328.
329.
$stmt->execute();
330.
$this->lastInsertId = $this->db->lastInsertId();
331.
332.
if ($stmt->rowCount() == 0) {
333.
$this->lasterror = 'Erreur Sql insert'.' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."<b>name</b> = [$name],<b>code</b> = [$code],<b>value</b> = [$value],<b>type</b> = [$type],<b>description</b> = [$description]";
334.
return false;
335.
}
336.
337.
return true;
338.
339.
} catch (Exception $e) {
340.
$this->lasterror = $e->getMessage().' (<b>Id</b> = '.$id.'). <b>Sqlst</b> = ['.$sqlSt.'] values : '."<b>name</b> = [$name],<b>code</b> = [$code],<b>value</b> = [$value],<b>type</b> = [$type],<b>description</b> = [$description]";
341.
return false;
342.
}
343.
}
344.
345.
/*
346.
Delete one reccord from metadata.
347.
348.
parameters : $id (reccord id).
349.
return : True if no error.
350.
*/
351.
function delete($id) {
352.
353.
$this->lasterror="";
354.
355.
$sqlSt="
356.
delete from metadata
357.
where id = :id;
358.
";
359.
360.
try {
361.
362.
$stmt = $this->db->prepare($sqlSt);
363.
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
364.
$stmt->execute();
365.
366.
if ($stmt->rowCount() == 0) {
367.
$this->lasterror = "Erreur Sql delete ".' (<b>Id</b> ='.$id.')';
368.
return false;
369.
}
370.
371.
} catch (Exception $e) {
372.
$this->lasterror = $e->getMessage().' (<b>Id</b>='.$id.')';
373.
return false;
374.
}
375.
return true;
376.
}
377.
378.
/*
379.
Get id min, id max, record count from metadata
380.
381.
parameters : None
382.
return : count value or False in case of error
383.
*/
384.
function getCount() {
385.
386.
$this->lasterror="";
387.
388.
$sqlSt="
389.
select min(id) as minid, max(id) as maxid, count(*) as count
390.
from metadata
391.
";
392.
393.
try {
394.
395.
$stmt = $this->db->prepare($sqlSt);
396.
$stmt->execute();
397.
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
398.
return $results[0];
399.
400.
} catch (Exception $e) {
401.
$this->lasterror = $e->getMessage();
402.
return array( 'minid' => -1, 'maxid' => -1, 'count' => -1);
403.
}
404.
405.
//return $this->count;
406.
return $results[0];
407.
}
408.
409.
410.
411.
412.
public $colmeta = [
413.
'a.id' => array (
414.
'metades' => "Identifiant paramètre",
415.
'sortformat' => "{{sortformat}}",
416.
'listlen' => 4,
417.
'listlib' => 'id',
418.
'listlink' => 'on',
419.
'selectlen' => 4,
420.
'selectlib' => 'id',
421.
'selectlink' => '',
422.
'selectdisplay' => '',
423.
'selectdisplaywidth' => '0',
424.
'selectdisplayheight' => '0',
425.
'listdisplay' => '',
426.
'listdisplaywidth' => '0',
427.
'listdisplayheight' => '0'
428.
429.
),
430.
'a.name' => array (
431.
'metades' => "name",
432.
'sortformat' => "",
433.
'listlen' => 24,
434.
'listlib' => 'name' ,
435.
'listlink' => '',
436.
'selectlen' => 24,
437.
'selectlib' => 'name' ,
438.
'selectlink' => '',
439.
'selectdisplay' => 'text',
440.
'selectdisplaywidth' => '0',
441.
'selectdisplayheight' => '0',
442.
'listdisplay' => 'text',
443.
'listdisplaywidth' => '0',
444.
'listdisplayheight' => '0'
445.
),
446.
'a.code' => array (
447.
'metades' => "code",
448.
'sortformat' => "",
449.
'listlen' => 12,
450.
'listlib' => 'code' ,
451.
'listlink' => '',
452.
'selectlen' => 12,
453.
'selectlib' => 'code' ,
454.
'selectlink' => '',
455.
'selectdisplay' => 'text',
456.
'selectdisplaywidth' => '0',
457.
'selectdisplayheight' => '0',
458.
'listdisplay' => 'text',
459.
'listdisplaywidth' => '0',
460.
'listdisplayheight' => '0'
461.
),
462.
'a.value' => array (
463.
'metades' => "value",
464.
'sortformat' => "",
465.
'listlen' => 12,
466.
'listlib' => 'value' ,
467.
'listlink' => '',
468.
'selectlen' => 4,
469.
'selectlib' => 'value' ,
470.
'selectlink' => '',
471.
'selectdisplay' => 'text',
472.
'selectdisplaywidth' => '0',
473.
'selectdisplayheight' => '0',
474.
'listdisplay' => 'text',
475.
'listdisplaywidth' => '0',
476.
'listdisplayheight' => '0'
477.
),
478.
'a.type' => array (
479.
'metades' => "type",
480.
'sortformat' => "",
481.
'listlen' => 6,
482.
'listlib' => 'type' ,
483.
'listlink' => '',
484.
'selectlen' => 4,
485.
'selectlib' => 'type' ,
486.
'selectlink' => '',
487.
'selectdisplay' => 'text',
488.
'selectdisplaywidth' => '0',
489.
'selectdisplayheight' => '0',
490.
'listdisplay' => 'text',
491.
'listdisplaywidth' => '0',
492.
'listdisplayheight' => '0'
493.
),
494.
'a.description' => array (
495.
'metades' => "description",
496.
'sortformat' => "",
497.
'listlen' => 4,
498.
'listlib' => 'description' ,
499.
'listlink' => '',
500.
'selectlen' => 4,
501.
'selectlib' => 'description' ,
502.
'selectlink' => '',
503.
'selectdisplay' => 'text',
504.
'selectdisplaywidth' => '0',
505.
'selectdisplayheight' => '0',
506.
'listdisplay' => 'text',
507.
'listdisplaywidth' => '0',
508.
'listdisplayheight' => '0'
509.
)
510.
511.
512.
];
513.
514.
/*
515.
Get field property value
516.
517.
Parameters : field name and property name.
518.
return : value of property.
519.
*/
520.
521.
function getMeta( $fieldkey , $metakey ) {
522.
return $this->colmeta [$fieldkey][$metakey];
523.
}
524.
525.
/*
526.
Get labels for list table header.
527.
528.
Parameters : None.
529.
return : String.
530.
*/
531.
532.
function getTableHeaderList() {
533.
534.
$labelsStr ='';
535.
536.
foreach ( $this->colmeta as $k => $v) {
537.
if ( $v['listlen'] > 0 )
538.
$labelsStr = $labelsStr.'<th title="'.$v['metades'].'" >'.$v['listlib'] .'</th>'."\n";
539.
}
540.
return $labelsStr;
541.
}
542.
543.
function getTableFieldLenList( $field ) {
544.
545.
return $this->colmeta[$field]['listlen'];
546.
}
547.
548.
function getTableFieldLinkList( $field ) {
549.
550.
return $this->colmeta[$field]['listlink'];
551.
}
552.
553.
/*
554.
Get labels for select table header.
555.
556.
Parameters : None.
557.
return : String.
558.
*/
559.
560.
function getTableHeaderSelect() {
561.
562.
$labelsStr ='';
563.
564.
foreach ( $this->colmeta as $k => $v) {
565.
if ( $v['selectlen'] > 0 )
566.
$labelsStr = $labelsStr.'<th title="'.$v['metades'].'" >'.$v['selectlib'] .'</th>'."\n";
567.
}
568.
return $labelsStr;
569.
}
570.
571.
function getTableFieldLenSelect( $field ) {
572.
573.
return $this->colmeta[$field]['selectlen'];
574.
}
575.
576.
function getTableFieldLinkSelect( $field ) {
577.
578.
return $this->colmeta[$field]['selectlink'];
579.
}
580.
581.
582.
/*
583.
Check user input values for metadata.
584.
585.
Parameters : array of fields with regexp paterns. ex: array( $nom =>'^(?!\s*$).+' , $icone => '')
586.
return : False if at last one regexp does not match.
587.
*/
588.
function checkValue( $arrayCheck ) {
589.
590.
$indexfield=1;
591.
foreach ( $arrayCheck as $value => $pattern) {
592.
593.
if ($pattern=='') continue;
594.
595.
$pattern='/'.$pattern.'/';
596.
597.
if (!preg_match($pattern, $value)) {
598.
$this->lasterror = "Erreur champ #".$indexfield. '. Valeur:['.$value.']';
599.
return false;
600.
}
601.
602.
$indexfield+=1;
603.
604.
}
605.
606.
return true;
607.
}
608.
609.
} // end class
610.
611.
?>
612.