jmansa — 2010-02-17T06:48:18-05:00 — #1
I'm trying to figure out how to output data from 2 different db tables depending on the release dates...
id, subject, content, date
id, nsubject, ncontent, ndate
How do I get both into the query and then print in order of the dates...
Thanks in advance
r937 — 2010-02-17T07:08:02-05:00 — #2
SELECT id, subject, content, `date`
SELECT id, nsubject, ncontent, ndate
jmansa — 2010-02-17T14:27:44-05:00 — #3
But what if the 2 fields don't have the same amount of fields... This is the full script I want to work
$sql="SELECT u.fname, u.lname, u.new_userid, n.news, n.clubid, n.newsid, cl.clubname FROM ".$prefix."_club_users AS cu
INNER JOIN ".$prefix."_clubnews AS n ON cu.clubid = n.clubid
INNER JOIN ".$prefix."_users u ON u.new_userid = n.userid
INNER JOIN ".$prefix."_club cl ON cl.clubid = cu.clubid
ORDER BY n.dbdate DESC
Now this is the table I want to join with the clubnews somehow:
$sql="SELECT lang, subject, content FROM ".$prefix."_news WHERE lang=$lang ORDER BY date DESC";
Can this be done?
r937 — 2010-02-17T14:41:50-05:00 — #4
then you should not "dumb down" your original question to make it look as though the tables have the same number of similar columns
you'll have to explain how the tables should be joined
jmansa — 2010-02-17T15:31:36-05:00 — #5
Sorry... My bad.
I the "clubnews" I have 2 fields (news, dbdate) which is the ones I use. In the "news" table I have (subject, content, date) which is the fields of important allthough I have a "lang" filed which determens the language of the news... I want to join those 2 tables and order the news from them both by date/dbdate.
Hope this helps...
r937 — 2010-02-17T17:24:58-05:00 — #6
when you say you want to "join" these two tables, are you ~sure~ youe want to match the rows from them on some column?
or are you really looking to do a UNION?
jmansa — 2010-02-18T01:29:53-05:00 — #7
I might want to be looking at UNION but not sure...
r937 — 2010-02-18T01:32:22-05:00 — #8
okay, that's fair
perhaps you could go and do a bit of research on what UNION does?
after that, you might be in a better position to describe what you want to do with your queries and tables...
jmansa — 2010-02-18T02:40:56-05:00 — #9
OK... Have been surfing around, and this is what I have come up with...
$sql="SELECT n.newsid, n.news, n.dbdate FROM ".$prefix."_clubnews n
SELECT gn.id, gn.content, gn.date FROM ".$prefix."_GA_news gn WHERE gn.lang=213
ORDER BY n.dbdate DESC LIMIT 5";
If I don't user the n. or gn. it works fine, but how come I cant use those???
r937 — 2010-02-18T04:21:39-05:00 — #10
because the result set of a UNION query has unqualified column names
the column names are assigned in the first SELECT
so in your php code, you would reference newsid, news, and dbdate
alternatively, you can assign column aliases...
SELECT n.newsid <font color='"Blue"'>AS the_id</font>
, n.news <font color='"blue"'>AS the_con</font>tent
, n.dbdate <font color='"blue"'>AS the_date</font>
FROM ".$prefix."_clubnews n
FROM ".$prefix."_GA_news gn
BY the_date DESC LIMIT 5
and then reference the_id, the_content, and the_date
but one thing still puzzles me
in post #3, those two queries return wildly different sets of columns, that really aren't very amenable to a UNION (unlike post #1)