How to add non-empty values from two tables in mySQL DB using php?

Question

I have two tables inside a mysql database. table_1 and table_2

Both of these tables have 4 columns for users

user_1, user_2, user_3 and user_4

Now using PHP I first want to fetch the non-null values from two tables and I use this query.

<?php
$query1 = "

select * from table_1 where user_1!='' and user_1 is not null 
union all

select * from table_1 where user_2!='' and user_2 is not null 
union all

select * from table_1 where user_3!='' and user_3 is not null 
union all

select * from table_1 where user_4!='' and user_4 is not null 
    
";

Then I fetch it and count the entries total = mysqli_num_rows and it gives me correct details as 3.

Similarly I do it for table_2

<?php
    $query2 = "
    
    select * from table_2 where user_1!='' and user_1 is not null 
    union all
    
    select * from table_2 where user_2!='' and user_2 is not null 
    union all
    
    select * from table_2 where user_3!='' and user_3 is not null 
    union all
    
    select * from table_2 where user_4!='' and user_4 is not null 
        
    ";

and it shows me total count as 2 which is correct.

But when I add count1 and count2

$count1 = mysqli_num_rows($data1);
$count2 = mysqli_num_rows($data2);

$total = $count1+$count2;

It shows either 1 or some weird value.Please suggest a fix

The code as you have described it won't produce the result you claim. Please post the actual code that delivers this result. — Tangentially Perpendicular
– Tangentially Perpendicular, Commented Aug 20, 2021 at 22:10
First let's just say that's a really weird data structure and it probably isn't helping your issue. What are these tables representing, why did you design them with the same data item in multiple different columns and tables? It's pretty much the first rule of database design that you don't do that because it causes issues. — ADyson
– ADyson, Commented Aug 20, 2021 at 22:12

Lajos Arpad · Accepted Answer · 2021-08-22 15:13:40Z

2

Everything depends on what $data1 and $data2 is. If they contain the results of $query and $query2, respectively, then one could expect the result to be 5. However, since your result is different, it follows that $data1 and $data2 is not what you think. So, in order to find out what the problem is, you will need to find out what $data1 and $data2 is. You will need to var_dump them and analyze what their value is, detect the nature of the anomaly. Once that's done, you will need to look at the code and figure out how that value was ending up into your variables.

answered Aug 22, 2021 at 15:13

Lajos Arpad

80.4k42 gold badges123 silver badges235 bronze badges

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

Comments

DolDurma · Accepted Answer · 2021-08-22 18:28:03Z

2

to getting which column is not empty, you should add some condition as AND and if your fields have whitespace please consider TRIM condition such as:

SELECT * FROM `table_1` WHERE `user_1` IS NOT NULL AND TRIM(column) <> '' union all

notice: if you want use more than one command in php variable as string, you should add semicolon on end of each command

$query ="
SELECT * FROM `table_1` WHERE `user_1` IS NOT NULL AND TRIM(user_1) <> '' union all;

SELECT * FROM `table_2` WHERE `user_2` IS NOT NULL AND TRIM(user_2) <> '' union all;

SELECT * FROM `table_3` WHERE `user_3` IS NOT NULL AND TRIM(user_3) <> '' union all;
"

edited Aug 22, 2021 at 18:28

answered Aug 22, 2021 at 15:15

DolDurma

17.6k59 gold badges223 silver badges428 bronze badges

Collectives™ on Stack Overflow

How to add non-empty values from two tables in mySQL DB using php?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related