CODEWITHSUNDEEP
phpmysql
John Paul
John Paul

Reputation: 55

Getting multiple row and store to a variable. Mysql and php

I am creating an average kilometer run calculator for my system. So I am trying to get the last two date and last two run on my database.

This is my current code:

<?php

$conn = mysqli_connect('localhost','root','','mypms');

 $sqldates = "SELECT dateinput FROM sched ORDER BY dateinput DESC LIMIT 2";
 $sqlrun = "SELECT reading FROM sched ORDER BY reading DESC LIMIT 2"; 

 $resultdate = mysql_query($conn,$sqldates);
 $resultrun = mysql_query($conn,$sqlrun);


 while($rowdate = mysql_fetch_assoc($resultdate))
 {
     this ->
 }

 $date_difference = $date2 - $date1;

 while($rowrun = mysql_fetch_assoc($resultrun))
 {
     this ->
 }

 $run_difference = $run2 - $run1;
 $averagerun = $run_difference/$date_difference

 echo $averagerun;
?>

what should I write on my this so I can store my $resultrun and $resultdate to $date1, $date2 and $run1, $run2.

Note: Datetype of dateinput is date on my mysqldatabase.

Upvotes: 0

Views: 29

Answers (1)

Andrew
Andrew

Reputation: 7768

Try this query instead - it will return your result

SELECT 
run_difference/date_difference AS averagerun
FROM
    (
        SELECT
        DATEDIFF(date2,date1) AS date_difference,
        reading2 - reading1 AS run_difference
        FROM
        (
            SELECT 
            (SELECT dateinput FROM sched ORDER BY dateinput LIMIT 0, 1) AS date1, 
            (SELECT dateinput FROM sched ORDER BY dateinput LIMIT 1, 1) AS date2,
            (SELECT reading FROM sched ORDER BY reading DESC LIMIT 0, 1) AS reading1,
            (SELECT reading FROM sched ORDER BY reading DESC LIMIT 1, 1) AS reading2 
            FROM DUAL
        ) t1
    )t2

or "one liner":

SELECT 
    (SELECT reading FROM sched ORDER BY reading DESC LIMIT 1, 1)-(SELECT reading FROM sched ORDER BY reading DESC LIMIT 0, 1)   /   DATEDIFF((SELECT dateinput FROM sched ORDER BY dateinput LIMIT 1, 1), (SELECT dateinput FROM sched ORDER BY dateinput LIMIT 0, 1))  AS averagerun
FROM DUAL

Upvotes: 1

Related Questions