Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New Coder
    Join Date
    Sep 2005
    Posts
    46
    Thanks
    4
    Thanked 0 Times in 0 Posts

    stuck in query..

    hey guys this is the structure of the Db:

    `Ex_ID` int(11) NOT NULL auto_increment,
    `N_Etudiant` varchar(25) NOT NULL,
    `Professeur` varchar(50) NOT NULL,
    `Foyer` varchar(50) NOT NULL,
    `Motif` varchar(50) NOT NULL,
    `Date` date NOT NULL,
    `Notes` text NOT NULL,
    `Autre` varchar(150) NOT NULL,

    so basically N_Etudiant is a student name..

    so when we insert it looks like

    `N_Etudiant` Mike
    `Professeur` mrpopins
    `Foyer` 301
    `Motif` fight
    `Date` 2011/01/01
    `Notes` none
    `Autre` 0

    so the query i need is to flag if (N_Etudiant) has the same (Professeur) more the 2 times

    cause we need to know if the student (N_Etudiant) has been expelled from class by the same teacher (Professeur) more then 2 times..

    is this clear?lol

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    What have you tried?

  • #3
    New Coder
    Join Date
    Sep 2005
    Posts
    46
    Thanks
    4
    Thanked 0 Times in 0 Posts
    i have tried

    SELECT * FROM `Expulsion` WHERE `N_Etudiant`=`Professeur` >= 2

    WHERE N_Etudiant= 2 AND Professeur >= 2

    and more..but i forget..

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Do you know how GROUP BY works? It collapses rows into groups, so if you group by N_Etudiant it would give you all the rows for each student into one. you could then count that group.

    That would give you how many times a student appeared in the table though and you want it how many times per teacher, so you'd group on N_Etudiant, Professeur.

    that would give you how many times a student shows with each professor.

    Does that make sense to you?

    Now use a HAVING clause to count each group for what you are looking for.

  • #5
    New Coder
    Join Date
    Sep 2005
    Posts
    46
    Thanks
    4
    Thanked 0 Times in 0 Posts
    ok so i started with

    SELECT *
    FROM `Expulsion`
    GROUP BY `Professeur`
    LIMIT 0 , 30

    the added

    SELECT *
    FROM `Expulsion`
    GROUP BY `Professeur`
    HAVING COUNT( `Professeur` ) =2
    LIMIT 0 , 30

    the i think i got it

    SELECT * FROM `Expulsion` GROUP BY `Professeur` HAVING COUNT( `Professeur` ) >2

    i feel im close..but the results are not good..
    Last edited by techker; 11-25-2011 at 12:55 PM.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    First, get out of the habit entirely of using SELECT * except in very specific cases.

    Your results aren't good because you collapsed your rows by professor only and not by each student as well.

    First just figure out the student, professor, you are close:

    Code:
    SELECT
      N_Etudient,
      Professeur
    FROM
      Expulsion
    GROUP BY
      N_Etudient,
      Professeur
    HAVING 
      COUNT(*) >= 2
    without the group by student you were only counting how many times a professor showed up so if he showed up twice for Gary and once for Tim he would show up a total of three times. and you only want it for Gary.
    Last edited by guelphdad; 11-25-2011 at 01:24 PM.

  • Users who have thanked guelphdad for this post:

    techker (11-26-2011)

  • #7
    New Coder
    Join Date
    Sep 2005
    Posts
    46
    Thanks
    4
    Thanked 0 Times in 0 Posts
    nice its working...
    i want to email the results..

    can i put an if COUNT(*) >= 2

    if (COUNT(*) >= 2){;
    email
    else
    do nothing

    };

    ok so i got this going.email part works but i can't seem to echo the teacher and student name?

    PHP Code:
    $query "SELECT N_Etudiant, Professeur
    FROM Expulsion
    GROUP BY N_Etudiant, Professeur
    HAVING COUNT( * ) >=2
    LIMIT 0 , 30"

    $result mysql_query ($query) or die ('Your query did not match any results: ' mysql_error()); 







                        if (
    mysql_num_rows($result)>0)  
                     
                            {                     

                                while(
    $row=mysql_fetch_array($result)) 
                             
                                    { 
                                    
                                    
    // multiple recipients
    $to  'techker@gmail.com'// note the comma


    // subject
    $subject 'Suivis Requis';

    // message
    $message '
    <html>
    <head>
      <title>Un suivis est requis</title>
    </head>
    <body>
      <p>Etudiant</p>
      <table>
        <tr>
          <th>Enseignant(e)$row[\"Professeur\"]</th>
        </tr>
        
      </table>
    </body>
    </html>
    '
    ;

    // To send HTML mail, the Content-type header must be set
    $headers  'MIME-Version: 1.0' "\r\n";
    $headers .= 'Content-type: text/html; charset=iso-8859-1' "\r\n";


    // Mail it
    mail($to$subject$message$headers);

                                    }
                                    } 
    Last edited by techker; 11-25-2011 at 11:48 PM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    PHP will *NOT* do replacement of PHP variables inside of strings delimited by apostrophes.

    That is,
    Code:
    $x = 'example';
    $msg = 'this is an $x but it does not work';
    That *only* works inside strings delimited by quotes.

    So
    Code:
    $x = 'example';
    $msg = "this is an $x that does works!";
    So you can't use $row[\"Professor\"] as you are doing.

    You could do:
    Code:
    $prof = $row["Professor"];
    $message = "....<th>Enseignant(e) $prof</th>...";
    Or you could do
    Code:
    $message = '....<th>Enseignant(e) ' . $row["Professeur"] . '</th> .... ';
    You need to learn what PHP is capable of and what it can't do.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    techker (11-26-2011)

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    can i put an if COUNT(*) >= 2

    if (COUNT(*) >= 2){;
    email
    else
    do nothing

    };
    You don't *NEED* to. The query will *ONLY* return results where the COUNT IS ALREADY >= 2.

    You will NEVER SEE any records where COUNT is 1 or 0.

    So if you you find it in the results, you need to send an email.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    techker (11-26-2011)

  • #10
    New Coder
    Join Date
    Sep 2005
    Posts
    46
    Thanks
    4
    Thanked 0 Times in 0 Posts
    ok cool.i get it.
    Old Pedant thx!

    i will try it out tonight!keep you posed

  • #11
    New Coder
    Join Date
    Sep 2005
    Posts
    46
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Alright so it sums up to this..really appreciat the help guys!super forum!!

    PHP Code:

    $query 
    "SELECT N_Etudiant, Professeur
    FROM Expulsion
    GROUP BY N_Etudiant, Professeur
    HAVING COUNT( * ) >=2
    LIMIT 0 , 30"

    $result mysql_query ($query) or die ('Your query did not match any results: ' mysql_error());                     
    $row mysql_fetch_assoc($result);
                                
    $prof $row["Professeur"];
    $eleve $row["N_Etudiant"];
                                    
    // multiple recipients
    $to  'techker@gmail.com'// note the comma


    // subject
    $subject 'Suivis Requis';

    // message
    $message "Suivis avec $prof pour Etudiant $eleve...";

    // To send HTML mail, the Content-type header must be set
    $headers  'MIME-Version: 1.0' "\r\n";
    $headers .= 'Content-type: text/html; charset=iso-8859-1' "\r\n";



    // Mail it
    mail($to$subject$message$headers); 

  • #12
    New Coder
    Join Date
    Sep 2005
    Posts
    46
    Thanks
    4
    Thanked 0 Times in 0 Posts
    by the way is there a way to remove the email?lol

    techker@fugit.dnsbox16.com

    this sis the email it sends the email with to..

    how can i put my domain?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •