Mysql - select 5 lastest data by date including data that does not exist

loveearthmix
January 23, 2024
187 views
1 vote
2 Answers

i want to select 5 lastest data by date (month) including data that does not exist in codeigniter

heres salesdata table (every date fixed by 1)

id	date	stock	sold
a1	01-01-2024	2	28
a1	01-08-2024	12	100
a1	01-04-2024	12	28
a3	01-01-2024	12	28
a1	01-02-2024	9	28
a2	01-01-2024	10	28
a1	01-03-2024	89	19
a1	01-10-2024	12	67
a1	01-06-2024	12	28

this is what i want on viewwhich.php
the "does not exist" data value are 0

id	date	stock	sold
a1	01-06-2024	12	28
a1	01-07-2024	0	0
a1	01-08-2024	12	100
a1	01-09-2024	0	0
a1	01-10-2024	12	67
	sum	36	195

i think it needs some javascript but idk

for now i only can select data that existed

my salescontroller.php

$whichid = "a1";

$data [
     'selectwhich1' => $this->modelsales->resultWhich1($whichid)->row(),
     'selectwhich2' => $this->modelsales->resultWhich2($whichid)->row(),
     'selectwhich3' => $this->modelsales->resultWhich3($whichid)->row(),
     'selectwhich4' => $this->modelsales->resultWhich4($whichid)->row(),
     'selectwhich5' => $this->modelsales->resultWhich5($whichid)->row(),
];

$this->load->view('viewwhich',$data);

and resultWhich1 code on modelsales.php

public function resultWhich1($data){
            return $this->db->query("SELECT * FROM (select * FROM salesdata ORDER BY date DESC LIMIT 5) AS x WHERE id = '$data' ORDER BY date ASC LIMIT 1");
        }
public function resultWhich2($data){
            return $this->db->query("SELECT * FROM (select * FROM salesdata ORDER BY date DESC LIMIT 4) AS x WHERE id = '$data' ORDER BY date ASC LIMIT 1");
        }

etc.

i separated them so i can sum all the resultWhich1-5

thanks

===

of course my viewwhich.php code will be

...
<tr>
<td><?= $selectwhich1['id'] ?></td>
<td><?= $selectwhich1['date'] ?></td>
<td><?= $selectwhich1['stock'] ?></td>
<td><?= $selectwhich1['sold'] ?></td>
</tr>
...

===

in my head i need to select the latest data

select date from salesdata where id = $data order by date desc 1

the result should be $lastdate = 2024-10-01

then find the last 5 months base that data like

$month1 = date($lastdate,strtotime("-4 months");
$month2 = date($lastdate,strtotime("-3 months");
$month3 = date($lastdate,strtotime("-2 months");
$month4 = date($lastdate,strtotime("-1 month");

then check again to database if there data with id and date

if (select * from sales data where id = $data and date = $month1){
    $idmonth1 = select id from sales data where id = $data and date = $month1;
    $datemonth1 = select date from sales data where id = $data and date = $month1;
    $stockmonth1 = select stock from sales data where id = $data and date = $month1;
    $soldmonth1 = select sold from sales data where id = $data and date = $month1;
} or {
    $idmonth1 = $data;
    $datemonth1 = $month1;
    $stockmonth1 = 0;
    $soldmonth1 = 0;
}

does that make sense

Answers

- dvicemuse
- January 22, 2024 at 2:44 pm
- 0 votes
0
I don’t know CodeIgniter but I do know mysql.

That being said, why wouldnt you use a query like this?

"SELECT * FROM `salesdata` WHERE `id` = '$id' ORDER BY date DESC LIMIT 5"

As far as i can tell it should be like this in CodeIgniter
```
$id   = "a1";
$data = [];
foreach($this->db->query("SELECT * FROM `salesdata` WHERE `id` = '$id' ORDER BY `date` DESC LIMIT 5")->result_array() as $k => $val){
  $data['selectwhich'.($k+1)] = $val;
}
```
Now your $data array should have all of the records in the correct order limited to the latest 5.

But like I said, I’ve never used CodeIgniter so maybe someone else have a better solution.
Login or Signup to reply.

- user1191247
- January 23, 2024 at 5:27 pm
- 0 votes
0
You could do it on the MySQL side with a recursive CTE to get the list of relevant dates for the given id:
```
WITH RECURSIVE cte (`id`, `date`, `last_date`) AS (
    SELECT `id`, MAX(`date`) - INTERVAL 4 MONTH, MAX(`date`)
    FROM salesdata
    WHERE `id` = 'a1'
    UNION ALL
    SELECT `id`, `date` + INTERVAL 1 MONTH, `last_date`
    FROM cte
    WHERE `date` < `last_date`
)
SELECT cte.id, cte.date, IFNULL(sd.stock, 0) AS stock, IFNULL(sd.sold, 0) AS sold
FROM cte
LEFT JOIN salesdata sd
    ON cte.date = sd.date
    AND cte.id = sd.id;
```
Output:

id date stock sold

a1 2024-06-01 12 28

a1 2024-07-01 0 0

a1 2024-08-01 12 100

a1 2024-09-01 0 0

a1 2024-10-01 12 67

Here’s a db<>fiddle.

This assumes that dates are stored correctly as the DATE datatype (yyyy-mm-dd) and there is only one row per id per month.

P.S. When passing values into your queries you should use Query Bindings or Named Bindings instead of just concatenating strings.
Login or Signup to reply.