Update single row

[phpDave]

I have been working with this for a while now.
User page has a counter that they can reset when they click on a link.
The link goes to a script that updates the counter to zero but when I test it all rows in table update to zero.

$colname_Recordset1 = "-1";
if (isset($_SESSION['MM_Username'])) {
  $colname_Recordset1 = $_SESSION['MM_Username'];
}
mysql_query("UPDATE user INNER JOIN registration SET counter=0 WHERE user_name = '$colname_Recordset1'");
$updateGoTo = "account.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    $updateGoTo .= $_SERVER['QUERY_STRING'];
  }
header(sprintf("Location: %s", $updateGoTo));

[CFROG]

Your query doesn't look quite right to me. Here is the syntax for using an inner join.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Can you provide a dump of the table structure ? Like this it's a little difficult to see the field you like to update.
If you want to update the counter field I don't see the logic to use a join. If you're DB structure is good, you don't need joins. The only thing to use is an id of the user you want to update.

[phpDave]

Thanks for replies, I'm still new to php. I'm trying to reset the counter column when logged in as a user. Trying to carry a session from account.php to reset.php where the script is. Session is user_name and the user_name is in registration table, counter is in the other.

Thanks for replies, I'm still new to php. I'm trying to reset the counter column when logged in as a user. Trying to carry a session from account.php to reset.php where the script is. Session is user_name and the user_name is in registration table, counter is in the other.

Same question again : can you put the table structure so we can give some ideas for improvement. I think the table structure is not good.

eg :

table registration :
user_id (int)
user_name (varchar)
...

table registration
user_id (int)
counter (int)
...

[phpDave]

Table registration
user_id(int, unsigned, primary, AI)
user_name(varchar)

Table user
user_two_id(int, unsigned, primary,AI)
user)id(int, unsigned, index)
counter(int, unsigned)

[CFROG]

I'm not overly confident about this one but you can give it a shot and see what it does :)

mysql_query("UPDATE u.user AS u SET counter=0 JOIN r.registration AS r ON u.user_id=r.user_id WHERE u.username='$colname_Recordset1' ");

<edit> Made additional changes - I had things backwards

Table registration
user_id(int, unsigned, primary, AI)
user_name(varchar)

Table user
user_two_id(int, unsigned, primary,AI)
user)id(int, unsigned, index)
counter(int, unsigned)

Try the next things :

• Put the user_id in a session variable
• Use the user_id to perform the query
• update the sql for your query like this : $sql = "update user set counter=0 where user_id = ".$_SESSION

Another problem I see is that the query is executed even if the session variable does not exist.

You have to run the query inside the if where you check if you're session variable exists.

if (isset($_SESSION['user_id'])) {
[INDENT]
$sql = "update user set counter=0 where user_id = ".$_SESSION['user_id'];
$result = mysql_query($sql);
[/INDENT]
} else {
[INDENT]
some code to indicate that the user is not defined
[/INDENT]
}

[phpDave]

HI,
I tried many many ways to do this over the past two days, here is the latest

if (isset($_SESSION['MM_Username'])) {
mysql_query("UPDATE user INNER JOIN registration SET counter=0 WHERE user_name = '$colname_Recordset1'"); 
}

It's like the WHERE clause is ignored. All or no rows update.

[CFROG]

