SQL code not catching duplicate entries

PHP
1

I am updating a website in php, XHTML, and CSS. I am using php 5x, Firefox browser. The website allows subscribers to establish a profile. Visitors can login and make comments about the subscribers. The code is written to prohibit visitors from making more than one comment per subscriber. The problem is the code to catch duplicate visitors for a subscriber never fires. The code for login_comment.php works correctly. The problem seems to be in the rating.php code. I am enclosing the code for both pages. I appreciate any help anyone can give me with this problem. It’s a long post. Hope I haven’t violated posting rules. Thanks. Jim

Rating.php


<?ob_start();?>
?<?php
$page= "rating";
include ("db.php");
include ("functions.php");

$login = true;	
if(isset($_COOKIE['userID'])) {
	$user_ID = $_COOKIE['userID'];

	$query = "SELECT * FROM patrons WHERE user_ID = '". $user_ID . "'";
	$result = @mysql_query($query) or die ("<p>Errpr : " . mysql_error());
	if ($row = mysql_fetch_array ($result)) {
		$hour = time() + 3600;
		setcookie('userID', $user_ID, $hour);
		$login = false;
	}
}

if ($login)	header("Location: login_comment.php?post=" . $_REQUEST['post']);

if (isset($_GET['rating'])) {
	$query = "SELECT * FROM comments WHERE post_ID = '". $_POST['post_ID'] . "'";
	$result = @mysql_query($query) or die ("<p>Errpr : " . mysql_error());
	while ($row = mysql_fetch_array ($result)) {}
/* JLH this else if does not function. If user has posted before, data just disappears; visitor not written to patrons.tbl and comment not written to comments.tbl. */	
} else if (isset($_POST['submit']) && $_POST['submit'] == "Submit Comment") {
	$query = "SELECT * FROM comments WHERE post_ID = '". $_POST['post_ID'] . "' AND user_ID = '".  $_POST['user_ID'] . "'";
	$result = @mysql_query($query) or die ("<p>Errpr : " . mysql_error());
	if ($row = mysql_fetch_array ($result)) {
		echo "You have posted a comment before to this hairdresser...";
	} else {

$Names = "(" . "post_ID";
$values = "('" . $_POST['post_ID'] . "'";

$Names .= ", " . "user_ID";
$values .= ", '" . $_POST['user_ID'] . "'";

$Names .= ", " . "rating1";
$values .= ", '" . $_POST['rating1'] . "'";

$Names .= ", " . "rating2";
$values .= ", '" . $_POST['rating2'] . "'";

$Names .= ", " . "rating3";
$values .= ", '" . $_POST['rating3'] . "'";

$Names .= ", " . "rating4";
$values .= ", '" . $_POST['rating4'] . "'";

$Names .= ", " . "comment";

if ($_POST['select'] != "")
$values .= ", '" . $_POST['select'] . "'";
else
$values .= ", '" . $_POST['comment'] . "'";

$Names .= ", " . "datetime" . ")";
$values .= ", '" . time() . "')";

echo $Names , "<br />";
echo $values, "<br />";


$query = "INSERT INTO comments " . $Names . " VALUES " . $values;

echo $query;

$result = mysql_query($query) or die ("<p>Errpr : " . mysql_error()); 
  mysql_close($mysql_link);
  
	}
      	header("Location: show_post.php?post=" . $_REQUEST['post_ID']);   
	

} else {
$post = "";
$page = "comment";
include ("header.php");

$post_ID = $_REQUEST['post'];

?>
	<h1>Add Comment to <?php echo $post; ?></h1>
		<form action="rating.php" method="post" onsubmit="return confirm();" >
			<input name="post_ID" type="hidden" value="<?php echo $post_ID ?>" />			
			<input name="user_ID" type="hidden" value="<?php echo $user_ID ?>" />			
			<input id="rating" name="rating" type="hidden" value="0" />			
<?php
			rate_line (1, "Cuts my hair the way I like");
			rate_line (2, "Pleasant personality");
			rate_line (3, "Ability to converse with patrons while working on them");
			rate_line (4, "Pays attention to patrons&#8217; comments, questions, needs");
?>			
			<label>Choose pre defined Comment:</label>
			<select name="select">
				<option>A good hairdresser.</option>
				<option>Reasonable prices.</option>
				<option>Good suggestions for color.</option>
				<option>Is up on the latest hair fashions.</option>
				<option>Listens attentively to what I have to say.</option>
				<option>Makes me glad I came in.</option>
			</select>
			<label>or Write your own Comment:</label>			
			<input class="ml80" name="comment" type="text" />
			<label>No flaming or negative comments</label>												
			<input name ="submit" class="Submit" type="submit" value="Submit Comment" />
			<input class="Reset" type="reset" value="Reset" onclick="rate(0, -9, 0);return true;" >
			<input class="Reset" type="reset" value="Cancel" onclick="history.back()" >
			<div class="clear"></div>			
		</form>
		<div class="clear"></div>		
	</div>
<script type="text/javascript">
	var rated = [0, 0, 0, 0, 0];
	
	function rate (e, act, id) {
		if (act == -9) {
			min = 1;
			max = 4;
			if (id != 0) {
				min = max = id;
			}
			for (di = min; di <= max; di++) {			
				for (i = 1; i <= 5; i++) {
					document.getElementById('s' + i + 'i' + di).src="images/white-star.png";
				}
				document.getElementById('rating' + di).value = 0;
				rated[di] = 0;
			}
		} else if (act == 0) {
			if (rated[id] == 0) rate (0, -9, id);
		} else if (act == -1 ) {
			last = e.src;
			e.src = "images/red-star.png";
		} else if (rated[id] == 0) {
			if (act > 0) {
				document.getElementById('rating' + id).value = act;
				rated[id] = 1;
			} else {
				act = -act;
			}
			for (i = 1; i <= act; i++) {
				document.getElementById('s' + i + 'i' + id).src="images/red-star.png";
			}
			for (; i <= 5; i++) {
				document.getElementById('s' + i + 'i' + id).src="images/white-star.png";
			}
		}
	}
	
	var Confirm = 0;
		
	function confirm () {
		if (Confirm == 0) {
			for (i = 1; i < 5; i++) {			
				if (document.getElementById('rating' + i).value == 0) Confirm = 1;
			}
			if (Confirm == 1) {
				alert ("Please fill all fields, before Submit!");
				Confirm = 0;		
				return false;
			}
		}
		return true;
	}





</script>	

<?php
}
	include ("footer.php");	?>
