PHP affected rows = 1 wont work properly.

Question

Hi. My script:

<?php
ob_start();
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header('Content-type: text/html; charset=utf-8');
include "tilslut.php";
$userid = $_GET["userid"];
$s = mysql_query("SELECT points, lastpoint FROM member_profile WHERE user_id = '".$userid."'");
$n = mysql_fetch_array($s);
$tid = time(); 
mysql_query("UPDATE member_profile set points = points+1, lastpoint=$tid  WHERE lastpoint<=$tid-60 AND user_id = '".$userid."'");
$e = mysql_query("SELECT points FROM member_profile WHERE user_id = '".$userid."'");
$f = mysql_fetch_array($e); 
if (mysql_affected_rows() == 1) {
$s = mysql_query("SELECT points FROM member_profile WHERE user_id = '".$userid."'");
$n = mysql_fetch_array($s);
?>
Inserted!
<?
}else{
echo "Already got";
}
ob_flush();
?>

I have this for giving points. The update query works, and only give point if lastpoint<=time()-60, but it still say "Inserted" even though it doesnt insert. I have tried to use mysql-affected-rows to check if it has affected or not, but this doesnt seem to work.

Answer 1

you have to call mysql_affected_rows immediately after the update, before you do another select. mysql_affected_rows will only work on the last query performed on the connection.

Answer 2

Youru query

$e = mysql_query("SELECT points FROM member_profile WHERE user_id = '".$userid."'");  

is causing the affected row to equal one. I suggest you check for affected rows immediately after update

Answer 3

You are :

• doing the update query
• then, doing a select query
• and, only then, calling mysql_affected_rows

It might work better if you were calling mysql_affected_rows immediatly after the update query, without having another query between those : mysql_affected_rows is supposed to work with the data from the last query -- even though the documentation doesn't say about select queries, I suppose this could cause some problem.

As a side-note : you have some risk of SQL Injection, here : you should escape your data before injecting into an SQL query (I'm thinking about $_GET["userid"]), or, at least, make sure it's an integer.

And you should use more descriptive variable names : $e, $f, $n, $s, ... this makes your code harder to read/understand/maintain :-(