Best match using MySQL and PHP

Question

I'm tackling my first project using PHP/MySQL in which I have a list of cities and ratings from 1-5 in certain categories (Food, Shopping, etc.). What I'm wanting to do is evaluate each row (each City), when a form is submitted on whether the categories are important or not.

This is how I want it to work.

Say, for example:
1. Chicago Food: 4, Shopping: 4, Nightlife: 4
2. New York Food: 4, Shopping: 5, Nightlife: 5
3. Boston Food: 5, Shopping: 4, Nightlife: 3
(the ratings are just for example)

And the user says that Food isn't important. Therefore the code will only evaluate Shopping and Nightlife... New York ends with 10, Chicago with 8 and Boston with 7.

As I have a list of around 35-40 cities that I want to evaluate on each category (if the user deems it "important") dynamically, and the winner will be the highest number at the end of the evaluation.

Does anyone have any ideas on how to go about this? I have the table built in MySQL with all the ratings, just need to write the code out now.

What I've tried: bringing in all of the values using arrays, but I've found it difficult to loop through each of the rows... help!

Is this supposed to filter search results or _sort_ them or what? — Yes Barry, Nov 20 '12 at 07:39
I brought in all of the data using arrays. So I have Food, Shopping, Nightlife arrays. Got kinda stuck at that point in evaluating each of the arrays. — ncf, Nov 20 '12 at 07:40
@NathanFrancy So this script is supposed to _import_ data into a database and give each category a "score" based on some criteria? — Yes Barry, Nov 20 '12 at 07:41
I'm sorry @mmmshuddup if I'm not being clear enough. I'm basically performing a compatibility match on the cities I have in my database next to my ratings in each category. But, realizing that each user is going to have different needs, you can choose what is most important to you. This is supposed to be a "Where should I go on vacation?" helper. — ncf, Nov 20 '12 at 07:46
@mmmshuddup yes. Each city will have a score at the end of the script and will have a winner by which one has the highest score. — ncf, Nov 20 '12 at 07:57

score 1 · Answer 1 · answered Nov 20 '12 at 07:41

1

You can accomplish this task with just a little bit of PHP code and an appropiate SQL statement.
Here is a possible solution:

$important_cat = $_POST['categories']; //which is an array
$sql = "SELECT city, sum(".implode(' + ',$important_cat).") AS cat
        FROM tbl
        ORDER BY cat DESC";
//query sql

answered Nov 20 '12 at 07:41

Shimu

1,137
11
25

How are you sure that _this_ is what the OP wanted? I still don't fully understand what his needs are.. – Yes Barry Nov 20 '12 at 07:43
I don't know exactly what he wants, but I think he wants to get the cities ordered by the categories which are relevant for an user. With this SQL statement he will get them. I haven't made any suggestion what he can do with the result... . – Shimu Nov 20 '12 at 07:50
Basically you save your relevant categories in an array ($important_cat). And then you query the database... . The sum(...) in the SELECT part, summarizes all of the relevant categories and you get them in your result. (The field is called cat) – Shimu Nov 20 '12 at 07:53

Decent Dabbler · Answer 2 · 2012-11-20T08:59:31.757

Assuming database tables similar to this (at least, they should be normalized in this fashion):

city ( id, name );
category ( id, name );
rating ( city_id, category_id, rating );

... with an array of interests similar to this:

$interests = array(
    'Food',
    'Shopping'
);

... the following sql:

$sql = 'SELECT
          city.name as city,
          GROUP_CONCAT( category.name || ": " || rating.rating, ", " ) as ratings,
          SUM( rating.rating ) as totalRating
        FROM
          rating
        JOIN
          city
          ON city.id = rating.city_id
        JOIN
          category
          ON category.id = rating.category_id
        WHERE
          category.name IN( ' . implode( ',', array_map( array( $db, 'quote' ), $interests ) ) . ' )
        GROUP BY
          city.name
        ORDER BY
          totalRating DESC';

(I assumed the use of PDO, utilizing PDO::quote() for escaping here, but substitute the callback array( $db, 'quote' ) with whatever quoting/escape mechanism your mysql library offers)

... will yield a result set similar to this (I've populated random rating data for my example):

array (
  0 => array (
    'name' => 'Chicago',
    'ratings' => 'Food: 3, Shopping: 3',
    'totalRating' => '6'
  ),
  1 => array (
    'name' => 'New York',
    'ratings' => 'Food: 1, Shopping: 4',
    'totalRating' => '5'
  ),
  2 => array (
    'name' => 'Seattle',
    'ratings' => 'Food: 4, Shopping: 1',
    'totalRating' => '5'
  ),
  3 => array (
    'name' => 'Los Angeles',
    'ratings' => 'Food: 2, Shopping: 2',
    'totalRating' => '4'
  ),
  4 => array (
    'name' => 'Boston',
    'ratings' => 'Food: 1, Shopping: 2',
    'totalRating' => '3'
  ),
  5 => array (
    'name' => 'San Francisco',
    'ratings' => 'Food: 1, Shopping: 1',
    'totalRating' => '2'
  )
)

If you only need the first result, append LIMIT 1 to the sql query.

This should give you an idea of how to go about accomplishing what you want.

Above all: let MySQL do all the work (filtering, sorting) — not PHP.

Best match using MySQL and PHP

2 Answers2

Linked