I have made this code for giving out +1 point, but it doesn't work properly.
mysql_query("
UPDATE member_profile
SET points= ' ".$points." ' + 1
WHERE user_id = '".$userid."'
");
the $points variable is the user´s points right now.. I want it to plus one to it.. so example if he had like 5 points, it should be 5+1 = 6.. but it doesnt, it just changes to 1
What have i done wrong? thank you
This can be done very simply, just execute a query like this
$sql = "UPDATE tablename SET col1=col1+1 WHERE key=99";
Or any value you like
$sql = "UPDATE tablename SET col1=col1+3 WHERE key=99";
there's no need to create another table, and max() will have problems acording to the auto_increment value of the table, do this:
CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
DECLARE next_id;
SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
SET NEW.field = next_id;
END
I declare the next_id variable because usually it will be used in some other way(*), but you could do straight new.field=(select ...)
CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
SET NEW.field=(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
END
Also in cases of (SELECT string field) you can use CAST value;
CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
SET NEW.field=CAST((SELECT aStringField FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl') AS UNSIGNED);
END
(*) To auto-name an image:
SET NEW.field = CONCAT('image_', next_id, '.gif');
(*) To create a hash:
SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );
Add your multiple columns with comma separations:
UPDATE settings SET postsPerPage = $postsPerPage, style= $style WHERE id = '1'
However, you're not sanitizing your inputs?? This would mean any random hacker could destroy your database. See this question: What's the best method for sanitizing user input with PHP?
Also, is style a number or a string? I'm assuming a string, so it would need to be quoted.
just use INSERT...ON DUPLICATE KEY UPDATE
INSERT INTO reports_adv (day, uid, siteid, cid, visits)
VALUES ('$day', '$uid', '$sid', '$cid', 1)
ON DUPLICATE KEY UPDATE visits=visits+1;
but before anything else, you should define a UNIQUE constraint on the columns.
ALTER TABLE reports_adv ADD CONSTRAINT tb_uq UNIQUE (day, uid, siteid, cid)
You could also just do this: