PHP MYSQL Foreach date list

Question

I have two table, "table1" and "table2". In "table1" is item title and in "table2" is dates and item receive quantity

SQL:

SELECT t2.title, t1.price, t1.quantity, t1.date
                  FROM table2 t2
                  JOIN table1 t1
                    ON t1.id = t2.t_id
                 WHERE t2.date BETWEEN '2017-07-12' AND '2017-07-15'

Now I get like this result:

title date quantity
item1 2017-07-12 100
item2 2017-07-12 120
item3 2017-07-12 150
item1 2017-07-13 200
item2 2017-07-13 320
item3 2017-07-13 450

But I want get result like this:

now I have this code in PHP:

            <?php foreach ($AmmunitionDate as $key => $row): ?>
            <tr>
                <td> <?=$row['id']?> </td>
                <td> <?=$row['title']?> </td>
                <td> <?=$row['quantity']?> </td>
                <td>  </td>
            </tr>
            <?php endforeach ?>

How can I solve this task?

Thank you

Try to organize your results from MySQL into multi-dimensional array, with the date as array key — Raptor
– Raptor, Commented Jul 17, 2017 at 9:52

Bibhudatta Sahoo · Accepted Answer · 2017-07-17 10:24:27Z

2

Try like this in mysql Query

SELECT t2.title, Group_concat( `t1.date` order by `t1.date`) as date, 
Group_concat( t1.quantity order by `t1.date`) as quantity  ,sum(`t1.quantity`) as total 
FROM table2 t2
JOIN table1 t1
ON t1.id = t2.t_id
WHERE t2.date BETWEEN '2017-07-12' AND '2017-07-15' group by `t2.title`

It produce output as

title    date                    quantity     total
item1   2017-07-12,2017-07-13    100,200       300
item2   2017-07-12 ,2017-07-13   120,320       440
item3   2017-07-12 ,2017-07-13   450,150       600

Then explode the coma separated value and display it as you wish.

answered Jul 17, 2017 at 10:24

Bibhudatta Sahoo

4,9142 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

George B. Over a year ago

Thank you, it's best solution!

JimmyLOL · Accepted Answer · 2017-07-17 10:12:44Z

0

First, you should handle your data and pass it to an array.

<?php 
foreach($AmmunitionDate as $item){
  $arr[$item['title']] = array(
          $item['date'] = array(
              'quantity' = $item['quantity'];
          )
     )
}
?>

After that, you can use any loop to echo your result in your table.

answered Jul 17, 2017 at 10:12

JimmyLOL

472 bronze badges

Comments

Abhishek · Accepted Answer · 2017-07-17 10:38:18Z

0

If you do want to keep your database as it is and make your life harder there are two solutions:

First solution:

Make two queries one for item table one for the history tabe. With PHP echo, construct a javascript where you have javascript variables containing the result of the MYSQL. Than make a table with javascript.

Second (Much better Solution):

Make a query where you get all what you need from the history table and get the dates. With that you can make the dimension of the table (because you know how many <td> in a <tr>). After that make an AJAX request in the background and have more table rows added with the history and another one with the names.

edited Jul 17, 2017 at 10:38

Abhishek

5397 silver badges26 bronze badges

answered Jul 17, 2017 at 10:10

dben

4391 gold badge6 silver badges23 bronze badges

Collectives™ on Stack Overflow

PHP MYSQL Foreach date list

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related