...

View Full Version : stuck in query..



techker
11-25-2011, 01: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, 01:26 PM
What have you tried?

techker
11-25-2011, 01: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, 01: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, 01: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, 02: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:



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-26-2011, 12:25 AM
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?



$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, 03:26 AM
PHP will *NOT* do replacement of PHP variables inside of strings delimited by apostrophes.

That is,

$x = 'example';
$msg = 'this is an $x but it does not work';

That *only* works inside strings delimited by quotes.

So

$x = 'example';
$msg = "this is an $x that does works!";


So you can't use $row[\"Professor\"] as you are doing.

You could do:


$prof = $row["Professor"];
$message = "....<th>Enseignant(e) $prof</th>...";

Or you could do

$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, 03:28 AM
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, 09:37 PM
ok cool.i get it.
Old Pedant thx!

i will try it out tonight!keep you posed

techker
11-26-2011, 09:50 PM
Alright so it sums up to this..really appreciat the help guys!super forum!!




$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, 09: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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum