Geek Answers Handbook

MySQL PDO INSERT ... SELECT IF statement not working

EDIT: fuzzy match of variable names. Simple mistakes made even after many years of coding.

   $query = "
    INSERT INTO table1 (team_id, user_id, team_leader, start_date)
    SELECT b.team_id,
           a.user_id,
           IF (a.agent_id = :leader, 1, 0),
           :startdate
    FROM table2 a
    LEFT JOIN table3 b ON a.ccb_team_id = b.ccb_team_id
    WHERE b.team_active = 1
      AND a.user_active = 1
      AND b.metro_id = :metroid 
      AND a.ccb_team_id = :teamid
    ON DUPLICATE KEY
       UPDATE team_leader = VALUES(team_leader)";

Data:

$query_data = array(
            ':metroid' => $metro_id,
            ':leader' => $a->ccb_leader_id,
            ':startdate' => $datetime,
            ':teamid' => $a->ccb_team_id
        );

If I try the above query in phpMyAdmin with the actual data where the PDO variables are located, it works fine if the team_leader command is correctly set to 1 for the corresponding users. Running this in the PHP CLI does not work, all team_leader fields return to 0.

I checked that it $a->ccb_team_leadercontains the correct information. PDO variables not working in IF statements?

Edit: So it seems that this is actually some kind of problem with the object when used in my PDO request. The $ a object from simplexml prints the value in order, but not when it is used in the request ....

+4

More articles:


All Articles