0

i am creating php script to take input from a data base containing [id,value,date] , there are many rows (increasing every minute using an api) . I want to fetch 6 rows everytime like 1to6 , then perform their average(val1+2+3+4+5+6/6) and store that average into a variable. This whole process is continuous, means 1to6->average->outputin variable then 6to12->average->outputin variable and like that

i am using php and i did

$var=1;
$raw = "SELECT id, value, date FROM db";
$result = $conn->query($raw);

$row_cnt = $result->num_rows;

printf("Result set has %d rows.\n", $row_cnt);

if ($result->num_rows < $var) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    
    echo nl2br("\n");
    echo "price: " . $row["value"];
    echo nl2br("\n");
  }
$var++;

please give me solution to my problem, ❤php

Ashutosh7i
  • 100
  • 10
  • 1
    `SELECT AVG(value) FROM (SELECT value FROM db LIMIT 6) t` but this will only fetch 6 records randomly.... you'd have to apply an appropriate WHERE condition for more precise selection (sorry, had to edit it twice) – Honk der Hase May 13 '22 at 16:30
  • Can you provide more code to make your goal more clean? The role of a `$var` is pretty unclean here. – Jared May 13 '22 at 19:34
  • thank you @HonkderHase your SQL query gave me the idea to get average, i made a query that eveytime takes 6 new values from db and averages them and outputs the average , here is the result- `SELECT AVG(price) FROM (SELECT price FROM price ORDER BY no DESC LIMIT 0, 6) items;` – Ashutosh7i May 14 '22 at 12:41

1 Answers1

1

instead of fetching data from sql to php script , i averaged the data itself on the sql server using this query

SELECT AVG(table) FROM (SELECT table FROM db ORDER BY id DESC LIMIT 0, 6) items;
Ashutosh7i
  • 100
  • 10
  • This is the correct soution for my problem, i saved this command as a procedure in my mysql server and this executes everytime a new data is entered , anyway , it gets my work done , thanks – Ashutosh7i May 24 '22 at 19:55