update set

misunderstood · #1 28-Dec-2003, 18:59

I have put a counter onto my news page to record how many times each article (recalled from database) has been read. However it doesnt work

.
I am using

PHP Code:


$sql2="update news set counter=$counter+1 where newsid ='$newsid'";

after my recall to add another number to the counter column. Does this line look OK?
If so can anybody help in pointing me in the right direction of what I may me doing wrong.
A bit vague I know but all the recalls work ok.

JdS · #2 29-Dec-2003, 06:53

Quote:

Originally Posted by misunderstood

PHP Code:


$sql2="update news set counter=$counter+1 where newsid ='$newsid'";

... Does this line look OK? ...

Sorry misunderstood, I was testing some new changes to the board and didn't notice this thread you started. Anyway, it doesn't look that it was ignored for very long...

Anyway, try:

PHP Code:


<?
$sql2 = "update news set `counter`=`counter`+1 where newsid ='$newsid'";
?>

That's another reason why I like to surround my db / table / field names with backticks - you can pick out typos easily!

misunderstood · #3 29-Dec-2003, 07:13

I think I will have to give you more code because I still cant get it to work. I think I have mixed my code somewhere but just cant see it.
The first section works but the second section doesnt. Am I mixing things?

PHP Code:


$sql2= "select * from news where newsid ='$newsid' ";
 $result2=mysql_query($sql2,$connection) or die("something is wrong");
$row2=mysql_fetch_array($result2);

$counter=mysql_result($result2,0,"counter");
$sql2 = "update news set `counter`=`counter`+1 where newsid ='$newsid'";

JdS · #4 29-Dec-2003, 08:08

OK, I see the problem:

Quote:

Calls to mysql_result() should not be mixed with calls to other functions that deal with the result set.

... read the PHP manual on the function mysql_result().

Anyway, it seems to me that all you want to do, out of this bit of code, is to update the counter by ONE right?

Then you can disable every line in your sample code above and just edit that last line like this:

PHP Code:


<?
//$sql2= "select * from news where newsid ='$newsid' ";
//$result2=mysql_query($sql2,$connection) or die("something is wrong");
//$row2=mysql_fetch_array($result2);

//$counter=mysql_result($result2,0,"counter");
$sql2 = "UPDATE `news` set `counter`=`counter`+1 where `newsid` = $newsid";
mysql_query( $sql2, $connection ) or die( mysql_errno().': '.mysql_error()."\r\n" );
?>

misunderstood · #5 29-Dec-2003, 08:27

Thanks JDS that works now

I swapped it around a little to

PHP Code:


<?
$sql = "UPDATE `news` set `counter`=`counter`+1 where `newsid` = $newsid";
mysql_query( $sql2, $connection ) or die( mysql_errno().': '.mysql_error()."\r\n" );

$sql2= "select * from news where newsid ='$newsid' ";
$result2=mysql_query($sql2,$connection) or die("something is wrong");
$row2=mysql_fetch_array($result2);



?>

It now updates the times viewed before recalling the item. No problems at the moment but can you see any potential problems with this ?

I have another question similar to this but I will read the php manual again to see if I can work it out.

JdS · #6 29-Dec-2003, 08:36

When I said this in my last post:

Quote:

... read the PHP manual on the function mysql_result().

What I actually meant to say is this:

Quote:

... read the PHP manual on the function mysql_result() if you want to verify it.

I welcome your questions and no I will not expect you to RTFM before you ask your questions here. This applies to anyone and everyone.

misunderstood · #7 29-Dec-2003, 09:34

One problem solved generates another :-(
Can I get my timestamp to ignore the counter column. It now shows posted on the time the visitor clicks the news item :-(
Can I alter the timestamp to only work from my admin and not the news page? It only took me half an hour to notice

.

JdS · #8 29-Dec-2003, 09:48

FWIW, I don't think the code we discussed in this thread is the cause of the timestamp being updated as well. It must be some other update query further down in your script thats doing this..

misunderstood · #9 29-Dec-2003, 12:56

Managed to sort the problem out by adding a counter table that had the id for my news inserted into the counter table when I added a new news item. I now update the counter table when the newsid matches the id in the counter table. My timestamp is still in the news table and isnt affected.

I am sure you have done an article on this now I think about it.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Google Update and DeepCrawl Alert	JdS	Search Engine Optimization Forum	24	16-Aug-2003 06:35
update problem	zuzupus	MySQL / PHP Forum	2	15-Aug-2003 08:00
Google update imminent?	JdS	Search Engine Optimization Forum	2	08-Aug-2003 11:08
The Google update this time...	JdS	Search Engine Optimization Forum	5	04-Jun-2003 16:25
Analyzing the March Google update	jrobbio	Search Engine Optimization Forum	3	12-Mar-2003 19:17