MySQL query - joining two tables with duplicate results

Question

I am executing the following MySQL query in PHP.

"SELECT * FROM `challenges`,`verifications` WHERE (`challenges`.`user_id`='".$this->record['id']."' OR `challenges`.`opponent_id`='".$this->record['id']."') AND `challenges`.`is_verified`='0' AND (`challenges`.`status`='in-progress' OR `challenges`.`status`='pending') AND ( (`verifications`.`user_id`='".$this->record['id']."' OR `verifications`.`opponent_id`='".$this->record['id']."') AND (`verifications`.`user_verified`!=NULL AND `verifications`.`opponent_verified`=NULL) ) LIMIT 100"; 

For some reason, this query returns duplicate records. If anyone has any ideas, I would really appreciate it.

Here is the structure for two tables (calls and checks):

Table of problems:

 CREATE TABLE `challenges` ( `id` int(11) NOT NULL auto_increment, `wager` int(11) NOT NULL, `type` varchar(255) NOT NULL, `user_id` int(11) NOT NULL, `opponent_id` int(11) NOT NULL, `start_date` date NOT NULL, `date_created` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `start_time` time NOT NULL, `is_verified` tinyint(1) NOT NULL default '0', `status` varchar(255) NOT NULL default 'pending', `winner_id` int(11) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ; 

Check table:

 CREATE TABLE `verify` ( `id` int(11) NOT NULL auto_increment, `user_id` int(11) NOT NULL, `opponent_id` int(11) NOT NULL, `challenge_id` int(11) NOT NULL, `user_verified` int(11) default NULL, `opponent_verified` int(11) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `challenge_id` (`challenge_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=0; 

Thanks for your help, and if you need more information, please let me know.

Answer 1

You must add the condition:

 challenges.id = verify.challenge_id 

where is the item below

 "SELECT * FROM `challenges`,`verifications` WHERE `challenges`.`id` = `verify`.`challenge_id` AND (`challenges`.`user_id`='".$this->record['id']."' OR `challenges`.`opponent_id`='".$this->record['id']."') AND `challenges`.`is_verified`='0' AND (`challenges`.`status`='in-progress' OR `challenges`.`status`='pending') AND ( (`verifications`.`user_id`='".$this->record['id']."' OR `verifications`.`opponent_id`='".$this->record['id']."') AND (`verifications`.`user_verified`!=NULL AND `verifications`.`opponent_verified`=NULL) ) LIMIT 100"; 

or using ANSI-92

 "SELECT * FROM `challenges` as `challenges` JOIN `verifications` as `verifications` on `challenges`.`id` = `verify`.`challenge_id` WHERE (`challenges`.`user_id`='".$this->record['id']."' OR `challenges`.`opponent_id`='".$this->record['id']."') AND `challenges`.`is_verified`='0' AND (`challenges`.`status`='in-progress' OR `challenges`.`status`='pending') AND ( (`verifications`.`user_id`='".$this->record['id']."' OR `verifications`.`opponent_id`='".$this->record['id']."') AND (`verifications`.`user_verified`!=NULL AND `verifications`.`opponent_verified`=NULL) ) LIMIT 100";