WHERE Clause does not work

deolsabh

My this code works perfect except Where clause
i want to send email to only people who has "No" in "ackno_a" field of the table.
Please help

<html>
<head>
<title>IT Phone List</title>
</head>
<BODY>
<?php

require_once("site_sc_fns.php");

if(empty($SEND)){$SEND="";}

switch($SEND){
case "Send":
if(!empty($MESSAGE)){
if(empty($SUBJECT)){$SUBJECT= " ";}
$HEADERS = "MIME-Version: 1.0\r\n";
$HEADERS .= "Content-type: text/html; charset=iso-8859-1\r\n";
$HEADERS .= "From: Info <Prithvi.Singh@kla-tencor.com>\r\n";

		$SUBJECT = "IT Phone List Update.  \r\n"; 

		$linkID = mysql_connect("localhost", "", "");
		mysql_select_db(virus, $linkID);

		$obRS = mysql_query("SELECT email_g FROM phlistnew WHERE ackno_a=No ", $linkID);

		while($row = mysql_fetch_row($obRS)){
			foreach ($row as $field){

		$email_g = $field ;

		$url = "show_cat.php?email_g=" .$field ;

$MESSAGE = " \n";
$MESSAGE .= "<table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"1\">\n";
$MESSAGE .= "<tr>\n";
$MESSAGE .= "<td>Our contents here. </td>\n";
$MESSAGE .= "</tr>\n";
$MESSAGE .= "<tr>\n";
$MESSAGE .= "<td>Email Address: $email_g</td>\n";
$MESSAGE .= "</tr>\n";
$MESSAGE .= "<tr height=\"10\">\n";
$MESSAGE .= "<td height=\"10\"></td>\n";
$MESSAGE .= "</tr>\n";
$MESSAGE .= "<tr>\n";
$MESSAGE .= "click <A CLASS=TBLACK HREF=\"http://ca1dco01/virus/phonenew/$url\">here</A> to verify/update your information. ";

$MESSAGE .= "</tr>\n";
$MESSAGE .= "</table>\n";

	mail($field, $SUBJECT, $MESSAGE, $HEADERS);
				print " $field <br>" ;

		}
			}

// mysql_close($linkID);
echo "<hr>MESSAGE DETAILS<hr>";
echo "Subject: $SUBJECT ";
echo "Message: $url ";

	}
	break;
default:
	echo "<FORM ACTION=itmail.php  METHOD=GET NAME=FORM1 >";
	echo "Please type in html code below.<BR>";
	echo "<TEXTAREA NAME=MESSAGE STYLE='width:500;height:300' WRAP=off  > </TEXTAREA><BR>";
	echo "<INPUT TYPE=BUTTON ONCLICK=\"document.FORM1.MESSAGE.value=''\" VALUE=Reset >&nbsp;<INPUT TYPE=SUBMIT VALUE=Send NAME=SEND > ";
	echo "</FORM>";

}

?>
</BODY></HTML>

uffis

What is the data type for column "ackno_a"? According to your code, I would think of it as CHAR or VARCHAR. If so, change the following line of your code

$obRS = mysql_query("SELECT email_g FROM phlistnew WHERE ackno_a=No ", $linkID);

to this

$obRS = mysql_query("SELECT email_g FROM phlistnew WHERE ackno_a = 'No' ", $linkID);

deolsabh

No it did not work

deolsabh

No it did not work

tsinka

Hi,

change the mysql_query line to

mysql_query("..") or die("SQL error: ".mysql_error());

to see if something goes wrong.

One suggestion:
Change
mysql_select_db(virus, $linkID);
to
mysql_select_db("virus", $linkID) or die("Couldn't select database: ".mysql_error());

That's just to debug stuff. In a production environment it might be better to print an error message instead of just using die().

Thomas

mrjoseph_com

I got the same problem:

$id=$POST['id'];
$firstname=$POST['firstname'];
$lastname=$POST['lastname'];
$tel=$POST['tel'];
$email=$_POST['email'];
$db="trevor";
$link = mysql_connect("localhost");
$URL="http://display.php";

if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link) or die("Select Error: ".mysql_error());
$result=mysql_query("UPDATE contactdetails set firstName='$firstname', lastName='$lastname', tel='$tel', email='$email', where id=$id ")or die("Update Error: ".mysql_error());

and my error message is:
Update Error: 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 'where id=2' at line 1

tsinka

Remove the , in front of the WHERE (that's the error) and enclose the $id variable at the end with single quotes (not using them isn't wrong if the value is numeric, but it is better to always use single quotes around the values. Exception: the value is a name of a column).

Thomas

ahundiak

Originally posted by tsinka
...and enclose the $id variable at the end with single quotes (not using them isn't wrong if the value is numeric, but it is better to always use single quotes around the values.
Thomas

Be careful of this. Using quotes around numeric fields results in non-standard SQL. Yes it does work with mysql but not on some other databases. IMHO it's generally better to stick with standards than not.

Of course, the same problem exists with strip/add slashes. But that battle would be almost impossible to win.