Jump to content

Archived

This topic is now archived and is closed to further replies.

bpops

silly mysql question

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
Not sure why those squares are showing up.. just ignore them :P

Share this post


Link to post
Share on other sites
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! ;)

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
for this example, let's just say it comes out of
extract($row)

every item has its own unique id

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
*bump one more time in a last ditch effort..

After tonight I'm giving up completely on this.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
I've given up and completely done this whole thing a new way, so I'm locking this thread.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.