How should this be done?

[Azzyh]

If you go to http://www.foxnews.com, and scroll down a little you would see the box "LATEST NEWS".

 

Now i want to do something like that, kinda, but with modification.

 

I have 3 queries, that gets out information from 3 tables.

 

And each table have around 5 tables and counting, with different types of news.

Those 3 tables are these types of News: Buisness, Politics and Sports.

 

Now i want to make a box, (i can do this in css), that displays the latest table of those 3 tables.

 

So let me give you an example to understanding better:

Here's 5 news title's:

 

Building a Brewery on a Shoestring
Obama Stumps for Coakley in Surprisingly Tight Massachusetts Senate Race
Wide Write: The Week 17 Scramble
Report: Doctor linked to Tiger part of PED probe
Did coach hit player? South Florida to look into it

 

And these titles(news) are from those 3 tables..

And then lets say i create a new column in "Sports", then it should update so it would be like this:

 

This Is A Title For Sport
Building a Brewery on a Shoestring
Obama Stumps for Coakley in Surprisingly Tight Massachusetts Senate Race
Wide Write: The Week 17 Scramble
Report: Doctor linked to Tiger part of PED probe
Did coach hit player? South Florida to look into it

 

Hope you all understand. Really appreciate this, ive been searching pretty long, and ended up here, phpfreaks.com the best? : )

 

[SWD]

Hi there,

To retrieve from multiple tables your best bet would be to use a UNION statement and ensure you include a column that has the last update datetime, that way you can order your final result set by the datetime column.

 

My initial response to your post was, why do you have separate tables for all the different categories of news?  Why not have all of the news in the same table with a news_category column?

 

Anyway hopefully my suggestion above helps....

 

Josh

[Azzyh]

Hello.

I dont know what UNION statement is, but i understanded the rest of it. And by datetime, you mean so each rows have datetimes stamps, and you then check for the latest through date(); ? why not just order by id ?

 

Im going to search up what UNION statement is

[ignace]

Your explanation reveals your database has been badly designed. You only need 2 tables: news and news_category. The table news contains any news (sports, politics, ..) and the table news_category contains the categories: sports, politics, ..

 

So if you want all sports related news articles:

 

SELECT * FROM news, news_category WHERE news_category.id = news.category_id AND news_category.name = 'Sports';

[Azzyh]

 

Thanks for reply

But what if i do have those 3 tables, and not a column news_category. Cause i wish to make this for other 3 tables too, which aint in news..

[Azzyh]

Thank you SWD. I found out was UNION statement was and used it, heres source, and i got what i wanted.

 

<?php

 

include("../tilslut.php");

$hent_stuff = mysql_query("SELECT * FROM nyheder UNION SELECT * FROM artikler UNION SELECT * FROM debat ORDER BY id DESC LIMIT 6");

echo "<b>Seneste Nyt:<br></b>";

while($sum = mysql_fetch_array($hent_stuff))

{

echo $sum['title'] . " Dato: " . $sum['dato']."<br>";

}

?>

 

[Azzyh]

Hey again what if the table "buisness" and "sports" doesnt have the same field, i mean if sports have:

 

"topic" as field name, and in buisness table it's "title",

 

how should i do to get it out both? thanks

[SWD]

hey there Azzyh,

Good to see you've made some progress. I'm with Igance on this one, the database design isn't great if you're stuck with 3,4 or 5 tables.  But let's presume you can't change your situation... if your tables have different column names for the same data - such as your title/topic predicament then the column position becomes your guide.

 

So let's presume you have the following:

 

Table: Sports

Topic

Content

Last_Modified

 

Table: Business

Title

News_Text

Last_Modified

 

Your Union statement would read as follows:

SELECT topic, content, last_modified

FROM sports

UNION

SELECT title, news_text, last_modified

FROM BUSINESS

ORDER BY 3 DESC

 

In the results your three column headings will be topic, content, last_modified.... if you wanted that naming to be different you could do the following:

 

SELECT topic as NewsText, content as NewsCopy, last_modified

FROM sports

UNION

SELECT title, news_text, last_modified

FROM BUSINESS

ORDER BY 3

 

When you're accessing your result set you would then be looking for NewsText, NewsCopy and last_modified as your column references!

 

Hope that helps and good luck

Josh

 

[Azzyh]

Thanks for reply.

Hello

 

How do I echo out the data??