silly mysql question

[bpops]

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

[digitalgod]

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.

[cmgmyr]

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

[bpops]

Sure I'll post it, give me one minute

[kenrbnsn]

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

[bpops]

Here's the code:

[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[/code]

[b]Problem that I'm having:[/b]
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 :)

[bpops]

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

[bpops]

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! ;)

[bpops]

Ok here's something else that might help..

If I comment out the mysql query in the else statement:
[code]
}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[/code]

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

[king arthur]

Where are you getting the $id variable from?

[bpops]

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.

[king arthur]

You used it in this line
[code]
$query_count = "UPDATE XXX SET hits=hits+1 WHERE id=$id";  //Note: $id is an auto_increment col
[/code]
but I can't see anywhere in your script where you've set it.

[bpops]

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

every item has its own unique id

[bpops]

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

[bpops]

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

After tonight I'm giving up completely on this.

[bpops]

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.

[bpops]

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