1rQdutMd

1<?php
2
3
4
5require_once 'queries.php';
6try{
7    $pdo = new PDO('pgsql:host=localhost;dbname=postgres ');
8    $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
9    $result = $pdo->exec(SQL_CREATE_MENU_TABLE);
10    var_dump($result);
11    for ($i = 1;$i<=3;$i++){
12        $sql = " INSERT INTO menu(title,url) VALUES ('Post #$i','/post/$i')";
13        $result = $pdo->exec($sql);
14        var_dump($result);
15    }
16
17}catch (PDOException $e) {
18    exit($e->getMessage());
19
20}
21
22
23
24
25
26
27
28
29
30
312 file
32
33
34
35<?php
36const SQL_CREATE_MENU_TABLE = '
37    CREATE TABLE IF NOT EXISTS menu (
38    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
39    parent_id INT UNSIGNED,
40    title VARCHAR(255) NOT NULL,
41    url VARCHAR(500) NOT NULL,
42    PRIMARY KEY (id)
43    )
44    ';
45
46
47
48
49const SQL_INSERT_MENU_ITEM = '
50    INSERT INTO menu(parent_id, title,url) VALUES (?,?,?)
51    ';
52
53
54const SQL_UPDATE_MENU_ITEM_BY_ID = '
55    UPDATE menu SET
56    parent_id = :parent_id,
57    title = :title,
58    url = :url
59    WHERE 
60    id = :id
61    ';
62
63
64const SQL_GET_MENU = '
65    SELECT id,parent_id,title, url FROM menu
66    ';
67
68
69const SQL_GET_MENU_ITEM = '
70    SELECT id,parent_id,title, url FROM menu WHERE id = :id
71    ';