[SOLVED] Heck of a time with the WHERE statement and querying the database

[suttercain]

Hi everyone,

 

I am running this code:

<?php
$checkuser = mysql_query("SELECT * FROM comicvote WHERE userid ='" . $row['comic_id'] . "'");
		  while ($uv =mysql_fetch_assoc($checkuser)) {
		  $uv = $uv['userid'];
		  echo "UV: $uv<br>"; }
?>

 

What I am trying to do is get a single row where the userid = the comic_id. $uv should echo out the number 1, 1 time, but I can;t get it.

 

Do I have the the mysql_query set up correctly?

 

Thanks.

[Barand]

try some checking

 

<?php
$checkuser = mysql_query("SELECT * FROM comicvote WHERE userid ='" . $row['comic_id'] . "'")
                     or die(mysql_error());
if (mysql_num_rows($checkuser) > 0) {
		  while ($uv =mysql_fetch_assoc($checkuser)) {
		  $uv = $uv['userid'];
		  echo "UV: $uv<br>"; }
}
else echo "No records found";
?>

[suttercain]

Hi Barrand, thanks for the help. I think I had mysql query statement a little messed up. I ended up running

$checkuser = mysql_query("SELECT * FROM comicvote WHERE userid ='" . $userid . "' LIMIT 1")
                     or die(mysql_error());

 

and it worked... well kind of

 

the $uv variable is set in the while loop:

<?php
if (mysql_num_rows($checkuser) > 0) {
		  while ($uv =mysql_fetch_assoc($checkuser)) {
		  $uv = $uv['userid'];
		  echo "UV: $uv<br>"; } //echoes 1 which is good
}
else echo "No records found"; 
		  $comp1 = $userid . "-" . $row['comic_id'];
		  $comp2 = $uv . "-" . $row['comic_id']; 
		  echo "Comp1: $comp1 <br>Comp2: $comp2 <br>"; //does not echo 1, like I need
?>

 

But when I try to echo it out after setting it up in $comp2 the $uv portion does not echo, only the $row['comic'] does.

[Barand]

You are overusing the $uv variable. You use it for the row returned by the fetch and also as the userid value assignment

[suttercain]

Wow, I need to lay off the crack. Joke. Thanks barrand, that solved the issue. I still getting use to php, 3 months in and those little things trip me up.

 

Have a great Sunday!

 

SC

[suttercain]

Okay, I am still stuck... the above code I ran kind of worked, but did not give me the desired results.

 

I am attempting to build a voting system that allows registered users to vote on a page. I want them to only be able to vote one time and am trying to get MySQL to compare the page id (comic_id) to the userid. If a row with both does not exist the user can vote. If the row already has the userid and comic_id they cannot vote.

 

I need to check the MySQL table before the insert statement.

 

Here is my entire code, please note this is used as an include and the $userid and $row['comic_id'] are set on the other php page:

 

<?php
//voting system
include ('../get_connected.php');

function voted() {
echo "Thank you for voting!"; }
		  
$voting = "SELECT * FROM comicvote WHERE comic_id ='" . $row['comic_id'] . "'";
$votes = mysql_query($voting) or die (mysql_error());
$sum = 0;
$total = mysql_num_rows($votes);
while ($rowvote = mysql_fetch_row($votes)) {
	$sum += $rowvote['0']; } 

if ($sum > 0) {
	$average = $sum/$total; }
		  if ($votes == !NULL) { 		
		  		if ($average == 1) {
					$rating = "<img src='../images/comics/grade_f.gif'>";
				} elseif ($average==0) {
					$rating = "Be The First To Vote!<br>";
				} elseif ($average == 2) {
					$rating = "<img src='../images/comics/grade_d.gif'>";
				} elseif ($average == 3) {
					$rating = "<img src='../images/comics/grade_c.gif'>";
				} elseif ($average == 4) {
					$rating = "<img src='../images/comics/grade_b.gif'>";
				} else {
					$rating = "<img src='../images/comics/grade_a.gif'>"; }
			echo $rating;
		  } 
		  
		  if ($total > 0) {
		  echo "<br>Based on $total votes!<br>"; }
		  $checkuser = mysql_query("SELECT * FROM comicvote WHERE comic_id ='" . $userid . "'")
                     or die(mysql_error());