It's never going to work the way you have it, the query is not structured properly as was stated in an earlier post. If you really want to use a JOIN in your query which isn't at all necessary you can look at the example above to see what you should be doing differently (the example above may even work if you try it) or try researching the proper syntax (http://www.w3schools.com/sql/sql_join_inner.asp) and start experimenting from there.

My advice at this point is to go with Soldierflup's suggestion, it's exactly what you are looking for without being needlessly drawn out and complicated. I have no doubts that it will work and do exactly what you want.

[phpDave]

Hi again, I tried code but after running some tests I found that if I echo $_SESSION it comes up user_name. I think if I can set the session to user_id to the user_id I can get it to work. I tried to set it but not working. Any thoughts?

[CFROG]

How did you try setting it? Please post your code.

[phpDave]

Thanks for reply,

$_SESSION['MM_Username'] = $_SESSION['user_name'];

But error shows unknown column user_name even though I use $_SESSION

[CFROG]

You're not actually doing anything with that piece of code to set a "user_id" as a $_SESSION variable. All that code is doing is ... well, nothing (or at least nothing useful). ;)

Somewhere on one of your pages you have a login script and more than likely somewhere in that script it's setting "user_name" as a $_SESSION variable after a successful login ... that's the ideal place to set the variable and the code I would like to see.

[phpDave]

$_SESSION['MM_Username'] = $loginUsername;

[CFROG]

Let's try this another way ... post the entire login page.

[phpDave]

<?php
if(isset($_POST['pwd'])) { $_POST['pwd'] = sha1($_POST['pwd']);}
//initialize the session
if (!isset($_SESSION)) {
  session_start();
}
$inactive = 2400;
  if(isset($_SESSION['timeout'])) {
	  $session_life = time() - $_SESSION['timeout'];
	  if($session_life > $inactive)
	  { session_destroy(); header("Location: logged_out.php");}
  }

  	$_SESSION['timeout']=time();

// ** Logout the current user. **
$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";
if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){
  $logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){
  //to fully log out a visitor we need to clear the session varialbles
  $_SESSION['MM_Username'] = NULL;
  $_SESSION['MM_UserGroup'] = NULL;
  $_SESSION['PrevUrl'] = NULL;
  unset($_SESSION['MM_Username']);
  unset($_SESSION['MM_UserGroup']);
  unset($_SESSION['PrevUrl']);
	
  $logoutGoTo = "index.php";
  if ($logoutGoTo) {
    header("Location: $logoutGoTo");
    exit;
  }
}
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}
?>
<?php
// *** Validate request to login to this site.
if (!isset($_SESSION)) {
  session_start();
}

$loginFormAction = $_SERVER['PHP_SELF'];
if (isset($_GET['accesscheck'])) {
  $_SESSION['PrevUrl'] = $_GET['accesscheck'];
}

if (isset($_POST['user_name'])) {
  $loginUsername=$_POST['user_name'];
  $password=$_POST['pwd'];
  $MM_fldUserAuthorization = "";
  $MM_redirectLoginSuccess = "account.php";
  $MM_redirectLoginFailed = "login_failed.php";
  $MM_redirecttoReferrer = false;
  mysql_select_db($database_connadmin, $connadmin);
  
  $LoginRS__query=sprintf("SELECT user_name, pwd FROM registration WHERE user_name=%s AND pwd=%s",
    GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text")); 
   
  $LoginRS = mysql_query($LoginRS__query, $connadmin) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
     $loginStrGroup = "";
    
    //declare two session variables and assign them
    $_SESSION['MM_Username'] = $loginUsername;
    $_SESSION['MM_UserGroup'] = $loginStrGroup;	      

    if (isset($_SESSION['PrevUrl']) && false) {
      $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];	
    }
    header("Location: " . $MM_redirectLoginSuccess );
  }
  else {
    header("Location: ". $MM_redirectLoginFailed );
  }
}
?>

[CFROG]

I really hate to have you hack up your login ... Have you tried the JOIN example from the previous post to see if works? If you tried it and it doesn't work add this to your login script and it will set the session variable for you. I hate to do it this way but it will work.

//declare two session variables and assign them
    $_SESSION['MM_Username'] = $loginUsername;
    $_SESSION['MM_UserGroup'] = $loginStrGroup;	      
 
$get_id = mysql_query("SELECT user_id FROM registration WHERE user_name='$loginUsername' ");
while($r=mysql_fetch_array($get_id)){
$_SESSION['user_id'] = $r['user_id']; }

[phpDave]

Thanks CFROG!
I'll try the join from previous post or change idea a little. I'm a little afraid to modify log in code, had bad experience a while back.
Thanks again.

[CFROG]

The login code will be fine if you add the code i supplied ... you won't have any problems with it. In a worse case scenario you could always just put it back the way that it was.

[phpDave]

Finally!, I got the JOIN to work from code you posted before. Thanks again.