Mysql selecting data from multiple table

Hi
I am having problem with calling data from many table in a database.
so here is my query

(SELECT * FROM current ORDER BY time DESC LIMIT 1)
UNION (SELECT * FROM energy ORDER BY time DESC LIMIT 1)
UNION (SELECT * FROM frequency ORDER BY time DESC LIMIT 1)
UNION (SELECT * FROM kw ORDER BY time DESC LIMIT 1)
UNION (SELECT * FROM pf ORDER BY time DESC LIMIT 1)
UNION (SELECT * FROM power ORDER BY time DESC LIMIT 1)
UNION (SELECT * FROM voltage ORDER BY time DESC LIMIT 1)

this is what i get

so the problem is, other value from energy, frequency and others goes into current.

What is the php/sql code so that I can echo the data out?

Thank you
Ayu

cont…

how do i solve so that i can get values from database like below picture

new user can’t post more than 1 picture in one post…

If I try to simulate that on a couple of quick tables I just get a syntax error, so I’m not sure where to go from that. Presuming that’s just something I am getting wrong, this note from the MySQL doc on UNION says:

“The column names from the first SELECT statement are used as the column names for the results returned.”

so all of your information will be called whatever the column in the “main” SELECT returns.

An alternative would be to collect the information from running separate queries. That’s probably more of a debate for the “database” area of the forum though.

So yeah when you’re using UNION the first select determines the field names for the entire result set. As you’re not naming fields explicitly in your query, you’re getting the names of the fields in the tables.

So you can do it like this:

(SELECT current.name, current.time, current.current AS value FROM current ORDER BY time DESC LIMIT 1) UNION (SELECT energy.name, energy.time, energy.energy AS value FROM energy ORDER BY time DESC LIMIT 1) UNION (SELECT frequency.name, frequency.time, frequency.frequency AS value FROM frequency ORDER BY time DESC LIMIT 1) UNION (SELECT kw.name, kw.time, kw.kw AS value FROM kw ORDER BY time DESC LIMIT 1) UNION (SELECT pf.name, pf.time, pf.pf AS value FROM pf ORDER BY time DESC LIMIT 1) UNION (SELECT power.name, power.time, power.power AS value FROM power ORDER BY time DESC LIMIT 1) UNION (SELECT voltage.name, voltage.time, voltage.voltage AS value FROM voltage ORDER BY time DESC LIMIT 1)

Here I’m assuming that each of your tables has a field that is named the same as the table eg current field in the current table.

Note that you don’t really need to use an alias for each part of the union as the names are already set by the first select (eg voltage.voltage or voltage.voltage AS value will be the same as they’re not the first part of the query), but it’s just how I like to do it.

Ah, the penny drops. My issue with the syntax error was because I didn’t put the initial SELECT clause in brackets, only the subsequent UNION, so it tried to apply the ORDER BY to all of it.

So is the only way that the OP can get the table information by hard-coding it, knowing that the first row will be current, the second will be energy, just because that’s the way the query is ordered?

Your database design is incorrect. You need to learn about database normalization.

Thanks guys for the help. i’ll improve my skill in db normalization.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.