Tinyint in MySQL Results Returning Wrong Values

Question

I have a table with about 50k records. Each record is associated with an activation code and a tinyint(1) that is either a 1 or a 0 depending on if it has been activated or not.

I wrote this script to search activation codes:

$count = 1;
foreach ($array as $value) {

    try{
        $stmt = $db->prepare("SELECT * FROM customers WHERE code = '$value'");
        $stmt->execute();
        $row = $stmt->fetchAll();

        foreach ($row as $row1) {
            echo "$count,{$row1['code']},{$row1['activated']} <br />";
        }

    }catch(PDOException $e){
        echo $e;
    }

    $count++;
}

It will print out results as such:

263,GCTDA598149901,1 
264,GCTDA363633527,1 
265,GCTDA474011458,1 
266,GCTDA610122649,1 
267,GCTDA973129612,1 
268,GCTDA472831092,1 
269,GCTDA567914117,1 
270,GCTDA763417638,1 
271,GCTDA833541425,1 
272,GCTDA556328307,1 
273,GCTDA441015640,1 
274,GCTDA266326284,1 
275,GCTDA495338154,1 
276,GCTDA320542455,1 
277,GCTDA429649757,1 
278,GCTDA468213166,1 
279,GCTDA264634579,1 
280,GCTDA842325439,1 
281,GCTDA331321327,1 
282,GCTDA280321014,1 
283,GCTDA904841155,1 
284,GCTDA728739105,1

All of the tinyint's are returned as a 1 whether is it a 1 or a 0 in the database. I am truly at a loss right now on what is causing it. I hope someone can help.

side note. since you are using PDO, (1) why are you injecting your variable directly in the query instead of as a parameter, and (2) why is your `->prepare()` in the loop as it would be better before, and just execute the parameter in the loop. — Sean, Jan 15 '15 at 20:27
I threw this together in a hurry just to try and solve my problem so I was not concerned with performance. I am getting the same results using `mysql()` as well. — Jeff Thomas, Jan 15 '15 at 20:32
Here is a sample from the array I am using: `$array = array('GCTDA274513880','GCTDA620441198','GCTDA122623890','GCTDA966345770','GCTDA713514145','GCTDA765930837');` — Jeff Thomas, Jan 15 '15 at 20:33
@JeffThomas not about performance it's about security also `mysqli` not `mysql`. — Shahar, Jan 15 '15 at 20:33
Security aside(this is just being ran on localhost to solve the problem), it should still not affect the `tinyint` results as the code is being returned properly. — Jeff Thomas, Jan 15 '15 at 20:35
There is nothing in this code that would cause all `$row1['activated']` to be 1. — Sean, Jan 15 '15 at 20:35
@Shahar you are welcome to prove me wrong. Given the code the OP has provided, there is nothing that would make `echo "$count,{$row1['code']},{$row1['activated']}
";` result in `{$row1['activated']}` being `1` for every line. So unless there is additional code the OP did not provide, I would question the db values before trying to blame the php code — Sean, Jan 15 '15 at 20:40
as a way to debug, modify your query to `SELECT * FROM customers WHERE code = '$value' AND activated != 1`. If nothing prints, you would know that those rows have `activated = 1` — Sean, Jan 15 '15 at 20:47
I have provided all of the code I am using with the exception of the PDO connection parameters. Once I receive permission from my client, I will post a dump of the database containing those 2 fields. — Jeff Thomas, Jan 15 '15 at 21:51
Here is an sql dump of those fields `https://www.dropbox.com/s/v3wp4bdmhmkeqph/customerstemp.sql?dl=0` — Jeff Thomas, Jan 15 '15 at 21:57
Also, here is the full array that I am checking it against: `http://pastebin.com/AHmMn8NK` — Jeff Thomas, Jan 15 '15 at 22:11

score 0 · Accepted Answer · answered Jan 15 '15 at 20:45

The most logical explanation for the behavior you observe...

the activated column contains a value of 1.

To see if you have anything other than a 1 stored, you could run a query like this:

SELECT c.activated, COUNT(*) AS cnt
  FROM customers
 GROUP BY c.activated

To find individual rows that have a NULL or a value other than 1

SELECT c.*
  FROM customers c
 WHERE NOT ( c.activated <=> 1 )
 LIMIT 100

If you want to test with your current code, add a predicate to the query

AND NOT (activated <=> 1)

I don't spot a problem in your code, I recommend checking the data.

Tinyint in MySQL Results Returning Wrong Values

1 Answers1