MySQL Query to Build Mockup Vue.js UserCP Timeline Page


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
# 1  
MySQL Query to Build Mockup Vue.js UserCP Timeline Page

Here is the query (and some sample results) I plan to use to build a new timeline page in the mockup vue.js usercp I am working on.

When the postid is the same as lastpostid, this means the timeline entry will be - "{{Member}} Started Discussion {{Thread Title}} at {{date and time}}" and when they are different, it means "{{Member}} Posted a Reply in {{Thread Title}} at {{date and time}":

Seem like this will make a nice timeline in the new usercp I'm working on:

Code:
mysql> select post.userid, post.username, thread.title, thread.dateline,post.postid, thread.lastpostid from thread inner join post ON post.threadid = thread.threadid  order by dateline desc limit 30;
+-----------+---------------+--------------------------------------------------------------------------------+------------+-----------+------------+
| userid    | username      | title                                                                          | dateline   | postid    | lastpostid |
+-----------+---------------+--------------------------------------------------------------------------------+------------+-----------+------------+
| 302061468 | newbie_01     | Need help with how to search a file for a variable string and delete that line | 1550899316 | 303031186 |  303031189 |
| 302122047 | RudiC         | Need help with how to search a file for a variable string and delete that line | 1550899316 | 303031189 |  303031189 |
| 302122727 | Don Cragun    | Need help with how to search a file for a variable string and delete that line | 1550899316 | 303031187 |  303031189 |
|         1 | Neo           | Vue.js UserCP Mockup Version 0.20 - Badge Notifications                        | 1550894775 | 303031184 |  303031188 |
|         1 | Neo           | Vue.js UserCP Mockup Version 0.20 - Badge Notifications                        | 1550894775 | 303031185 |  303031188 |
|         1 | Neo           | Vue.js UserCP Mockup Version 0.20 - Badge Notifications                        | 1550894775 | 303031188 |  303031188 |
| 302136317 | wisecracker   | Apple looking to switch to using in-house ARM chips for their HW.              | 1550864937 | 303031180 |  303031180 |
|     41441 | bakunin       | Combining multiple greps                                                       | 1550845443 | 303031164 |  303031191 |
| 302052697 | Scrutinizer   | Combining multiple greps                                                       | 1550845443 | 303031183 |  303031191 |
| 302116191 | MadeInGermany | Combining multiple greps                                                       | 1550845443 | 303031191 |  303031191 |
| 302170346 | stomp         | Combining multiple greps                                                       | 1550845443 | 303031163 |  303031191 |
| 302181753 | Xubuntu56     | Combining multiple greps                                                       | 1550845443 | 303031161 |  303031191 |
| 302181753 | Xubuntu56     | Combining multiple greps                                                       | 1550845443 | 303031177 |  303031191 |
| 302181753 | Xubuntu56     | Combining multiple greps                                                       | 1550845443 | 303031190 |  303031191 |
|     21292 | milhan        | Remove lines ending with a certain character                                   | 1550836350 | 303031150 |  303031157 |
| 302122047 | RudiC         | Remove lines ending with a certain character                                   | 1550836350 | 303031151 |  303031157 |
| 302122047 | RudiC         | Remove lines ending with a certain character                                   | 1550836350 | 303031154 |  303031157 |
| 302122727 | Don Cragun    | Remove lines ending with a certain character                                   | 1550836350 | 303031152 |  303031157 |
| 302122727 | Don Cragun    | Remove lines ending with a certain character                                   | 1550836350 | 303031156 |  303031157 |
| 302180382 | maya3         | Remove lines ending with a certain character                                   | 1550836350 | 303031145 |  303031157 |
| 302180382 | maya3         | Remove lines ending with a certain character                                   | 1550836350 | 303031153 |  303031157 |
| 302180382 | maya3         | Remove lines ending with a certain character                                   | 1550836350 | 303031157 |  303031157 |
|         1 | Neo           | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031143 |  303031165 |
| 302024200 | drl           | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031146 |  303031165 |
| 302122047 | RudiC         | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031144 |  303031165 |
| 302181968 | danielshell   | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031131 |  303031165 |
| 302181968 | danielshell   | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031165 |  303031165 |
|         1 | Neo           | Help to remove malware                                                         | 1550784585 | 303031132 |  303031132 |
|     43551 | Corona688     | Help to remove malware                                                         | 1550784585 | 303031117 |  303031132 |
| 302182064 | asoh65        | Help to remove malware                                                         | 1550784585 | 303031113 |  303031132 |
+-----------+---------------+--------------------------------------------------------------------------------+------------+-----------+------------+
30 rows in set (0.00 sec)

# 2  
But to build a link to the thread for each entry, will need to modify a bit and add the threadid (to build the link to click on in the timeline to visit the discussion thread):

Code:
mysql> select thread.threadid, post.userid, post.username, thread.title, thread.dateline,post.postid, thread.lastpostid from thread inner join post ON post.threadid = thread.threadid  order by dateline desc limit 30;
+----------+-----------+---------------+--------------------------------------------------------------------------------+------------+-----------+------------+
| threadid | userid    | username      | title                                                                          | dateline   | postid    | lastpostid |
+----------+-----------+---------------+--------------------------------------------------------------------------------+------------+-----------+------------+
|   281241 |         1 | Neo           | MySQL Query to Build Mockup Vue.js UserCP Timeline Page                        | 1550927675 | 303031192 |  303031192 |
|   281240 | 302061468 | newbie_01     | Need help with how to search a file for a variable string and delete that line | 1550899316 | 303031186 |  303031189 |
|   281240 | 302122047 | RudiC         | Need help with how to search a file for a variable string and delete that line | 1550899316 | 303031189 |  303031189 |
|   281240 | 302122727 | Don Cragun    | Need help with how to search a file for a variable string and delete that line | 1550899316 | 303031187 |  303031189 |
|   281239 |         1 | Neo           | Vue.js UserCP Mockup Version 0.20 - Badge Notifications                        | 1550894775 | 303031184 |  303031188 |
|   281239 |         1 | Neo           | Vue.js UserCP Mockup Version 0.20 - Badge Notifications                        | 1550894775 | 303031185 |  303031188 |
|   281239 |         1 | Neo           | Vue.js UserCP Mockup Version 0.20 - Badge Notifications                        | 1550894775 | 303031188 |  303031188 |
|   281238 | 302136317 | wisecracker   | Apple looking to switch to using in-house ARM chips for their HW.              | 1550864937 | 303031180 |  303031180 |
|   281237 |     41441 | bakunin       | Combining multiple greps                                                       | 1550845443 | 303031164 |  303031191 |
|   281237 | 302052697 | Scrutinizer   | Combining multiple greps                                                       | 1550845443 | 303031183 |  303031191 |
|   281237 | 302116191 | MadeInGermany | Combining multiple greps                                                       | 1550845443 | 303031191 |  303031191 |
|   281237 | 302170346 | stomp         | Combining multiple greps                                                       | 1550845443 | 303031163 |  303031191 |
|   281237 | 302181753 | Xubuntu56     | Combining multiple greps                                                       | 1550845443 | 303031161 |  303031191 |
|   281237 | 302181753 | Xubuntu56     | Combining multiple greps                                                       | 1550845443 | 303031177 |  303031191 |
|   281237 | 302181753 | Xubuntu56     | Combining multiple greps                                                       | 1550845443 | 303031190 |  303031191 |
|   281236 |     21292 | milhan        | Remove lines ending with a certain character                                   | 1550836350 | 303031150 |  303031157 |
|   281236 | 302122047 | RudiC         | Remove lines ending with a certain character                                   | 1550836350 | 303031151 |  303031157 |
|   281236 | 302122047 | RudiC         | Remove lines ending with a certain character                                   | 1550836350 | 303031154 |  303031157 |
|   281236 | 302122727 | Don Cragun    | Remove lines ending with a certain character                                   | 1550836350 | 303031152 |  303031157 |
|   281236 | 302122727 | Don Cragun    | Remove lines ending with a certain character                                   | 1550836350 | 303031156 |  303031157 |
|   281236 | 302180382 | maya3         | Remove lines ending with a certain character                                   | 1550836350 | 303031145 |  303031157 |
|   281236 | 302180382 | maya3         | Remove lines ending with a certain character                                   | 1550836350 | 303031153 |  303031157 |
|   281236 | 302180382 | maya3         | Remove lines ending with a certain character                                   | 1550836350 | 303031157 |  303031157 |
|   281235 |         1 | Neo           | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031143 |  303031165 |
|   281235 | 302024200 | drl           | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031146 |  303031165 |
|   281235 | 302122047 | RudiC         | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031144 |  303031165 |
|   281235 | 302181968 | danielshell   | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031131 |  303031165 |
|   281235 | 302181968 | danielshell   | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031165 |  303031165 |
|   281234 |         1 | Neo           | Help to remove malware                                                         | 1550784585 | 303031132 |  303031132 |
|   281234 |     43551 | Corona688     | Help to remove malware                                                         | 1550784585 | 303031117 |  303031132 |
+----------+-----------+---------------+--------------------------------------------------------------------------------+------------+-----------+------------+
30 rows in set (0.00 sec)

# 3  
So, this PHP code works fine on the server side to create the JSON array needed in the Vue.js application:

Code:
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: GET, OPTIONS");
require_once '/var/www/global.php';
$timeline = "select thread.threadid, post.userid, post.username, thread.title, thread.dateline,post.postid, thread.lastpostid from thread inner join post ON post.threadid = thread.threadid  order by dateline desc limit 30";

$results = $vbulletin->db->query_read($timeline);
while ($item = $db->fetch_array($results)) {
    if ($item['postid'] == $item['lastpostid']) {
        $item['title'] = $item['username'] . ' started discussion ' . $item['title'];
    } else {
        $item['title'] = $item['username'] . ' replied to ' . $item['title'];
    }
    $item['when'] = date("D M j G:i:s T Y", $item['dateline']);
    unset($item['postid']);
    unset($item['lastpostid']);
    $final[] = $item;
}

$serialize = json_encode($final);
echo $serialize;

With sample PHP results:

Quote:
[{"threadid":"281241","userid":"1","username":"Neo","title":"Neo replied to MySQL Query to Build Mockup Vue.js UserCP Timeline Page","dateline":"1550927675","when":"Sat Feb 23 7:14:35 CST 2019"},{"threadid":"281241","userid":"1","username":"Neo","title":"Neo started discussion MySQL Query to Build Mockup Vue.js UserCP Timeline Page","dateline":"1550927675","when":"Sat Feb 23 7:14:35 CST 2019"},{"threadid":"281240","userid":"302061468","username":"newbie_01","title":"newbie_01 replied to Need help with how to search a file for a variable string and delete that line","dateline":"1550899316","when":"Fri Feb 22 23:21:56 CST 2019"},{"threadid":"281240","userid":"302122047","username":"RudiC","title":"RudiC started discussion Need help with how to search a file for a variable string and delete that line","dateline":"1550899316","when":"Fri Feb 22 23:21:56 CST 2019"},{"threadid":"281240","userid":"302122727","username":"Don Cragun","title":"Don Cragun replied to Need help with how to search a file for a variable string and delete that line","dateline":"1550899316","when":"Fri Feb 22 23:21:56 CST 2019"},{"threadid":"281239","userid":"1","username":"Neo","title":"Neo replied to Vue.js UserCP Mockup Version 0.20 - Badge Notifications","dateline":"1550894775","when":"Fri Feb 22 22:06:15 CST 2019"},{"threadid":"281239","userid":"1","username":"Neo","title":"Neo replied to Vue.js UserCP Mockup Version 0.20 - Badge Notifications","dateline":"1550894775","when":"Fri Feb 22 22:06:15 CST 2019"},{"threadid":"281239","userid":"1","username":"Neo","title":"Neo started discussion Vue.js UserCP Mockup Version 0.20 - Badge Notifications","dateline":"1550894775","when":"Fri Feb 22 22:06:15 CST 2019"},{"threadid":"281238","userid":"302136317","username":"wisecracker","title":"wisecracker started discussion Apple looking to switch to using in-house ARM chips for their HW.","dateline":"1550864937","when":"Fri Feb 22 13:48:57 CST 2019"},{"threadid":"281237","userid":"41441","username":"bakunin","title":"bakunin replied to Combining multiple greps","dateline":"1550845443","when":"Fri Feb 22 8:24:03 CST 2019"},{"threadid":"281237","userid":"302052697","username":"Scrutinizer","title":"Scrutinizer replied to Combining multiple greps","dateline":"1550845443","when":"Fri Feb 22 8:24:03 CST 2019"},{"threadid":"281237","userid":"302116191","username":"MadeInGermany","title":"MadeInGermany replied to Combining multiple greps","dateline":"1550845443","when":"Fri Feb 22 8:24:03 CST 2019"},{"threadid":"281237","userid":"302170346","username":"stomp","title":"stomp replied to Combining multiple greps","dateline":"1550845443","when":"Fri Feb 22 8:24:03 CST 2019"},{"threadid":"281237","userid":"302181753","username":"Xubuntu56","title":"Xubuntu56 replied to Combining multiple greps","dateline":"1550845443","when":"Fri Feb 22 8:24:03 CST 2019"},{"threadid":"281237","userid":"302181753","username":"Xubuntu56","title":"Xubuntu56 replied to Combining multiple greps","dateline":"1550845443","when":"Fri Feb 22 8:24:03 CST 2019"},{"threadid":"281237","userid":"302181753","username":"Xubuntu56","title":"Xubuntu56 replied to Combining multiple greps","dateline":"1550845443","when":"Fri Feb 22 8:24:03 CST 2019"},{"threadid":"281237","userid":"302181753","username":"Xubuntu56","title":"Xubuntu56 started discussion Combining multiple greps","dateline":"1550845443","when":"Fri Feb 22 8:24:03 CST 2019"},{"threadid":"281236","userid":"21292","username":"milhan","title":"milhan replied to Remove lines ending with a certain character","dateline":"1550836350","when":"Fri Feb 22 5:52:30 CST 2019"},{"threadid":"281236","userid":"302122047","username":"RudiC","title":"RudiC replied to Remove lines ending with a certain character","dateline":"1550836350","when":"Fri Feb 22 5:52:30 CST 2019"},{"threadid":"281236","userid":"302122047","username":"RudiC","title":"RudiC replied to Remove lines ending with a certain character","dateline":"1550836350","when":"Fri Feb 22 5:52:30 CST 2019"},{"threadid":"281236","userid":"302122727","username":"Don Cragun","title":"Don Cragun replied to Remove lines ending with a certain character","dateline":"1550836350","when":"Fri Feb 22 5:52:30 CST 2019"},{"threadid":"281236","userid":"302122727","username":"Don Cragun","title":"Don Cragun replied to Remove lines ending with a certain character","dateline":"1550836350","when":"Fri Feb 22 5:52:30 CST 2019"},{"threadid":"281236","userid":"302180382","username":"maya3","title":"maya3 replied to Remove lines ending with a certain character","dateline":"1550836350","when":"Fri Feb 22 5:52:30 CST 2019"},{"threadid":"281236","userid":"302180382","username":"maya3","title":"maya3 replied to Remove lines ending with a certain character","dateline":"1550836350","when":"Fri Feb 22 5:52:30 CST 2019"},{"threadid":"281236","userid":"302180382","username":"maya3","title":"maya3 started discussion Remove lines ending with a certain character","dateline":"1550836350","when":"Fri Feb 22 5:52:30 CST 2019"},{"threadid":"281235","userid":"1","username":"Neo","title":"Neo replied to Mtime or the equivalent for HP-UX","dateline":"1550808466","when":"Thu Feb 21 22:07:46 CST 2019"},{"threadid":"281235","userid":"302024200","username":"drl","title":"drl replied to Mtime or the equivalent for HP-UX","dateline":"1550808466","when":"Thu Feb 21 22:07:46 CST 2019"},{"threadid":"281235","userid":"302122047","username":"RudiC","title":"RudiC replied to Mtime or the equivalent for HP-UX","dateline":"1550808466","when":"Thu Feb 21 22:07:46 CST 2019"},{"threadid":"281235","userid":"302181968","username":"danielshell","title":"danielshell replied to Mtime or the equivalent for HP-UX","dateline":"1550808466","when":"Thu Feb 21 22:07:46 CST 2019"},{"threadid":"281235","userid":"302181968","username":"danielshell","title":"danielshell started discussion Mtime or the equivalent for HP-UX","dateline":"1550808466","when":"Thu Feb 21 22:07:46 CST 2019"}]

Off Topic Note to Self, Don Cragun and the Mod Team regarding ICODE, CODE and QUOTE BBCODE tags.

The CODE tags do not work well for the JSON output above and neither do QUOTE tags, and the ICODE tags do not work either, so no current BBCODE tag solution is perfect. This means (TODO) someday in the future (after finish the Vue.js mockup and initial code base) I am going to (try to) create a new BBCODE tag which is like ICODE or CODE tags but wraps properly, is responsive (formats well in all screen sizes), has a different background color, and has more margin. (TODO).

In the time being, I'm going to leave the wrap property on the ICODE tags, as I am finding having ICODE tags wrap have pros-and-cons, even though they were designed not to wrap. I don't want members to start using ICODE tags instead of CODE tags for code and output. ICODE tags should only be used for very short "phrases" which normally do not wrap (within the ICODE tag); however I realize there are "exceptions" and so I need to create a new kind of BBCODE tag this year.

In a nutshell, neither CODE, ICODE or QUOTE tags work well for the large "single line" JSON file output above. Of the three, QUOTE tags worked the best for this example.

Hmmm.
# 4  
OK... update! The query above had a bug.... should be firstpostid not lastpostid (stupid me!)

Code:
mysql> select thread.threadid, post.userid, post.username, thread.title, post.dateline,post.postid, thread.firstpostid from thread inner join post ON post.threadid = thread.threadid  order by dateline desc limit 100;
+----------+-----------+-----------------+--------------------------------------------------------------------------------+------------+-----------+-------------+
| threadid | userid    | username        | title                                                                          | dateline   | postid    | firstpostid |
+----------+-----------+-----------------+--------------------------------------------------------------------------------+------------+-----------+-------------+
|   281237 | 302122047 | RudiC           | Combining multiple greps                                                       | 1550932276 | 303031197 |   303031161 |
|   281080 |         1 | Neo             | Bug in ?                                                        | 1550930556 | 303031196 |   303030308 |
|   281241 |         1 | Neo             | MySQL Query to Build Mockup Vue.js UserCP Timeline Page                        | 1550929298 | 303031195 |   303031192 |
|   281237 | 302181753 | Xubuntu56       | Combining multiple greps                                                       | 1550928864 | 303031194 |   303031161 |
|   281241 |         1 | Neo             | MySQL Query to Build Mockup Vue.js UserCP Timeline Page                        | 1550927982 | 303031193 |   303031192 |
|   281241 |         1 | Neo             | MySQL Query to Build Mockup Vue.js UserCP Timeline Page                        | 1550927675 | 303031192 |   303031192 |
|   281237 | 302116191 | MadeInGermany   | Combining multiple greps                                                       | 1550927210 | 303031191 |   303031161 |
|   281237 | 302181753 | Xubuntu56       | Combining multiple greps                                                       | 1550924266 | 303031190 |   303031161 |
|   281240 | 302122047 | RudiC           | Need help with how to search a file for a variable string and delete that line | 1550917274 | 303031189 |   303031186 |
|   281239 |         1 | Neo             | Vue.js UserCP Mockup Version 0.20 - Badge Notifications                        | 1550912808 | 303031188 |   303031184 |
|   281240 | 302122727 | Don Cragun      | Need help with how to search a file for a variable string and delete that line | 1550905657 | 303031187 |   303031186 |
|   281240 | 302061468 | newbie_01       | Need help with how to search a file for a variable string and delete that line | 1550899316 | 303031186 |   303031186 |
|   281239 |         1 | Neo             | Vue.js UserCP Mockup Version 0.20 - Badge Notifications                        | 1550898573 | 303031185 |   303031184 |
|   281239 |         1 | Neo             | Vue.js UserCP Mockup Version 0.20 - Badge Notifications                        | 1550894775 | 303031184 |   303031184 |
|   281237 | 302052697 | Scrutinizer     | Combining multiple greps                                                       | 1550892712 | 303031183 |   303031161 |
|   280896 | 302130619 | baris35         | How to remove everything after a word containing string?                       | 1550881379 | 303031182 |   303029361 |
|   281155 | 302136317 | wisecracker     | Hearing Aid for OSX 10.12.x and greater.                                       | 1550876067 | 303031181 |   303030697 |
|   281238 | 302136317 | wisecracker     | Apple looking to switch to using in-house ARM chips for their HW.              | 1550864937 | 303031180 |   303031180 |
|   281233 |     43551 | Corona688       | Command line overwrite                                                         | 1550857767 | 303031179 |   303031104 |
|   281233 | 302181427 | steveo314       | Command line overwrite                                                         | 1550855904 | 303031178 |   303031104 |
|   281237 | 302181753 | Xubuntu56       | Combining multiple greps                                                       | 1550854881 | 303031177 |   303031161 |
|   281219 | 302122047 | RudiC           | Massive ftp                                                                    | 1550852841 | 303031176 |   303031020 |
|   281219 |     43551 | Corona688       | Massive ftp                                                                    | 1550852747 | 303031175 |   303031020 |
|   281219 | 302161273 | tricampeon81    | Massive ftp                                                                    | 1550850948 | 303031174 |   303031020 |
|   281233 |     43551 | Corona688       | Command line overwrite                                                         | 1550850337 | 303031173 |   303031104 |
|   281233 | 302181427 | steveo314       | Command line overwrite                                                         | 1550850038 | 303031172 |   303031104 |
|   281233 |     43551 | Corona688       | Command line overwrite                                                         | 1550849924 | 303031171 |   303031104 |
|   281219 |     43551 | Corona688       | Massive ftp                                                                    | 1550849569 | 303031170 |   303031020 |
|   281233 | 302181427 | steveo314       | Command line overwrite                                                         | 1550849260 | 303031168 |   303031104 |
|   281233 |     43551 | Corona688       | Command line overwrite                                                         | 1550849127 | 303031167 |   303031104 |
|   281233 | 302122727 | Don Cragun      | Command line overwrite                                                         | 1550848580 | 303031166 |   303031104 |
|   281235 | 302181968 | danielshell     | Mtime or the equivalent for HP-UX                                              | 1550847600 | 303031165 |   303031131 |
|   281237 |     41441 | bakunin         | Combining multiple greps                                                       | 1550846538 | 303031164 |   303031161 |
|   281237 | 302170346 | stomp           | Combining multiple greps                                                       | 1550845986 | 303031163 |   303031161 |
|   281233 | 302181427 | steveo314       | Command line overwrite                                                         | 1550845706 | 303031162 |   303031104 |
|   281237 | 302181753 | Xubuntu56       | Combining multiple greps                                                       | 1550845443 | 303031161 |   303031161 |
|   281231 | 302154737 | cmccabe         | Bash variable available for use outside loop                                   | 1550844711 | 303031160 |   303031101 |
|   281186 | 302182075 | Naina2019       | Unable to establish connection over TLS 1.2 on AIX 7.1/7.2                     | 1550844406 | 303031159 |   303030871 |
|   281219 | 302161273 | tricampeon81    | Massive ftp                                                                    | 1550843864 | 303031158 |   303031020 |
|   281236 | 302180382 | maya3           | Remove lines ending with a certain character                                   | 1550843499 | 303031157 |   303031145 |
|   281236 | 302122727 | Don Cragun      | Remove lines ending with a certain character                                   | 1550843379 | 303031156 |   303031145 |
|   281213 | 302102414 | Azrael          | Combine GetOpt And Variadic Function In C                                      | 1550841256 | 303031155 |   303030978 |
|   281236 | 302122047 | RudiC           | Remove lines ending with a certain character                                   | 1550840761 | 303031154 |   303031145 |
|   281236 | 302180382 | maya3           | Remove lines ending with a certain character                                   | 1550839294 | 303031153 |   303031145 |
|   281236 | 302122727 | Don Cragun      | Remove lines ending with a certain character                                   | 1550838067 | 303031152 |   303031145 |
|   281236 | 302122047 | RudiC           | Remove lines ending with a certain character                                   | 1550837907 | 303031151 |   303031145 |
|   281236 |     21292 | milhan          | Remove lines ending with a certain character                                   | 1550837864 | 303031150 |   303031145 |
|   281230 | 302182108 | LouisLakoute    | Sun fire x2270                                                                 | 1550837452 | 303031148 |   303031097 |
|   281219 | 302161273 | tricampeon81    | Massive ftp                                                                    | 1550836895 | 303031147 |   303031020 |
|   281235 | 302024200 | drl             | Mtime or the equivalent for HP-UX                                              | 1550836737 | 303031146 |   303031131 |
|   281236 | 302180382 | maya3           | Remove lines ending with a certain character                                   | 1550836350 | 303031145 |   303031145 |
|   281235 | 302122047 | RudiC           | Mtime or the equivalent for HP-UX                                              | 1550831404 | 303031144 |   303031131 |
|   281235 |         1 | Neo             | Mtime or the equivalent for HP-UX                                              | 1550830225 | 303031143 |   303031131 |
|   281193 | 302116191 | MadeInGermany   | &quot;sudo su -&quot; showing lot of information on OpenLDAP                   | 1550829217 | 303031142 |   303030887 |
|   281229 | 302122047 | RudiC           | awk Script Output Help                                                         | 1550828438 | 303031141 |   303031095 |
|   281233 |     21292 | milhan          | Command line overwrite                                                         | 1550828168 | 303031140 |   303031104 |
|   281233 | 302122047 | RudiC           | Command line overwrite                                                         | 1550827042 | 303031139 |   303031104 |
|   281219 | 302122047 | RudiC           | Massive ftp                                                                    | 1550826581 | 303031138 |   303031020 |
|   281230 | 302116191 | MadeInGermany   | Sun fire x2270                                                                 | 1550823294 | 303031137 |   303031097 |
|   281207 | 302181242 | nezabudka       | Help building a variable string from a keyword - character replacements!       | 1550819198 | 303031136 |   303030946 |
|   281233 | 302052697 | Scrutinizer     | Command line overwrite                                                         | 1550816093 | 303031135 |   303031104 |
|   281207 | 302148617 | RavinderSingh13 | Help building a variable string from a keyword - character replacements!       | 1550814489 | 303031134 |   303030946 |
|   281207 | 302181242 | nezabudka       | Help building a variable string from a keyword - character replacements!       | 1550813803 | 303031133 |   303030946 |
|   281234 |         1 | Neo             | Help to remove malware                                                         | 1550811877 | 303031132 |   303031113 |
|   281235 | 302181968 | danielshell     | Mtime or the equivalent for HP-UX                                              | 1550808466 | 303031131 |   303031131 |
|   281230 | 302182108 | LouisLakoute    | Sun fire x2270                                                                 | 1550799421 | 303031130 |   303031097 |
|   281219 | 302161273 | tricampeon81    | Massive ftp                                                                    | 1550797928 | 303031128 |   303031020 |
|   281229 |     37898 | vgersh99        | awk Script Output Help                                                         | 1550792040 | 303031127 |   303031095 |
|   281207 | 302116191 | MadeInGermany   | Help building a variable string from a keyword - character replacements!       | 1550791764 | 303031126 |   303030946 |
|   281229 | 302122727 | Don Cragun      | awk Script Output Help                                                         | 1550791451 | 303031125 |   303031095 |
|   281232 |     43551 | Corona688       | Queue enabler for Solaris 11.3                                                 | 1550790171 | 303031124 |   303031103 |
|   281219 |     43551 | Corona688       | Massive ftp                                                                    | 1550790098 | 303031123 |   303031020 |
|   281232 | 302165712 | feroccimx       | Queue enabler for Solaris 11.3                                                 | 1550789433 | 303031122 |   303031103 |
|   281229 | 302182107 | Marquez3105     | awk Script Output Help                                                         | 1550789370 | 303031120 |   303031095 |
|   281230 | 302116191 | MadeInGermany   | Sun fire x2270                                                                 | 1550788506 | 303031119 |   303031097 |
|   281219 |     43551 | Corona688       | Massive ftp                                                                    | 1550787798 | 303031118 |   303031020 |
|   281234 |     43551 | Corona688       | Help to remove malware                                                         | 1550787757 | 303031117 |   303031113 |
|   281229 | 302122047 | RudiC           | awk Script Output Help                                                         | 1550787072 | 303031116 |   303031095 |
|   281232 | 302116191 | MadeInGermany   | Queue enabler for Solaris 11.3                                                 | 1550786232 | 303031115 |   303031103 |
|   281219 | 302122047 | RudiC           | Massive ftp                                                                    | 1550785992 | 303031114 |   303031020 |
|   281234 | 302182064 | asoh65          | Help to remove malware                                                         | 1550784585 | 303031113 |   303031113 |
|   281229 |     37898 | vgersh99        | awk Script Output Help                                                         | 1550783445 | 303031112 |   303031095 |
|   281219 | 302161273 | tricampeon81    | Massive ftp                                                                    | 1550783020 | 303031111 |   303031020 |
|   281233 | 302181427 | steveo314       | Command line overwrite                                                         | 1550781867 | 303031110 |   303031104 |
|   281232 |     43551 | Corona688       | Queue enabler for Solaris 11.3                                                 | 1550781623 | 303031109 |   303031103 |
|   281231 |     43551 | Corona688       | Bash variable available for use outside loop                                   | 1550781393 | 303031108 |   303031101 |
|   281233 | 302052697 | Scrutinizer     | Command line overwrite                                                         | 1550780787 | 303031107 |   303031104 |
|   281231 | 302136317 | wisecracker     | Bash variable available for use outside loop                                   | 1550780492 | 303031106 |   303031101 |
|   281229 | 302182107 | Marquez3105     | awk Script Output Help                                                         | 1550779805 | 303031105 |   303031095 |
|   281233 | 302181427 | steveo314       | Command line overwrite                                                         | 1550779477 | 303031104 |   303031104 |
|   281232 | 302165712 | feroccimx       | Queue enabler for Solaris 11.3                                                 | 1550778090 | 303031103 |   303031103 |
|   281229 | 302126107 | rdrtx1          | awk Script Output Help                                                         | 1550778034 | 303031102 |   303031095 |
|   281231 | 302154737 | cmccabe         | Bash variable available for use outside loop                                   | 1550777016 | 303031101 |   303031101 |
|   281207 | 302126107 | rdrtx1          | Help building a variable string from a keyword - character replacements!       | 1550775920 | 303031100 |   303030946 |
|   281229 | 302182107 | Marquez3105     | awk Script Output Help                                                         | 1550774371 | 303031099 |   303031095 |
|   281207 | 302182084 | ghaniba         | Help building a variable string from a keyword - character replacements!       | 1550773723 | 303031098 |   303030946 |
|   281230 | 302182108 | LouisLakoute    | Sun fire x2270                                                                 | 1550773021 | 303031097 |   303031097 |
|   281229 |     37898 | vgersh99        | awk Script Output Help                                                         | 1550772924 | 303031096 |   303031095 |
|   281229 | 302182107 | Marquez3105     | awk Script Output Help                                                         | 1550771178 | 303031095 |   303031095 |
|   281222 |         1 | Neo             | Forum Advisor MySQL Query for New UserCP Table Mockup                          | 1550769939 | 303031094 |   303031047 |
+----------+-----------+-----------------+--------------------------------------------------------------------------------+------------+-----------+-------------+
100 rows in set (0.00 sec)

# 5  
And so the current PHP file (working well in testing) is:

Code:
<?php
header("Content-Type: application/json");
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: GET, OPTIONS");
require_once('/var/www/global.php');
$timeline = "select thread.threadid, post.userid, post.username, thread.title, post.dateline,post.postid, thread.firstpostid from thread inner join post ON post.threadid = thread.threadid  order by dateline desc limit 100";

$results = $vbulletin->db->query_read($timeline);
while ($item = $db->fetch_array($results)) {
    if($item['postid'] == $item['firstpostid'])
    {
    $item['title'] =  $item['username'].' started discussion '.$item['title'];
    }
    else{
    $item['title'] =  $item['username'].' replied to '.$item['title'];
    }
    $item['when'] =  date("D M j G:i:s T Y", $item['dateline']); 
    $final[] = $item;

}

$serialize = json_encode($final);
echo  $serialize;

I will update the new build with working timeline next Smilie

Edit: Done!

https://www.unix.com/web-programming...-new-post.html
Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #642
Difficulty: Easy
The first full release of NeXTSTEP 1.0 shipped on September 18, 1988.
True or False?

9 More Discussions You Might Find Interesting

1. What is on Your Mind?

Video Overview of the Vue.js UserCP @UNIX.com

Here is my second live video screencast (in my life, using Camtasia) with voice for the new usercp: Overview of the Vue.js UserCP @UNIX.com Shout outs to Don Cragun, Corona688, Rudi, Wolf, Made in Germany, stomp, Ravinder, Creative Tim, PubNub and others in the video. Thanks. If you are... (1 Reply)
Discussion started by: Neo
1 Replies

2. What is on Your Mind?

UserCP Screeching Frog 0.7446 Using Vue.js

Here is a status update on the new forum usercp. The current version of the new UserCP is Screeching Frog v0.7446. Most users will need to clear the files from your browser cache, quit and restart your browser to see the new version (check bottom of the page for version). Safari seems to... (9 Replies)
Discussion started by: Neo
9 Replies

3. What is on Your Mind?

New UserCP Update Profile Image Page (UserCP Screeching Frog 0.7485)

Update! UserCP Screeching Frog 0.7485 Created a new page for uploaded a profile picture (profile pictures are different than avatar pictures). https://www.unix.com/usercp/#/settings/other https://www.unix.com/members/1-albums225-picture1158.png ... (0 Replies)
Discussion started by: Neo
0 Replies

4. Web Development

Simple Vue.js Component to Redirect to External Web Page Using Vue Router

Vue Router has some quirks and on of the quirks is that it is not reliable when adding external links using the vue-router library. After struggling with many solutions, I have found that creating a simple Vue.js component like this one seems to work the best (so far): Component Example: ... (0 Replies)
Discussion started by: Neo
0 Replies

5. What is on Your Mind?

My Charts in the Prototype Vue.js UserCP

Yea.... something I thought would take me an hour ended up taking most of the day. Well, it's not like those YT video tutorials where it take a week or more to make a video and the guys (gals) make it look so easy. But having said that, I'm happy to share with forum members the first "My... (6 Replies)
Discussion started by: Neo
6 Replies

6. Web Development

New Badge Timeline in Vue.js UserCP Mockup

Continuing to think Vue.js is AWESOME, we now have a new badges timeline in version 0.26 of the UserCP Mockup: https://www.unix.com/cp/index.php#/pages/badges Changes: Added Mockup from Badges timeline. Changed notifications (upper right) to use v-for: bindings. Fixes minor vue routing... (0 Replies)
Discussion started by: Neo
0 Replies

7. Web Development

New Discussion Timeline in Vue.js UserCP Mockup

Well, thanks to the amazing power of Vue.js, we now have a new timeline in version 0.23 of the UserCP Mockup: Wow! I'm really impressed with Vue.js. https://www.unix.com/cp/index.php#/pages/timeline In this version: Created database, and PHP model for the remote AJAX (Axios) call to... (1 Reply)
Discussion started by: Neo
1 Replies

8. Web Development

Vue.js UserCP Mockup Version 0.20 - Badge Notifications

Vue.js UserCP Mockup Version 0.20 - Badge Notifications https://www.unix.com/cp/index.php#/dashboardIn this mockup release: Badge Notifications are working with live data: Upper Right (see image) Added Axios to Vue and changed large table updates to axios (ajax) Note: Will reformat... (2 Replies)
Discussion started by: Neo
2 Replies

9. What is on Your Mind?

Promotion! Like Our New Facebook Timeline Page for 10,000 Bits!

Hey! We have started a new Forum HQ Facebook "Timeline" page where we plan to create a unix/linux timeline with major milestones for unix/linux, company start up dates for major releases, etc. We are just getting started, so I would like to personally invite you to visit and "Like" our new... (10 Replies)
Discussion started by: Neo
10 Replies

Featured Tech Videos