PHP/MySQL make top 10 out of selection -

a user can input it's preferences find other users. based on input, i'd top 10 best matches preferences.

what thought is:

1) create select statement resolves users preferences

if ($stmt = $mysqli->prepare("select sex ledenvoorkeuren userid = you")) $stmt->bind_result($ownsex); 

2) create select statement checks users except yourself

if ($stmt = $mysqli->prepare("select sex ledenvoorkeuren userid <> you")) $stmt->bind_result($othersex); 

3) match select statement 1 select statement 2

while ($stmt->fetch()) {                 $match = 0;                 if ($ownsex == $othersex) {                     $match = $match + 10;                 } // check next preference 

4) start variable value 0, if preference matches -> variable + 10%

problem is, can members, how can select top 10??? think need in sql statement, have no idea how...

ofcourse 1 1 preference , super simple version of code, you'll idea. there 15 preference settings.

// edit //

i see how match rating on screen!

well, question start upvoted , wasted 1 hour produce following :)

data

i have used db named test , table named t our experiment here.

below can find screenshot showing table's structure (3 int columns, 1 char(1) column) , complete data

as can see, rather simple - have 4 columns, id serving primary key, , few records (rows).

what want achieve

we want able select limited set of rows table based upon complex criteria, involving comparison of several column's values against needed parameters.

solution

i've decided create function this. sql statement follows:

use test;  drop function if exists calcmatch;  delimiter //  create function calcmatch (recordid int, neededqty int, neededsex char(1)) returns int begin     declare selectedqty int;     declare selectedsex char(1);      declare matchvalue int;     set matchvalue = 0;      select qty, sex selectedqty, selectedsex t id = recordid;      if selectedqty = neededqty         set matchvalue = matchvalue + 10;     end if;      if selectedsex = neededsex         set matchvalue = matchvalue + 10;     end if;      return matchvalue; end// delimiter ; 

minor explanation

function calculates how 1 particular record matches specified set of parameters, returning int value result. bigger value - better match.

function accepts 3 parameters:

• recordid - id of record need calculate result(match value)
• neededqty - needed quantity. if record's qty matches it, result increased
• neededsex - needed sex value, if record's sex matches it, result increased

function selects via id specified record table, initializes resulting match value 0, makes comparison of each required columns against needed value. in case of successful comparison return value increased 10.

live test

so, solves problem. feel free use own project, add needed parameters function , compare them against needed columns in table.

cheers!