[SOLVED] php Code is not updating mysql entry if it exists.

[Woodsyx]

I just have a simple database setup and the script below should check if the player name already exists in the table and update it but all its doing is creating a new entry everytime. Thanks for any help.

 

if($player['points'] == 0){//Check if player has zero points thus spectating.
	$name = $player['name']; //var for the players name
	$value = MYSQL_QUERY("SELECT name FROM idlers WHERE name='%s'", mysql_real_escape_string($name));
	echo "Got name from DB"."<br>";
	echo $player['points']."<br>";
	echo $player['name']."<br>";
	echo $value."<br>";
	if($value == $name){
	//Check if name already has entry
	$ptime = MYSQL_QUERY("SELECT lasttime FROM idlers WHERE name='%s'", mysql_real_escape_string($name));
	echo "Get previous time"."<br>";
	//Retreives the last max idle time
	$connection = $player['connected'];
	$time = substr($connection, 0, 1);//Converts time in min
	$time = (int)$time * 60;
	$time2 = substr($connection, 0, 1);
	$time = $time.(int)$time2;
	//Retreives total idle time so far.
	$ttime = MYSQL_QUERY("SELECT totaltime FROM idlers WHERE name='%s'", mysql_real_escape_string($name));
	echo "Get total time"."<br>";
	if ($ptime <= $time){//Check if last idle is larger than current idle.
						//If true they are continuing their current idle

		//Add Current time - Counted time to Previous Total
		$ttime .= $time - $ptime;
	}
	else{
	//Add current time to total time.
	$ttime .= $time;
	}
   	
//Update totaltime
MYSQL_QUERY("UPDATE idlers SET time=".$ttime." WHERE name=".$name)	;
//Sets new max idle time to current idle time
MYSQL_QUERY("UPDATE idlers SET lasttime=".$time." WHERE name=".$name);

}
else{//Create a new table entry with the correct data.
	echo "Creating new entry"."<br>";
	$time = substr($player['connected'], 0, 1) * 60;//Converts time in min
	$time .= substr($player['connected'], 0, 1);
	$name = $player['name'];
	$ttime = $time;
	$ptime = (int)$time;

  $result=MYSQL_QUERY("INSERT INTO idlers (totaltime, time, name, lasttime)
		VALUES ('$ttime','$time','$name','$ptime')"); 
	echo "Created new entry"."<br>";
}
}

[Philip]

Unless you created your own version of the function mysql_query, you're not using it properly.

resource mysql_query ( string $query [' date= resource $link_identifier ] )

 

It looks like you're trying to use mysql_query as a (s)printf, or prepared statement. You're also trying to pull the value of the mysql_query (which will return a resource, not a value) when you do echo $value."<br>";

 

Change:

$value = MYSQL_QUERY("SELECT name FROM idlers WHERE name='%s'", mysql_real_escape_string($name));

to:

 // put the value in the query - and you can limit the number of results since you're just checking to see if that name exists
$value = mysql_query("SELECT name FROM idlers WHERE name='".mysql_real_escape_string($name)."' LIMIT 1");
// get the number of rows returned
$num_rows = mysql_num_rows($value); 
// show number
echo $num_rows;

[Woodsyx]

Ok well that code bit worked and now its checking if there is already an entry in the db but instead of updating the current entry its just creating a new one.

 

if($player['points'] == 0){//Check if player has zero points thus spectating.
	$name = $player['name']; //var for the players name
	 // put the value in the query - and you can limit the number of results since you're just checking to see if that name exists
	$value = mysql_query("SELECT name FROM idlers WHERE name='".mysql_real_escape_string($name)."' LIMIT 1");
	// get the number of rows returned
	$num_rows = mysql_num_rows($value); 
	// show number
	echo $num_rows;
	echo "Got name from DB"."<br>";
	echo $player['points']."<br>";
	echo $player['name']."<br>";
	echo $value."<br>";
	if($value == $name){
	//Check if name already has entry
	$ptime = mysql_query("SELECT lasttime FROM idlers WHERE name='".mysql_real_escape_string($name)."' LIMIT 1");
	echo "Get previous time"."<br>";
	//Retreives the last max idle time
	$connection = $player['connected'];
	$time = substr($connection, 0, 1);//Converts time in min
	$time = $time * 60;
	$time2 = substr($connection, 3, 4);
	$time = $time.$time2;
	//Retreives total idle time so far.
	$ttime = mysql_query("SELECT totaltime FROM idlers WHERE name='".mysql_real_escape_string($name)."' LIMIT 1");
	echo "Get total time"."<br>";
	if ($ptime <= $time){//Check if last idle is larger than current idle.
						//If true they are continuing their current idle

		//Add Current time - Counted time to Previous Total
		$ttime .= $time - $ptime;
	}
	else{
	//Add current time to total time.
	$ttime .= $time;
	}
   	
//Update totaltime
MYSQL_QUERY("UPDATE idlers SET time=".$ttime." WHERE name=".$name)	;
//Sets new max idle time to current idle time
MYSQL_QUERY("UPDATE idlers SET lasttime=".$time." WHERE name=".$name);

}
else{//Create a new table entry with the correct data.
	echo "Creating new entry"."<br>";
	$time = substr($player['connected'], 0, 1) * 60;//Converts time in min
	$time .= substr($player['connected'], 3, 4);
	$name = $player['name'];
	$ttime = $time;
	$ptime = (int)$time;

  $result=MYSQL_QUERY("INSERT INTO idlers (totaltime, time, name, lasttime)
		VALUES ('$ttime','$time','$name','$ptime')"); 
	echo "Created new entry"."<br>";
}
}

[Philip]

Okay - if($value == $name){

 

Again, $value will be a mysql resource, not the value from the database. You need to call mysql_fetch_assoc to get the name... or just use the number of rows returned to see what you should do.

if($player['points'] == 0){//Check if player has zero points thus spectating.
	$name = $player['name']; //var for the players name
	 // put the value in the query - and you can limit the number of results since you're just checking to see if that name exists
	$value = mysql_query("SELECT name FROM idlers WHERE name='".mysql_real_escape_string($name)."' LIMIT 1");
	// get the number of rows returned
	$num_rows = mysql_num_rows($value); 
	echo "Got name from DB"."<br>";
	echo $player['points']."<br>";
	echo $player['name']."<br>";
	echo $value."<br>";

	// If the number of rows returned is greater than 0 - the name is in use.
	if($num_rows > 0 ){
		//Check if name already has entry
		$ptime = mysql_query("SELECT lasttime FROM idlers WHERE name='".mysql_real_escape_string($name)."' LIMIT 1");
		echo "Get previous time"."<br>";
		//Retreives the last max idle time
		$connection = $player['connected'];
		$time = substr($connection, 0, 1);//Converts time in min
		$time = $time * 60;
		$time2 = substr($connection, 3, 4);
		$time = $time.$time2;
		//Retreives total idle time so far.
		$ttime = mysql_query("SELECT totaltime FROM idlers WHERE name='".mysql_real_escape_string($name)."' LIMIT 1");
		echo "Get total time"."<br>";
		if ($ptime <= $time){//Check if last idle is larger than current idle.
							//If true they are continuing their current idle

			//Add Current time - Counted time to Previous Total
			$ttime .= $time - $ptime;
		}
		else{
		//Add current time to total time.
		$ttime .= $time;
		}

		//Update totaltime
		MYSQL_QUERY("UPDATE idlers SET time=".$ttime." WHERE name=".$name)	;
		//Sets new max idle time to current idle time
		MYSQL_QUERY("UPDATE idlers SET lasttime=".$time." WHERE name=".$name);

	}
	else{//Create a new table entry with the correct data.
		echo "Creating new entry"."<br>";
		$time = substr($player['connected'], 0, 1) * 60;//Converts time in min
		$time .= substr($player['connected'], 3, 4);
		$name = $player['name'];
		$ttime = $time;
		$ptime = (int)$time;

	  $result=MYSQL_QUERY("INSERT INTO idlers (totaltime, time, name, lasttime)
			VALUES ('$ttime','$time','$name','$ptime')"); 
		echo "Created new entry"."<br>";
	}
}

 

Now, looking at the rest of your code, you're not using mysql_fetch_assoc/array - so $ptime and $ttime are both going to be resources not values to compare.

[Woodsyx]

I looked up what you posted and tried to figure it out and came up with these 2 results and neither of them work. Did I get the syntax wrong or am I going down the wrong path of what to do.

 

		$result = mysql_query("SELECT lasttime FROM idlers WHERE name='".mysql_real_escape_string($name));
	$row = mysql_fetch_array($result);
	$ptime = $row['lasttime'];

 

		$result2 = mysql_query("SELECT totaltime FROM idlers WHERE name='".mysql_real_escape_string($name));
	$row2 = mysql_fetch_array($result2);
	$ttime = mysql_result($result2, 1);