Two basic MySQL questions.

[Harm_Millaard]

Due to my lack of expertise with MySQL I have two rather basic questions.

• I use this query to give me the results I need, but I have to adjust the bold figures manually:

SELECT computer_ID, 100 - ( nr -1) * (100/157), 100 - (nr_rpi - 1)*(100/157), ... FROM xyz WHERE Version LIKE 6.0 ORDER BY nr

The problem is the number 157. Ideally this should read 'COUNT(*)-1' or 'MAX(nr)-1'. But somehow this only gives me a single row, not the complete list. What I need to do is change absolute rankings on different variables to a scale of 0 to 100. I have - in this example - rankings from 1 to 158 (1 being top) and I need to adjust them to a scale of 100 (100 being top) to 0.

What do I need to change in this query?

• I need to generate the rankings used above and currently I use manual labor to create these, but obviously it is far from ideal.

This is what I use to manually generate these ranking figures:

First empty Benchmark_results. Then get rank for total time:

Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)

SELECT @rank:=@rank+1 AS Rank,

@Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6,

@Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi,

@Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk,

@Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd,

@Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264,

Ref_ID

FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i

WHERE 1 ORDER BY total_time, crpi, cgain ASC LIMIT 0, 5000

Now update rank for total time:

UPDATE Personal_data, Benchmark_results

SET Personal_data.nr=Benchmark_results.RankCS6

WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0

Now empty Benchmark_results again and repeat for crpi:

Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)

SELECT @rank:=@rank+1 AS Rank,

@Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6,

@Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi,

@Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk,

@Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd,

@Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264,

Ref_ID

FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i

WHERE 1 ORDER BY crpi, total_time, cgain ASC LIMIT 0, 5000

Now update rank for crpi only:

UPDATE Personal_data, Benchmark_results

SET Personal_data.nr_rpi=Benchmark_results.nr_rpi

WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0

Now repeat for Disk_io:

Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)

SELECT @rank:=@rank+1 AS Rank,

@Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6,

@Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi,

@Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk,

@Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd,

@Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264,

Ref_ID

FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i

WHERE 1 ORDER BY Diskio, crpi, total_time, cgain ASC LIMIT 0, 5000

And update rank for Diskio:

UPDATE Personal_data, Benchmark_results

SET Personal_data.nr_disk=Benchmark_results.nr_disk

WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0

Repeat for dvd:

Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)

SELECT @rank:=@rank+1 AS Rank,

@Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6,

@Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi,

@Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk,

@Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd,

@Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264,

Ref_ID

FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i

WHERE 1 ORDER BY dvd, crpi, total_time, cgain ASC LIMIT 0, 5000

And update rank for dvd:

UPDATE Personal_data, Benchmark_results

SET Personal_data.nr_mpeg=Benchmark_results.nr_dvd

WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0

Repeat for h264:

Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID)

SELECT @rank:=@rank+1 AS Rank,

@Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6,

@Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi,

@Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk,

@Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd,

@Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264,

Ref_ID

FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i

WHERE 1 ORDER BY h264, crpi, total_time, cgain ASC LIMIT 0, 5000

And update rank for h264:

UPDATE Personal_data, Benchmark_results

SET Personal_data.nr_h264=Benchmark_results.nr_h264

WHERE Benchmark_results.Ref_ID=Personal_data.Ref_ID AND Personal_data.Version LIKE 6.0

Now clean up again:

Insert into Benchmark_results(Rank, RankCS6, nr_rpi, nr_disk, nr_dvd, nr_h264, Ref_ID, Total, RPI, Gain)

SELECT @rank:=@rank+1 AS Rank,

@Nr:=CASE WHEN Version LIKE 6.0 THEN @Nr+1 ELSE @Nr END AS RankCS6,

@Nr_rpi:=CASE WHEN Version LIKE 6.0 THEN @Nr_rpi+1 ELSE @Nr_rpi END AS nr_rpi,

@Nr_disk:=CASE WHEN Version LIKE 6.0 THEN @Nr_disk+1 ELSE @Nr_disk END AS nr_disk,

@Nr_mpeg:=CASE WHEN Version LIKE 6.0 THEN @Nr_mpeg+1 ELSE @Nr_mpeg END AS nr_dvd,

@Nr_h264:=CASE WHEN Version LIKE 6.0 THEN @Nr_h264+1 ELSE @Nr_h264 END AS nr_h264,

Ref_ID, Diskio+dvd+h264+IFNULL(MPE_On,MPE_Off) As Total, FORMAT((50.893*Diskio+158.333*dvd+77.027*h264+950*IFNULL(MPE_On,MPE_Off))/12138.0,4) As RPI, FORMAT(MPE_Off/IFNULL(MPE_On,MPE_Off),3) As Gain

FROM Personal_data, (SELECT @rank:=0, @Nr:=0, @Nr_rpi:=0, @Nr_disk:=0, @Nr_mpeg:=0, @Nr_h264:=0 AS i) AS i

WHERE 1 ORDER BY total_time, crpi, cgain ASC LIMIT 0, 5000

This of course is far from ideal, but the only way I figured out that works, but among you geniuses there must be someone who can help me optimize this workflow and I'm open to all suggestions.

