Good afternoon. All, I ask for help .. I can not correctly display the data in the table.

There are three tables in the database:

  1. city(id, name)
  2. week_days(id, name)
  3. report(id, city_id, week_day_id, value)

Content of the report table:

 1 | 1 | 1 | 10 2 | 1 | 2 | 08 3 | 1 | 3 | 05 4 | 1 | 1 | 02

There is a code that pulls out this data:

 $db = new PDO('mysql:host=localhost;dbname=test','root',''); $getDays = $db->prepare("SELECT * FROM week_days");//Достаем дни недели $getDays->execute(); $resultDays = array(); $resultDays = $getDays->fetchAll();//Создаем массив дней $getCity = $db->prepare("SELECT * FROM city");//Достаем города $getCity->execute(); $resultCity = array(); $resultCity = $getCity->fetchAll();//Создаем массив городов $getTables= $db->prepare("SELECT r.*, c.name as city_name FROM report as r INNER JOIN city as c ON(r.city_id = c.id) INNER JOIN week_days as wd ON(r.week_day_id = wd.id) "); $getTables->execute(); $result = $getTables->fetchAll(); $resultTables =$result; $result1=$result; for($i=0;$i<count($resultTables);$i++){ $ress[$i]=$resultTables[$i]; foreach($resultDays as $day){ $ress[$i]["days"][$day['id']]=""; if($day['id'] == $resultTables[$i]['day']){ $ress[$i]["days"][$day['id']]=$resultTables[$i]['value']; }else{ foreach($result1 as $kr=>$table1){ if($day['id']==$table1['week_day_id'] && $table1['city_id']==$resultTables[$i]['city_id'] && $resultTables[$i]['id'] != $table1['id'] ){ $ress[$i]["days"][$day['id']]=$table1['value']; } } } } } <!-- Main jumbotron for a primary marketing message or call to action --> <div class="jumbotron"> <div class="container"> <div class="row"> <div class="col-lg-9"> <h1>Таблица</h1> </div> <div class='col-lg-3'> <div class="thumbnail"> <div class="caption"> <h3>Добавить В Календарь</h3> <p><select class='filial form-control'> <option value='0'>--Филиал--</option> <?php if(!empty($resultCity )){//если массив не пустой?> <?php foreach($resultCity as $city){?> <option value="<?=$city['id']?>"><?=$city['name']?></option> <?php }?> <?php }?> </select></p> <p><select class='day form-control'> <?php if(!empty($resultDays)){//если массив не пустой?> <?php foreach($resultDays as $day){?> <option value="<?=$day['id']?>"><?=$day['name']?></option> <?php }?> <?php }?> </select></p> <p><input type='text' class='time_fil form-control' placeholder='Время'></p> <p><button class="save_this btn btn-primary" role="button">Добавить</button> </p> </div> </div> </div> </div> </div> </div> <div class="container"> <!-- Example row of columns --> <div class="row"> <div class="col-lg-12"> <h2>Календарь</h2> <table class='table table-striped'> <thead> <tr> <th>Филиал</th> <?php if(!empty($resultDays)){//если массив не пустой?> <?php foreach($resultDays as $day){?> <th><?=$day['name']?></th> <?php }?> <?php }?> </tr> </thead> <tbody> <?php if(!empty($ress)){?> <?php foreach($ress as $table){?> <tr> <td><?=$table['id'].")".$table['city_name']?></td> <?php if(!empty($table['days'])){ foreach($table['days'] as $day){?> <td><?=$day?></td> <?php } ?> </tr> <?php }}?> <?php } ?> </tbody> </table> </div> </div>

With this request, the table will be released:

 ГОРОД | Понедельник | Вторник | Среда | Четверг | Пятница | Суббота | Воскресенье Москва | 10 | | | | | | | Москва | | 08 | | | | | | Москва | | | 05 | | | | | Москва | 02 | | | | | | |

And this is not correct, it is necessary to get this:

 ГОРОД | Понедельник | Вторник | Среда | Четверг | Пятница | Суббота | Воскресенье Москва | 10 | 08 | 05 | | | | | Москва | 02 | | | | | | |

Tell me, please, I'm already fighting more than one day ...

Closed due to the fact that the question is too general for the participants cheops , zRrr , aleksandr barakin , user194374, D-side 16 Jun '16 at 21:40 .

Please correct the question so that it describes the specific problem with sufficient detail to determine the appropriate answer. Do not ask a few questions at once. See “How to ask a good question?” For clarification. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • one
    formulate the rules by which the table should be built. looking from the side, you build a pivot, but expect some very strange result to get. - Yura Ivanov
  • Thanks for the answer, PIVOT is a cool thing - but it is more suitable for summing up the values, but I need the values ​​to be unique, for example, if there are two values ​​on Monday, it appears as two lines in the label .... I don’t know how to do it - gateway
  • Now I have made a sql query like this: SELECT city_id, MAX (IF (day_id = '1', value, NULL)) as day1, MAX (IF (day_id = '2', value, NULL)) as day2, MAX (IF ( day_id = '3', value, NULL)) as day3, MAX (IF (day_id = '4', value, NULL)) as day4, MAX (IF (day_id = '5', value, NULL)) as day5, MAX (IF (day_id = '6', value, NULL)) as day6, MAX (IF (day_id = '7', value, NULL)) as day7 FROM report GROUP BY city_id received the result: Moscow (1), 12, 08, 05, null, null, null, null and you need: Moscow (1), 10, 08, 05, null, null, null, null Moscow (1), 02, null, null, null, null, null, null - gateway

1 answer 1

It’s not clear how the lines are different

 1 | 1 | 1 | 10

and

 4 | 1 | 1 | 02

In addition to the position in the table, which is not guaranteed by the database: delete something and an entry from another sequence may be higher. Those. 10 and 02 can be swapped. Both entries refer to Moscow and Monday, how to understand that the first entry should be in the first sequence, and the second - in the second? Additional fields are needed to allow grouping. In the current version it is not possible to solve the problem unequivocally.