CODEWITHSUNDEEP
phpmysqlsqldatabase
GeekWithGlasses
GeekWithGlasses

Reputation: 592

PHP- Join Multiple Tables not working as expected

I have two tables in my database.

I want to compare the each row of Email of table-1 with the each row of Email2 of table-2 if their content matches then I want to store the Mac of table-2 to Mac of Result table .

this is table-1

Name       Email

name1      [email protected]
name2      [email protected]

this is table-2

Email2           Mac

[email protected]     77777
[email protected]       88888

this is Result Table

ID      Mac

1     77777
2     xxxxx

Currently I am using this code to compare and insert data in result_table . It is not running as expected. Its not even comparing Email1 and Email2 instead of, it just keeps on dumping the the values(Mac) multiple times in table like 28 times on running script only one single time.

<?php
include 'conn.php';
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
} 
$query = mysqli_query($conn, "insert into result_tbale(mac)
select table2.mac from table2
inner join table1 on table1.email = table1.email");

?>

I am well aware the similar questions is being asked before but none of them is like my question or has complete a answer that's why I asked

Upvotes: 0

Views: 84

Answers (6)

Jeff
Jeff

Reputation: 9684

This will do what you want

insert into results (mac)
    select t2.mac
    from table1 t1
    join table2 t2
        on t1.email = t2.email;

The SQL Fiddle for it: http://sqlfiddle.com/#!9/5d311/1

Upvotes: 0

Shadow
Shadow

Reputation: 34253

You have typo in the join condition:

...inner join table1 on table1.email = table2.email2

Upvotes: 4

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

There is a bug in your code. You are using table1 twice in the ON clause of your query:

insert into result_table (mac)
select table2.mac from table2
inner join table1 on table1.email = table2.email

Demo here

Upvotes: 3

Steven Nigh
Steven Nigh

Reputation: 1

Part of your problem is that your join looks to be on the same field. Try changing your join such that table1.email = table2.email

Upvotes: 0

Rachid B.
Rachid B.

Reputation: 388

Your inner join seems useless because you join on table1.email=table1.email.

You need to join on table2.email

Upvotes: 0

Monofuse
Monofuse

Reputation: 827

Your inner join is wrong, and if what your columns are called is correct than you should have the following:

table1.email = table2.email2

Upvotes: 0

Related Questions