Skip to content
Advertisement

is there a more efficient way of comparing two variables obtained from a db?

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];
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement