Need help with PHP and MYSQL

[Technoken]

I'm about to create a page with recent activities and I couldn't make it work. I'm just starting to code php so please pardon me 

I'm trying to test it with this code but the results are not sorted by the time

$sql = "SELECT * FROM `editlog`, `deletelog`, `loginlog`, `logoutlog` ORDER BY COALESCE(editlog.time , deletelog.time, loginlog.time, logoutlog.time) ASC";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {
echo $row[0].'<br />';
echo $row[1].'<br />';
echo $row[2].'<br />';
echo $row[3].'<br />';
}

What i'm trying to do is select all data from tables editlog, deletelog, loginlog, and logoutlog then merge the results. Then sort them based from the datetime in ascending order.

 

then after collecting the information(already sorted) it will be displayed like this

 

e.g

datetime  ------------------ emp id ----------- activity

2016-07-10 10:54:01 --- 100 --------------- logged in

2016-07-10 10:55:05 ----102 --------------- deleted record no 23

2016-07-10 10:57:00---- 103 --------------- logged in

2016-07-10 10:58:00---- 103 --------------- logged out

2016-07-10 10:58:30---- 103 --------------- edit record no 24

 

 

 

Here's a sample of the database

 

 

For anybody who can help. Thank you so much!

Edited July 10, 2016 by Technoken

[Habilis]

You didn't say whats the problem...

 

but I can suggest you search Union

query clause

 

To make union work all query should have same columns number ans name,

 

 

I cannot help you more, my database doesn't have those tables, im not logging all this

only 

Logins and commands here is an example

SELECT `time`, `user`, CONCAT(loginlog.log,' (', loginlog.ip, ')') as log
FROM `loginlog` 
WHERE ip NOT IN ('127.0.0.1', '0.0.0.0', '192.168.2.60')
UNION SELECT atcommand_date as time, account_id as user, CONCAT(char_name,' ', map, ' ', command) as log 
FROM atcommandlog

ORDER BY `time` ASC

time                            user      log

2016-06-17 13:46:31 andreia login ok (192.168.2.12)

2016-06-17 13:52:53 andreia login ok (192.168.2.12)

2016-06-17 13:53:36 2000001 Habilis payon @commands

2016-06-17 14:04:17 andreia login ok (192.168.2.12)

2016-06-17 14:08:18 andreia login ok (192.168.2.12)

2016-06-17 14:17:27 2000001 Habilis prontera @go prontera

2016-06-17 14:18:06 andreia login ok (192.168.2.12)

2016-06-17 14:19:02 2000001 Habilis izlude @go izlude

2016-06-17 14:19:53 2000001 Habilis prontera @go prontera

2016-06-17 14:22:45 2000001 Habilis prontera @dye 1

2016-06-17 14:22:51 2000001 Habilis prontera @dye 2

2016-06-17 14:22:54 2000001 Habilis prontera @dye 4

 

 

This is a log of logins and atcommands

[Technoken]

 

You didn't say whats the problem...

 

but I can suggest you search Union

query clause

 

To make union work all query should have same columns number ans name,

 

 

I cannot help you more, my database doesn't have those tables, im not logging all this

only 

Logins and commands here is an example

SELECT `time`, `user`, CONCAT(loginlog.log,' (', loginlog.ip, ')') as log
FROM `loginlog` 
WHERE ip NOT IN ('127.0.0.1', '0.0.0.0', '192.168.2.60')
UNION SELECT atcommand_date as time, account_id as user, CONCAT(char_name,' ', map, ' ', command) as log 
FROM atcommandlog

ORDER BY `time` ASC

time                            user      log

2016-06-17 13:46:31 andreia login ok (192.168.2.12)

2016-06-17 13:52:53 andreia login ok (192.168.2.12)

2016-06-17 13:53:36 2000001 Habilis payon @commands

2016-06-17 14:04:17 andreia login ok (192.168.2.12)

2016-06-17 14:08:18 andreia login ok (192.168.2.12)

2016-06-17 14:17:27 2000001 Habilis prontera @go prontera

2016-06-17 14:18:06 andreia login ok (192.168.2.12)

2016-06-17 14:19:02 2000001 Habilis izlude @go izlude

2016-06-17 14:19:53 2000001 Habilis prontera @go prontera

2016-06-17 14:22:45 2000001 Habilis prontera @dye 1

2016-06-17 14:22:51 2000001 Habilis prontera @dye 2

2016-06-17 14:22:54 2000001 Habilis prontera @dye 4

 

 

This is a log of logins and atcommands

 

I'm having problems how to query it.

 

I need to select all the data from multiple tables which are editlog, deletelog, loginlog, and logoutlog.

now after selecting all the data, I need to merge all the results from those tables and sort them based from their time column.

 

 

All of those tables have the same 'time' column. but the thing is the deletelog and editlog has 4 columns, while loginlog and logoutlog only has 2 columns. Is it still possible?

[Habilis]

See my exemple

Using union i did 2 separate queries select all coulumns you want or fake a column like so  ,

'This column is not in this table' as column

 

but to use union all united queries must have same number and name of coluns

Edited July 10, 2016 by Habilis

[Technoken]

See my exemple

Using union i did 2 separate queries select all coulumns you want or fake a column like so  ,

'This column is not in this table' as column

 

but to use union all united queries must have same number and name of coluns

Thank you! Somehow union is what im looking for. lol