Jump to content


Photo

silly mysql question


  • This topic is locked This topic is locked
16 replies to this topic

#1 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 09:40 PM

Hi, I've been using mySQL for a while now, but I have a silly question. I'm trying to troubleshoot a problem I'm having, and was thinking this might be it..

So here's the question.

If you connect to a database, and query to select a row, is it ok to then update that row with a new value in some column before closing the connection?
So,

-connect to mysql host
-switch to correct database
-select row from a table
-display that row
-UPDATE that same row in the table
-close connection

or should I close the connection before I update?
I wouldn't think this would be a problem, but its the only thing I can think of that may be wrong in my code..

thanks

#2 digitalgod

digitalgod
  • Members
  • PipPipPip
  • Advanced Member
  • 374 posts

Posted 31 July 2006 - 09:43 PM

If you want to close the connection you close it at the very end, because when you're updating a row you're still connected to the db.

#3 cmgmyr

cmgmyr
  • Members
  • PipPipPip
  • Advanced Member
  • 1,278 posts
  • LocationUSA

Posted 31 July 2006 - 09:44 PM

It shouldn't really matter if you keep it open or close it and open a new connection.

Can you post some code and let us know where you think the problem is?

-Chris

#4 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 09:45 PM

Sure I'll post it, give me one minute

#5 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 31 July 2006 - 09:45 PM

If you close the connection, you wouldn't be able to do any more transactions. Actually, you don't need the "close connection", since it closes automatically when the script ends.

Please post the code that's causing the problems and the exact problem (or error message) your having.

Ken

#6 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 09:59 PM

Here's the code:

//Set up query to randomly select a row
$query = "SELECT * FROM XXX ORDER BY RAND() LIMIT 1";

//Connect to Database
$host="XXX";
$database = "XXX";
$user = "XXX";
$password = "XXX";
$connection = mysql_connect($host,$user,$password)
	or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection)
	or die ("Couldn't select database");	
	
//Run the query
$result = mysql_query($query)
       or die ("Error: Please check ID.");

//Get the data
$nrows = mysql_num_rows($result);
$row = mysql_fetch_array($result);
extract($row);
	
//I later use these variables in HTML (after the cookie below)

//Now I want to increment a hit counter in that row based on a cookie.. each item in my table has its own counter

//The following part is due to the help of lukelambert in one of my previous posts
	
//Counter Cookie
//Set query
$query_count = "UPDATE XXX SET hits=hits+1 WHERE id=$id";  //Note: $id is an auto_increment col
//Set cookie info
$cc_name = "counter"; // Name of the cookie
$cc_expires = time() + 3600; // Expires in an hour
$cc_item = "[$id]"; // Item identifier in [XX] format
$cookie = $_COOKIE;
if ($_COOKIE[$cc_name]){
	  $cc_value = $_COOKIE[$cc_name];		
	  if (strpos($cc_value, $cc_item) === false) // If the user has not visited this page
	  {
  		// Increase view count in database  
		$result2 = mysql_query($query_count)
  		 or die("Error");
  		$cc_value .= $cc_item; // Add the item to the cookie
  	}
	}else{
  		$cc_value = $cc_item; // Add the item to the cookie
  		// Increase view count in database		
  		$result3 = mysql_query($query_count)
  			or die("Error");
	}
  	setcookie($cc_name, $cc_value, $cc_expires,'/XXX/','.XXXX.com',0); // Create or re-create the cookie

Problem that I'm having:
When there is no cookie, the else statement is generated (as expected), but for some reason, the column is incremented by either 2 or 3 (NOT 1)
When there IS a cookie, but THAT particular item has not been generated, the nested if statement is executed (as expected), but the column is incremented by 2 or 3 (NOT 1)

I can put small debugging catches in those nested if-else's so I know they are executing correctly. I just can't figure out why the counters are incremented so much! (And I know I'm the only person executing this code since I have it in a secure location).

Thanks for the help :)

#7 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 10:00 PM

Not sure why those squares are showing up.. just ignore them :P

#8 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 11:05 PM

I've had several views since I've posted this, but no one has responded.

If you've read the code, and don't see the problem, or you think everything written should work fine, could you post and say just that? I just want to make sure I'm not overlooking something stupid. If others see no problem, I'll know atleast that I have some work ahead of me trying to figure this one out.

I've spent several hours on this seemingly easy problem already with no luck. thanks! ;)

#9 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 11:16 PM

Ok here's something else that might help..

If I comment out the mysql query in the else statement:
	}else{
  		$cc_value = $cc_item; // Add the item to the cookie
  		// Increase view count in database		
  		//$result3 = mysql_query($query_count)
  		//	or die("Error");
	}
  	setcookie($cc_name, $cc_value, $cc_expires,'/XXX/','.XXXX.com',0); // Create or re-create the cookie

then everything works correctly, except that if no cookie exists, then that item is not incremented.
Any ideas?

#10 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 31 July 2006 - 11:40 PM

Where are you getting the $id variable from?
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#11 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 11:45 PM

you can access any item in the database by typing  http://www.XXXX.com/item.php?id=XXX

and each id is stored in the database. So I suppose the $id here is coming straight from the table.

#12 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 31 July 2006 - 11:50 PM

You used it in this line
$query_count = "UPDATE XXX SET hits=hits+1 WHERE id=$id";  //Note: $id is an auto_increment col
but I can't see anywhere in your script where you've set it.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#13 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 11:59 PM

for this example, let's just say it comes out of
extract($row)

every item has its own unique id

#14 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 01 August 2006 - 12:10 AM

if nothing else can someone please point me to a simple counter script using mysql and php?
I've been racking my brain for hours over this and am frustrated beyond belief.

thanks

#15 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 01 August 2006 - 03:14 AM

*bump one more time in a last ditch effort..

After tonight I'm giving up completely on this.

#16 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 01 August 2006 - 03:44 AM

i changed the code a bit:

$cc_name = 'counter'; //counter name
$cc_expires = time() + 3600; //expires in an hour
$cc_item = '['.$id.']'; //item id in brackets
$cc_value = $_COOKIE[$cc_name];
if(strpos($cc_value,$cc_item) === FALSE){
$TEST.="Cookie code does not exist. ";
mysql_query("UPDATE items SET hits=hits+1 where id=$id");
if(isset($_COOKIE[$cc_name]))
$cc_value = $_COOKIE[$cc_name];
else
$cc_value = $cc_item;
}else{
$TEST.="Cookie created and has visisted page.";
                $cc_value .= $cc.item;
}
setcookie($cc_name,$cc_value,$cc_expires); //Create or recreate the cookie


The problem is that if the count in the table is currently 1, then this will increment it twice (why?), otherwise, it works.

#17 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 01 August 2006 - 04:58 AM

I've given up and completely done this whole thing a new way, so I'm locking this thread.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users