0

Hello everyone! This is my first post, been a long time reader though.
First of all, it's been a while since I don't program and I'm rusty (very).

The problem is: I just can't learn how to use the JOIN or UNION commands, everything I read is confusing.
I'm simply trying to retrieve info from two different tables (same db) at the same time.

Here's my code:

if ($_POST['Teachers'] && $_POST['Students']) {
    $sql = "SELECT email FROM teachers"; //Here I also want to pull this off: SELECT email FROM students.
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result) or die(mysql_error())) {
        $mails.= "$row[0],";
    }
}

The $_POST variables come from a previous page where the school's principal will select if to send a mass mail to students and teachers or just one of them, so there are other if statements above, all working good. The $mails will be used on mail().

So, that's it, I'm trying my best and I've been reading for 3 days to no avail.
Thanks in advance guys.
Carlos I. Romero. (Systems Engineer and Administration: studying in Mexico).

Edited by Charls Frdinand: n/a

2
Contributors
7
Replies
8
Views
7 Years
Discussion Span
Last Post by Charls Frdinand
0

This is a great opportunity to refactor the tables so that a student is a person and some things about being a student; a teacher is a person and some things about being a teacher. Then the select is done on the person table and your problem disappears.
like:

create table person (
  id integer not null auto_increment,
  email varchar(100)
  /* etc */
)
create table student (
  id integer not null auto_increment /* not strictly needed */,
  person_id integer not null foreign key references person.id, /* syntax? */
  current_GPA float /* or whatever a teacher would not have */
  /* etc */
)
 /* similar for teacher */
0

Hi! Thank you very much for helping me, really appreciate it.
So here's the code, depending on the election of the principal it will retrieve the requested mails (hopefully):

(Admins are teachers with privileges)

//Only Students
if ($_POST['Students'] empty($_POST['Teachers'] empty($_POST['Admins']) {
    $sql = "SELECT email FROM students ORDER BY nombre";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $correos .= "$row[0],";
    }
//Only Teachers
} elseif ($_POST['Teachers'] && empty($_POST['Students']) empty($_POST['Admins']) {
    $sql = "SELECT email FROM teachers ORDER BY email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $correos .= "$row[0],";
    }
//Only Admins
} elseif ($_POST['Admins'] && empty($_POST['Students']) && empty($_POST['Teachers']) {
    $sql = "(SELECT email FROM teachers WHERE Admin='1') UNION (SELECT email FROM students) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $correos .= "$row[0],";
    }
//Students and Teachers
} elseif ($_POST['Students'] && $_POST['Teachers'] && empty($_POST['Admins']) {
    $sql = "(SELECT email FROM students) UNION (SELECT email FROM teachers) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $correos .= "$row[0],";
    }
//Students and Admins
} elseif ($_POST['Students'] && $_POST['Admins'] && empty($_POST['Teachers']) {
    $sql = "(SELECT email FROM alumnos) UNION (SELECT email FROM maestros WHERE Admin='1') order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $correos .= "$row[0],";
    }
//Teachers and Students
} elseif ($_POST['Teachers'] && $_POST['Students'] && empty($_POST['Admins']) {
    $sql = "(SELECT email FROM teachers) UNION (SELECT email FROM students) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $correos .= "$row[0],";
    }
//Teachers and Admins
} elseif ($_POST['Teachers'] && $_POST['Admins'] && empty($_POST['Students']) {
    $sql = "SELECT email FROM maestros order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $correos .= "$row[0],";
    }
//Admins and students
} elseif ($_POST['Admins'] && $_POST['Students'] && empty($_POST['Teachers']) {
    $sql = "(SELECT email FROM teachers WHERE Admin='1') UNION (SELECT email FROM students) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $correos .= "$row[0],";
    }
//Admins and Teachers
} elseif ($_POST['Admins'] && $_POST['Teachers'] && empty($_POST['Students']) {
    $sql = "(SELECT email FROM teachers WHERE Admin='1') UNION (SELECT email FROM teachers) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $correos .= "$row[0],";
    }    
}
0

(Sorry for the double post, don't know what I did)
Hi! Thank you very much for helping me, really appreciate it.
So here's the code, depending on the election of the principal it will retrieve the requested mails (hopefully):

(Admins are teachers with privileges)

//Only Students
if ($_POST['Students'] empty($_POST['Teachers'] empty($_POST['Admins']) {
    $sql = "SELECT email FROM students ORDER BY name";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $mails .= "$row[0],";
    }
//Only Teachers
} elseif ($_POST['Teachers'] && empty($_POST['Students']) empty($_POST['Admins']) {
    $sql = "SELECT email FROM teachers ORDER BY email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $mails .= "$row[0],";
    }
//Only Admins
} elseif ($_POST['Admins'] && empty($_POST['Students']) && empty($_POST['Teachers']) {
    $sql = "(SELECT email FROM teachers WHERE Admin='1') UNION (SELECT email FROM students) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $mails .= "$row[0],";
    }
//Students and Teachers
} elseif ($_POST['Students'] && $_POST['Teachers'] && empty($_POST['Admins']) {
    $sql = "(SELECT email FROM students) UNION (SELECT email FROM teachers) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $mails .= "$row[0],";
    }
//Students and Admins
} elseif ($_POST['Students'] && $_POST['Admins'] && empty($_POST['Teachers']) {
    $sql = "(SELECT email FROM alumnos) UNION (SELECT email FROM maestros WHERE Admin='1') order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $mails .= "$row[0],";
    }
//Teachers and Students
} elseif ($_POST['Teachers'] && $_POST['Students'] && empty($_POST['Admins']) {
    $sql = "(SELECT email FROM teachers) UNION (SELECT email FROM students) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $mails .= "$row[0],";
    }
//Teachers and Admins
} elseif ($_POST['Teachers'] && $_POST['Admins'] && empty($_POST['Students']) {
    $sql = "SELECT email FROM maestros order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $mails .= "$row[0],";
    }
//Admins and students
} elseif ($_POST['Admins'] && $_POST['Students'] && empty($_POST['Teachers']) {
    $sql = "(SELECT email FROM teachers WHERE Admin='1') UNION (SELECT email FROM students) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $mails .= "$row[0],";
    }
//Admins and Teachers
} elseif ($_POST['Admins'] && $_POST['Teachers'] && empty($_POST['Students']) {
    $sql = "(SELECT email FROM teachers WHERE Admin='1') UNION (SELECT email FROM teachers) order by email";
    $result = mysql_query($sql,$db);
    while ($row = mysql_fetch_row($result)) {
        $mails .= "$row[0],";
    }    
}

Thank you very much for your time!
Carlos I. Romero.

Edited by Charls Frdinand: n/a

0

Code at line 17 does not match the comment at line 15 or the test at line 16

Code at line 59 is inefficient (double select not needed)

Note: Some of the table names are inconsistent: Translated or not.

You are welcome. Please mark the thread solved when you are happy that it is indeed done.

0

Thank you very much for your time and your help!
I fixed all the code, including the errors you pointed.
It is working now!
Thanks again!

0

Thank you very much for your time and your help!
I fixed all the code, including the errors you pointed.
It is working now!
Thanks again!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.