MySQL bind_param writes NULL to table

Question

Problem: Even though my $settings array has values (int) in them, MySQL writes NULL into the table both when the value is 0 and 2.

For reference, each index in the $settings array is an array of [0] = max and [1] = min.

public function updateAdaptiveArmour($gameid, $shipid, $settings){

    foreach ($settings as $key => $value){
        debug::log($key." ".$value[0]." ".$value[1]);

       //just to show the contents
       //   [561103190304f][2015-10-04 12:44:41] particle 4 2 
       //   [56110319035b3][2015-10-04 12:44:41] laser 0 0 
       //   [56110319035b3][2015-10-04 12:44:41] molecular 0 0 
    }   


    try {
        if ($stmt = $this->connection->prepare(
                "UPDATE 
                    tac_adaptivearmour
                 SET
                    particlealloc = ?,
                    laseralloc = ?,
                    molecularalloc = ?
                 WHERE 
                    gameid = ?
                    AND shipid = ?
                 "
        ))
        {
            $stmt->bind_param('iiiii', $settings[0][1], $settings[1][1],  $settings[2][1], $gameid, $shipid);
            $stmt->execute();
            $stmt->close();
        }
    }
    catch(Exception $e) {

        throw $e;
    }
}

Ideally, for this example, I would want to UPDATE to 2 / 0 / 0 instead of null / null / null.

`mysqli` doesn't throw exceptions unless you have used : [Turning query errors to Exceptions in MySQLi](http://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments). You have a syntax error of an extra comma before `where` — Ryan Vincent, Oct 04 '15 at 13:03
fixed the typo, a remainder of when i shortened the code to display it here (orginally, the array has more entries). So this is the not the cause. I have debugging enabled ans get to mysql error. And i said it is updating the table, but with NULL. — C. Finke, Oct 04 '15 at 13:26

score 1 · Accepted Answer · answered Oct 04 '15 at 13:31

1

Your array is populated as follows:

$settings = [
    'particle' => [4, 2],
    'laser' => [0, 0],
    'molecular' => [0, 0]
];

I hope that answers the question without needing to further explain it.

It will be fixed as follows:

$stmt->bind_param('iiiii',
    $settings['particle'][1],
    $settings['laser'][1],
    $settings['molecular'][1],
    $gameid,
    $shipid
);

answered Oct 04 '15 at 13:31

hjpotter92

78,589
36
144
183

So i cant use the index in the way i used it because $settings["particle"] does not equal $settings[0] ? – C. Finke Oct 04 '15 at 15:57
@C.Finke Array indices are probably being set by you? If so, you'll have to change the code there. – hjpotter92 Oct 04 '15 at 16:00
Yes they are set by me. However, answering my question, even though i did push into the array in that order, ["particle"] is not being targetable by [0] ? – C. Finke Oct 04 '15 at 16:15
@C.Finke No. You're creating a hash table. – hjpotter92 Oct 04 '15 at 16:17

MySQL bind_param writes NULL to table

1 Answers1