2006-04-22 (토), 08:43 +0900, kmh496 쓰시길: > hi, > i am running a join query between the german_english table and the > user_todo_german_english tables, to figure out which words a user is > studying. > the key i am using is a combination of wordid + pos + posn (that is part > of speech == pos) > however, i am not able to correctly do the join because of improper > results. please see the following > > > mysql> select * from user_todo_german_english where date_col > > '2006-04-22 07:00:00'; > +---------+--------+------+------+---------------------+ > | mb_id | wordid | posn | pos | date_col | > +---------+--------+------+------+---------------------+ > | curious | 94122 | 1 | 0 | 2006-04-22 07:04:26 | > | curious | 327400 | 1 | 0 | 2006-04-22 07:04:40 | > | curious | 327402 | 1 | 0 | 2006-04-22 07:40:41 | > | curious | 59553 | 1 | 0 | 2006-04-22 07:40:55 | > | curious | 86851 | 1 | 0 | 2006-04-22 08:14:12 | > +---------+--------+------+------+---------------------+ > 5 rows in set (0.00 sec) > > mysql> select wordid,pos,posn from german_english where wordid in > (86851,59553); > +--------+------+------+ > | wordid | pos | posn | > +--------+------+------+ > | 59553 | m | 1 | > | 86851 | m | 1 | > +--------+------+------+ > 2 rows in set (0.00 sec) > > mysql> select * from user_todo_german_english where wordid = '86851' and > posn = '1' and pos = 'm' AND mb_id='curious'; > +---------+--------+------+------+---------------------+ > | mb_id | wordid | posn | pos | date_col | > +---------+--------+------+------+---------------------+ > | curious | 86851 | 1 | 0 | 2006-04-22 08:14:12 | > +---------+--------+------+------+---------------------+ > 1 row in set, 1 warning (0.00 sec) > > mysql> select * from user_todo_german_english where wordid = '86851' and > posn = '1' and pos = 'm' AND mb_id='curious'; > +---------+--------+------+------+---------------------+ > | mb_id | wordid | posn | pos | date_col | > +---------+--------+------+------+---------------------+ > | curious | 86851 | 1 | 0 | 2006-04-22 08:14:12 | > +---------+--------+------+------+---------------------+ > 1 row in set, 1 warning (0.00 sec) > > mysql> > > clearly, pos is ZERO in the database, and the letter 'm' in the query. > or is my brain farting again? > > > -- > my site <a href="http://www.myowndictionary.com";>myowndictionary</a> was > made to help students of many languages learn them faster. > further information
mysql 2035 0.4 1.6 22548 17088 ? Sl Apr09 78:46 /usr/local/mysql-5.1.7-beta/libexec/mysqld --defaults-file=/etc/my.cnf.dict_explicit --basedir=/usr/local/mysql-5.1.7-beta --datadir=/bup/mysqldata/data --socket=/tmp/mysql.sock --log-output=FILE the same results from the commandline using the old mysql client [EMAIL PROTECTED] current]$ mysql --version mysql Ver 14.7 Distrib 4.1.11, for redhat-linux-gnu (i686) and using php. the databse encoding is utf8; but i get the following from php after calling 'set names utf8' and from the clien without saying so. the table structure is mysql> show create table user_todo_german_english; +--------------------------+------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------------------+------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------+ | user_todo_german_english | CREATE TABLE `user_todo_german_english` ( `mb_id` varchar(25) default NULL, `wordid` int(11) default NULL, `posn` tinyint(3) default '1', `pos` tinyint(3) default '0', `date_col` datetime NOT NULL default '2006-03-23 22:50:02' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +--------------------------+------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> -- my site <a href="http://www.myowndictionary.com";>myowndictionary</a> was made to help students of many languages learn them faster. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
