Error while trying to check

Question

i wanna do check for few things.... but i'm not able to...(check below).

Okey so this works how it is but i wanna add more one check.

mysql_query('UPDATE characters SET voted=1 where account_name like \''.$row['login'].'\' and online=1;') ;

Anyway what i wanna add is after online=1 to check where MIN(lastAccess)

I tried few things but i failed... like:

mysql_query('UPDATE characters SET voted=1 where account_name like \''.$row['login'].'\' and online=1 having min(lastaccess);') ;

What are you trying to do with with `min(lastaccess)`? Do you want to compare it to something in the row? — Barmar, Apr 03 '13 at 05:22
this check the first logged from this IP and set voted to him — Unique Power, Apr 03 '13 at 05:22

score 0 · Answer 1 · edited May 23 '17 at 12:03

0

You can even further join the tables to work as desired,

UPDATE  characters a
        INNER JOIN
        (
            SELECT  account_name, MIN(lastaccess) min_date
            FROM    characters
            GROUP   BY account_name
        ) b ON  a.Account_Name = b.Account_name AND
                a.lastAccess = b.min_date
SET     a.voted = 1
WHERE   a.Account_Name = 'nameHere' AND
        a.online = 1

UPDATE 1

Try using double quotes, eg

$userName = $row['login'];
$result = mysql_query(" UPDATE  characters a
                                INNER JOIN
                                (
                                    SELECT  account_name, MIN(lastaccess) min_date
                                    FROM    characters
                                    GROUP   BY account_name
                                ) b ON  a.Account_Name = b.Account_name AND
                                        a.lastAccess = b.min_date
                        SET     a.voted = 1
                        WHERE   a.Account_Name = '$userName' AND
                                a.online = 1");

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 12:03

Community

1
1

answered Apr 03 '13 at 05:22

John Woo

258,903
69
498
492

@J W Sorry this is too hard for me... is there simple way? – Unique Power Apr 03 '13 at 05:25
can you explain what are you trying to do? I'm a little confused on your statement *"...check the first logged from this IP and set voted to him."* – John Woo Apr 03 '13 at 05:28
This is vote reward system by ip, when player click on banner it does check first logged character and set voted to him, after that he can get reward. The player logged first have lower lastaccess number... Anyways 1364959212953 this is lastaccess code and i don't think its b.min_date – Unique Power Apr 03 '13 at 05:31
i think the statement above will work for you or if not, can give sample records with your desired result like [this question](http://stackoverflow.com/questions/15656273/mysql-pivot-table-column-data-as-rows/15656384). – John Woo Apr 03 '13 at 05:33
I checked and it doesn't show error but doesn't work too... UPDATE characters a INNER JOIN ( SELECT account_name, MIN(lastaccess) min_date FROM characters GROUP BY account_name ) b ON a.Account_Name = b.Account_name AND a.lastAccess = b.min_date SET a.voted = 1 WHERE b.account_name = \''.$row['login'].'\' AND a.online = 1 This is how i edit it... – Unique Power Apr 03 '13 at 05:34
I'm sorry but i don't get it... can you continue help please :D – Unique Power Apr 03 '13 at 05:44
please add sample data with desired result. – John Woo Apr 03 '13 at 05:45
I'm sorry i'm totaly newbie anyway, lets start from first error i get `Parse error: syntax error, unexpected 'nameHere' (T_STRING) in D:\xampp\htdocs\vote\vote.php on line 64`/code`` `Column 'account_Name' in where clause is ambiguousColumn 'account_Name' in where clause is ambiguousColumn 'account_Name' in where clause is ambiguousColumn 'account_Name' in where clause is ambiguousColumn 'account_Name' in where clause is ambiguousColumn 'account_Name' in where clause is ambiguousColumn 'account_Name' in where clause is ambiguousColumn 'account_Name' in where clause is ambiguous` – Unique Power Apr 03 '13 at 05:51
is it `UPDATE characters a INNER JOIN ( SELECT account_name, MIN(lastaccess) min_date FROM characters GROUP BY account_name ) b ON a.Account_Name = b.Account_name AND a.lastAccess = b.min_date SET a.voted = 1 WHERE account_name like \''.$row['login'].'\' AND a.online = 1` this? – Unique Power Apr 03 '13 at 06:07
it should be `a.Account_Name = 'nameHere'` the error say ambiguous column. – John Woo Apr 03 '13 at 06:09
Okey this solved but it still doesn't check lastaccess, can i ask if its possible to change min_date to value or number or smth? Becouse 1364959212953 this is not date or minutes. – Unique Power Apr 03 '13 at 06:12
i think i totaly broke it :D `mysql_query('UPDATE characters a INNER JOIN ( SELECT account_name, MIN(lastaccess) min_time FROM characters GROUP BY account_name ) b ON a.account_name = \''.$row['login'].'\' AND a.lastAccess = b.min_time SET a.voted = 1 WHERE a.account_name = b.min_time AND a.online = 1') ;` – Unique Power Apr 03 '13 at 06:19
anyway forget it.. i gave up.. its just insane... its so simple and i can't write it... LOL :D – Unique Power Apr 03 '13 at 06:32
`mysql_query('characters SET voted=1 where account_name like \''.$row['login'].'\' and online=1 HAVING MIN(lastaccess);') ;` When i try use this i get error `MySQL server version for the right syntax to use near` any idia is it possible to be fixed somehow? – Unique Power Apr 03 '13 at 06:36
you have no `UPDATE` keyword. – John Woo Apr 03 '13 at 06:37
Okey `mysql_query('update characters SET voted=1 where account_name like \''.$row['login'].'\' and online=1 having MIN(lastaccess);') ;` but nothing changed. `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'having MIN(lastaccess)'` – Unique Power Apr 03 '13 at 06:40
i already told you `having MIN(lastaccess)` is not the proper way of using it. this is my last comment to ask, can you give sample records with your desired result so we can play with it? [just like this question (click here)](http://stackoverflow.com/questions/15656273/mysql-pivot-table-column-data-as-rows/15656384) – John Woo Apr 03 '13 at 06:42
Okey moved to http://stackoverflow.com/questions/15798035/how-to-correct-this-mysql-querty-use-the-right-syntax-and-make-it-work – Unique Power Apr 03 '13 at 21:03

Error while trying to check

1 Answers1

Linked