PHP MySQL JOIN problem

Question

Charls Frdinand 0 Newbie Poster

14 Years Ago

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).

mysql php

Edited 14 Years Ago by Charls Frdinand because: n/a

2 Contributors
7 Replies
237 Views
10 Hours Discussion Span
Latest Post 14 Years Ago Latest Post by Charls Frdinand

griswolf 304 Veteran Poster

14 Years Ago

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 */

griswolf 304 Veteran Poster

14 Years Ago

Assuming you don't have the ability to refactor the database schema, you want the union command: http://dev.mysql.com/doc/refman/5.0/en/union.html $sql = "(SELECT email FROM student) UNION (SELECT email FROM teacher) order by email"

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Charls Frdinand 0 Newbie Poster · Answer 1 · 2010-05-27T03:44:43+00:00

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],";
    }    
}

Charls Frdinand 0 Newbie Poster · Answer 2 · 2010-05-27T04:13:46+00:00

(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.

griswolf 304 Veteran Poster · Answer 3 · 2010-05-27T05:43:10+00:00

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.

Charls Frdinand 0 Newbie Poster · Answer 4 · 2010-05-27T06:04:01+00:00

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!

Charls Frdinand 0 Newbie Poster · Answer 5 · 2010-05-27T06:16:56+00:00

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!