CODEWITHSUNDEEP
phpmysql
user2953877
user2953877

Reputation: 43

SELECT from two tables in mysql and display all the row's in the column in one of them?

okay, I'm a bit baffled with this one and I'm not entirely sure how to even explain it. I will try my best to explain it and i hope you guys understand what i mean!

I have a table in mysql called members which stores the members data. I also have a table which is called transactions which stores the orders/transactions made by the users.

what i need to do is this:

1- select both of the tables.

2- look to see which columns in both tables have the same email address (the emails are saved in both tables).

3- finally display the result from the transactions table with the same email.

I've tried something like this but i know this is wrong:

<?php 
// Query member data from the database and ready it for display
$sql = "SELECT payer_email FROM transactions
UNION
SELECT email FROM members";
$query = mysqli_query($db_conx, $sql);
$count = mysqli_num_rows($query);
if ($count > 1) {
    echo "There is no user with that id here.";
    exit(); 
}
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ 
$email2 = $sql;
}
?>

could someone please help me out with this?

EDIT:

enter image description here

EDIT:

HERE IS WHAT I HAVE SO FAR: Please ignore the mysql queries, i will convert them to mysqli before going live. this is just for a test.

<?php 
// Run a select query to get my letest 6 items
// Connect to the MySQL database  
include "config/connect_to_mysql.php"; 
$dynamicList22 = "";
$sql = mysql_query("SELECT 
t.payer_email,
t.first_name, 
GROUP_CONCAT( t.payer_email )
FROM
transactions t
INNER JOIN
members m
ON( m.email = t.payer_email )
GROUP BY t.payer_email");
$productCount = mysql_num_rows($sql); // count the output amount
    while($row = mysql_fetch_array($sql)){
             $payer_email = $row["payer_email"];
             $dynamicList22 .= '<table width="100%" border="0" cellspacing="0" cellpadding="6">
        <tr>
          <td width="17%" valign="top">'. $payer_email .'</td>
        </tr>
      </table>';
}
mysql_close();
?>

Upvotes: 0

Views: 2762

Answers (2)

Manu
Manu

Reputation: 901

Try this...

SELECT 
t.payer_email,
t.first_name, 
GROUP_CONCAT( t.item_name ) AS item_name
FROM
transactions t
INNER JOIN
members m
ON( m.email = t.payer_email )
GROUP BY t.payer_email

Read more about GROUP_CONCAT ..

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Use Inner join as

select * from `transactions` inner join `members` on `members`.`email` = `transactions`.`email`

Upvotes: 2

Related Questions