CODEWITHSUNDEEP
phpmysqlselect
Nick
Nick

Reputation: 169

PHP/MySQL make top 10 out of selection

A user can input it's preferences to find other users. Now based on that input, I'd like to get the top 10 best matches to the preferences.

What I thought is:

1) Create a select statement that resolves users preferences

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

2) Create a select statement that checks all users except for yourself

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

3) Match select statement 1 with select statement 2

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

4) Start with a variable with value 0, if preference matches -> variable + 10%

Problem is, I can do this for all members, but how can I then select the top 10??? I think I need to do this in the SQL statement, but I have no idea how...

Ofcourse this is one just one preference and a super simple version of my code, but you'll get the idea. There are like 15 preference settings.

// EDIT //

I would also like to see how much the match rating is on screen!

Upvotes: 2

Views: 230

Answers (4)

Trogvar
Trogvar

Reputation: 856

Well, it was a good question from the start so I upvoted it and then wasted about 1 hour to produce the following :)

Data

I have used a DB named test and table named t for our experiment here.

Below you can find a screenshot showing this table's structure (3 int columns, 1 char(1) column) and complete data

source table with data

As you can see, everything is rather simple - we have a 4 columns, with id serving as primary key, and a few records (rows).

What we want to achieve

We want to be able to select a limited set of rows from this table based upon some complex criteria, involving comparison of several column's values against needed parameters.

Solution

I've decided to create a function for 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 into selectedQty, selectedSex from t where id = recordId;

    if selectedQty = neededQty then
        set matchValue = matchValue + 10;
    end if;

    if selectedSex = neededSex then
        set matchValue = matchValue + 10;
    end if;

    return matchValue;
end//
delimiter ;

Minor explanation

Function calculates how well one particular record matches the specified set of parameters, returning an int value as a result. The bigger the value - the better the match.

Function accepts 3 parameters:

  • recordId - id of the record for which we need to calculate the result(match value)
  • neededQty - needed quantity. if the record's qty matches it, the result will be increased
  • neededSex - needed sex value, if the record's sex matches it, the result will be increased

Function selects via id specified record from the table, initializes the resulting match value with 0, then makes a comparison of each required columns against needed value. In case of successful comparison the return value is increased by 10.

Live test

live test of the function

So, hopefully this solves your problem. Feel free to use this for your own project, add needed parameters to function and compare them against needed columns in your table.

Cheers!

Upvotes: 1

San
San

Reputation: 624

you may try this

SELECT sex FROM ledenvoorkeuren WHERE userid = you limit 0, 10 order by YOUR_PREFERENCE

Upvotes: 0

Chris Laarman
Chris Laarman

Reputation: 1597

You can set a limit in your query like this:

SELECT sex FROM ledenvoorkeuren WHERE userid <> yourid AND sex <> yourpreferredsex limit 0, 10

Where the '0' is the offset, and the '10' your limit

More info here

Upvotes: 0

Code Lღver
Code Lღver

Reputation: 15593

Use the limit and offset in query:

SELECT sex FROM ledenvoorkeuren WHERE userid = you limit 10 offset 0

This will give the 10 users data of top most.

Upvotes: 0

Related Questions