if (mysql_num_rows($checkuser) > 0) {
		  while ($uvote =mysql_fetch_row($checkuser)) {
		  $uv = $uvote['userid'];
		  echo "UV: $uv<br>"; }
}
else echo "No records found<br>"; 
		  $comp1 = $userid . "-" . $row['comic_id'];
		  $comp2 = $uv . "-" . $row['comic_id']; 
		  echo "Comp1: $comp1 <br>Comp2: $comp2 <br>";
		  if ($vbulletin->userinfo['usergroupid'] == '6' || 
		  	  $vbulletin->userinfo['usergroupid'] == '2') {
			  if (!isset($_POST['submit'])) { 
			  	voteNow(); 
					} if ($comp1 == $comp2) {
					echo "Your vote has been counted.";
		  			} else {
					$rating = $_POST['rating'];
					$comic_id = $row['comic_id'];
					$sent = mysql_query ("INSERT INTO comicvote 
														(rating, 
														comic_id, 
														userid) 
														VALUES 
														('$rating', 
														'$comic_id', 
														'$userid')") 
														or die(mysql_error());
					voted ();
		  				} 
					}
		  
		  
		  function voteNow() {
		  ?>
		  	<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post">
			     <div align="center">
				  <select name="rating">
			 	     <option value="5">A</option>
				     <option value="4">B</option>
				     <option value="3">C</option>
				     <option value="2">D</option>
				     <option value="1">F</option>
			       </select>
		  		  <input type="submit" name="submit" value="Vote">
				   </div>
		  	</form>
		  <?php 
		  } 
		  ?>

 

I have been going at this for two days and am going crazy. The mysql table is like this:

 

comic_id | userid | rating

------------------------

          1 |      1 |      3

------------------------

      203 |      1 |      4

------------------------

 

Based on the above table user "1" should not be able to ever vote again on comics 1 or 203. But the above code is allowing the to reload the page and keep counting votes.

 

 

[suttercain]

Also, this is what it is currently echoing:

 

Based on 9 votes!

No records found

Comp1: 1-406

Comp2: -406

Thank you for voting!

[Barand]

First thing I notice is that these are functions so even though $userid and $row['comic_id'] might be defined in the main page they are not known inside the functions (read up on variable scope).

 

You need to pass them as arguments to the functions.

[suttercain]

Okay, I apologize I am lost. I am very new to this and instead of downloading a voting script from hotscripts.com I wanted to build my own... best way to learn right?

 

 

I have read up on the variable scope from some books and I understand that one cannot be used outside of the function unless I designate it as global (I hope that's right).

 

But I thought the only two function I had in the above code was this:

function voted() {
echo "Thank you for voting!"; }

 

and this:

function voteNow() {
		  ?>
		  	<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post">
			     <div align="center">
				  <select name="rating">
			 	     <option value="5">A</option>
				     <option value="4">B</option>
				     <option value="3">C</option>
				     <option value="2">D</option>
				     <option value="1">F</option>
			       </select>
		  		  <input type="submit" name="submit" value="Vote">
				   </div>
		  	</form>
		  <?php 
		  } 
		  ?>

 

Also since I am able to echo $userid and $row['comic_id'] I thought I could use them in the MySQL statement... I may be wrong, but that's what I thought?

 

Thanks for your time.

[Barand]

Sorry, thought the query following the voted() function was part of that function, I didn't spot the } tucked away at the end of the line.

[suttercain]

Sorry, that's my sloppy coding.

 

The way I have it set up now though isn't working.

 

How would I set up the mysql_query to only fetch where userid = comic_id?

 

I thought I had it set up right.

[JakeTheSnake3.0]

I went through your code...maybe it was just some weird quirk with the textbox in this forum but you were missing some crucial { } brackets here and there....plus I'm confused as to how the $row['comic_id'] near the top comes into play...

 

<?php
//voting system
include ('../get_connected.php');

function voted() {
echo "Thank you for voting!";
}

$voting = "SELECT * FROM comicvote WHERE comic_id ='" . $row['comic_id'] . "'";
$votes = mysql_query($voting) or die (mysql_error());
$sum = 0;
$total = mysql_num_rows($votes);
while ($rowvote = mysql_fetch_row($votes)) {
$sum += $rowvote['0'];
}

if ($sum > 0) {
$average = $sum/$total;
}

if ($votes == !NULL) { 		
if ($average == 1) {
	$rating = "<img src='../images/comics/grade_f.gif'>";
} elseif ($average==0) {
	$rating = "Be The First To Vote!<br>";
} elseif ($average == 2) {
	$rating = "<img src='../images/comics/grade_d.gif'>";
} elseif ($average == 3) {
	$rating = "<img src='../images/comics/grade_c.gif'>";
} elseif ($average == 4) {
	$rating = "<img src='../images/comics/grade_b.gif'>";
} else {
	$rating = "<img src='../images/comics/grade_a.gif'>";
}
echo $rating;
}

if ($total > 0) {
echo "<br>Based on $total votes!<br>";
}

$checkuser = mysql_query("SELECT * FROM comicvote WHERE comic_id ='" . $userid . "'") or die(mysql_error());
if (mysql_num_rows($checkuser) > 0) {
while ($uvote =mysql_fetch_row($checkuser)) {
	$uv = $uvote['userid'];
	echo "UV: $uv<br>";
}
} else {
echo "No records found<br>";
$comp1 = $userid . "-" . $row['comic_id'];
$comp2 = $uv . "-" . $row['comic_id']; 
echo "Comp1: $comp1 <br>Comp2: $comp2 <br>";
if ($vbulletin->userinfo['usergroupid'] == '6' || $vbulletin->userinfo['usergroupid'] == '2') {
	if (!isset($_POST['submit'])) {
	voteNow(); 
	}
	if ($comp1 == $comp2) {
	echo "Your vote has been counted.";
	} else {
		$rating = $_POST['rating'];
		$comic_id = $row['comic_id'];
		$sent = mysql_query ("INSERT INTO comicvote (rating, comic_id, userid) VALUES ('$rating', '$comic_id', '$userid')") or die(mysql_error());
		voted ();
	}
}
}


function voteNow() {
echo "
<form action=\"" . $_SERVER['PHP_SELF'] . "\" method=\"post\">
<div align=\"center\">
<select name=\"rating\">
<option value=\"5\">A</option>
<option value=\"4\">B</option>
<option value=\"3\">C</option>
<option value=\"2\">D</option>
<option value=\"1\">F</option>
</select>
<input type=\"submit\" name=\"submit\" value=\"Vote\">
</div>
</form>
"
} 
?>

[suttercain]

So so close... Okay, I got the voteing to work as far as not allowing a user who has already voted to vote and only allowing user who have not voted to vote... whew...

 

BUT now for some reason it is only adding 0 into the rating column even if I select 5, 4, 3, 2, or 1. I have not touched that part of the code:

 

The form for selcecting a rating:

function voteNow() {
		  ?>
		  	<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post">
			     <div align="center">
				  <select name="rating">
			 	     <option value="5">A</option>
				     <option value="4">B</option>
				     <option value="3">C</option>
				     <option value="2">D</option>
				     <option value="1">F</option>
			       </select>
		  		  <input type="submit" name="submit" value="Vote">
				   </div>
		  	</form>
		  <?php 
		  } 
		  ?>

 

The insert of the rating:

//Inserting the record if new vote!
		  if ($vbulletin->userinfo['usergroupid'] == '6' || 
		  	  $vbulletin->userinfo['usergroupid'] == '2') {
			  if (!isset($_POST['submit'])) { 
			  	voteNow(); 
					} if ($comp1 == $comp2) {
					echo "Your vote has been counted.";
		  			} else {
					$rating = $_POST['rating'];
					$comic_id = $row['comic_id'];
					$sent = mysql_query ("INSERT INTO comicvote 
														(rating, 
														comic_id, 
														userid) 
														VALUES 
														('$rating', 
														'$comic_id', 
														'$userid')") 
														or die(mysql_error());
					voted ();
		  				} 
					}

 

[suttercain]

Hi Jake,

 

The $row['comic_id'] is from another page. The code above is being used as an include. The $row['comic_id'] is set and works.

[suttercain]

Okay I see what is happening. If the user is logged in and they go view any comic, it automatically votes for them, I believe this is because the $_POST['submit'] is set. When they view a page, because they have not selected a rating 1-5, it automatically votes with a 0.

 

Is it possible to unset the $_POST['submit'] after the user has voted without redirecting the page? I asked this last night, but did not get a reply.

 

Thanks guys.

 

 

[JakeTheSnake3.0]

Nope, pretty much impossible.  Though all you would need to do is to redirect to the same page.  Just do your form calculations before any output is sent....aka before <html>

[suttercain]

Thanks Jake,

 

How would you recommend doing the redirect? Should I use php's header(), javascript?

 

I'm not sure how to this since I have never done it. I'll do a goole search, but if you have any advice, it would be much appreciated. Thanks.

 

SC

[suttercain]

Hey guys,

 

It appears that I have resolved the isse. Jake you were right about the redirect. I used javascript to take care of it:

 

elseif ($comp1 == $comp2) {
					echo "Your vote has been counted.";
		  			} else {
					$rating = $_POST['rating'];
					$comic_id = $row['comic_id'];
					$sent = mysql_query ("INSERT INTO comicvote 
														(rating, 
														comic_id, 
														userid) 
														VALUES 
														('$rating', 
														'$comic_id', 
														'$userid')") 
														or die(mysql_error());
					echo "<script type='text/javascript'>
					window.location = 'view_comics1.php?id=" . $row['comic_id'] . "'
					</script>";
		  				} 
					}

 

Thanks guys!