How to get the X'th most recent SQL query

Question

I'm using the google charts API to create a graph. I have 48 data points, a point for every 30 minutes in a day. My php script runs every 15 seconds and saves the calculated Bitcoin Price to a database. Right now to get the most recent price from the database, I am using this code.

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT priceLBTC FROM prices ORDER BY id DESC LIMIT 1";
$result = $conn->query($sql);
$bitcoinPrice = 0;
if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $bitcoinPrice = round($row["priceLBTC"], 2);
    }
} else {
    echo "0 results";
}

    $sql2 = "SELECT priceLBTC FROM prices ORDER BY id DESC LIMIT 5760";
    $result2 = $conn->query($sql);
    $bitcoinPriceArr = array();
    if ($result->num_rows > 0) {
        // output data of each row
        $i=-1;
        while($row = $result->fetch_assoc()) {
            $i++;
            if ($i%120) continue;

            $bitcoinPriceArr[] = round($row["priceLBTC"], 2);
        }
    } else {
        echo "0 results";
    }


$conn->close();
?>

So the first data point on the graph should be the most recent price, which is $bitcoinPrice, and then the 47 following data points should be the 120 rows before one another. ex: the id of the next data point is ((row of the most recent price) - 120)). (php script runs every 15 seconds, so 4 times a minute * 30 minutes = 120 times every 30 minutes)

Is there an easier way to get all that data and put it into the chart without creating 47 other variables and querying them individually?

Here is the relevant google chart javascript

function drawAxisTickColors() {
      var data = new google.visualization.DataTable();
      data.addColumn('number', 'X');
      data.addColumn('number', 'Price');

            data.addRows([
    [0, 0],    [1, 10],   [2, <?php echo $bitcoinPriceArr[0]; ?>],  [3, 17],   [4, 18],  [5, 9],
    [6, 11],   [7, 27],  [8, 33],  [9, 40],  [10, 32], [11, 35], [12, 35], [13, 35], [14, 35], [15, 35], [16, 35], [17, 35], [18, 35], [19, 35], [20, 35], [21, 35], [22, 35], [23, 35],
    [24, 5], [25, 5], [26, 5], [27, 5], [28, 5], [29, 5], [30, 5], [31, 5], [32, 5], [33, 5], [34, 5], [35, 5], [36, 5], [37, 5], [38, 5], [39, 5], [40, 31], [41, 320], [42, 315], [43, 305],
    [44, 299], [45, 309], [46, 300], [47, <?php echo $bitcoinPrice; ?>]
  ]);

Answer 1

Please check the forum before just posting a question.

This is basically explained here: How do you select every n-th row from mysql

Applied to your usecase that would be something like this:

SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, priceLBTC 
    FROM ( 
        SELECT @row :=0) r, prices 
    ) ranked 
WHERE rownum % [120] = 1

An easier - and maybe even more performant - way though, would be just getting all of the data and cherry pick the points you need with PHP.

$sql = "SELECT priceLBTC FROM prices ORDER BY id DESC LIMIT 5760";
$result = $conn->query($sql);
$bitcoinPrice = array();
if ($result->num_rows > 0) {
    // output data of each row
    $i=-1;
    while($row = $result->fetch_assoc()) {
        $i++;
        if ($i%120) continue;

        $bitcoinPrice[] = round($row["priceLBTC"], 2);
    }
} else {
    echo "0 results";
}


$conn->close();

What happens is:

• Limit is set to 48 times 120 requests (5760: the maximum for 24 hours)
• $bitcoinPrice should be an array, since it needs to hold a couple of values
• $i=0 after the first iteration
• From there on, only data points are picked for which $i leaves no remainder after division by 120

Even better would be to get all queried results at once via mysqli::fetch_all and build the $bitcoinPrice array by only getting every 120th entry. This way you would not have to loop through 119 data points just to get the one you need.

ADD:

You turn a php variable into something usable in javscript with json_encode($bitcoinPriceArr). If you echo the result of that in an javascript assignemnt you will end up with a javascript array:

var bitcoinPrizes = <?php echo json_encode($bitcoinPriceArr); ?>;

With that you can build your chart:

var rows=[];

for (var i in bitcoinPrizes ){
    rows.push([i, bitcoinPrizes [i]]);
}

data.addRows($rows);

I assume you could also do:

for (var i in bitcoinPrizes ){
    data.addRows([[i, bitcoinPrizes [i]]);
}

But I am not entirely sure there.

Pleae keep in mind that this is no place to get your whole tasks sorted out and solved. Hire a developer for that. If you have a specific question or run into a concrete problem, you may ask for help here. But it is still up to you to learn the very basics of the languages and technologies you use.