How to loop data from multiple tables in MySQL?

Question

I have these tables with the following columns:

Article

1.1. title
1.2. text
1.3. sortnr

Congress

2.1. title
2.2. text
2.3. sortnr

And, obviously, others, but these are the ones that are the same. And what I'd like to do is write a query where I can then loop through these fields as if they were from one table.

What it looks like:

SELECT * FROM article, congress ORDER BY sortDate DESC LIMIT 3

but with that I can't use fields like title and so on because during the loop, all the, for example, title fields are being turned in to the ones from congress table.

Is there a way to mix the two tables and treat them like they were from one table considering that they aren't joined in any way?

Lookup JOIN syntax in my sql. [update] did not see the same column names, better do it like my next comments — JustOnUnderMillions
– JustOnUnderMillions, Commented Apr 28, 2017 at 11:56
@JustOnUnderMillions already did, which join would you suggest? — Xeen
– Xeen, Commented Apr 28, 2017 at 11:57
SELECT a.title as atitle , b.title as btitle FROM article a, konferansenentry b — JustOnUnderMillions
– JustOnUnderMillions, Commented Apr 28, 2017 at 11:57
@JustOnUnderMillions yes, but then when looping the title, I'd have to use atitle and btitle, but I want to use just title so that the php part doesn't get affected. — Xeen
– Xeen, Commented Apr 28, 2017 at 11:59

Ahmed Ginani · Accepted Answer · 2017-04-28 11:59:36Z

2

Try this:

SELECT *, 'article' as tblnm FROM article
UNION
SELECT *, 'konferansenentry' as tblnm FROM konferansenentry 
ORDER BY sortDate DESC 
LIMIT 3

It will only work if both table have same field other wise you have to select individual one by one.

answered Apr 28, 2017 at 11:59

Ahmed Ginani

6,6452 gold badges17 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Xeen Over a year ago

Thank you very much, works like a charm :) will accept in 9min

JustOnUnderMillions Over a year ago

Nice solution. Thump up.

Ahmed Ginani Over a year ago

Glad it worked. You can also visit : Ask questions, get answers, no distractions to get more information about SO and support me as much as you can.

Collectives™ on Stack Overflow

How to loop data from multiple tables in MySQL?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related