Combining two SQL queries PHP

Question

Thanks for reading my question. I've been going at this for a day and a half now and I cant seem to get this to work. What I'm trying to do is combine two SQL queries.

The connection to the database is correctly set-up. This is all working. So the queries I'm trying to combine are:

$sql = "SELECT * FROM pf_postmeta WHERE meta_key = '_wp_attached_file' ";
$result = $conn->query($sql);
foreach ($result as $row) {
        // do my stuff
    }
$conn->close();

And

$sql = "SELECT * FROM pf_posts WHERE post_type = 'cases' ";
$result = $conn->query($sql);
foreach ($result as $row) {
        // do my stuff
    }
$conn->close();

Now I've read about JOIN and UNION but I can't get the two queries to work together.

I have tried using UNION ALL to no avail.

$sql = "SELECT *FROM pf_postmeta WHERE meta_key = '_wp_attached_file'
        UNION ALL
        SELECT *FROM pf_posts WHERE post_type = 'cases'";
$result = $conn->query($sql);
foreach ($result as $row) {
        // do stuff
    }
$conn->close();

This doesn't show anything.

UPDATE AS REQUESTED: If I remove the UNION ALL SELECT part my query works. If I run this code in MySQLWorkbench it say's

09:07:30 SELECT * FROM deb100651n2_pf.pf_posts WHERE post_type='cases' UNION ALL SELECT * FROM deb100651n2_pf.pf_postmeta WHERE meta_key = '_wp_attached_file' Error Code: 1222. The used SELECT statements have a different number of columns 0.050 sec

Is there anyone who has a solution form me? This has been bugging me for the past 1,5 day's...

(Note: I'm pretty new to the whole SQL thing, so please be gentle ...)

Again thanks for your time...

Have you checked the error output to see what errors the UNION query is giving you? That'd be a good place to start. — Nick Coons, Oct 18 '17 at 06:57
Can you tell my how to get the debug "output"? I will be happy to update the question. — Jay-oh, Oct 18 '17 at 06:59
Instead of running these in PHP you might be better off running them in MySQL Workbench or PHPMyAdmin so you can see if there are any errors. We can't really help you to much without seeing your schema. JOIN will only work if there is a relationship between the two tables — Halfpint, Oct 18 '17 at 07:02
Did you try to execute `Union` query manually in mysql workbatch of somewhere else? — Parixit, Oct 18 '17 at 07:02
if you want to do a join you need a field in common, if you whant to do an union you need to have the same structure, so, what is the structure of the two tables? — Roberto Bisello, Oct 18 '17 at 07:04
if you going to select * then the column of both tables must be equal to run query. SELECT city FROM Customers WHERE Country='Germany' UNION All SELECT city FROM Suppliers WHERE Country='Germany' — newbie, Oct 18 '17 at 07:04
Is there any similarities that you can connect Query1 with Query2? e.g. pf_posts has a column called pf_post_meta_id or something like that? I mean, are they "connectable"? If they are, you should use a join, if they aren't, I'd recommend using two queries like you're already doing. — MadsBinger, Oct 18 '17 at 07:07
and that message contains the exact reson why it failed!!!! `different number of columns` — Paul Maxwell, Oct 18 '17 at 07:10
Your SQL error is because as earlier commented, that the columns doesn't match. The columns from BOTH SELECT must be equal. It's possible to give them names by yourself by SELECT column as 'tekst' in both queries. Then you should get it running. — MadsBinger, Oct 18 '17 at 07:12
Yes, So... Any way I can still combine these queries. Or should I run them separate? And then combine the output? — Jay-oh, Oct 18 '17 at 07:12
It seems to me like the error message is completely self-explanatory. — Strawberry, Oct 18 '17 at 07:14
@Jay-oh consider reading at https://stackoverflow.com/questions/1227835/combine-two-tables-for-one-output — newbie, Oct 18 '17 at 07:18

score 3 · Answer 1 · answered Oct 18 '17 at 07:07

To perform a "union query" the number of columns MUST match: plus, the data type of each column must be compatible (e.g. column 3 of first query is decimal, and column 3 of second query is integer). If either the number of columns is different, or any column is of an incompatible type, the query will produce an SQL error.

So. Here is a very great reason to cease using select * in any production code. You must specify the columns names, making sure both select lists have the same number of columns, and that those columns are compatible.

score 1 · Answer 2 · answered Oct 18 '17 at 07:20

1

As far I guessed from what you have stated above there might be column miss match issue in your union query. While using union operator you must remember three things-

Each SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement must also be in the same order

Therefore if you perform a SELECT * .. operation on both table then your pf_postmeta and pf_post table must have same number of column having similar data types and ordering. You may try SELECT id from pf_postmeta UNION ALL SELECT id from pf_post for clearing your concept how union works assuming there is id column at both table.

answered Oct 18 '17 at 07:20

Erfan Ahmed

1,536
4
19
34

This sounds logical. Will this work even if the ID's are not the same? `fp_postmeta` has `meta_id` and `fp_posts` has `ID`. Must they match in row name? – Jay-oh Oct 18 '17 at 07:54
Yes. `union` fetches all rows from the combining tables. Think like an algebraic `union` of two set. If set `a = {1,2}` and set `b = {5,6}` then `a union b = {1,2,5,6}`. Name of the column must not be same as long as they both have same data type. For more detail visit this link - https://www.w3schools.com/sql/sql_union.asp Hope that helps. – Erfan Ahmed Oct 18 '17 at 07:58
So if they don't match I cant use `union` allright. Well, back to the drawing board i think then. And do it some other way... – Jay-oh Oct 18 '17 at 08:02

score -4 · Answer 3 · answered Oct 18 '17 at 07:08

-4

why to make so complicate make it simple. Try it:

SELECT *  FROM pf_postmeta 
WHERE meta_key = '_wp_attached_file'
UNION
SELECT * FROM pf_posts
WHERE post_type = 'cases'

answered Oct 18 '17 at 07:08

vicky

31
2

UNION does not solve this. UNION ALL is perfectly valid. What isn't good is using `select *` and that is why the number of columns is different. The top select has != columns to the bottom select. (NB: UNION ALL is faster by the way.) – Paul Maxwell Oct 18 '17 at 07:13

Combining two SQL queries PHP

3 Answers3