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.

[laserlight]

Looks okay to me, assuming that this is just a code snippet and everything else is correct. How do you get $row['comic_id'] in the first place, and do you only expect exactly one result row?

[suttercain]

Hi Laser,

The $row['comic_id'] is from another query and it's set. I checked to make sure. What I a trying to do is allow registered users to vote on a page. This code that I posted here is to help me make a comparison. If the user has already voted, they can't vote again. If they have not voted they can vote once.

Here is the entire code for the voting script I wrote. Please note this is an include so this is NOT the entire code for the final 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 userid ='" . $userid . "'");
		  while ($uv =mysql_fetch_assoc($checkuser)) {
		  $uv = $uv['userid'];
		  echo "UV: $uv<br>"; }
		  $comp1 = $userid . "-" . $row['comic_id'];
		  $comp2 = $uv['0'] . "-" . $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 
		  } 
		  ?>

[suttercain]

My ultimate goal is to get these to match if the user has voted:

<?php
$comp1 = $userid . "-" . $row['comic_id'];
$comp2 = $uv . "-" . $row['comic_id'];
?>

[laserlight]

Looking at your code, it is clear to me that $row['comic_id'] is not set. Also, you really need to improve on your indentation. Your code is unnecessarily and incoherently indented.

I suspect that the code you want could be as simple as:

$query = "SELECT COUNT(userid) FROM comicvote WHERE userid='$userid' LIMIT 1";
$result = mysql_query($query);
if (mysql_result($result, 0) == 0) {
    // User has not voted yet.
} else {
    // User has voted.
}

[suttercain]

Thank you Laser, I appreciate your help, but the $row['comic_id'] is set, as I stated, the above code is an include that is placed in another PHP file with another mysql_query.

Trust me it's set.

If I echo $row['comic_id'] I get the result.

[Piranha]

Take a look at [man]string[/man]. It states that you need a space after the variable name to get PHP to understand that the variable have ended. If you don't want a space there are other ways to solve it, mentioned in the link above. The row I'm talking about is this:

echo "UV: $uv<br>";

[edit]Note that I have only looked at your first post and glanced at lasersight's posts, there might be something else strange as well[/edit]

[Roger_Ramjet]

The thing I really don't like is this

$uv = $uv['userid'];

This may work in php - can't say myself - but if it does, it should not.

Can't see the point of it anyway - whether it works or not. Why not just concatenate
$uv['userid'] into the string directly.

[laserlight]

Thank you Laser, I appreciate your help, but the $row['comic_id'] is set, as I stated, the above code is an include that is placed in another PHP file with another mysql_query.

Post the smallest and simplest well formatted code that demonstrates the problem. Have you not considered what I outlined?

It states that you need a space after the variable name to get PHP to understand that the variable have ended.

I dont think so. It just tries a greedy matching, and in this case should stop at the '<'.

This may work in php - can't say myself - but if it does, it should not.

I do not like such reuse of the variable name either, but it should work as long as $uv is not used as an array after that point, until the next iteration of the loop, at least (and it should loop only once, from what I see, making the loop redundant).

[rvdavid]

Hi there,

suttercain wrote:

Thank you Laser, I appreciate your help, but the $row['comic_id'] is set, as I stated, the above code is an include that is placed in another PHP file with another mysql_query.

Trust me it's set.

If I echo $row['comic_id'] I get the result.

My apologies for jumping in, however, you are not seeing $row['comic_id'] or the $row variable in its entirety for that matter due to your $row is being set outside the scope of your vote() function.

See the spike script below.

<?php
$row = array('test');
function vote()
{
	if (isset($row)) {
		echo '$row is seen from local scope';
	} else {
		echo '$row cannot be seen from local scope';
	}
}
vote(); // will return "$row cannot be seen from local scope";
?>

Here are your options:

Pass $row in as a parameter

<?php
$row = array('test');
function vote($row)
{
	if (isset($row)) {
		echo '$row is seen from local scope';
	} else {
		echo '$row cannot be seen from local scope';
	}
}
vote(); // will return "$row is seen from local scope";
?>

Advantage:
Natural - you can see this later on and "follow the bouncing ball" you can see what creates row and how the votes() function accesses it.

You can read the code without really trying to hunt down where you set the $row variable and where changes have happened prior to calling your vote() function.

Disadvantage:
You will have to refactor your existing code which uses the vote() function to pass an additional parameter. - but IMO this is a small sacrifice that will save you time later on when you need to revisit your code.

Declare $row as global

<?php
$row = array('test');
function vote()
{
        global $row;
	if (isset($row)) {
		echo '$row is seen from local scope';
	} else {
		echo '$row cannot be seen from local scope';
	}
}
vote(); // will return "$row is seen from local scope";
?>

Advantage:
Quick fix - all you need to do is reach out to the global scope from your function and you've got it. No changes to code.

Disadvantage:
Promotes usage of global data - so if you use and/or alter the $row variable from other functions or outside of the function, when the time comes to debug, you will not know who or what changed the $row variable.

Reading this code a few weeks later on, you will have to think about what's actually happening. Where is $row coming from? What's changing $row?

HTH.

Regards,

[laserlight]

My apologies for jumping in, however, you are not seeing $row['comic_id'] or the $row variable in its entirety for that matter due to your $row is being set outside the scope of your vote() function.

As far as I can tell, there is no vote() function, unless it is in an included file. If you are talking about the voted() function, then you have been misled by the poor indenting. The voted() function is a one-liner.

[rvdavid]

hmmm was just a stab in the dark... I must have just skimmed over it as the code was ill formatted 🙁

back to old drawing board I'll say 🙂

[rvdavid]

Try the following:

//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($voting)); 

// see $votes
echo '<pre>';
print_r($votes);
echo '</pre>';

What output do you get?

regards,

[el_timm]

You are overwriting the $uv ARRAY and setting $uv to the INTEGER userid. Then you are trying to reverence an index as if $uv is still an array:

$checkuser = mysql_query("SELECT * FROM comicvote WHERE userid ='" . $userid . "'");
while ($uv =mysql_fetch_assoc($checkuser)) { 
// $uv is now an array
$uv = $uv['userid']; 
// $uv is now an integer
echo "UV: $uv<br>"; } 
// The next line, you are using $uv as an array again.  It is not.
$comp2 = $uv['0'] . "-" . $row['comic_id'];

Try this instead (with fixed formatting):

$result = mysql_query("SELECT * FROM comicvote WHERE userid ='$userid'");
if ($result && $uv =mysql_fetch_assoc($result)) {
	$uid = $uv['userid']; 
	echo "Userid: $uid<br>";
} 
// The next line, you are using $uv as an array again.  It is not.
$comp2 = $uid. "-" . $row['comic_id'];