php - modifying a mysql table to show ranking based on column value

62

I'm working on some code in php/MySql and I need to update a field called scorerank based on the score value. I found a previous question here that addressed what I was looking for:

Update the rank in a MySQL Table

I modified their example to work with my database. I also added a condition so that I only modified columns that had a specific tourneyid and tableid. I used a unique index variable in the inner join to id which row to update - I think I did it right, but I get the following error:

1248 - Every derived table must have its own alias

Here's my code. Can anybody spot the error(s)?

UPDATE   tscores
JOIN     (SELECT    p.userid,p.scoreid,
                    IF(@lastPoint <> p.score, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    IF(@lastPoint = p.score, 
                       @curRank := @curRank + 1, 
                       @curRank),
                    @lastPoint := p.score
          FROM      tscores p
          WHERE     (p.tourneyid=1 and p.tableid=2)
          JOIN      (SELECT @curRank := 0, @lastPoint := 0) 
          ORDER BY  p.score DESC

         ) ranks ON (ranks.userid = tscores.userid)
SET      tscores.scorerank = ranks.scorerank
WHERE    tscores.scoreid = p.scoreid 

Here's the table I'm working with:

CREATE TABLE IF NOT EXISTS `tscores` (
  `scoreid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tourneyid` int(10) unsigned NOT NULL,
  `tableid` int(10) unsigned NOT NULL,
  `userid` int(10) unsigned NOT NULL,
  `score` int(16) unsigned NOT NULL DEFAULT '0',
  `scorestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `scoreround` int(3) unsigned NOT NULL DEFAULT '0',
  `scoregroup` int(3) unsigned NOT NULL DEFAULT '0',
  `scorerank` int(4) unsigned DEFAULT NULL,
  PRIMARY KEY (`scoreid`),
  KEY `tourneyid` (`tourneyid`,`tableid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=62 ;
876

Answer

Solution:

In your last line you are trying to use an alias that is inside a subquery.. see the fix below

WHERE    tscores.scoreid = ranks.scoreid 
334

Answer

Solution:

one more attempt: try putting an alias (name or a leTTER) after this (i put mm)

JOIN      (SELECT @curRank := 0, @lastPoint := 0) mm
550

Answer

Solution:

not that but swap the places of where and join join comes after from clause not after where
change this ..

WHERE     (p.tourneyid=1 and p.tableid=2)
   JOIN      (SELECT @curRank := 0, @lastPoint := 0) 

to this

JOIN      (SELECT @curRank := 0, @lastPoint := 0) 
WHERE     (p.tourneyid=1 and p.tableid=2) 

and keep previous fixes as well

354

Answer

Solution:

Here is a revised version of your query:

UPDATE   tscores JOIN
         (SELECT    p.userid, p.scoreid,
                    IF(@lastPoint <> p.score, 
                       @curRank := @curRank + 1, 
                       @curRank)  AS rank,
                    @lastPoint := p.score
          FROM      tscores p CROSS JOIN
                    (SELECT @curRank := 0, @lastPoint := 0) const
          WHERE     (p.tourneyid = 1 and p.tableid = 2) 
          ORDER BY  p.score DESC
         ) ranks
         ON tscores.scoreid = p.scoreid 
    SET      tscores.scorerank = ranks.rank;

Here are changes:

  1. Made the join condition onscoreid. This is the primary key ontscores so that is all that is needed.
  2. Removed thewhere clause and the redundant condition onuserid.
  3. Removed the secondif() statement, which also incremented@curRank.
  4. Changed theJOIN to aCROSS JOIN. Without anon clause, you should useCROSS JOIN (even though MySQL does allowJOIN there).
  5. Added a table alias for the assignment subquery.
  6. Changed theset clause to refer torank instead ofscorerank.
  7. Moved the assignment subquery before thewhere clause.

People are also looking for solutions to the problem: html - PHP form not POSTing multiple select fields

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.