<?ob_flush();?>

Login_comment.php


<?ob_start();?>
<?php
$page="login_comment";
include("db.php");
include("header.php");
$err = "";

if (isset($_POST['user']) && $_POST['pass'] != "") {
	if ($_POST['submit'] == "Register") {
		$query = "INSERT INTO patrons (email, pass) VALUES ('" . $_POST['user'] . "', '". $_POST['pass'] . "')";
		$result = @mysql_query($query) or die ("<p>Errpr : " . mysql_error());
	}	
	$query = "SELECT * FROM patrons WHERE email = '". $_POST['user'] . "' AND pass = '". $_POST['pass'] . "'";
	$result = @mysql_query($query) or die ("<p>Errpr : " . mysql_error());
	if (!($row = mysql_fetch_array ($result))) {
		//$err = "Invalid inputs, Can't post comment!";
                //Added by JLH 9/30/2010.
                  $err = "Sorry. We could not find you in the database. Please register.";
	} else {
		$userID = $row['user_ID'];
		$hour = time() + 3600;
		setcookie('userID', $userID, $hour);
                header("Location: rating.php?post=" . $_POST['post']);  
}
	}

?>
    <h2>Add a Comment</h2>
      <div class ="topBox">
             <br>            
        </div>     
      <div class ="topBox">
             <form id="login" action="login_comment.php" method="post";
             <label>Email</label>
             <input class="login" name="user" type="text" />
             <label>Password</label>
             <input class="login" name="pass" type="password" />
             <label>&nbsp;</label>
       </div>   
       <div class ="topBox">
             <?php
    if ($err != "") {
        echo '<h2>' . $err . '</h2>';
    } else {     
        echo '<br>'; 
 
        echo '<input name="post" type="hidden" value="' . $_GET['post'] .'  " />';
        echo '<input name="submit" type="submit" value="Log-In" />';
        echo '<br>'; 
        echo '<br>';
        echo '<br>';
        echo '<input name="submit" type="submit" value="Register" />';
    }
?>
    </form>
       </div>   
<?php include("footer.php");?>
<?ob_flush();?>
2

In your MySQL query i dont understand why your using mysql_fetch_array. when you should be using [B]mysql_num_rows

Change
[/B]

    if ($row = mysql_fetch_array ($result)) {
        echo "You have posted a comment before to this hairdresser...";
    } else {

To

    if (mysql_num_rows($result)) {
        echo "You have posted a comment before to this hairdresser...";
    } else {
3

mysql_fetch_array returns false for an empty result set, so that should also work. Though I do agree that mysql_num_rows is a lot nicer :slight_smile:

Anyway, as far as I can see the problem lies in the parts I’ve highlighted in red in the following line:


$query = "SELECT * FROM comments WHERE post_ID = '". $_POST['post[COLOR="Red"]_ID[/COLOR]'] . "' AND user_ID = '".  $[COLOR="Red"]_POST[/COLOR]['user_ID'] . "'";
4

The reason its not been trigged is because the value you have set in the else if statment in rating.php is wrong. In the submit button the name is “submit” so that is the value you would check for

PHP:

} else if (isset($_POST[‘submit’]) && $_POST[‘submit’] == “submit”) {

Thanks for the suggestion. I was under the impression that type= submit passed the “value” attribute. When I changed the code to $_POST[‘submit’] ==“submit”, it stopped writing anything to the database. I did notice that WHERE post_ID = '". $_POST[‘post_ID’] is not returning the correct post_ID. It is returning 0. Any other ideas? Jim

5

Do NOT name your submit button “submit” as that can stop the button working at all in some versions of Internet Explorer where the reference to the button ends up overriding the actual submit function so that it then can’t submit the form because it generates a reference to the button instead…

6

Thanks. I’ll give it a try and let you know.