Php Timestamp multiple tables results

[RazorRamon]

Ok I'm trying to build a forum site. I have multiple tables representing the different categories that the user can select from. I'm working on the head page and what I want to do is retrieve the 10 last most recent comments posted across all tables. I want to use the time stamp that I have created BUT i'm not sure if i'm using the most efficent coding to record time. This is what I'm using.

$tablename='sports';
$topic=$_POST['topic'];
$comment=$_POST['comment'];
$username=$_POST['username'];
$date= date("M jS, Y");
$time= date("g:ia");

mysql_query ("INSERT INTO $tablename (topic, comment, username, date, time) 
VALUES ('$topic','$comment','$username','$date','$time')");

Is there a more efficent way to record time to where I can combine date and time and just select the 10 most recent results?

[rch1231]

I think what you are looking for is probably a combination of the LIMIT option and the UNION command. If you have 4 sets of like results for 4 queries (each query returns like columns and the same number of columns ) then write your selection statements in parentheses and put UNION between them. Something like this:

(Select table1.topic, table1.comment, table1.username, table1.mydate from table1
where topic = topic1
order by mydate desc
Limit 10)
UNION
(Select table2.topic, table2.comment, table2.username, table2.mydate from table2
where topic = topic2
order by mydate desc
Limit 10)
UNION
(Select table3.topic, table3.comment, table3.username, table3.mydate from table3
where topic = topic3
order by mydate desc
Limit 10)

Each table will be limited to 10 results and all of the results will appear in a 30 column table.

[almostbob]

timestamps are not stored in text
dates and times are a single column value as php date()
when storing the timestamp us mysql now
the date and time are extracted from this 4byte numeric field
some reading of standards is required

[diafol]

I always use unix timestamps (int) myself as they make arithmetic operations really easy. Perhaps I'm being naïve?

@AB - what're your thoughts on this, I remember you having un petit tête à tête with somebody over this a year or so ago. Can't remember the outcome.

[RazorRamon]

I think what you are looking for is probably a combination of the LIMIT option and the UNION command. If you have 4 sets of like results for 4 queries (each query returns like columns and the same number of columns ) then write your selection statements in parentheses and put UNION between them. Something like this:

(Select table1.topic, table1.comment, table1.username, table1.mydate from table1
where topic = topic1
order by mydate desc
Limit 10)
UNION
(Select table2.topic, table2.comment, table2.username, table2.mydate from table2
where topic = topic2
order by mydate desc
Limit 10)
UNION
(Select table3.topic, table3.comment, table3.username, table3.mydate from table3
where topic = topic3
order by mydate desc
Limit 10)

Each table will be limited to 10 results and all of the results will appear in a 30 column table.

So will the results show??

10 results from table1 desc by mydate
10 results from table2 desc by mydate
10 results from table3 desc by mydate

But wouldn't this have the possibility of the 2nd and 3rd set of results be more current than the 1st? Sounds like this is on the right track though.

[rch1231]

In effect your are running three separate queries to three separate tables with each one pulling the most current records from their respective tables and then joining the output into one large table. In answer to your question a slim maybe. The system runs the three queries and then once it has the results combines them into one table. The three queries would run at the same time. Does that make sense?

[almostbob]

after the first query is run,
output to:; a temp table, an array, or other element the op is comfortable using,
run another query on the temp table limit 10, arsort the array, or other element the op is comfortable using, to get the required 10

[RazorRamon]

(Select table1.topic, table1.comment, table1.username, table1.mydate, table2.topic, table2.comment, table2.username, table2.mydate, table3.topic, table3.comment, table3.username, table3.mydate from table1, table2, table3
order by mydate desc
Limit 10)

would this work? I haven't built the other tables or anything, but i want to know if it would work before i try going this route.