recursive-dbdirlisting-pgsql
Gesponsorde koppelingen
PHP script bestanden
De Directories-tabel:
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
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
CREATE TABLE directories
(
id serial NOT NULL,
parent_id integer NULL,
name character varying(100) NOT NULL,
description text NOT NULL,
CONSTRAINT pk_categories PRIMARY KEY (id),
CONSTRAINT fk_directories_id FOREIGN KEY (parent_id)
REFERENCES directories (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
)
INSERT INTO
directories
(name, description)
VALUES
('Hoofd map', 'Dit is de hoofdmap');
INSERT INTO
directories
(parent_id, name, description)
VALUES
(0, 'Cat 1', 'cat1'),
(0, 'Cat 2', 'cat2'),
(1, 'Cat 3', 'cat3'),
(1, 'Cat 5', 'cat5'),
(3, 'Cat 4', 'cat4');
(
id serial NOT NULL,
parent_id integer NULL,
name character varying(100) NOT NULL,
description text NOT NULL,
CONSTRAINT pk_categories PRIMARY KEY (id),
CONSTRAINT fk_directories_id FOREIGN KEY (parent_id)
REFERENCES directories (id)
ON UPDATE RESTRICT
ON DELETE RESTRICT
)
INSERT INTO
directories
(name, description)
VALUES
('Hoofd map', 'Dit is de hoofdmap');
INSERT INTO
directories
(parent_id, name, description)
VALUES
(0, 'Cat 1', 'cat1'),
(0, 'Cat 2', 'cat2'),
(1, 'Cat 3', 'cat3'),
(1, 'Cat 5', 'cat5'),
(3, 'Cat 4', 'cat4');
(Even aangepast, dank je wel Jezpur)
Het script: RecursiveDBDirs.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
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
<?php
function RecursiveDBDirs($DB, $parent_id = -1)
{
/**
* Recursive Database Directorielisting
* Author: Jens
* Date: January 1th, 2009
* PHP Version: 5.1.0
*/
$aCats = $DB->fetchDB("
SELECT
id,
COALESCE(parent_id, '-1') AS my_parent_id,
name,
description
FROM
directories
WHERE
COALESCE(parent_id, '-1') = $1
ORDER BY
id
", array($parent_id));
if($aCats)
{
for($i = 0; $i < count($aCats); $i++)
{
echo '<div class="indent">';
echo 'Name: ' . $aCats[$i]['name'] . ' ID: ' . $aCats[$i]['id'] . ' Parent: ' . $aCats[$i]['parent_id'] . '<br />';
$aCatsCheck = $DB->fetchDB("
SELECT
*
FROM
directories
WHERE
parent_id = $1
ORDER BY
id
", array($aCats[$i]['id']));
if($aCatsCheck)
{
RecursiveDBDirs($DB, $aCats[$i]['id']);
}
echo '</div>';
}
}
}
?>
function RecursiveDBDirs($DB, $parent_id = -1)
{
/**
* Recursive Database Directorielisting
* Author: Jens
* Date: January 1th, 2009
* PHP Version: 5.1.0
*/
$aCats = $DB->fetchDB("
SELECT
id,
COALESCE(parent_id, '-1') AS my_parent_id,
name,
description
FROM
directories
WHERE
COALESCE(parent_id, '-1') = $1
ORDER BY
id
", array($parent_id));
if($aCats)
{
for($i = 0; $i < count($aCats); $i++)
{
echo '<div class="indent">';
echo 'Name: ' . $aCats[$i]['name'] . ' ID: ' . $aCats[$i]['id'] . ' Parent: ' . $aCats[$i]['parent_id'] . '<br />';
$aCatsCheck = $DB->fetchDB("
SELECT
*
FROM
directories
WHERE
parent_id = $1
ORDER BY
id
", array($aCats[$i]['id']));
if($aCatsCheck)
{
RecursiveDBDirs($DB, $aCats[$i]['id']);
}
echo '</div>';
}
}
}
?>
Het voorbeeldje: voorbeeld.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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Running through Database Directories</title>
<style>
div.indent {
margin-left: 15px;
}
</style>
</head>
<body>
<?php
require('pg.class.php');
require('RecursiveDBDirs.php');
$DB = new DB;
RecursiveDBDirs($DB);
?>
</body>
</html>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Running through Database Directories</title>
<style>
div.indent {
margin-left: 15px;
}
</style>
</head>
<body>
<?php
require('pg.class.php');
require('RecursiveDBDirs.php');
$DB = new DB;
RecursiveDBDirs($DB);
?>
</body>
</html>
De Database-class: pg.class.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
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
<?php
class DB
{
/**
* PostgreSQL Database Class
* Author: Jens, Inspired by Thomas Ponet, OpenTrivia
* Date: January 1th, 2009
* PHP Version: 5.1.0
*/
/**
* pgSQL settings
*/
var $connectionstring = "host=localhost port=5432 dbname=**** user=**** password=****";
var $connection;
/**
* Establish connection
* PHP Version 4
*/
function connectDB()
{
$this->connection = pg_connect($this->connectionstring) or die (pg_last_error()); // Connect
}
/**
* Close connection
* PHP Version 4
*/
function closeDB()
{
if($this->connection)
{
pg_close($this->connection); // Close connection
}
}
/**
* Execute query
* PHP Version 5.1.0
*/
function execDB($query, $array)
{
$results = pg_query_params($query, $array) or die (pg_last_error());
return $results;
}
/**
* Count rows query
* PHP Version 5.1.0
*/
function num_rowsDB($query, $array)
{
$count = pg_num_rows($this->execDB($query, $array));
return $count;
}
/**
* Fetch query
* PHP Version 4
*/
function fetchDB($query, $array)
{
$results = $this->execDB($query, $array);
if(pg_num_rows($results) > 0) // Check if there are results
{
$i = -1; // Set start array '-1'
while($row = pg_fetch_array($results))
{
$i++;
$fetch[$i] = $row;
}
return $fetch;
}
else // No results -> Return FALSE
{
return FALSE;
}
}
}
?>
class DB
{
/**
* PostgreSQL Database Class
* Author: Jens, Inspired by Thomas Ponet, OpenTrivia
* Date: January 1th, 2009
* PHP Version: 5.1.0
*/
/**
* pgSQL settings
*/
var $connectionstring = "host=localhost port=5432 dbname=**** user=**** password=****";
var $connection;
/**
* Establish connection
* PHP Version 4
*/
function connectDB()
{
$this->connection = pg_connect($this->connectionstring) or die (pg_last_error()); // Connect
}
/**
* Close connection
* PHP Version 4
*/
function closeDB()
{
if($this->connection)
{
pg_close($this->connection); // Close connection
}
}
/**
* Execute query
* PHP Version 5.1.0
*/
function execDB($query, $array)
{
$results = pg_query_params($query, $array) or die (pg_last_error());
return $results;
}
/**
* Count rows query
* PHP Version 5.1.0
*/
function num_rowsDB($query, $array)
{
$count = pg_num_rows($this->execDB($query, $array));
return $count;
}
/**
* Fetch query
* PHP Version 4
*/
function fetchDB($query, $array)
{
$results = $this->execDB($query, $array);
if(pg_num_rows($results) > 0) // Check if there are results
{
$i = -1; // Set start array '-1'
while($row = pg_fetch_array($results))
{
$i++;
$fetch[$i] = $row;
}
return $fetch;
}
else // No results -> Return FALSE
{
return FALSE;
}
}
}
?>
Verder zou ik willen zeggen dat opbouwende commentaar welkom is.
Jens