Displaying the latest entries from multiple tables

[ketanco]

Hello,

I have several tables say newsaaa, newsbbb newsccc etc... which are all the same but I use them to store news that belong to different categories. The columns are id (primary key and auto incr.), date, title, paragraph1.

When I want to display the latest entry or entries form just one particular table, it is no problem and I know what to do.

But now I would also like to display the title, date and paragraph1 values of latest entry when looked at ALL of the tables. Then under that, I want to display the second to latest entry (again when looking at ALL tables). Then under that, third latest entry and so on....Of course this is considering the date...(If two entries have the same date, then I am not sure how to handle or if it displays automatically under each other)...

Can someone write a sample line of code or correct what I wrote below…This is what I could think of but needs improvement…

$query=mysql_query("SELECT * FROM newsaaa OR newsbbb OR newsccc WHERE date ORDER BY date DESC LIMIT 1");
while ($row = mysql_fetch_array($query)) {
echo"<p class='newstitle1'>" . $row['date']."<br/>". $row['title'] . "</p>". 
"<p id=newstext1>" . $row['paragraph1']."<br/>"."<br/>";
$b=$row['id'];
echo"<a href='http://www………………..com/(xxx)*/display_(xxx)*_whole.php?id=$b'".">"."(more...)"."</a>"  ; }

*Note: (xxx) means I want the last three letters of the table name there (the table from which it displays for that instance), in order to display proper link…Is that possible? How is the syntax?

[bpat1434]

perhaps you should rethink your database design. If the only thing different between the tables is a category name, perhaps you should combine the data into one table, and add a column "category" (or better yet category_id) and fill that with a different value based on the category. For example, to keep things simple, I'd have 2 tables: news and news_categories. I'd have them set up like so:

+------------------------------------------------------------------------------+
| news_categories                                                              |
+------------------------------------------------------------------------------+
| id | name                                                                    |
+----+-------------------------------------------------------------------------+
|  1 | News AAAA                                                               |
|  2 | News BBBB                                                               |
|  3 | News CCCC                                                               |
+----+-------------------------------------------------------------------------+

+------------------------------------------------------------------------------+
| news                                                                         |
+------------------------------------------------------------------------------+
| id | category_id | title           | paragraph1               | date         |
+----+-------------+-----------------+--------------------------+--------------+
|  1 |      1      | Test entry      | this is ome demo text... | 2009-08-08   |
|  2 |      2      | Test entry 2    | this is some demo ...    | 2009-08-08   |
|  3 |      3      | Test entry 3    | this is another demo ... | 2009-08-08   |
|  4 |      2      | Test entry 4    | and another demo...      | 2009-08-08   |
|  5 |      3      | Test entry 5    | yet another demo...      | 2009-08-08   |
|  6 |      1      | A final entry   | The last one...          | 2009-08-08   |
+----+-------------+-----------------+--------------------------+--------------+

Then when you wanted to display all posts from a category, it's as simple as:

SELECT n.*, nc.name AS `category`
FROM `news` AS n
LEFT JOIN `news_categories` AS nc
    ON n.`category_id` = nc.`id`
WHERE n.`category_id` = {$category_id}
ORDER BY n.`date` DESC

That would get all those items from a single category ordered by date. Then to get the top 3 items by date, no matter what category, just remove the "where" clause from the query above and now you'll have the latest 3 entries from all categories instead of just from one.

Hope that helps.

[NogDog]

I would strongly recommend pursuing Bpat's suggestion of using one table. If, however, that is not an option, I think you could possibly do a UNION query, something like:

(SELECT col1, col2, col3, date FROM table_a)
UNION
(SELECT col1, col2, col3, date FROM table_b)
UNION
(SELECT col1, col2, col3, date FROM table_c)
ORDER BY date DESC
LIMIT 1

[ketanco]

Thanks, I know what you guys are saying about the table structure, and thought about that too, but right now it is best if I don't mess with it... I will use the union statement, it looks like the solution, based on my current table structure. I appreciate the responses... How about my last question, can you use part of the table name based on from what table the data is displayed at that moment? Lets say the data is being displayed form newsbbb. Can I take that bbb and use it to produce the link? so that when the user hits (more...) he goes to that table...Please see the last line of code in my question.

[bpat1434]

Sure, when using the UNION clause, just add an extra string to the SELECT clause to declare what table it's coming from:

(
    SELECT col1, col2, col3, date, 'aaa' AS `table`
    FROM `newsaaa`
)
UNION
(
    SELECT col1, col2, col3, date, 'bbb' AS `table`
    FROM `newsbbb`
)
UNION
(
    SELECT col1, col2, col3, date, 'ccc' AS `table`
    FROM `newsccc`
)
ORDER BY `date` DESC
LIMIT 5

Then the data would come back and have either "aaa" or "bbb" or "ccc" as the column "table" and you can use $row['table'] to tell them apart.

[ketanco]

Thanks, I wrote based on what you said...Two more questions here:

1) On line 1 below, it gives unexpected T string error. Probably a syntax or quotation mistake but dont know how to resolve. You wrote it this way for some reason I believe, but from one of my other php files, I have the syntax as $query=mysql_query("select * from newsai ORDER BY id DESC LIMIT 1"); for instance...The quotation below, based on what you wrote is different, but I am not sure how to convert...

2) Based on what you said about having another column as 'table', I also wanted to have another column as 'folder', in the output...Did I do it correctly below?

3) In the last line, in place of aaii I want to insert that $row[folder] and in place of ai I want to insert $row[table] column name. How to do that with correct syntax and quotation?

$query=mysql_query (SELECT id, title, date, par1, 'ai' AS folder 'ai' AS table FROM newsai)
UNION(SELECT id, title, date, par1, 'domestic' AS folder, 'dom' AS table FROM newsdom)
UNION(SELECT id, title, date, par1, 'events' AS folder, 'eve' AS table FROM newseve)
UNION(SELECT id, title, date, par1, 'industrial' AS folder, 'ind' AS table FROM newsind)

ORDER BY date DESC LIMIT 3;

while ($row = mysql_fetch_array($query)) {

$b=$row['id'];
$c=date("F j", strtotime($row['date']));

echo"<span id=newsdate1>".$c."</span>"."<span class='newstitle1'>" . "<br/>". $row['title'] . "</span>".

"<span id=newstext1>" . $row['par1'] ."<a href='aaii/display_ai_whole.php?id=$b'".">"."(more...)"."</a>" ; }

[NogDog]

The entire query string needs to be quoted (double quotes would work in this case). Remember that PHP is not doing the query, it is sending the query string to MySQL so that it can execute it, thus PHP just treats it as another string literal.

I find it a bit cleaner (and more flexible) to assign the query string to a variable, then use that variable as the parameter for mysql_query().

$sql = "
(SELECT id, title, date, par1, 'ai' AS `folder` 'ai' AS `table` FROM `newsai`)
UNION
(SELECT id, title, date, par1, 'domestic' AS `folder`, 'dom' AS `table` FROM `newsdom`)
UNION
(SELECT id, title, date, par1, 'events' AS `folder`, 'eve' AS `table` FROM `newseve`)
UNION
(SELECT id, title, date, par1, 'industrial' AS `folder`, 'ind' AS `table` FROM `newsind`)
ORDER BY `date` DESC
LIMIT 3
";
$query = mysql_query($sql);

[ketanco]

Okay now what you wrote works but there is another error for the next line I which I dont understand... I put in what you wrote...And right after that, the code continues as (just look at the first line that starts with while):

while ($row = mysql_fetch_array($query)) {

$b=$row['id'];
$c=date("F j", strtotime($row['date']));

echo"<span id=newsdate1>".$c."</span>"."<span class='newstitle1'>" . "<br/>". $row['title'] . "</span>". 

"<span id=newstext1>" . $row['par1'] ."<a href='ai/display_ai_whole.php?id=$b'".">"."(more...)"."</a>"  ; }

In this code above, for the first line, it gives the following error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource ...

I dont understand why...All table names and column names in the $sql were correct (there are more columns in these tables, but I dont think that matters).... The database is correct. But there is still this error...

[bpat1434]

Then there was something wrong with teh query. Modify the mysql_query() line to this:

$query = mysql_query($sql) or die('MySql Error: '.mysql_error());

WAIT!!! I see it now. Charles' SQL is wrong 🙁 Let me grab it and fix it for ya.

NOTE: I am just giving you the SQL, this still needs to be in a string like Charles said a couple posts above.

Items in RED are missing and causing problems:

(SELECT id, title, date, par1, 'ai' AS `folder`[b][color=red],[/color][/b] 'ai' AS `table` FROM `newsai`)
UNION
(SELECT id, title, date, par1, 'domestic' AS `folder`, 'dom' AS `table` FROM `newsdom`)
UNION
(SELECT id, title, date, par1, 'events' AS `folder`, 'eve' AS `table` FROM `newseve`)
UNION
(SELECT id, title, date, par1, 'industrial' AS `folder`, 'ind' AS `table` FROM `newsind`)
ORDER BY `date` DESC
LIMIT 3 

Note that it's a comma in the first SELECT clause.

[ketanco]

Thanks a lot, and this works flawless now...

However, it produced a result that I didn't anticipate before... I want the latest one entry from each category, and then they should be sorted by date.

This produced a result that, if one category's latest entries are later than another category, it first gives those latest entries, so it may give more than one row for a category....But what I want is only one latest row from each category. Then all of those latest rows from each category should be sorted...So, in other words, I want to see all categories latest entries...Even if category A's 2nd latest is newer than category B's most latest, I dont want to see category A's 2nd latest. I still want to see category B's latest. (I know it sounds confusing but I hope I was able to tell) How can I do that?

[NogDog]

Then you'll have to put an ORDER BY DESC LIMIT 1 at the end of each of the individual (parenthesized) queries as well.

PS: You can probably then remove the LIMIT clause at the end of the entire query, then, if you want one of each.