關於同時執行兩個排序 (MYSQL)


將測試的結果記錄一下,可能之後的東西也會用到…今次案子中預見的其中一個問題..可能方法上有更好的,但是懶去試了..用上就好.大至上網址後加 ?sk=plan 是可使用的算法,至於 index.php 的那個是測試 SQL 可行不,最後還是要用 ?sk=plan 自己的算法 = =..

不知道 MYSQL 可不可以直接執行此方法,但是在剛想一想,如果可以的話,那 DZ 等的不就應該可以更加好?但是記憶中的好像都是用緩存來達成?..懶得再去解碼了 = = ..用自己的分法算了..應該不會佔太多資源吧?

[2007-07-04 01:50] 修正置頂後沒按時置頂時間排序問題

SQL 文件 :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP TABLE IF EXISTS `data`;
CREATE TABLE IF NOT EXISTS `data` (
`id` tinyint(3) unsigned NOT NULL auto_increment,
`itemid` varchar(20) NOT NULL,
`displaydate` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

INSERT INTO `data` VALUES (1, 'Apple', 0);
INSERT INTO `data` VALUES (2, 'Banana', 0);
INSERT INTO `data` VALUES (3, 'Cat', 0);
INSERT INTO `data` VALUES (4, 'Egg', 0);
INSERT INTO `data` VALUES (5, 'foot', 1183480432);
INSERT INTO `data` VALUES (6, 'girl', 1183480401);
INSERT INTO `data` VALUES (7, 'Air', 0);

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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
<?php
set_time_limit(0);

Class MySQL {

function connect() {
mysql_connect("localhost","root","1218") or exit('can not connect mysql');
}

function select() {
mysql_select_db("test_orderby") or exit('can not select database');
}

function query($sql) {
return mysql_query($sql);
}

function fetch_array($query) {
return mysql_fetch_array($query);
}

function close() {
mysql_close();
}

}

Class SeekOrderBy {

function func_rand($length) {
$hash = '';
$chars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789abcdefghijklmnopqrstuvwxyz';
$max = strlen($chars) - 1;
mt_srand((double)microtime() * 1000000);
for($i = 0; $i < $length; $i++) {
$hash .= $chars[mt_rand(0, $max)];
}
return $hash;
}

function func_orderby($order_data, $column_sort) {
for ($i=0; $i<count($order_data); $i++) {
$lines_order[$order_data[$i][$column_sort]] = $order_data[$i];
}

@krsort($lines_order);
@reset($lines_order);

return $lines_order;
}

function page_main() {
echo "<form action='$_SERVER[PHP_SELF]?sk=add' method='post'>";
echo "<table>";
echo "<tr>";
echo " <td>ItemID : </td>";
echo " <td><input type='text' name='itemid' /> <input type='submit' value='add' /></td>";
echo "</tr>";
echo "<table>";
echo "</form>";

echo "<hr size='0' />";

$query = MySQL::query("SELECT * FROM data ORDER BY itemid ASC, displaydate DESC");

echo "<table>";
while($row=MySQL::fetch_array($query)) {
echo "<tr>";
echo " <td>".$row['itemid']."</td>";
echo " <td>".$row['displaydate']."</td>";
echo " <td><a href='$_SERVER[PHP_SELF]?sk=top&n=$row[id]'>[TOP]</a></td>";
echo "</tr>";
}
echo "<table>";
}

function page_plan() {

$query = MySQL::query("SELECT * FROM data ORDER BY itemid ASC");

$temp_1 = $temp_2 = array();
while($row=MySQL::fetch_array($query)) {
if (!empty($row['displaydate'])) {
$temp_1[] = array(
'id' => $row['id'],
'itemid' => $row['itemid'],
'displaydate' => $row['displaydate']
);
}else{
$temp_2[] = array(
'id' => $row['id'],
'itemid' => $row['itemid'],
'displaydate' => $row['displaydate']
);
}
$i++;
}

$temp = array_merge($this->func_orderby($temp_1, 'displaydate'), $temp_2);

foreach($temp as $k => $v) {
echo "<table>";
echo "<tr>";
echo " <td>".$v['itemid']."</td>";
echo " <td>".$v['displaydate']."</td>";
echo " <td><a href='$_SERVER[PHP_SELF]?sk=top&n=$v[id]'>[TOP]</a></td>";
echo "</tr>";
echo "<table>";
}

}

function page_add() {
MySQL::query("INSERT INTO data (itemid) VALUES ('$_POST[itemid]')");
header("Location:$_SERVER[PHP_SELF]");
}

function page_top() {
MySQL::query("UPDATE data SET displaydate = '".time()."' WHERE id = '$_GET[n]'");
header("Location:$_SERVER[PHP_SELF]");
}

function show() {
MySQL::connect();
MySQL::select();
MySQL::query("SET NAMES 'utf8'");
switch($_GET['sk'] ? $_GET['sk'] : $_POST['sk']) {
case 'add':
$this->page_add();
break;

case 'top':
$this->page_top();
break;

case 'plan':
$this->page_plan();
break;

default:
$this->page_main();
break;
}
MySQL::close();
}

}

$o = new SeekOrderBy;
$o ->show();
?>