Retrieving data from nonexistent rows in the database
First of all, this is a large system, but Im using a simple example.
Snippet of code:
$query = "select * from table where date between '2014-11-01' and '2014-11-13'";
$stmt = $con->prepare($query);
$stmt->execute();
$rows = $stmt->fetchAll();
$num = count($rows);
if($num>0){
foreach ($rows as $row) {
echo date('d-m-Y', strtotime($row['Date']));
echo $row['code'];
echo number_format($row['price'], 0, ",", ".");
}
The result of my query for getting data from November 1st to November 13th
date | code | price 2014-11-03 | 'abc' | 140 2014-11-04 | 'abc' | 110 2014-11-05 | 'abc' | 85 2014-11-06 | 'abc' | 100 2014-11-07 | 'abc' | 120 2014-11-10 | 'abc' | 85 2014-11-11 | 'abc' | 97 2014-11-13 | 'abc' | 100
The system only generates data when there is an entry price per day. If there is no data in the database on weekends or holidays, in this case November 1-2 and weekends 8-9 and November 12 are holidays.
What I want to achieve is a holiday or a weekend, it will get the price the day before the weekend / holiday. Over the course of the output algorithm, I extract it by checking if the day of the current row is Saturday and then displays 2 more rows with data on Fridays. The problem occurred when it was being celebrated and if Saturday or Sunday is the first day of the month then it will not display data.
Desired output:
--if the price on 2014-10-31 is 90
date | code | price
2014-11-01 | 'abc' | 90
2014-11-02 | 'abc' | 90
2014-11-03 | 'abc' | 140
2014-11-04 | 'abc' | 110
2014-11-05 | 'abc' | 85
2014-11-06 | 'abc' | 100
2014-11-07 | 'abc' | 120
2014-11-08 | 'abc' | 120
2014-11-09 | 'abc' | 120
2014-11-10 | 'abc' | 85
2014-11-11 | 'abc' | 97
2014-11-12 | 'abc' | 97
2014-11-13 | 'abc' | 100
It should be easy if the date exists in the database or to create a new table, but it is best not to create a really large database of it. How can I achieve this? Thank.
source to share
This should do what you want.
SELECT cur_date, IFNULL(code, 'abc'), IFNULL(price,'any_price') FROM
(
SELECT @rowid:=date_add(@rowid, interval 1 day) as cur_date
FROM `table`, ( SELECT @rowid:='2014-10-31' ) as init
WHERE @rowid < '2014-11-13'
) TBL_DATE LEFT JOIN
(
select `Date`, `code`, `price`
from `table`
where `date` between '2014-11-01' and '2014-11-13'
) TBL_MAIN ON TBL_DATE.cur_date = TBL_MAIN.`Date`
For SQL-Server:
DECLARE @start DATE, @end DATE;
SELECT @start = '20141101', @end = '20141113';
;WITH n AS
(
SELECT TOP (DATEDIFF(DAY, @start, @end) + 1)
n = ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.all_objects
)
SELECT DATEADD(DAY, n-1, @start) as cur_date
FROM n;
Now you get dates and the rest are the same ...
source to share