CODEWITHSUNDEEP
phpmysql
ncf
ncf

Reputation: 556

Best match using MySQL and PHP

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!

Upvotes: 2

Views: 405

Answers (2)

Decent Dabbler
Decent Dabbler

Reputation: 22783

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.

Upvotes: 1

Shimu
Shimu

Reputation: 1147

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

Upvotes: 1

Related Questions