problem with php-mysql for rating sytem

[pascal_22]

Hello to all!

 

I have a question about how to do something with php and mysql.

 

On my site, user can upload photos and choose if they want to let others users rate this photos (from 1 to 10).

 

So actually, on my website, i have a section to shows members with best rating... .but the problem that users told me... is if a members has 3 pics with 3 different rate (let say 9, 8 and 7) this members will be show 3 time.... once at the position 9, another time to the position 8 and a last time at the position 7....

 

So the list can be very long.....

 

My question is:

 

Now i try to make is simple and better.. so i will show photo with best rate as i already did, but the difference is a same members will only appears once... so if a members has 3 pics... i will show the pics with the best rate and i want to add at the bottom of this pic a list of all other photo of this member that has been rated...

So, if i take the same exemple, i will show the photo that rated 9 and below, in small thumbnail the 2 other pics that rated 8 and 7..

after i will continue to with the next result... maybe 8.9 ... or other..

 

I thought to every row, to query to get other photo of the actual user.... but it will query a lot of time the server.. so i dont think it's the best way....

 

Can you please give me a hint!

 

 

Note: i have 1 table  'Photos' and every photo have his own row(id,path, rate.....)

 

thanks for you help

 

Pascal

[pascal_22]

Any hint?

 

thanks !!

 

Pascal

[ginerjm]

200 views and no replies - usually means we don't understand what you are asking.  Your descript may make sense to you - you know what you are doing.  We don't and can't figure it out from your sketchy outline.

 

Want to try again?  I really don't understand it at all.

[davidannis]

I think that I understand the question. Is this it?

 

You have a table of photos like this

ID , rating, user, ... other fields

 

You might have data like this

 

ID      Rating   User

1          6          bob

2          9          bob

3         10         dave

4           3         bob

5          7         susan

6         8          dave

7        2          kevin

8         1         kevin

 

And you want to display:

 

dave: image 10 (links to image 6)

bob: Image 2 (links to images 1 and 4)

susan: image 5

