MySQL query outputs odd rows only

Question

I have the following PHP method that returns a JSON string from a MySQL query:

$sys_words_ref_join_query = mysql_query("
 SELECT user_words.*, sys_words.*
 FROM user_words, sys_words
 WHERE user_words.sys_words_ref = sys_words.sys_words_ref 
 & user_words.user_info_ref = '1'
 LIMIT 0, 7
");

$json_array = array();

while($words_obj = mysql_fetch_object($sys_words_ref_join_query)) {
 $json_array[] = $words_obj;
}

$result = json_encode($json_array);

echo $result;

The problem I'm having is that the $result is echoing only odd DB rows, eg. 1,3,5..., etc.

Any idea why? Thanks.

Answer 1

You should probably be using a Logical AND (AND) instead of a Bitwise AND (&) in the where clause:

WHERE user_words.sys_words_ref = sys_words.sys_words_ref 
 AND user_words.user_info_ref = '1'

Answer 2

Did you run the mysql_query function? I can't see there. Also try to echo this:

print '<pre>';
print_r ($result);

Answer 3

Rows in a relational database don't have an external ordering, hence have no row numbers. A RDBMS is free to use whatever internal ordering the developers wish. If you want an order, you must explicitly specify it with an ORDER BY clause.

Answer 4

This condition:

user_words.sys_words_ref = sys_words.sys_words_ref & user_words.user_info_ref = 1

implies bitwise AND between sys_words.sys_words_ref and user_words.user_info_ref, which is later compared to user_words.sys_words_ref

So you get only the rows with user_words.sys_words_ref matching the bitwise AND between these two fields which is probably only true for the odd rows (with the last bit set).