Weird issue with an SQL query

I've edited the question to be more specific and clear

I have the following co开发者_如何学Gode on a standalone PHP file:

$conn = mysql_connect("localhost", "", "");

mysql_select_db("name", $conn);

$result = mysql_fetch_array(mysql_query("SELECT EVENT_PRIVATE FROM events WHERE EVENT_ID = 68")); 

var_dump($result);

result is NULL

However, if I copy the exact same query to phpMyAdmin, you can see that EVENT_PRIVATE has a value 1 in it.

I have only one db, so I'm definitely using the same db, also, if I select a different column, I can see the correct value.

I don't get it, do you?

I've tried this on matching versions of PHP and MySQL, and it does seem to be something odd about the way the PHP "mysql" functions handle bit fields, or at least something I don't understand. And it certainly looks a bit like a bug. It works as I'd expect in the "mysqli" extension. Here's what I tried:

<?php
// mysql: 
$conn = mysql_connect('localhost', 'test', 'test');
mysql_select_db("test", $conn);
$result = mysql_fetch_array(mysql_query('SELECT EVENT_PRIVATE FROM test WHERE id = 2'));
var_dump($result);

/* Result: 
array(2) {
  [0]=>
  string(1) ""
  ["EVENT_PRIVATE"]=>
  string(1) ""
}
*/

// mysqli:
$mysqli = new mysqli('localhost', 'test', 'test', 'test');
$mysqli_result = $mysqli->query('SELECT EVENT_PRIVATE FROM test WHERE id = 2');
$result = $mysqli_result->fetch_array();
var_dump($result);

/* Result: 
array(2) {
  [0]=>
  string(1) "1"
  ["EVENT_PRIVATE"]=>
  string(1) "1"
}
*/

EDIT: Aha!

The book "High Performance SQL" says 'MySQL treats BIT as a string type, not a numeric type; when you retrieve a BIT(1) value the result is a string but the contents are the binary value 0 or 1, not the ASCII value "0" or "1"'.

I was expecting BIT to be like the SQL Server "bit" data type, which is a single bit designed for storing a boolean true/false (0/1) value. However, in MySQL it's much more a bitfield -- unlike SQL Server, you can have a bit(8) field, for example, which is eight bits long. And MySQL has a specific "boolean" type instead for storing booleans.

I'm guessing this is the root of what's going on. Notice that what's coming back for the mysql result is a string(1) -- that's a one-character long string, not an empty string, which would be string(0). If you look at the value of that single character:

echo ord($result['EVENT_PRIVATE']); 
1

...then it's an ASCII 1!

So, I'd say what's happening is probably, oddly, by design. The mysql extension is being very literal about interpreting the bitfield, and giving you a string encoding the actual bits that are stored in the field in MySQL, at the bit level. The mysqli extension is being a bit friendlier, perhaps, and converting the value into a string representation of the bits.

I'll leave it to you to experiment with what happens if you grab a bit(8) set to 10101010 using mysql and mysqli...

Try to narrow your query to a single event_id. Run it on PHPMyAdmin and then via PHP. You should see the same values.

Note that the assignment to $final is not relevant to the question, as it cannot be flawed. The problem is in $results.

replace your * with the field names to see if it complains about EVENT_PRIVATE. maybe it's not supposed to be caps or you are getting a result for the wrong join.

Please try this one:

$result = mysql_fetch_array(mysql_query("SELECT EVENT_PRIVATE FROM events WHERE EVENT_ID = '68'")); 

I just add single quotation around 68. Let me know if it work for you.

Edit:

Just for more information about single quote and double quote in sql query you can check THIS link