php - 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);') ;

Answer 1

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?