从计数匹配不同的请求表格获得两个表mysql php的结果

Question

sorry for my bad english

i tired have problem, i want to matched field in 2 table

table 1

CREATE TABLE `may` (
  `idKEYWORD` int(11) NOT NULL,
  `faku` varchar(45) NOT NULL,
`ipk` varchar(45) NOT NULL,
`per` varchar(45) NOT NULL,
`has` varchar(45) NOT NULL,
`sem` varchar(45) NOT NULL,
`jur` varchar(45) NOT NULL
);

table 2
CREATE TABLE `matched_may` (
`idmayKEYWORD` int(11) NOT NULL,
  `namemay` varchar(50) NOT NULL,
  `req_faku` varchar(50) NOT NULL,
  `req_jur` varchar(50) NOT NULL,
  `req_ipk` varchar(50) NOT NULL,
  `req_perkap` varchar(50) NOT NULL,
  `req_has` varchar(50) NOT NULL,
  `req_sem` varchar(50) NOT NULL

);

in php like this but i need ini mysql because i have so much record i want to use order by total match and use limit

if($red->faku == $res->req_faku OR $res->req_faku == '') $tot[] = 1;
if($red->jur == $res->req_jur OR $res->req_jur == '') $tot[] = 1;
if($red->ipk >= $res->req_ipk OR $res->req_ipk == '') $tot[] = 1;
if($red->per >= $res->req_perkap OR $res->req_perkap == 0) $tot[] = 1;
if($red->has <= $res->req_has OR $res->req_has == 0) $tot[] = 1;
if($red->sem == $res->req_sem OR $res->req_sem == 0) $tot[] = 1;

i need to get 4 or more matched from 6 request

sorry i want to get result like

namemay | matched
name1   | 4
name2   | 5
name3   | 4

Answer 1

If I'm guessing what you want to do, you need to join the tables. Then you can compare the columns from the two tables, and add up the number of matches.

SELECT namemay, 
    (req_faku = '' OR req_faku = faku) + (req_jur = '' OR req_jur = jur) 
    + (req_ipk = '' OR req_ipk <= ipk) + (req_perkap = 0 OR req_perkap = per)
    + (req_has = 0 OR req_has >= has) + (req_sem = 0 OR req_sem = sem) AS matched
FROM may
JOIN matched_may ON idKEYWORD = idmayKEYWORD
HAVING matched >= 4

Comparison expressions return 1 when they're true, 0 when false, so you can use addition to get the total columns that matches.