7

I am using the WPDB object inside of Wordpress to communicate with a MySQL database. My database has a column with a type of bit(1), however, Wordpress does not extract these as a 0 or 1 on my production server (they did on my local machine).

Question:

If I have a database value from Wordpress, I can't do a simple comparison to 0 or 1:

if ($data[0]->Sold == 1) { //Always false
...
if ($data[0]->Sold == 0) { //Always false

How can I check if the value is 0 of 1?

Background:

This was not an issue on my local machine, but only in production.

I query the database like this:

$data = $wpdb->get_results("...");

When I do a var_dump() on the results from the database, here is the output my browser shows:

array(1) {
  [0] => object(stdClass)#261 (10) {
    ["SaleID"]     => string(4) "1561"
    ["BookID"]     => string(2) "45"
    ["MerchantID"] => string(1) "1"
    ["Upload"]     => string(19) "2012-11-20 15:46:15"
    ["Sold"]       => string(1) ""
    ["Price"]      => string(1) "5"
    ["Condition"]  => string(1) "5"
    ["Written"]    => string(1) ""
    ["Comments"]   => string(179) "<p>I am the first owner of this barely used book. There aren't any signs of normal wear and tear, not even any creases on the cover or any of its pages. It would pass for new.</p>"
    ["Expiring"]   => string(19) "2013-05-20 15:46:15"
  }
}

Notice how Sold and Written show a string size of 1, but don't have an associated value. These values should be populated with 1 and 0, respectively.

The Chrome inspector tool shows something quite interesting for these values:

enter image description here

enter image description here

What is \u1 or \u0 and why aren't they simply 1 or 0, so I can do comparisons?

Thank you for your time.

Oliver Spryn
  • 16,871
  • 33
  • 101
  • 195
  • It sounds like the values are not set in the mySQL database. Try updating the rows in mySQL. – fredrik Sep 02 '13 at 08:11
  • @fredrik I had mentioned: "These values should be populated with `1` and `0`, respectively." – Oliver Spryn Sep 02 '13 at 08:13
  • Well `\u1` and `\u0` is content, unfortunately the unicode representation of control characters. Maybe you could try to cast them to an integer. – feeela Sep 02 '13 at 08:14
  • Yet you say "should", not are. That implies a possibility that they are incorrectly populated. Also providing the table definition might help. – fredrik Sep 02 '13 at 08:15
  • @feeela I will try that. Do you know why on my development server this was not an issue? – Oliver Spryn Sep 02 '13 at 08:16
  • @fredrik Sorry, you're right about that. They do have the values of 1 and 0. ;) – Oliver Spryn Sep 02 '13 at 08:17
  • It could have to do with different DB setups (i.e. MySQL Version). You could also try to [cast the value to int using MySQL](http://stackoverflow.com/questions/6918897/how-can-i-cast-an-int-to-a-bit-in-mysql-5-1). – feeela Sep 02 '13 at 08:17
  • 1
    Then I'm guessing that you have programmatially added a 1 or 0 char, not a string containing 1 or 0. which would explain the `\u0` and `\u1`. meaning: you have put a `0` or `1` in the row, not a `"0"` or `"1"`. big difference. – fredrik Sep 02 '13 at 08:18
  • 1
    @fredrik That really could be the reason. [at]spryno724 See also the MySQL docs: [»To specify bit values, b'value' notation can be used. value is a binary value written using zeros and ones. For example, b'111' and b'10000000' represent 7 and 128, respectively.«](https://dev.mysql.com/doc/refman/5.0/en/bit-type.html) – feeela Sep 02 '13 at 08:20
  • @feeela @fredrik Thank you both. You could have been correct with me putting the values in as chars. I simply converted the type `bit(1)` to `tinyint(1)` and all has worked fine now. – Oliver Spryn Sep 02 '13 at 08:32

2 Answers2

1

Check this answer out: https://stackoverflow.com/a/5323169/794897

"When you select data from a MySQL database using PHP the datatype will always be converted to a string."

You can either do:

if ($data[0]->Sold === "1") { 
...
if ($data[0]->Sold === "0") {

or type cast the variable, e.g.

$Sold = (int) $data[0]->Sold;

if ($Sold === 1) { 
...
if ($Sold === 0) {
Community
  • 1
  • 1
Elliot Lings
  • 1,096
  • 1
  • 9
  • 16
0

For me the solution was to use ord function: http://us1.php.net/manual/en/function.ord.php

Edit

Yet the behavior seems to differ depending on the server. On Arch Linux with MariaDB 10.0.14 and Ubuntu with MySQL 5.5.37-0ubuntu0.13.10.1 wpdb returns good old "0" or "1" strings, not the problematic bit-strings, which happen on CentOS 6.4 MySQL 5.1.73

Veelkoov
  • 1,366
  • 14
  • 26