Member Avatar for 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?

  • 4 Contributors
  • 7 Replies
  • 259 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by RazorRamon

Dani AI

Generated

Short answer: stop storing human-friendly date strings and give each post a single machine-friendly timestamp (DATETIME or TIMESTAMP), index that column, then sort the combined result set and apply LIMIT. If you can, consolidate per-category tables into one posts table with a category column — that makes querying the "latest 10 across all categories" trivial and fast.

Recommended patterns (examples):

Create a single timestamped row (let the DB set it) and index it:

-- schema sketch
CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  topic VARCHAR(255),
  comment TEXT,
  username VARCHAR(100),
  category VARCHAR(50),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(created_at)
);

If you must keep separate tables, combine them with UNION ALL and ORDER/LIMIT the combined set (do NOT rely on LIMIT inside each subquery if you want the global top N):

(SELECT topic, comment, username, created_at, 'sports' AS source FROM sports)
UNION ALL
(SELECT topic, comment, username, created_at, 'news'   AS source FROM news)
ORDER BY created_at DESC
LIMIT 10;

Insertion: let the DB populate created_at and use prepared statements (avoid concatenated SQL and PHP-formatted date strings):

$stmt = $pdo->prepare('INSERT INTO posts (topic, comment, username, category) VALUES (?,?,?,?)');
$stmt->execute([$topic, $comment, $username, $category]);

Practical tips and caveats

  • As advised, don’t use formatted text for sorting — use a proper timestamp type. ’s unix-timestamp idea works too, but MySQL TIMESTAMP/DATETIME with an index is usually more convenient.
  • ’s UNION idea is on the right track; the key is ordering the combined result set, not each source individually.
  • The FROM table1, table2, ... approach produces Cartesian joins and won’t give the latest rows.
  • For high-read sites consider a materialized “recent_posts” table updated by triggers or a periodic job to avoid expensive unions at runtime.
  • Store UTC, format for users on output, and always validate/escape input (use prepared statements).

Recommended Answers

All 7 Replies

Member Avatar for 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.

Member Avatar for 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

Member Avatar for Member #120589

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.

Member Avatar for 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.

Member Avatar for 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?

Member Avatar for 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

Edited by almostbob because: n/a
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.