Email help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Email help

Post 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 :)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Email help

Post 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.
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Email help

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Email help

Post 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.
danwguy
Forum Contributor
Posts: 256
Joined: Wed Nov 17, 2010 1:09 pm
Location: San Diego, CA

Re: Email help

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Email help

Post 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.
(#10850)
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Email help

Post 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";
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Email help

Post 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");
}
}
Post Reply