stuck in query..

techker · 11-25-2011, 12:10 PM

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

guelphdad · 11-25-2011, 12:26 PM

What have you tried?

techker · 11-25-2011, 12:31 PM

i have tried

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

WHERE N_Etudiant= 2 AND Professeur >= 2

and more..but i forget..

guelphdad · 11-25-2011, 12:38 PM

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.

techker · 11-25-2011, 12:51 PM

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

guelphdad · 11-25-2011, 01:07 PM

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.

techker · 11-25-2011, 11:25 PM

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

                                }
                                }

Old Pedant · 11-26-2011, 02:26 AM

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.

Old Pedant · 11-26-2011, 02:28 AM

Quote:

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.

techker · 11-26-2011, 08:37 PM

ok cool.i get it.
Old Pedant thx!

i will try it out tonight!keep you posed

techker · 11-26-2011, 08:50 PM

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

techker · 11-26-2011, 08:52 PM

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?