tblmember.sql.php 1.86 KB
Newer Older
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
<?php

$sql["count_tblmember"] = "
SELECT 
    COUNT(member_id) AS total
FROM tbl_member
WHERE
    1 = 1
    --search--
";
$sql["get_tblmember"] = "
SELECT 
    a.member_id AS `id`,
    a.member_name AS `name`,
    a.member_job AS `job`,
    a.member_address AS `address`,
    a.insert_timestamp AS `insert_timestamp`,
    a.insert_user_id AS `insert_user_id`,
    a.update_timestamp AS `update_timestamp`,
    IFNULL(CONCAT(b.`user_real_name`,', ',DATE_FORMAT(a.update_timestamp,'%d %b %Y %H:%i')),CONCAT(b.`user_real_name`,', ',DATE_FORMAT(a.insert_timestamp,'%d %b %Y %H:%i'))) AS last_modified
FROM tbl_member a
LEFT JOIN gtfw_user b ON b.`user_id` = a.`insert_user_id`
WHERE
    1 = 1
    --search--
--limit--    
";
$sql["get_detail_tblmember"] = "
SELECT 
    a.member_id AS `id`,
    a.member_name AS `name`,
    a.member_job AS `job`,
    a.member_address AS `address`,
    a.insert_timestamp AS `insert_timestamp`,
    a.insert_user_id AS `insert_user_id`,
    a.update_timestamp AS `update_timestamp`,
    CONCAT(b.`user_real_name`,', ',DATE_FORMAT(a.update_timestamp,'%%d %%b %%Y %%H:%%i')) AS last_update,
    CONCAT(b.`user_real_name`,', ',DATE_FORMAT(a.insert_timestamp,'%%d %%b %%Y %%H:%%i')) AS last_insert
FROM tbl_member a
LEFT JOIN gtfw_user b ON b.`user_id` = a.`insert_user_id`
WHERE member_id = %d
";
$sql["insert_tblmember"] = "
INSERT INTO tbl_member
(
    member_name,
    member_job,
    member_address,
    insert_timestamp,
    insert_user_id
) VALUES (
    %s,
    %s,
    %s,
    NOW(),
    %s
)
";
$sql["update_tblmember"] = "
UPDATE tbl_member
SET 
    member_name = %s,
    member_job = %s,
    member_address = %s,
    update_timestamp = NOW()
WHERE member_id = %d
";
$sql["delete_tblmember"] = "
DELETE FROM tbl_member
WHERE member_id = %d
";
$sql["list_tblmember"] = "
SELECT
    member_id AS `id`,
    member_name AS `name`
FROM
    tbl_member
ORDER BY member_name
";