Query "Not Running"

PHP
1

Got a strange problem here:

    public function remove_used_scans($scans_used) {
        dump($scans_used);
        try {
            $sql="
                UPDATE
                    ue_game_continent_unit
                SET
                    `number` = `number` - :number
                WHERE
                    (unit_id = :unit_id)
                AND
                    (con_id = :con_id)
            ";
            dump($scans_used['number']);
            $stmt = $this->db->prepare($sql);
            $stmt->bindParam(':number', $scans_used['number'],PDO::PARAM_INT);
            $stmt->bindParam(':unit_id', $scans_used['unit_id'],PDO::PARAM_INT);
            $stmt->bindParam(':con_id', $scans_used['continent'],PDO::PARAM_INT);
            db_dump($stmt);
            $run = $stmt->execute();
            dump($run);
            return true;
        }
        catch (PDOException $e) {
            //$this->db->rollBack();
            error_log('Error reading the game configuration.');
            error_log(' Query with error: '.$sql);
            error_log(' Reason given:'.$e->getMessage()."\n");
            return false;
        }                            
    }

The various dumps give:

array (size=3)
‘con_id’ => int 1
‘unit_id’ => int 23
‘number’ => int 1
SQL: [163]
UPDATE
ue_game_continent_unit
SET
number = number - :number
WHERE
(unit_id = :unit_id)
AND
(con_id = :con_id)

Params: 3
Key: Name: [7] :number
paramno=0
name=[7] “:number”
is_param=1
param_type=1
Key: Name: [8] :unit_id
paramno=1
name=[8] “:unit_id”
is_param=1
param_type=1
Key: Name: [7] :con_id
paramno=2
name=[7] “:con_id”
is_param=1
param_type=1

boolean true

The query “runs” but isn’t changing the value, I don’t know if this is another case of where PHP7 is stricter with stuff. Is it something obvious that’s staring me in the face?

2

What does rowCount() give you?
And did you try to run the query in PHPMyAdmin? Does it work there?

3

Your initial dump($scans_used) gives

array (size=3)
 'con_id' => int 1
 'unit_id' => int 23
 'number' => int 1

but you’re using

 $stmt->bindParam(':con_id', $scans_used['continent'],PDO::PARAM_INT);

rather than $scans_used[‘con_id’]. Is that the issue?

2 Likes
4

I’ve created a library designed to help view a parameterized query with the bound arguments replacing the placeholders. It can give you a representation of the query that is executed against the database. If you’re trying to debug queries, it can be a big help:

You’ll have to configure your PDO object to use the EPDOStatement as it’s statement class. Then, you can view the query by calling

echo $stmt->interpolateQuery();

after binding the parameters. Maybe that can help you identify the cause of your issue

5

Well spotted! slaps self for missing it

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.