Page 1 of 1

Email help

Posted: Fri Apr 22, 2011 2:53 am
by shauns2007
Hi There

Basically I have this script that runs a few queries then emails the results.

Code: Select all

if (isset($_POST['submit'])) {
	$course_info = "SELECT sno, cid FROM course_student WHERE cid=".$_POST['cid'];
	$course_info_result = mysql_query($course_info) or die ("Could not retrieve info from course_student for mail: " . mysql_error());
	while($row_course_student = mysql_fetch_array($course_info_result)){
	$sno = $row_course_student['sno'];
	$cid = $row_course_student['cid'];
	
	$student_info = "SELECT sno, fname, sname, email, contact_flag FROM student WHERE sno = $sno AND contact_flag = 'y' ORDER BY sno";
	$result = mysql_query($student_info) or die ("Could not retrieve student info for mail: " . mysql_error());
	$num = mysql_num_rows($result);
	$i = 0;
	while($i < $num){
	$row = mysql_fetch_array($result);
	$mail = $row['email'];
	
	$fname = mysql_result($result, $i, 'fname');
	$sname = mysql_result($result, $i, 'sname');
	$email .= mysql_result($result, $i, 'email').",";
	$text .= $fname . ' ' . $sname . ' - ' . $mail. "\n";
	$i++;
	}	
	$course_name = "SELECT cid, cname FROM course WHERE cid = $cid";
	$course_name_query = mysql_query($course_name) or die ("Could not retrieve course name from database: " . mysql_error());
	while($row_course = mysql_fetch_array($course_name_query)){
	$cname = $row_course['cname'];
	 }
	 }
	 $from = 'From: Info <info@spargweb.co.za>' . "\r\n";
	 $to = $email;
	 $subject = "Successfully Registered";
	 $body = "Hi $fname $sname,\n\nHere is a list of all students currently studying $cname that are willing to be contacted for help\n\n$text";
	 mail($to, $subject, $body , $from);
	 header("location:student_man.php");
}
It gets the $_POST['cid'] from this which is on the same page

Code: Select all

$qry = "SELECT cid, cname FROM course ORDER BY cname ASC";
$result = mysql_query($qry) or die (mysql_error());
echo '<option value = "">Choose a Course</option>';
while ($row = mysql_fetch_array($result)) {
$cid = $row['cid'];
$cname = $row['cname'];
echo "<option value=$cid>$cname</option>";
}
The problem I'm having is if the value of cid is 0 I get this error.

Could not retrieve info from course_student for mail: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I have tried adding if statements but then the mail() doesn't work. I've also tried changing this:

Code: Select all

echo '<option value = "">Choose a Course</option>
to

Code: Select all

echo '<option value = "0">Choose a Course</option>
but then I get these errors:

Undefined variable: email in E:\www\spargzco\spargweb.co.za\wwwroot\student_reg.php on line 133
Undefined variable: fname in E:\www\spargzco\spargweb.co.za\wwwroot\student_reg.php on line 135
Undefined variable: sname in E:\www\spargzco\spargweb.co.za\wwwroot\student_reg.php on line 135
Undefined variable: cname in E:\www\spargzco\spargweb.co.za\wwwroot\student_reg.php on line 135
Undefined variable: text in E:\www\spargzco\spargweb.co.za\wwwroot\student_reg.php on line 135
Warning: mail() [function.mail]: SMTP server response: 503 5.5.2 Need Rcpt command. in E:\www\spargzco\spargweb.co.za\wwwroot\student_reg.php on line 136
Warning: Cannot modify header information - headers already sent by (output started at E:\www\spargzco\spargweb.co.za\wwwroot\student_reg.php:135) in E:\www\spargzco\spargweb.co.za\wwwroot\student_reg.php on line 137

lines 132 - 137 are

Code: Select all

	 $from = 'From: Info <info@spargweb.co.za>' . "\r\n";
	 $to = $email;
	 $subject = "Successfully Registered";
	 $body = "Hi $fname $sname,\n\nHere is a list of all students currently studying $cname that are willing to be contacted for help\n\n$text";
	 mail($to, $subject, $body , $from);
	 header("location:student_man.php");
Any help would be appreciated :)

Re: Email help

Posted: Fri Apr 22, 2011 3:05 pm
by califdon
As soon as your script determines that the cid value is zero, that's when you need to take action. So right after the line

Code: Select all

$cid = $row['cid'];
you need to have an if test and if $cid == 0, you need to do something, perhaps just die("No user ID found!") ; or something.

Re: Email help

Posted: Fri Apr 22, 2011 3:18 pm
by shauns2007
Thanks for the reply, did you mean this?

Code: Select all

