How to get data from 3 different mysql tables with one query

Question

My question is that I need to get a data (email) from different tables with one query. I have searched and I couldnt find enough information so I decided to ask here.

The idea is to send email to certain people from different departments and also send the email to everybody in all departments. My code is below;

if ($_POST['recipient'] == 'parents'){$query = "SELECT `email`, `first_name` FROM `users` WHERE `allow_email` = 1 AND `active` = 1";}
        if ($_POST['recipient'] == 'teachers'){$query = "SELECT `email`, `name` FROM `teachers` WHERE `status` = 1";}
        if ($_POST['recipient'] == 'staff'){$query = "SELECT `email`, `name` FROM `staff`";}


        $result = $con->query($query);
        while ($row = $result->fetch_array(MYSQLI_ASSOC)) {

            $mail = new PHPMailer();
            $mail->IsHTML(true);
            $mail->SMTPAuth   = true;                  // enable SMTP authentication
            $mail->Host       = $site_settings['smtp_host']; // sets the SMTP server
            $mail->Port       = 26;                    // set the SMTP port
            $mail->Username   = $site_settings['smtp_username']; // SMTP account username
            $mail->Password   = $site_settings['smtp_password']; // SMTP account password
            $email->From      = $site_settings['school_email'];
            $email->FromName  = $site_settings['school_name'];
            $email->Subject   = 'Newsletter: '.$_POST['subject'];
            $email->Body      = $_POST['body'];
            $email->AddAddress( $row['email'] );

            $email->Send(); 
        }

Could anyone help?

Parents Table = user_id, username, password, first_name, last_name, email, telephone, email_code, active, password_recover, type, allow_email, profile. Teachers = id, name, surname, telephone, email, status Staff = id, name, surname, email, telephone, position. I want to get the email data from all tables... — Vurkac, Oct 09 '15 at 16:41
Use a [UNION](https://dev.mysql.com/doc/refman/5.6/en/union.html) between the three — Mark Baker, Oct 09 '15 at 16:44
I tried but UNION did not work or may be I couldnt use it properly. Could you give me an example? — Vurkac, Oct 09 '15 at 16:45

max · Answer 1 · 2015-10-12T12:47:25.683

0

I suppose you could create a view in MySQL database and aggregate data for one entity like this:

CREATE VIEW `all_user_emails` (email, name, type) AS 
SELECT `email`, `first_name`, 'user' FROM `users` WHERE `allow_email` = 1 AND `active` = 1
UNION
SELECT `email`, `name`, 'teacher' FROM `teachers` WHERE `status` = 1
UNION
SELECT `email`, `name`, 'staff' FROM 'staff'

Then you may query this view like a table like this

SELECT * FROM all_user_emails WHERE type = `staff`

edited Oct 12 '15 at 12:47

answered Oct 09 '15 at 16:45

max

2,757
22
19

I have tried your code but it did not produce any data. – Vurkac Oct 09 '15 at 17:12
@Vurkac Are there any errors? There is a typo here: `WHERE status =` (should be `WHERE status = 1`), but aside from that, the code looks to be correct and seems to be what you want. – Oct 09 '15 at 17:42
I have fixed that typo and I get this -> Call to a member function fetch_assoc() on a non-object. Code is below: $sql = " CREATE VIEW `all_user_emails` (email, name, type) AS SELECT `email`, `first_name`, 'user' FROM `users` WHERE `allow_email` = 1 AND `active` = 1 UNION SELECT `email`, `name`, 'teacher' FROM `teachers` WHERE `status` = 1 UNION SELECT `email`, `name`, 'staff' FROM 'staff' SELECT * FROM all_user_emails WHERE type = `staff` "; $result = $con->query($sql); // output data of each row while($row = $result->fetch_assoc()) { echo $row['email']; } – Vurkac Oct 09 '15 at 17:47
@Vurkac You create the view in your database separately. Then you run the `SELECT` statement on your PHP page. – Oct 09 '15 at 20:07

score 0 · Answer 2 · answered Oct 09 '15 at 19:32

You can use a UNION between the three tables to get one result set. The query would look like this:

SELECT `email`, `first_name`, 'user' FROM `users` WHERE `allow_email` = 1 AND `active` = 1
UNION
SELECT `email`, `name`, 'teacher' FROM `teachers` WHERE `status` = 1
UNION
SELECT `email`, `name`, 'staff' FROM `staff`

Your PHP code might look like this:

if ($_POST['recipient'] == 'parents'){$query = "SELECT `email`, `first_name` FROM `users` WHERE `allow_email` = 1 AND `active` = 1";}
elseif ($_POST['recipient'] == 'teachers'){$query = "SELECT `email`, `name` FROM `teachers` WHERE `status` = 1";}
elseif ($_POST['recipient'] == 'staff'){$query = "SELECT `email`, `name` FROM `staff`";}
elseif ($_POST['recipient'] == 'all')($query="SELECT `email`, `first_name`, 'user' FROM `users` WHERE `allow_email` = 1 AND `active` = 1 UNION SELECT `email`, `name`, 'teacher' FROM `teachers` WHERE `status` = 1 UNION SELECT `email`, `name`, 'staff' FROM `staff`")

The column names from the query would be the column names from the first SELECT of the UNION, so to access the value that would contain 'teacher', 'staff' or 'user' you would use $row['user'] and if you wanted to use the person's name in your code you would use $row['first_name'], not $row['name'], but you could change that for consistency and clarity by starting out with:

SELECT `email`, `first_name` as name, 'user' as type FROM `users` ...

Thank you for reply. But I get tis error: Illegal mix of collations for operation 'UNION'. But when I remove the SELECT query for users, everything works fine. I think the reason is TEACHERS and STAFF tables have the same amount of column and the USERS table has more columns. Is there anything to do to solve this issue? — Vurkac, Oct 10 '15 at 10:16
That makes things complicated. I think perhaps this answer describes the problem better than I could: http://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql?rq=1 — Chris Hughes, Oct 10 '15 at 20:49

How to get data from 3 different mysql tables with one query

2 Answers2