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

Question

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.

This does not look like a class so why are you expecting to use $this-> ? — RiggsFolly
– RiggsFolly, Commented Apr 2, 2016 at 0:08

Andrew · Accepted Answer · 2016-04-02 00:48:25Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related