[SOLVED] How is my count off?

[86Stang]

Hey Everyone,

 

I've got what is hopefully a quick question that someone can help me out with.

 

I've got a table in a database with the following fields:

id
business
ad_url
num_views
num_prints
start_date
stop_date
category
description

 

I've got two records in the database to use for examples at this point.

 

I'm displaying the ads but I want to increment the num_views field each time an ad is displayed.  If you see below, I'm doing that but it's incrementing by 3 each time instead of just one.  Can you see where I'm going wrong?

 

<?php
$result = mysql_query(SELECT * FROM ads);
while($row = mysql_fetch_assoc($result))
{
	extract($row);
	echo "<img src=\"/$ad_url\" width=\"400\">";
	echo "<h4>$business</h4>$description";

	$qry = "UPDATE ads SET num_views = num_views + 1 WHERE id = $row[id]";
	mysql_query($qry) or die ("Error during query!");
}
?>

[Dragoa]

Looks like your id might not be unique, which is causing all the records to be updated each time it's called. Have you tried swapping out the Update for a Select statement to make sure you only selecting a single record each time?

[86Stang]

No I haven't tried that.  How would that work?

[paul2463]

oops sorry was talking rubbish

[Adam]

try something as stupid as changing like...

 

$row[id] .. to .. $row['id']

 

things like that have gotten weird results for me before...

[86Stang]

Thanks for the tip MrAdam but that made it error out.

[webent]

<?php

$result = mysql_query(SELECT * FROM ads);

while($row = mysql_fetch_assoc($result))

{

extract($row);

echo "<img src=\"/$ad_url\" width=\"400\">";

echo "<h4>$business</h4>$description";

 

$qry = "UPDATE ads SET num_views = num_views + 1 WHERE id = $row[id]";

mysql_query($qry) or die ("Error during query!");

}

?>

 

Perhaps try going old school on it and try that, pull out the num_views variable, increment it and then stick it back in... i.e.,

 

$num_views = $row['num_views'] + 1;
$qry = "UPDATE ads SET num_views = $num_views WHERE id = $row[id]";

 

[obsidian]

I wonder if you are not really getting the results you intend in your $row variable. Try echoing out your query to be sure it is what you expect it to be before executing it:

<?php
$qry = "UPDATE ads SET num_views = num_views + 1 WHERE id = $row[id]";
echo "<pre>" . print_r($qry, TRUE) . "</pre>";
?>

 

What does that give you?

[Dragoa]

To change the UPDATE to a SELECT just take the WHERE clause from your update and basically stick it into a Select.

 

So:

 

SELECT *
FROM ads
WHERE id = $row[id]

 

Then just run that query and display the results onto a page somehow, or just print out the statement and run it manually.

[DarkWater]

Thanks for the tip MrAdam but that made it error out.

 

