My knowledge of MySQL is basic. I want to build a query to return all rows when is reached that sum a given amount value
table:
id name expirydate amount
1 name1 11-08-16 0.20
2 name2 10-08-16 1.50
3 name3 08-08-16 1.00
I need the result from id 3, 2 order by expiry date.
You can accomplish this using MySQL user defined variables
SET @n := 1.2;
SELECT
t.id,
t.name,
t.expirydate,
t.amount
FROM
(
SELECT
*,
@bal := @bal + amount AS runningAmount,
IF(@bal >= @n, @doneHere := @doneHere + 1 , @doneHere) AS whereToStop
FROM table_amount
CROSS JOIN (SELECT @bal := 0.0 , @doneHere := 0) var
ORDER BY expirydate
) AS t
WHERE t.whereToStop <= 1
See a WORKING DEMO
If you are using PHP, you can use the following loop to get all results from query. Query will get result and through while loop we can display all records.
<?php
$query = mysqli_query($db, "SELECT * FROM table WHERE amount >= 1.2 ORDER BY expirydate");
while($array = mysqli_fetch_array($query)){
echo $row['name'].' '.$row['amount'].' '.$row['expirydate'].'<br>';
}
?>