This works but is there a shorter(more efficient) way of doing this? code compares 2 numbers. One is from the most recent record, the other is from 7 records down.
$sql5="SELECT market_cap_rank FROM intelligence WHERE id='$thiscoin' order by day desc limit 0,1"; $row5 = mysqli_fetch_row(mysqli_query($conn, $sql5)); $newest = $row5[0]; $sql6="SELECT market_cap_rank FROM intelligence WHERE id='$thiscoin' order by day desc limit 6,1"; $row6 = mysqli_fetch_row(mysqli_query($conn, $sql6)); $oldest = $row6[0]; $rankdiff=$oldest-$newest;
Advertisement
Answer
You can combine both queries in to a single one avoiding round trips to DB server.
Assign ranks to each of the 7 rows after order by with the help of SQL variable. Now, sum()
all values by filtering the rows which have rank either 1
or 7
.
If rank = 1
, add it’s negative state.
As a side note, if $thiscoin
is coming from user, you can better make parameterized queries to avoid SQL injection attacks using PDO
.
Query:
select sum(if(rank = 1,-mcr,mcr)) as diff_mcr from ( SELECT @rank := @rank + 1 as rank,market_cap_rank as mcr FROM intelligence,(select @rank := 0) r WHERE id = '$thiscoin' order by day desc limit 0,7 ) derived where rank = 1 or rank = 7
Code:
This would simply be
$rankdiff = mysqli_fetch_row(mysqli_query($conn, $sql))[0];