How to add 2 tables with multiple table columns?

Question

I have 2 tables with same field sponsor. I am trying to get data from both the tables.

When i try to get data from single column it works properly for example:

$q1 = " SELECT username FROM member_master WHERE sponsor = 'user1' UNION SELECT fname FROM upgrade_master WHERE sponsor = 'user1' ";

But when i add more columns in query it gives error mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\

This is my code:

<?php require("connect.php"); $q1 = "SELECT username FROM member_master WHERE sponsor='user1' UNION SELECT fname, lname FROM upgrade_master WHERE sponsor='user1'"; $e1 = mysqli_query($connection, $q1); if(mysqli_num_rows($e1)>0) { echo '<table><tr>'; while($row = mysqli_fetch_assoc($e1)) { $username = $row['username']; $fname = $row['fname']; $lname = $row['lname']; echo '<td>'. $username . '</td>'; echo '<td>'. $fname . '</td>'; echo '<td>'. $lname . '</td>'; } echo '</tr></table>'; } ?>

Do not try your query in PHP until you make it run in mysql

Your Common Sense
– Your Common Sense

2020-04-25 18:33:59 +00:00
Commented Apr 25, 2020 at 18:33 — Your Common Sense
– Your Common Sense, Commented Apr 25, 2020 at 18:33

Santda · Accepted Answer · 2020-04-25 18:43:20Z

I think you need to use a JOIN instead of a UNION for this. Try this statement:

SELECT mm.username, um.fname, um.lname FROM member_master mm JOIN upgrade_master um ON mm.sponsor = um.sponsor where mm.sponsor = 'user1';

A union is when you want to pull one similar field from two tables and list all the values for that field. That is why it only works with one column.

forpas · Accepted Answer · 2020-04-25 19:39:42Z

UNION requires both queries to return the same number of columns.
What you can do is concatenate the 2nd query's 2 columns to 1 column:

SELECT username FROM member_master WHERE sponsor='user1' UNION SELECT concat(fname, ' ', lname) FROM upgrade_master WHERE sponsor='user1'

or add in the 1st query a dummy null column:

SELECT username, null lname FROM member_master WHERE sponsor='user1' UNION SELECT fname, lname FROM upgrade_master WHERE sponsor='user1'

Also note that UNION ALL is always better for performance, if you don't want want duplicates filtered out which is what UNION does.

@JSLearner Are you sure that the answer that you accepted does what you want? Because this is not what you described in the question.
I got new learning with your post that column should be same for both the tables.
@JSLearner the answer that you accepted joins the tables but you asked union in your question. These are 2 different things.

wayan edi · Accepted Answer · 2020-04-25 19:28:45Z

you must use INNER JOIN to do like that

"SELECT member_master.username, upgrade_master.fname, upgrade_master.lname FROM member_master INNER JOIN upgrade_master ON member_master.sponsor = upgrade_master.sponsor WHERE member_master.sponsor='user1'"

Collectives™ on Stack Overflow

How to add 2 tables with multiple table columns?

3 Answers 3

2 Comments

4 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

1 Comment

Related