Member Avatar for NullReturned

So I'm having this weird issue, where I can do SELECT statements and INSERT statements just fine, but when I do this UPDATE statement, it sorta works. It runs without errors, and even outputs that 1 row is affected. But when I check the MySQL, nothing is updated. I've echo-ed out the UPDATE statement, which has all variables being passed to it correctly. When I take that echo-ed statement and input directly into MySQL, it works fine.

I've double checked that $login_count is actually an INT Value. I've even tried converting the statement into an "INSERT ON DUPLICATE KEY UPDATE" and that has the exact same results. Am I missing something??

<?php
session_start();

require_once ('../../database_connection.php');

// username and password sent from form 
$myusername=$_POST['myusername']; 
$mypassword=$_POST['mypassword'];
$encrypted_mypassword=md5($mypassword);

// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$myusername = mysql_real_escape_string($myusername);

$query_user_login = mysql_query ("SELECT * FROM members WHERE username='$myusername' and password='$encrypted_mypassword'");

// Mysql_num_row is counting table row
if(mysql_num_rows($query_user_login)==1)
// If result matched $myusername and $mypassword, table row must be 1 row
{
	// Register $myusername, $mypassword and redirect to file "login_success.php"
	$user_array = mysql_fetch_array($query_user_login);
	$memberid = $user_array['memberid'];
	$login_count = $user_array['login_count'] + 1;
	
	$last_login = date('Y-m-d H:i:s');
	
	$query_update_last_login = ("UPDATE `members` set `login_count` = $login_count where `memberid`=$memberid") or die(mysql_error());
	
	mysql_close(); // Close the database connection.
	
	$_SESSION['memberid'] = $user_array['memberid'];
	$_SESSION['username'] = $user_array['username'];
	
	header("location:../../index.php");
}
else 
{
	echo mysql_error();
	
	mysql_close(); // Close the database connection.
	
	header("location:../../index.php?action=bad_login");
}
?>
  • 2 Contributors
  • 3 Replies
  • 307 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by mikulucky

Dani AI

Generated

Good catch by — the root cause here was that the UPDATE string was built but never executed before the script closed the DB connection and redirected. That pattern (build query, forget to run it) is an easy oversight and explains why the SELECT/INSERT paths appeared to work while the UPDATE seemed to have no effect. Thread starter confirmed the same symptom and fixed that missing execution.

A short debugging checklist that usually helps when an UPDATE “does nothing”:

  • Confirm the query is actually executed (and executed on the same connection opened by the app) before closing the connection or redirecting.
  • Check the query return value and the database error output (or exceptions) rather than assuming success.
  • Use the engine’s affected-row count (mysqli_stmt->affected_rows / PDOStatement->rowCount()) to verify change; if it’s zero, the new value may equal the old one.
  • Verify the script is connected to the intended database/schema and that the DB user has UPDATE privileges.
  • Log the exact SQL string and run it directly on the server when needed to compare environments.

A few safer practices and a short modern example (avoid old mysql_* functions):

  • Use parameterized queries (PDO or mysqli) and cast numeric IDs to int.
  • For incrementing a login counter, prefer an atomic SQL update to avoid race conditions: UPDATE members SET login_count = login_count + 1, last_login = :last WHERE memberid = :id.
  • Replace insecure password storage (MD5) with password_hash() / password_verify(), and call session_regenerate_id() after successful login.

Example (PDO, minimal):

$stmt = $pdo->prepare(
  'UPDATE members
   SET login_count = login_count + 1, last_login = :last
   WHERE memberid = :id'
);
$stmt->execute([':last' => date('Y-m-d H:i:s'), ':id' => (int)$memberId]);

Final note: execute the UPDATE before closing the connection, check return values, and consider migrating to PDO/mysqli and stronger password handling for lasting reliability and security.

Recommended Answers

All 3 Replies

Member Avatar for mikulucky

Is this line

$query_update_last_login = ("UPDATE `members` set `login_count` = $login_count where `memberid`=$memberid") or die(mysql_error());

You are not actually executing a query change this line to the following:

$query_update_last_login = "UPDATE `members` set `login_count` = $login_count where `memberid`=$memberid";

mysql_query($query_update_last_login ) or die(mysql_error());

Hope that helps :)

Member Avatar for NullReturned

GAH! Thanks mikulucky! It's so simple and obvious, but I was focusing on the statement and forgot to format it to run the query. I feel so stupid, but thanks for the second set of eyes.

Member Avatar for mikulucky

No problem, it is easy to get caught up in other things. If it has worked please mark thread as solved and give rep as appropriate.

Thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.