id is not auto_increment and trying to get difference between rows mysql php

Question

I am tring to get difference between frequency, so first row minus next rows,second row minus third row,etc. However, my id in the table like below. So I cannot use minus 1 to get the previous row, and I trying to use <, but not working,help, appreciate.

id    game   number frequency 
1      a      3        4
5      c      3        5
6      a      3        7
9      a      2        9
13     a      2        19

My query is:

SELECT t1.frequency-t2.frequency as diff 
FROM $table as t1 
JOIN $table as t2 ON t2.id < t1.id 
WHERE t1.game=a AND t1.num=2

I see that you tagged your question with php - is a php solution good for you? — Dan Ovidiu Boncut, Jan 31 '16 at 02:42
If a sql solution is desired you can take a look at this: http://stackoverflow.com/questions/710212/there-is-a-way-to-access-the-previous-row-value-in-a-select-statement — Dan Ovidiu Boncut, Jan 31 '16 at 02:42

score 0 · Answer 1 · edited Jan 31 '16 at 02:53

0

A php solution for your requirement could be this:

$result = mysqli_query($connection, "SELECT * FROM $tableName");    
$rows = mysqli_fetch_all ($result, MYSQLI_ASSOC);
for($i=0; i<count($rows)-1; i++){
    echo ( abs($rows[$i]-$rows[$i+1) );
}

edited Jan 31 '16 at 02:53

Funk Forty Niner

74,450
15
68
141

answered Jan 31 '16 at 02:48

Dan Ovidiu Boncut

3,083
4
25
45

Your answer will fail. Read this => http://stackoverflow.com/questions/17498216/can-i-mix-mysql-apis-in-php and you'll see why. Edit: I see you fixed it somewhat, but I fixed the syntax error. ;-) – Funk Forty Niner Jan 31 '16 at 02:49

id is not auto_increment and trying to get difference between rows mysql php

1 Answers1