CODEWITHSUNDEEP
phpmysqlsql
mobile
mobile

Reputation: 317

Sorting mysql results by times from 3 tables

I have 3 tables: questions, articles and pictures

The rows in each table contain a current_timestamp column posted, and link to an id. I'd like to sort the results of the rows of all three by their timestamp and only echo the newest of the three (for example: if the question is newest from the ID, display that only)

if(count($interests) != 0){ foreach($interests as $interests_following){
    $interestid = mysql_result(mysql_query("SELECT `id` FROM `interests` WHERE `name` = '$interests_following'"),0);
    $interestquestions = @mysql_result(mysql_query("SELECT `question_text`, `posted` FROM `questions` WHERE `interest` = '$interests_following'"),0);
    $interestarticles = @mysql_result(mysql_query("SELECT `article_title`, `posted` FROM `articles` WHERE `interest_id` = '$interestid'"),0);
    $interestpictures = @mysql_result(mysql_query("SELECT `interest_pic_title`, `posted` FROM `interest_pictures` WHERE `interest_id` = '$interestid'"),0);

echo '.$interests_following.': //<Only display 1 newest item (article/picture/question here>

Upvotes: 0

Views: 92

Answers (4)

BryanH
BryanH

Reputation: 6062

I'll show you the SQL queries so you can see what I did; adapt it to your code as needed.

SELECT 
  (SELECT question_text 
     FROM questions ORDER BY posted DESC LIMIT 1 ),

  (SELECT article_title 
    FROM articles ORDER BY posted DESC LIMIT 1 ),

  (SELECT interest_pic_title 
    FROM interest_pictures ORDER BY posted DESC LIMIT 1)
;

This sorts by the timestamp DESCending, so the latest record is first, then limits it to the single record. The result will be question_text, article_title, interest_pic_title.

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Use UNION ALL:

SELECT posted 
FROM
(
    SELECT `question_text`, `posted` FROM `questions` 
    WHERE `interest` = '$interests_following'
    UNION ALL
    SELECT `article_title`, `posted` FROM `articles` 
    WHERE `interest_id` = '$interestid'
    UNION ALL
    SELECT `interest_pic_title`, `posted` FROM `interest_pictures` 
    WHERE `interest_id` = '$interestid'
) t
ORDER BY posted DESC LIMIT 1

Upvotes: 3

Arriane zafe
Arriane zafe

Reputation: 133

try this

$interestquestions = @mysql_result(mysql_query("SELECT `question_text`, `posted` FROM `questions` WHERE `interest` = '$interests_following' ORDER BY timestamp_field DESC limit 1"),0);


this will show only the latest data base on your timestamp.

Upvotes: 0

Cal
Cal

Reputation: 7157

To get a single row back:

$sql = "SELECT * FROM WHERE `name`='$interests_following' ORDER BY `posted` DESC LIMIT 1";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);

All of the work is in the SQL

ORDER BY `posted` DESC LIMIT 1

This orders results to have the newest first, then only return the first row.

If you mean to return only one item out of the 3 tables, you have 2 choices:

  • Fetch a row from each and then determine the newest in PHP
  • Use a UNION to find the single newest row in SQL

The latter makes for less code, but will probably be less readable.

Upvotes: 0

Related Questions