Basically, I want to ask a user if a record refers to them. In my first table I have the records which I want to use as reference. In the second table I put the users id and the id of the record which I am asking them about, once I have asked them.
My query basically says, pick a random record from the first table, IF the fields match some of the users information, AND there is no record in teh second table which has both the users id, and the id of the row in the first table which I am trying to match them with. Sounds ok huh. Once I have asked them if a record refers to them, I put a new record in the second table with the id of the record in the first table, and their user id. This means I won't ask them about the same record again.
The problem is, first of all, the query brings two results, second, the query does not bring back a null result when there is only one matching record in the first table, but also a record in the second table because they have already been asked about this one.
The query is:
SELECT missingfriend.id FROM missingfriend, missingask
WHERE (missingask.userid<>'7' AND missingfriend.id<>missingask.missingid)
AND missingfriend.firstname LIKE '%ka%'
AND ((missingfriend.lastname LIKE '%asa%') OR (missingfriend.lastname LIKE '%rk%'))
AND missingfriend.gender='2'
AND missingfriend.isfound='0'
AND missingfriend.dbstatus<'3'
GROUP BY missingfriend.id
ORDER BY RAND()
LIMIT 1
I stuck group by in there to try and solve the double result problem, but that is just trying to hide the problem rather than hide it so I guess it should be the first thing to go.
My table structures are:
# Server version 4.0.12-nt
#
# Table structure for table 'missingask'
#
CREATE TABLE `missingask` (
`id` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL default '0',
`missingid` int(10) unsigned NOT NULL default '0',
`recorddatetime` datetime NOT NULL default '0000-00-00 00:00:00',
`found` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;
#
# Table structure for table 'missingfriend'
#
CREATE TABLE `missingfriend` (
`id` int(10) unsigned NOT NULL auto_increment,
`userid` int(10) unsigned NOT NULL default '0',
`schoolid` int(10) unsigned NOT NULL default '0',
`returnuserid` int(10) unsigned NOT NULL default '0',
`firstname` varchar(30) NOT NULL default '',
`lastname` varchar(30) NOT NULL default '',
`age` int(2) default NULL,
`gender` char(1) default NULL,
`isfound` int(3) NOT NULL default '0',
`infotext` text,
`recorddatetime` datetime NOT NULL default '0000-00-00 00:00:00',
`updatedatetime` datetime NOT NULL default '0000-00-00 00:00:00',
`views` int(10) unsigned NOT NULL default '0',
`displays` int(10) unsigned NOT NULL default '0',
`remark` varchar(120) default NULL,
`dbstatus` int(2) NOT NULL default '0',
`photoid` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;
I have spent the last 4 hours boggling over this, it seems so simple in my mind, but translating that in to working logic seems impossible!!! Please, can someone suggest what I have done wrong, a solution would be magic too!
