使用 MySQL 和 PHP 的最佳匹配 [英] Best match using MySQL and PHP

问题描述

我正在使用 PHP/MySQL 处理我的第一个项目，其中我有一个城市列表，并在某些类别(食品、购物等)中提供 1-5 的评级.我想要做的是在提交表单时评估每一行(每个城市)，以确定类别是否重要.

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.

比如说:
1. 芝加哥美食:4，购物:4，夜生活:4
2. 纽约美食:4，购物:5，夜生活:5
3. 波士顿美食:5，购物:4，夜生活:3
(评分只是举例)

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)

用户说食物不重要.因此，代码只会评估购物和夜生活......纽约以 10 结束，芝加哥以 8 结束，波士顿以 7 结束.

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.

因为我有一个大约 35-40 个城市的列表，我想对每个类别进行动态评估(如果用户认为它重要")，获胜者将是评估结束时的最高数字.

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.

有没有人对如何解决这个问题有任何想法?我已经在 MySQL 中建立了包含所有评级的表，现在只需要写出代码即可.

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!

推荐答案

假设数据库表与此类似(至少，它们应该以这种方式标准化):

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'
);

...下面的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';

(我假设使用 PDO，利用 PDO::quote() 用于在此处转义，但将回调 array( $db, 'quote' ) 替换为您的 mysql 库提供的任何引用/转义机制)

(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'
  )
)

如果您只需要第一个结果，请将 LIMIT 1 附加到 sql 查询中.

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.

最重要的是:让 MySQL 完成所有工作(过滤、排序)——而不是 PHP.

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

这篇关于使用 MySQL 和 PHP 的最佳匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！