I am using a simple script to count clicks of a link... problem is that when it is supposed to increment the value of a table in the DB... it, well, doesn't.

Here is the click.php:

    ...
        // get id and protect it
        $id = htmlspecialchars($_GET[id]);      
        // check db
        $get = mysql_fetch_assoc(mysql_query("SELECT * FROM `affiliates` WHERE `id` = '$id' LIMIT 1"));        
        // increment hits
        $insert = mysql_query("UPDATE `affiliates` SET `out` = 'out+1' WHERE `id` = '$id'");  

    break;
}
?>

It doesn't increment 'out' at all... and if i set the value of "out" to something other than 0 and call it, it defaults it back to 0.

What am i doing wrong?

Again, in sql, the result of your operation depends of the field datatype.

If your column out is a varchar, you cannot use the db to make out+1, because 'something'+1='something1' (or an exception)

You can do that if your column is a numeric type, and in that case, you have to drop the single quotes, which denotes a varchar

UPDATE affiliates SET out = out+1
--and not
UPDATE affiliates SET out = 'out+1'

Now, if your field is in a text datatype, the best would be to convert it to integer.

It is an int type... and with or without the single quotes I'm still having no luck. Running a query without the single quotes gives me a syntax error... i tried it many times before as that was how all of the examples i found were on other sites. With the quotes it will (if the column is not 0) increase it by 1... if i run it again, it goes back to default (0).

Maybe I'm missing something though. The database was set up like:

CREATE TABLE `affiliates` (
    `id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL default '',
    `banner` text NOT NULL,
    `url` text NOT NULL,
    `email` varchar(255) NOT NULL default '',
    `in` int(11) NOT NULL default '0',
    `out` int(11) NOT NULL default '0',
    `active` int(1) NOT NULL default '0',
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM;

I'm definitely somewhat new to most of this so i am a little lost here.

Well i got it working by using:

$insert = mysql_query("UPDATE affiliates SET `out` = `out`+'1' WHERE id = '$id'");

(the single quotes on 'out' before + '1')

I dont know for what, but could it be that 'out' is a reserved SQL key word? That would explain why you had to escape it with the `` quote things (what ever they're called ). Otherwise Tripy's code snippet should work fine.

No, out is not a reserved word.

I forgot to talk about it, but if the previous value of the column was null, then it's perfectly clear that out+1 stays null.
Null can be interpreted as "unknown", and "unknown"+1 is still unknown...
But as the definition says "not null", I must say that I'm puzzled...

Originally Posted by alexjames01

It is an int type... and with or without the single quotes I'm still having no luck. Running a query without the single quotes gives me a syntax error... i tried it many times before as that was how all of the examples i found were on other sites. With the quotes it will (if the column is not 0) increase it by 1... if i run it again, it goes back to default (0).

tripy was right. You have to use UPDATE `affiliates` SET `out` = out+1. It's works, I've checked.

Please, provide us vervion of your MySQL and error messages that you've received.