That made it error out because you aren't properly echoing out the array inside of double quotes.  It won't mess with the query (because you're doing it wrong), but it's just bad practice.  MrAdam had it right:

 

$qry = "UPDATE ads SET num_views = num_views + 1 WHERE id = {$row['id']}";

 

Notice the { }.  Or you could have done:

 

$qry = sprintf('UPDATE ads SET num_views = num_views + 1 WHERE id = %d', $row['id']); //My personal preference.

[86Stang]

@webent

I tried that but it is still incrementing all records by 3.

 

@obsidian

your code outputs:

UPDATE ads SET num_views = num_views + 1 WHERE id = 29

for the first record and

UPDATE ads SET num_views = num_views + 1 WHERE id = 31

for the second record, which are the correct id's for the records.

 

@DarkWater

You're completely right about the curly brackets.  No more errors but the num_views are still incrementing by 3 each time.

 

[DarkWater]

Oh yeah.  Why are you using $row['id'] when you already extract()'d $row anyway? >_>

[86Stang]

Honestly, I'm not sure - this is the first time toying with extract.  I'm open to any direction you'd like to give me.

[DarkWater]

Oh yeah.  Does the number 3 have any significant meaning based upon the amount of records in your database or something?  Try adding a few more to see if the count increases by a larger number or something.  If it doesn't, we at least know it's not that.

[86Stang]

I added four more records but it's still incrementing by 3 so we can count that out.

[unkwntech]

I have seen several cases were an SQL query runs multiple times, without due cause, this may be the case here.

[rhodesa]

First, your initial select doesn't have quotes around it...i assume that was a copy/paste issue...

 

also, how are you calling this code? through an include? is there more code around it? if you use the following, what happens?

 

<?php
global $__test_catch;
if($__test_catch)
	die("This code has already been run");
$__test_catch = true;

$result = mysql_query("SELECT * FROM ads");
while($row = mysql_fetch_assoc($result))
{
	extract($row);
	echo "<img src=\"/$ad_url\" width=\"400\">";
	echo "<h4>$business</h4>$description";

	$qry = "UPDATE ads SET num_views = num_views + 1 WHERE id = $row[id]";
	mysql_query($qry) or die ("Error during query!");
}
?>

[86Stang]

When I try that I get an increment of 3 and no "This code has already been run" notice.  And yes, the missing quotes was a copy/paste error on my part.  Here's the entire code:

 

<table width="600px">
    <tr valign="top">
        <td>

	<form action="http://www.mysite.com/print" method="POST">
		<div id="print_btn">
			<input class="submit" type="image" value="Print Selected" src="images/button_print.jpg" style="vertical-align:bottom;" />
		</div>

		<?php
			$result = mysql_query("SELECT * FROM ads");
			while($row = mysql_fetch_assoc($result))
			{
				extract($row);
				echo "<img src=\"/$ad_url\" width=\"400\">";
				echo "<span id=\"select_option\"><br />Select:</span><input type=checkbox name=\"a$id\" value=\"$id\" style=\"vertical-align:middle;\"><br />\n";
				echo "<h4>$business</h4>$description<br /><br />";

				$qry = "UPDATE ads SET num_views = num_views + 1 WHERE id = $id";
				mysql_query($qry) or die ("Error during query!");
			}
		?>

		<input type=hidden name="endoflist" value="-1000">
	</form>					

        </td>
    </tr>
</table> 

 

I thought that the checkbox naming might be the culprit but I removed it completely and it still increments by 3.

[rhodesa]

<?php

$result = mysql_query(SELECT * FROM ads);

while($row = mysql_fetch_assoc($result))

{

extract($row);

echo "<img src=\"/$ad_url\" width=\"400\">";

echo "<h4>$business</h4>$description";

 

$qry = "UPDATE ads SET num_views = num_views + 1 WHERE id = $row[id]";

mysql_query($qry) or die ("Error during query!");

}

?>

 

Perhaps try going old school on it and try that, pull out the num_views variable, increment it and then stick it back in... i.e.,

 

$num_views = $row['num_views'] + 1;
$qry = "UPDATE ads SET num_views = $num_views WHERE id = $row[id]";

 

 

have you tried webent's suggestion?

[86Stang]

Would I do that by just adding that line via:

 

<?php
   $result = mysql_query(SELECT * FROM ads);
   while($row = mysql_fetch_assoc($result))
   {
      extract($row);
      echo "<img src=\"/$ad_url\" width=\"400\">";
      echo "<h4>$business</h4>$description";
      
      $num_views = $row['num_views'] + 1;
      $qry = "UPDATE ads SET num_views = num_views + 1 WHERE id = $row[id]";
      mysql_query($qry) or die ("Error during query!");
   }
?>

[86Stang]

I tried the method I just posted and it's still incrementing by 3...

[gmwebs]

If you are using extract($row) doesn't it then create variables from the field names for the row?

 

<?php
// While a row of data exists, put that row in $row as an associative array
// Note: If you put extract($row); inside the following loop, you'll
//       then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
}
?>

 

I don't understand why you would be doing:

<?php $num_views = $row['num_views'] + 1; ?>

 

What if you try the following...

 

<?php
   $result = mysql_query(SELECT * FROM ads);
   while($row = mysql_fetch_assoc($result))
   {
      extract($row);
      echo "<img src=\"/$ad_url\" width=\"400\">";
      echo "<h4>$business</h4>$description";
           
      $qry = "UPDATE ads SET num_views = num_views + 1 WHERE id = $row[id]";
      mysql_query($qry) or die ("Error during query!");
   }
?>

[thebadbad]

You're missing a $ in your last attempt, and you're adding to the number twice. num_views is not the same as $num_views you know

 

<?php
  $result = mysql_query('SELECT * FROM ads');
  while($row = mysql_fetch_assoc($result))
  {
     extract($row);
     echo "<img src=\"/$ad_url\" width=\"400\">";
     echo "<h4>$business</h4>$description";
     
     $num_views = $row['num_views'] + 1;
     $qry = "UPDATE ads SET num_views = $num_views WHERE id = $row[id]";
     mysql_query($qry) or die ("Error during query!");
  }
?>

 

And there's no sense in using extract() if you're only using some of the extracted vars. In the below code I'm using the extracted vars consistently:

 

<?php
  $result = mysql_query('SELECT * FROM ads');
  while($row = mysql_fetch_assoc($result))
  {
     extract($row);
     echo "<img src=\"/$ad_url\" width=\"400\">";
     echo "<h4>$business</h4>$description";
     
     $number_views = $num_views + 1;
     $qry = "UPDATE ads SET num_views = $number_views WHERE id = $id";
     mysql_query($qry) or die ("Error during query!");
  }
?>

 

Hope it works now.

[86Stang]

Tried it - still counting by 3's.

[gmwebs]

Which did you try? Thebadbad or mine, or both?