if (isset($_POST['submit'])) {
	$course_info = "SELECT sno, cid FROM course_student WHERE cid=".$_POST['cid'];
	$course_info_result = mysql_query($course_info) or die ("Could not retrieve info from course_student for mail: " . mysql_error());
	while($row_course_student = mysql_fetch_array($course_info_result)){
	$sno = $row_course_student['sno'];
	$cid = $row_course_student['cid'];
	if ($cid == 0) {
	die("No user ID found!");
	}
I still get all those errors, unless I'm doing it wrong

Re: Email help

Posted: Fri Apr 22, 2011 3:48 pm
by califdon
Let's go back to your original post: you said
The problem I'm having is if the value of cid is 0 I get this error.

Could not retrieve info from course_student for mail: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
I was addressing the problem of having a zero ID field. I should have looked more closely at your code (but after reading several dozen scripts like this today, I get a little careless). Here's the basic idea: when you know that something is wrong, test for possible wrong values at the earliest stage, don't wait until the program crashes. When you're not sure what the problem is, echo out the values (on a temporary basis, until you've solved the problem) to see if the values are what you expect them to be. That's the way to narrow down the possible causes of the error.

You've done some of that with your mysql_error() messages, which is good practice. Your error message tells you that something is wrong with SQL statement where you are querying the course_student table. So it would be prudent to echo out that SQL statement and take a look at it, right? You can do that by temporarily modifying your or die(mysql_error()) statement like this:

Code: Select all

$course_info_result = mysql_query($course_info) or die ("Could not retrieve info from course_student for mail: " . mysql_error() . "<br />$course_info");
Then if MySQL encounters an error when it tries to process the query, it will print out 3 things: your statement identifying which query had the error, the error message from MySQL, and the actual SQL that it was trying to process. Usually this will make it clear what's wrong with the statement, such as a missing value. Then you can figure out why the value is missing and correct the problem.

Re: Email help

Posted: Fri Apr 22, 2011 10:31 pm
by danwguy
not sure if it will fix your problem for sure but I would change yout code...

Code: Select all

$qry = "SELECT cid, cname FROM course ORDER BY cname ASC";
$result = mysql_query($qry) or die (mysql_error());
echo '<option value = "">Choose a Course</option>';
while ($row = mysql_fetch_array($result)) {
$cid = $row['cid'];
$cname = $row['cname'];
echo "<option value=$cid>$cname</option>";
}
 
to...

Code: Select all

$qry = "SELECT cid, cname FROM course ORDER BY cname ASC";
$result = mysql_query($qry) or die (mysql_error());
echo '<option value = "">Choose a Course</option>';
while ($row = mysql_fetch_array($result)) {
echo "<option value='" .$row['cid']. "'>" .$row['cname']. "</option>";
}
 
worth a shot, it looked like the original code isn't outputting the values right.

Re: Email help

Posted: Sat Apr 23, 2011 12:57 am
by Christopher
From your original post, it looks like the second query is failing -- probably because $sno is not defined. You have to be careful with unquoted values in comparisons. I'd recommend checking if it is_numeric().

When that query fails then $email, $fname, $cname are all probably null or zero. And it goes downhill from there. Database could really need if() checks after each query with succeeding dependent queries inside nested if()s.

Re: Email help

Posted: Sat Apr 23, 2011 3:11 am
by shauns2007
Thanks christopher

So the $sno in the loop above doesn't define it? Here is the code so you can see what I mean :)

Code: Select all

$sno = $row_course_student['sno'];
        $cid = $row_course_student['cid'];
       
        $student_info = "SELECT sno, fname, sname, email, contact_flag FROM student WHERE sno = $sno AND contact_flag = 'y' ORDER BY sno";

Re: Email help

Posted: Sat Apr 23, 2011 7:22 am
by shauns2007
I added this and the error is gone :) What it's supposed to do is send the mail to multiple recipients but it doesn't. Any suggestions as the code looks right to me

Code: Select all

	if ($_POST['cid'] != 0) {
	$course_info = "SELECT sno, cid FROM course_student WHERE cid=".$_POST['cid'];
	$course_info_result = mysql_query($course_info) or die ("Could not retrieve info from course_student for mail: " . mysql_error());
	while($row_course_student = mysql_fetch_array($course_info_result)){
	$sno = $row_course_student['sno'];
	$cid = $row_course_student['cid'];
	
	$student_info = "SELECT sno, fname, sname, email, contact_flag FROM student WHERE sno = $sno AND contact_flag = 'y'";
	$result = mysql_query($student_info) or die ("Could not retrieve student info for mail: " . mysql_error());
	$num = mysql_num_rows($result);
	$i = 0;
	while($i < $num){
	
	$fname = mysql_result($result, $i, 'fname');
	$sname = mysql_result($result, $i, 'sname');
	$email .= mysql_result($result, $i, 'email').",";
	$text .= $fname . ' ' . $sname . ' - ' . $email. "\n";
	$i++;
	}
	$course_name = "SELECT cid, cname FROM course WHERE cid = $cid";
	$course_name_query = mysql_query($course_name) or die ("Could not retrieve course name from database: " . mysql_error());
	while($row_course = mysql_fetch_array($course_name_query)){
	$cname = $row_course['cname'];
	 }
	 }
	 
	 $from = 'From: Info <info@spargweb.co.za>' . "\r\n";
	 $to = $email;
	 $subject = "Successfully Registered";
	 $body = "Hi $fname $sname,\n\nHere is a list of all students currently studying $cname\n\n$text";
	 mail($to, $subject, $body , $from);
	 header("location:student_man.php");
}
}