[RESOLVED] adding to a number in a column

Fritz_fx

G'Day all
I've been trying to find a solution to this problem myself but keep ending up with some really strange results.
Basically what I'm trying to do, is grab a number out of a column and add "1" to it.

My script grabs player info from a game server log and inserts it into the DB if they are not already there and in the "logins" column inserts the number "1".
Now if the next day, they login again, I want to change that number to "2" and so on..
So far this is what I have for inserting all info for their first login, but I'm stuck on the update for the rest..

$sql = mysql_query("select * from players where guid = '$guid' AND ip='$ip'") or die ("query 1: " . mysql_error());
$mysql_num = mysql_num_rows($sql);

if ($mysql_num == 0)
{
@$insert = "INSERT INTO players (guid, name, ip, first_seen, last_seen_date, last_seen_time, logins) VALUES ('$guid', '$name', '$ip', '$date $h:$m:$s', '$date', '$h:$m', '1')";
$result = mysql_query($insert) or die(mysql_error());


}
else
{// **grab their login number and add "1" to it**  This is where I'm stuck
}

j_70

Just retrieve the login value and add 1:

<?php



$sql = mysql_query("select * from players where guid = '100' AND ip='127.0.0.1'") or die ("query 1: " . mysql_error()); 
$mysql_num = mysql_num_rows($sql);

if ($mysql_num == 0)
{
@$insert = "INSERT INTO players (guid, name, ip, first_seen, last_seen_date, last_seen_time, logins) VALUES ('$guid', '$name', '$ip', '$date $h:$m:$s', '$date', '$h:$m', '1')";
$result = mysql_query($insert) or die(mysql_error());


}
else
{
$DisplayLogin=mysql_fetch_array($sql);

print $DisplayLogin[6]+1;// **grab their login number and add "1" to it**  This is where I'm stuck
} 
?>

Fritz_fx

OK thanks, never used fetch_array before.
Can I do something like this: (column containing "the number" is named logins)

$DisplayLogin=mysql_fetch_array("select logins from players where guid = '$guid'");
$result =  $DisplayLogin + 1;
mysql_query("UPDATE logins SET logins = '$result' WHERE guid = '$guid'");

I'm still a bit of a newbie and it's 2am, the idea above was just the first one that popped into my head, will it work? or am I way off?

laserlight

No, since mysql_fetch_array() is used to place a row from a result set into an array, not execute an SQL statement.

Now, do you need to print the number of logins? If you do not, then your first SQL statement should just select the count, and the second SQL statement should either insert a new row, or increment the number of logins. For example:

$query = "SELECT COUNT(*) FROM players WHERE guid='$guid' AND ip='$ip'";
$result = mysql_query($query) or die(mysql_error());
if (mysql_result($result, 0) == 0)
{
    $query = "INSERT INTO players
        (guid, name, ip, first_seen, last_seen_date, last_seen_time, logins)
        VALUES ('$guid', '$name', '$ip', '$date $h:$m:$s', '$date', '$h:$m', '1')";
    mysql_query($query) or die(mysql_error());
}
else
{
    $query = "UPDATE players SET logins=logins+1 WHERE guid='$guid' AND ip='$ip'";
    mysql_query($query) or die(mysql_error());
}

Incidentally, you may want to consider switching from the legacy MySQL extension to the PDO extension or the MySQLi extension.

Fritz_fx

Ah now, I never knew you could do this:
UPDATE players SET logins=logins+1 WHERE guid='$guid' AND ip='$ip'
I think that might just do

Thanks for the hints on the extensions. Might just look into those... Google is my friend 😃