[bregent]

>What do I need to change in this query?

max() and count() are aggregate functions and do not work properly unless they are used in a select with a group by clause. One solution is get the max value in another query. Another solution is to get it using a derived table within your current query. How many rows are there in the table?

Unfortunately I'm heading out of town and don't have time to look at your second question. If I have time this weekend I'll take a look.

[Harm_Millaard]

BUMP.

Any suggestions?

[bregent]

I gave you a few ideas about the first question. Did you have any luck?

For #2, you should be able to update the rankings in the personal_data table directly using this technique:

http://jaran.de/goodbits/2012/01/08/rankings-in-mysql-global-ranks-and-local-ranks-within-subsets/

[Harm_Millaard]

I'm probably making a stupid mistake with regard to point 1.

I have this .php code:

<?php

mysql_select_db($database_mysql, $mysql);

$query_count='SELECT(COUNT(*)-1) AS number FROM Personal_data WHERE Version LIKE 6.0 AND MPE_On >= 1';

$count=mysql_query($query_count, $mysql) or die(mysql_error());

$query_recordset1='SELECT computer_ID,

100-(nr-1)*(100/144) AS Ranking,

100-(nr_rpi-1)*(100/144) AS RPI,

100-(nr_disk-1)*(100/144) AS Disk,

100-(nr_mpeg-1)*(100/144) AS MPEG,

100-(nr_h264-1)*(100/144) AS H264,

100-(nr_mpe-1)*(100/144) AS MP FROM `Personal_data` WHERE Version LIKE 6.0 AND MPE_On >= 1 ORDER BY nr ASC';

$recordset1=mysql_query($query_recordset1, $mysql) or die(mysql_error());

while ($record=mysql_fetch_array($recordset1, MYSQL_NUM)){

echo printf("ID: '%s', Total: '%d', RPI: '%d', Disk: '%d', MPEG: '%d', H264: '%d', MPE: '%d' ",

$record[0], $record[1], $record[2], $record[3], $record[4], $record[5], $record[6]);

}

mysql_free_result($recordset1);

?>

I was hoping that using a variable $count or even 'number' would solve the issue, but it does not work. As soon as I replace:

100/144 by 100/$count I get an error that $count is not a column name and when replacing it with 100/number I only get 0 as a result.

In the second place, I get these echo printf results when checking the correctness of the query:

ID: 'Harms Monster', Total: '100', RPI: '100', Disk: '100', MPEG: '100', H264: '100', MPE: '100' 97ID: 'Asrock X79 Extreme 9', Total: '99', RPI: '98', Disk: '88', MPEG: '93', H264: '97', MPE: '99' 98ID: 'Magnus EDIT3 4.6GHz', Total: '98', RPI: '97', Disk: '97', MPEG: '97', H264: '93', MPE: '98' 97ID: 'RFD 2012', Total: '97', RPI: '97', Disk: '95', MPEG: '89', H264: '89', MPE: '97' 86ID: 'Extreme 9 GTX TITAN ', Total: '97', RPI: '99', Disk: '98', MPEG: '99', H264: '98', MPE: '97' 98ID:

etc.

In some mysterious way the RED figures are added to the echo statement and they should not be there. For readability it would be nice to add a CRLF to the printf string, so each record is on a new line.

[bregent]

>I was hoping that using a variable $count or even 'number' would solve the issue, but it does not work. As soon as I replace:

>

>100/144 by 100/$count I get an error that $count is not a column name and when replacing it with 100/number I only get 0 as >a result.

Your query returns the calculated column as 'number' in the recordset. Currently, you tried assigning the recordset to the $count variable. You need to assign the column value from a row in the recordset to a php variable. Then use that in the second query.

[bregent]

>In some mysterious way the RED figures are added to the echo statement and they should not be there.

Sorry, I have no idea

>For readability it would be nice to add a CRLF to the printf string, so each record is on a new line.

Just echo a BR or P tag in the while loop.

[Harm_Millaard]

Changing this line:

echo printf("ID: '%s', Total: '%d', RPI: '%d', Disk: '%d', MPEG: '%d', H264: '%d', MPE: '%d' ",

$record[0], $record[1], $record[2], $record[3], $record[4], $record[5], $record[6]);

to

echo sprintf("ID: '%s', Total: '%d', RPI: '%d', Disk: '%d', MPEG: '%d', H264: '%d', MPE: '%d'<br /> ",

$record[0], $record[1], $record[2], $record[3], $record[4], $record[5], $record[6]);

solved the mysterious RED figures and the addition of the <br /> made it much more readable. At least I now know that the query works.

Next step is to figure out a way to display the results of this .php page to show a lightbox view of the selected record in a fabrik list in detail view like this:

based on the record selected, with the figures as shown in the previous post. So they should be dynamic. In this example:

ID: mystery name-3930K OC, Total: 75, RPI: 79, Disk: 65, MPEG: 88, H264: 79, MPE: 75

Selecting a different record with different numbers should then adjust the graphs or gauges accordingly. Or, in a slightly different layout: