· 8 years ago · Aug 27, 2018, 11:34 PM
1Dynamic SQL ordering help
2CREATE TABLE `questions` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `parent_id` int(10) NOT NULL,
5 `entry_type` varchar(8) NOT NULL,
6 `entry_content` varchar(1024) NOT NULL,
7 `entry_poster_id` varchar(10) NOT NULL,
8 `entry_status` varchar(1) NOT NULL,
9 `entry_score` varchar(10) NOT NULL,
10 `time_posted` varchar(10) NOT NULL,
11 PRIMARY KEY (`id`),
12 KEY `id` (`id`)
13) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
14
15--
16-- Dumping data for table `questions`
17--
18
19INSERT INTO `questions` VALUES(1, 1, 'question', 'How do I does SQL?', 'CodyC', '0', '2', '1308641965');
20INSERT INTO `questions` VALUES(2, 1, 'answer', 'Easy, you eat cheese!', 'PatrickS', '0', '-4', '1308641965');
21INSERT INTO `questions` VALUES(3, 2, 'comment', 'WTF are you on noobass?!', 'FraserK', '0', '100', '1308641965');
22INSERT INTO `questions` VALUES(4, 1, 'answer', 'blah', '5', '0', '0', '1308642204');
23INSERT INTO `questions` VALUES(5, 4, 'comment', 'blah2', '4', '0', '0', '1308642247');
24INSERT INTO `questions` VALUES(6, 2, '2', '3', '3', '3', '3', '3');
25
26SELECT *
27FROM questions
28WHERE parent_id =1
29OR parent_id
30IN (
31 SELECT id
32 FROM questions
33 WHERE parent_id =1
34 AND parent_id != id
35)
36
37SELECT *
38FROM questions
39order by case when parent_id != id then parent_id else id end, id;
40
41function output_lis_pages($parentID = 0)
42{
43 $stack = array(); //create a stack for our <li>'s
44
45 $arr = array();
46 $sql = "select pageid, pagetitle, pagelink, parentid
47 from pages
48 where parentid = $parentID
49 order by orderid";
50
51 $crs = mysql_query($sql);
52
53 if(mysql_num_rows($crs)==0)
54 {
55 // no child menu exists for this page
56 return false;
57 }
58 else
59 {
60 while($crow = mysql_fetch_array($crs))
61 {
62 $arr [] = array(
63 'pagetitle'=> stripslashes($crow["pagetitle"]),
64 'pagelink'=> $crow["pagelink"],
65 'parentid'=>$crow["parentid"],
66 'pageid'=>$crow["pageid"]
67 );
68 }
69 }
70
71 foreach($arr as $a)
72 {
73 $str = '';
74 //if the item's parent matches the parentID we're outputting...
75 if($a['parentid']==$parentID)
76 {
77 if($a['pagelink']=="")
78 $tmplink = "page.php?pageid=".$a['pageid'];
79 else
80 $tmplink = $a['pagelink'];
81
82
83 $str.='<li><a href="'.$tmplink.'">'.$a['pagetitle']."</a>";
84
85 $subStr = output_lis_pages($a['pageid']);
86
87 if($subStr){
88 $str.="n".'<ul>'.$subStr.'</ul>'."n";
89 }
90
91 $str.='</li>'."n";
92 $stack[] = $str;
93 }
94 }
95 //If we have <li>'s return a string
96 if(count($stack)>0)
97 {
98 return join("n",$stack);
99 }
100
101 //If no <li>'s in the stack, return false
102 return false;
103}
104
105SELECT *
106 , CASE WHEN parent_id = 1 THEN id ELSE parent_id END AS sort_level
107 FROM questions
108 WHERE parent_id = 1
109 OR parent_id
110 IN (
111 SELECT id
112 FROM questions
113 WHERE parent_id = 1
114 AND parent_id != id
115 )
116 ORDER BY sort_level
117 , id
118
119$question_id = $database->escape_string($question_id); //escape input
120$q = "SELECT * FROM questions WHERE parent_id = $question_id OR parent_id IN (SELECT id FROM questions WHERE parent_id = $question_id AND parent_id != id) ORDER BY parent_id , id";
121$database->dbquery($q);//query the DB
122while($row = $database->result->fetch_assoc()){//Process results to standard array.
123 //other irrelevant stuff happens here
124 $unsorted[] = $row;
125}
126$question = array_shift($unsorted);//take the question off the array
127$sorted[] = $question;//add it to the start of the sorted array
128$qusetion_id = $question['id'];
129foreach($unsorted as $row){//this creates a multidimensional hierarchy of the answers->comments
130 if($row['parent_id'] == $question_id){//if its an answer
131 $sorted_multi[$row['id']] = array();//create a new answer sub-array
132 $sorted_multi[$row['id']][] = $row;//append it
133 }else{
134 $sorted_multi[$row['parent_id']][] = $row;//append the answer to the correct sub-array
135 }
136}
137foreach($sorted_multi as $temp){//converts the multidimensional into a single dimension appending it to the sorted array.
138 foreach($temp as $row){
139 $sorted[] = $row;
140 }
141}
142
143SELECT `id`,
144(CASE
145 WHEN `entry_type` = 'question' THEN CONCAT(`id`, '-', `parent_id`)
146 WHEN `entry_type` = 'answer' THEN CONCAT(`id`, '-', `parent_id`)
147 WHEN `entry_type` = 'comment' THEN CONCAT(`parent_id`, '-', `id`)
148END) `sort_order`,
149`entry_type`, `entry_content`
150FROM `questions`
151ORDER BY `sort_order`;
152
153+----+------------+------------+--------------------------+
154| id | sort_order | entry_type | entry_content |
155+----+------------+------------+--------------------------+
156| 1 | 1-1 | question | How do I does SQL? |
157| 2 | 2-1 | answer | Easy, you eat cheese! |
158| 3 | 2-3 | comment | WTF are you on noobass?! |
159| 6 | 2-6 | comment | 3 |
160| 4 | 4-1 | answer | blah |
161| 5 | 4-5 | comment | blah2 |
162+----+------------+------------+--------------------------+
163
164SELECT `id`,
165(CASE
166 WHEN (`entry_type` IN ('question', 'answer')) THEN `id`
167 WHEN `entry_type` = 'comment' THEN `parent_id`
168END) `sort_order_1`,
169(CASE
170 WHEN (`entry_type` IN ('question', 'answer')) THEN `parent_id`
171 WHEN `entry_type` = 'comment' THEN `id`
172END) `sort_order_2`,
173(CASE
174 WHEN (`entry_type` IN ('question', 'answer')) THEN `parent_id`
175 WHEN `entry_type` = 'comment' THEN (SELECT `Q1`.`parent_id` FROM `questions` `Q1` WHERE `Q1`.`id` = `Q`.`parent_id`)
176END) `question_id`,
177`entry_type`, `entry_content`
178FROM `questions` `Q`
179HAVING `question_id` = 1
180ORDER BY `sort_order_1`, `sort_order_2`;
181
182+----+--------------+--------------+-------------+------------+--------------------------+
183| id | sort_order_1 | sort_order_2 | question_id | entry_type | entry_content |
184+----+--------------+--------------+-------------+------------+--------------------------+
185| 1 | 1 | 1 | 1 | question | How do I does SQL? |
186| 2 | 2 | 1 | 1 | answer | Easy, you eat cheese! |
187| 3 | 2 | 3 | 1 | comment | WTF are you on noobass?! |
188| 6 | 2 | 6 | 1 | comment | 3 |
189| 4 | 4 | 1 | 1 | answer | blah |
190| 5 | 4 | 5 | 1 | comment | blah2 |
191+----+--------------+--------------+-------------+------------+--------------------------+
192
193SELECT `a`.`id` AS `question_id`, `a`.`entry_content` AS `question`,
194 `b`.`id` AS `answer_id`, `b`.`entry_content` AS `answer`,
195 `c`.`id` AS `comment_id`, `c`.`entry_content` AS `comment`
196FROM `questions` `a`
197LEFT JOIN `questions` `b` ON (`a`.`id` = `b`.`parent_id` AND `b`.`entry_type` = 'answer')
198LEFT JOIN `questions` `c` ON (`b`.`id` = `c`.`parent_id` AND `c`.`entry_type` = 'comment')
199WHERE `a`.`entry_type` = 'question'
200AND `a`.`id` = 1
201ORDER BY `a`.`id`, `b`.`id`, `c`.`id`;
202
203+----+--------------------+------+-----------------------+------+--------------------------+
204| id | question | id | answer | id | comment |
205+----+--------------------+------+-----------------------+------+--------------------------+
206| 1 | How do I does SQL? | 2 | Easy, you eat cheese! | 3 | WTF are you on noobass?! |
207| 1 | How do I does SQL? | 2 | Easy, you eat cheese! | 6 | 3 |
208| 1 | How do I does SQL? | 4 | blah | 5 | blah2 |
209+----+--------------------+------+-----------------------+------+--------------------------+
210
211Simply use the "ORDER BY" clause to select the ordering you want!
212
213SELECT *
214 FROM questions
215 WHERE parent_id =1
216 OR parent_id
217 IN (
218 SELECT id
219 FROM questions
220 WHERE parent_id =1
221 AND parent_id != id
222 )
223 ORDER BY Parent_id , id