kevin: image 7 (link to image

 

so you display each user's highest rated image, followed by links to all other images owned by that user.

[pascal_22]

Thanks for your replies!

 

What Davidannis explain in  exactly what i need!

 

Sorry if my question was hard to understand.

 

Actually on my site: i have a query that get photopath order by Rating desc so i display it and some user appears a lot of times...

 

How can i do it as Davidannis explain?

 

Thanks a lot!

 

Pascal

[Barand]

Telling us you table structure would help

[pascal_22]

Sorry for the response time... i didn't see the email....

for sure here is the table structure.. please note that some field has a french name.....

 

photosprofils

----------------

id

idmember

CheminPhoto (path)

SectionVote (if the photo can be rated or not)

DateEnvoi (sent date)

Note

TotalVote

NbVote

idprofil

Approuver (approuved)

 

Thanks a lot!

[Barand]

Your query would be something like this. The subquery finds the highest vote for each member. It then matches the records for each member to find the record matching this highest vote and uses the fields from that record

SELECT p.idmember, p.CheminPhoto, p.TotalVote
FROM photosprofils p
    INNER JOIN 
    (
    SELECT idmember, MAX(TotalVote) as maxVote
    FROM photosprofils
    GROUP BY idmember   
    ) as max
    ON p.idmember = max.idmember AND p.TotalVote = max.maxVote
GROUP BY p.idmember

Edited January 22, 2014 by Barand

[pascal_22]

Ok thanks, i'm trying it now! i'll come back!!

I really appreciate!!!! 

thanks a lot!!!

 

Pascal

Edited January 23, 2014 by pascal_22

[pascal_22]

Thanks Barand!

I tested it and the result was it select the highest vote for each user... but if i add a limit 20 and order by p.TotalVote desc, to show 20 results per page, and then i make an EXPLAIN.... it search in all rows of my database.... that can load my server....

 

Is that normal?

 

 

After i selected the highest Vote for this user... how can i get other pics of the same members...?

i know that i can query like this:

while($row = $highestVote->fetch_assoc())
{
  //display highest vote of this user...
  ...
  
  //check if this user has others pics
  $otherpics = $db->query(select * from photosprofils where idmember=".$row["idmember"]." and id!=".$row["id"])
  
  if($otherpics->num_rows>0)
  {
    //display in thumbnail otherpics of this user 
  }
}

but i'm pretty sure that it is not the BEST WAY to do this.... send a query to the database at each rows(20 rows per page)... that can load the server...... ??? no?

 

Because, i want to show the highest vote picture of a member(maybe 150px width and height)  + i want to show other pics of the same members(before showing the next highest vote of an other member) in small(maybe 75px width and height) after the highest vote(bigger picture)

 

i dont know if my explication is good?

 

is it ?

 

Thanks a lot!!

 

Pascal

[Barand]

my test data

 

 

mysql> SELECT * FROM photosprofils;
+----+----------+-------------+-----------+
| id | idmember | CheminPhoto | TotalVote |
+----+----------+-------------+-----------+
|  1 |        1 | Photo 1A    |         8 |
|  2 |        2 | Photo 2A    |         7 |
|  3 |        3 | Photo 3A    |         4 |
|  4 |        1 | Photo 1B*   |         9 |
|  5 |        2 | Photo 2B*   |         8 |
|  6 |        2 | Photo 2C    |         3 |
|  7 |        3 | Photo 3B    |         6 |
|  8 |        3 | Photo 3C*   |        10 |
|  9 |        4 | Photo 4A*   |         8 |
| 10 |        5 | Photo 5A*   |         7 |
| 11 |        4 | Photo 4B    |         6 |
+----+----------+-------------+-----------+

 

 

 

The code

$sql = "SELECT p.idmember, p.CheminPhoto, p.TotalVote, other.CheminPhoto as other
FROM photosprofils p
    INNER JOIN 
    (
    SELECT idmember
    , MAX(TotalVote) as maxVote
    FROM photosprofils
    GROUP BY idmember   
    ) as max
        ON p.idmember = max.idmember AND p.TotalVote = max.maxVote
    LEFT JOIN photosprofils other
        ON p.idmember = other.idmember AND p.id <> other.id
ORDER BY p.TotalVote DESC, p.idmember";

$res = $mysqli->query($sql);

$currMember = '';
while (list($mem, $mainphoto, $vote, $otherphoto) = $res->fetch_row()) {
    if ($currMember != $mem) {
        echo "<br>Member : $mem<br>Main Photo : $mainphoto<br>Other photos :<br>";
        $currMember = $mem;
    }
    echo " • $otherphoto<br>";
}

the results

Member : 3
Main Photo : Photo 3C*
Other photos :
• Photo 3B
• Photo 3A

Member : 1
Main Photo : Photo 1B*
Other photos :
• Photo 1A

Member : 2
Main Photo : Photo 2B*
Other photos :
• Photo 2A
• Photo 2C

Member : 4
Main Photo : Photo 4A*
Other photos :
• Photo 4B

Member : 5
Main Photo : Photo 5A*
Other photos :
• 

[jazzman1]

Hm...it's weird

 

I have a "Orders" table very similar to yours, Barry.

mysql> SELECT * FROM test.Orders;

+----------+---------+------------+----------+------------+
| order_ID | cust_ID | order_name | quantity | order_sold |
+----------+---------+------------+----------+------------+
|        1 |       1 | CD         |        1 | 2014-01-20 |
|        2 |       2 | Books      |        1 | 2014-01-21 |
|        3 |       2 | DVD        |        2 | 2014-01-19 |
|        4 |       1 | laptop     |        4 | 2014-01-21 |
|        5 |       3 | computer   |        2 | 2014-01-16 |
|        6 |       3 | CD         |        3 | 2014-01-01 |
|        7 |       2 | phone      |        3 | 2014-01-12 |
+----------+---------+------------+----------+------------+

When I run the following query I don't get the expected result.

SELECT 
    o.cust_ID, o.quantity, other.order_name as other
FROM
    test.Orders o
        INNER JOIN
    (SELECT 
        cust_ID, MAX(quantity) as max_q
    FROM
        test.Orders
    GROUP BY cust_ID) as o1 
ON (o1.cust_ID=o.cust_ID AND o.quantity=o1.max_q)
LEFT JOIN test.Orders other
        ON (o.cust_ID = other.cust_ID AND o.order_ID <> other.order_ID)
ORDER BY o.quantity DESC, o.cust_ID

Result:

+---------+----------+----------+
| cust_ID | quantity | other    |
+---------+----------+----------+
|       1 |        4 | CD       |
|       2 |        3 | Books    |
|       2 |        3 | DVD      |
|       3 |        3 | computer |
+---------+----------+----------+
4 rows in set (0.00 sec)

Does the column named "cust_ID", yours "idmember" the values inside should be in particular order? 

 

I did not play with yours.

[Barand]

 

 

When I run the following query I don't get the expected result.

 

To save me polishing my crystal ball, what results were you expecting?

[jazzman1]

Sorry for the delay master. I was tired yesterday night

My attempt was to find out a customer (who belongs to cust_ID) who ordered the most along with the total order quantity.

I was thinking I would accomplish this with the query wrriten by you, but I see today you put a different logic on it. 

So, I've done mine with next query: 

SELECT 
    o.cust_ID, o.order_name, o1.Total
FROM
    test.Orders o
        INNER JOIN
    (SELECT 
        cust_ID, order_name, sum(quantity) as Total
    FROM
        test.Orders
    GROUP BY cust_ID) as o1 USING (cust_ID)
WHERE
    o1.Total = (SELECT 
            MAX(Total)
        FROM
            (SELECT 
                cust_ID, sum(quantity) as Total
            FROM
                test.Orders
            GROUP BY cust_ID) as o2)

Result:

+---------+------------+-------+
| cust_ID | order_name | Total |
+---------+------------+-------+
|       2 | Books      |     6 |
|       2 | DVD        |     6 |
|       2 | phone      |     6 |
+---------+------------+-------+

The result is correct.

[Barand]

A couple of alternatives

SELECT cust_ID
    , SUM(quantity) as totQty
    , GROUP_CONCAT(order_name,' (', quantity,')' 
        ORDER BY quantity DESC 
        SEPARATOR ', ') as items
FROM orders
GROUP BY cust_ID
ORDER BY totQty DESC;

+---------+--------+-------------------------------+
| cust_ID | totQty | items                         |
+---------+--------+-------------------------------+
|       2 |      6 | phone (3), DVD (2), Books (1) |
|       1 |      5 | laptop (4), CD (1)            |
|       3 |      5 | CD (3), computer (2)          |
+---------+--------+-------------------------------+

or

SELECT o.cust_ID, o2.totQty, o.order_name, o.quantity
FROM orders o
    INNER JOIN (
        SELECT cust_ID, SUM(quantity) as totQty
        FROM orders
        GROUP BY cust_ID
    ) as o2
    USING (cust_ID)
ORDER BY totQty DESC, cust_id;

+---------+--------+------------+----------+
| cust_ID | totQty | order_name | quantity |
+---------+--------+------------+----------+
|       2 |      6 | DVD        |        2 |
|       2 |      6 | phone      |        3 |
|       2 |      6 | Books      |        1 |
|       1 |      5 | CD         |        1 |
|       1 |      5 | laptop     |        4 |
|       3 |      5 | CD         |        3 |
|       3 |      5 | computer   |        2 |
+---------+--------+------------+----------+

[pascal_22]

Hi Barand!

 

Thanks a lot for your help!

That works perfectly! That exacly what i need!!

 

But on more thing that i can't figure it out...

 

What to do with my limit 20

 

I mean some user has 9 picture or more.... so for this user: it means 8 rows...    so if i did a limit 20, this page will have only 11 members...... (assuming that other members as only one pic)

 

What sould i do?

Change my select? or another way?

 

Thanks for your help

 

Pascal 

[Barand]

Plan B

$sql = "SELECT p.idmember, p.CheminPhoto, p.TotalVote, max.CheminPhoto as other
FROM photosprofils p
    INNER JOIN 
    (
    SELECT idmember
    , MAX(TotalVote) as maxVote
    , GROUP_CONCAT(CheminPhoto ORDER BY TotalVote DESC) as CheminPhoto
    FROM photosprofils
    GROUP BY idmember   
    ) as max
        ON p.idmember = max.idmember AND p.TotalVote = max.maxVote
ORDER BY p.TotalVote DESC, p.idmember";

/* gives these results
+----------+-------------+-----------+-----------------------------+
| idmember | CheminPhoto | TotalVote | other                       |
+----------+-------------+-----------+-----------------------------+
|        3 | Photo 3C*   |        10 | Photo 3C*,Photo 3B,Photo 3A |
|        1 | Photo 1B*   |         9 | Photo 1B*,Photo 1A          |
|        2 | Photo 2B*   |         8 | Photo 2B*,Photo 2A,Photo 2C |
|        4 | Photo 4A*   |         8 | Photo 4A*,Photo 4B          |
|        5 | Photo 5A*   |         7 | Photo 5A*                   |
+----------+-------------+-----------+-----------------------------+
*/

$res = $mysqli->query($sql);

while (list($mem, $mainphoto, $vote, $otherphoto) = $res->fetch_row()) {
    echo "<br>Member : $mem<br>Main Photo : $mainphoto<br>";
    $photos = explode(',', $otherphoto);
    array_shift($photos); // remove main photo
    if (count($photos) > 0) {
        echo "Other photos :<br>";
        foreach ($photos as $other) {
            echo " • $other<br>";
        }
    }
}

[pascal_22]

Thanks so much Barand!!!

 

It works perfectly!

 

Does that kind of query are heavy for a server..? i mean for performance...?

 

Also, can i add another field as OTHER, maybe OTHER2 that will get each NOTE of the picture in the same order thans 'OTHER' field ---> 10, 9.5,8,6.....

 

So when i display thumbnail of other pics, i will also display the NOTE (maxvote) of this pic!

 

Thanks so much!

 

Pascal

[Barand]

Without your data I cannot do any timings but I have avoided the usual performance killers such as left joins and dependent subqueries. Make sure you have an index on idmember column.

 

When using GROUP_CONCAT() there is a 1024 char limit on the grouped field size so inclusion of the notes using that method could be a problem depending on the number of other photos and the size of the notes. Will be OK just getting notes for the main photo.

 

You can get the votes for the other photos using the same method as alternative 1 in #15 above.

[pascal_22]

Hello!!

 

i'm a bit confuse...

My actual query:

select p.idmember,p.Note,p.CheminPhoto, usagers.username,profiles.ville,profiles.sexe,profiles.age from photosprofils inner join profiles.idmember=p.idmember inner join usagers on usagers.idmember=photosprofils.idmember inner join province on province.idprovince=profiles.idprovince where.... order by note desc

in the where clause, depending if user is logged or not, i add the looking sex....

When i did a EXPAIN on it... it take 0.0055 seconde.... i think it's pretty good

 

When i try you Query.... without adding all my INNER JOIN... it take 0.5 seconde..... when i add all my INNER JOIN (usager, province, profil,...) the time is between 0.9 and 2.0 seconde....

 

oufff.... do i miss something...

 

Because yes i display pics but i show the name of the member, the city(ville), the province or state, the sexe, the age......

 

Thanks a lot for you precious help!

Have a good week end!

Pascal

[pascal_22]

any suggestions?

thanks!

Have great day!