There was a problem with the output of the query using php. The essence of the problem is that some data is superimposed by others. Something like this, I don’t know how to write differently. For more details, see the examples below:

I wrote the MSSQL query, checked it in the DBMS itself, it works fine and the overlap does not occur. Below is the query itself:

SELECT a1.INCIDENT_ID, dateadd(hour,8,c1.OPEN_TIME), a1.BRIEF_DESCRIPTION, a1.ACTION, a1.NUMBER, a1.CATEGORY, dateadd(hour,8,c1.UPDATE_TIME), a1.ASSIGNMENT, a1.STATUS, a1.CLOSE_TIME, a1.CLOSED_BY, a1.VENDOR, a1.REFERENCE_NO, a1.CAUSE_CODE, a1.RESOLUTION_CODE, a1.RESOLUTION, a1.ASSIGNEE_NAME, a1.UPDATE_ACTION, a1.ACTOR, a1.OPEN_GROUP, a1.CLOSING_COMMENTS, a1.UPDATED_BY, a1.PROBLEM_STATUS, a1.SUBCATEGORY, a1.SYSMODUSER, b1.OPER_BIZDESC, b1.CALLBACK_CONTACT_DEPT, b1.CALLBACK_CONTACT, c1.ALTERNATE_CONTACT, d1.CONTACT_PHONE, d1.FULL_NAME, e1.CONTACT_PHONE, e1.FULL_NAME FROM PROBSUMMARYM1 a1 INNER JOIN PROBSUMMARYM2 b1 ON a1.NUMBER=b1.NUMBER INNER JOIN INCIDENTSM1 c1 ON a1.INCIDENT_ID=c1.INCIDENT_ID INNER JOIN CONTCTSM1 d1 ON d1.CONTACT_NAME=c1.ALTERNATE_CONTACT INNER JOIN CONTCTSM1 e1 ON a1.ASIGNMENT=e1.OPERATOR_ID WHERE a1.INCIDENT_ID LIKE 'IM10234' or a1.NUMBER LIKE 'IM10234' ORDER BY a1.INCIDENT_ID DESC

The problem occurs when displaying the result using php. Shut up precisely here in this place:

 INNER JOIN CONTCTSM1 d1 ON d1.CONTACT_NAME=c1.ALTERNATE_CONTACT INNER JOIN CONTCTSM1 e1 ON a1.ASIGNMENT=e1.OPERATOR_ID

Displays the result of the last merge.

Maybe it can be broken into two requests or one request to be inserted into the cycle of another? How to do this, I can not understand something. The php code itself, which displays information without combining with "e1" with "d1", works perfectly:

 <?php include 'templates/header.php'; ?> <body> <?php //получаем данные через $_POST if (isset($_POST['search'])) { // подключаемся к базе include('inc/db.php'); $db = new db(); $word =$_POST['search']; // Строим запрос $sql = "SELECT a1.INCIDENT_ID, c1.OPEN_TIME, a1.BRIEF_DESCRIPTION, a1.ACTION, a1.NUMBER, a1.CATEGORY, c1.UPDATE_TIME, a1.ASSIGNMENT, a1.STATUS, a1.CLOSE_TIME, a1.CLOSED_BY, a1.VENDOR, a1.REFERENCE_NO, a1.CAUSE_CODE, a1.RESOLUTION_CODE, a1.RESOLUTION, a1.ASSIGNEE_NAME, a1.UPDATE_ACTION, a1.ACTOR, a1.OPEN_GROUP, a1.CLOSING_COMMENTS, a1.UPDATED_BY, a1.PROBLEM_STATUS, a1.SUBCATEGORY, a1.SYSMODUSER, b1.OPER_BIZDESC, b1.CALLBACK_CONTACT_DEPT, b1.CALLBACK_CONTACT, c1.ALTERNATE_CONTACT, d1.CONTACT_PHONE, d1.FULL_NAME FROM PROBSUMMARYM1 a1 INNER JOIN PROBSUMMARYM2 b1 ON a1.NUMBER=b1.NUMBER INNER JOIN INCIDENTSM1 c1 ON a1.INCIDENT_ID=c1.INCIDENT_ID INNER JOIN CONTCTSM1 d1 ON d1.CONTACT_NAME=c1.ALTERNATE_CONTACT WHERE a1.INCIDENT_ID LIKE '%" . $word . "%' or a1.NUMBER LIKE '%" . $word . "%'"; ?> <div id="wrapper"> <?php include 'templates/menu.php'; ?> <div id="page-wrapper"> <div class="row"> <div class="col-lg-12"> <h1 class="page-header">Поиск по номеру обращения и инциденту в УФК по Красноярскому краю V.0.2</h1> <h4>Введите номер обращения или инцидента:</h4> <form method="post" action="do_search.php"> <div class="input-group custom-search-form"> <input type="text" name="search" class="form-control" placeholder="Введите номер обращения, например: SD1000969. Для поиска нажмите Enter..."> <span class="input-group-btn"> <button class="btn btn-default" type="submit"> <i class="fa fa-search"></i> </button> </span> </div> </form> <br> <!-- /input-group --> </div> <!-- /.col-lg-12 --> </div> <div class="row"> <div class="col-lg-12"> <div class="panel panel-default"> <div class="panel-heading"> Вы искали: <b><?php echo $_POST['search'] ?></b><br> <!--<a class="btn btn-default" title="Выгрузить данные в Excel" type="button" href="test.php?test=<?php echo $_POST['search'] ?>"/> <i class="fa fa-table fa-fw"></i>Выгрузка данных в Excel </a> --> </div> <!-- /.panel-heading --> <div class="panel-body"> <div class="table-responsive"> <table class="table table-striped table-bordered table-hover" id="dataTables-example"> <thead> <tr> <th>№</th> <th>Номер обращения</th> <th>Номер инцидента</th> <th>Статус</th> <th>Дата создания</th> <th>Дата закрытия</th> <th>Контактное лицо</th> <th>Назначенный</th> <th>Краткое описание проблемы</th> </tr> </thead> <tbody> <? // Переменные $sent = 'По обращению:'; $im_w_sent = iconv("UTF-8", "windows-1251", $sent); // Получаем результаты $row = $db->select_list($sql); if(count($row)) { $end_result = ''; foreach($row as $r) { // Контактная информация о специалисте\ //определение даты, создание нового формата $counter++; $date = new DateTime ($r['OPEN_TIME']); $date2 = new DateTime ($r['CLOSE_TIME']); $result1 .= '<tr class="odd gradeX">' . '<td>' . $counter . '</td>' . '<td><button class="btn btn-primary btn-lg" data-toggle="modal" data-target="#' . $r['INCIDENT_ID'] . '">' . $r['INCIDENT_ID'] . '</button> <!-- /.panel-heading --> <div class="panel-body"> <!-- Button trigger modal --> <!-- Modal --> <div class="modal fade" id="' . $r['INCIDENT_ID'] . '" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true"> <div class="modal-dialog"> <div class="modal-content"> <div class="modal-header"> <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button> <h4 class="modal-title" id="myModalLabel"> Информация по обращению:&nbsp' . $r['INCIDENT_ID'] . '</h4></div> <div class="modal-body"> <div class="panel-heading"> <div class="alert alert-success">' . 'Статус:&nbsp' . $r['STATUS'] . '</div></div> <!-- /.panel-heading --> <div class="panel-body"> <!-- Nav tabs --> <ul class="nav nav-tabs"> <li class="active"><a href="#home-' . $r['INCIDENT_ID'] .'" data-toggle="tab">Информация</a> </li> <li><a href="#profile-' . $r['INCIDENT_ID'] .'" data-toggle="tab">Описание проблемы</a> </li> <li><a href="#messages-' . $r['INCIDENT_ID'] .'" data-toggle="tab">Решение</a> </li> </li> <li><a href="#active-' . $r['INCIDENT_ID'] .'" data-toggle="tab">Активность</a> </li> </ul> <!-- Tab panes --> <div class="tab-content"> <div class="tab-pane fade in active" id="home-' . $r['INCIDENT_ID'] .'"> </br> <table class="table table-striped table-bordered table-hover" id="dataTables-example"> <thead> <tr> <th>Контактное лицо</th> <th>Контакты</th> </tr> </thead> <tbody> <tr class="odd gradeX"> <td> <p>' . "".iconv("windows-1251", "utf-8",$r['FULL_NAME'])."" . '</p> </td> <td> <p>' . "".iconv("windows-1251", "utf-8",$r['CONTACT_PHONE'])."" . '</p> </td> </tr> </tbody> </table> </div> <div class="tab-pane fade" id="profile-' . $r['INCIDENT_ID'] .'"> </br> <table class="table table-striped table-bordered table-hover" id="dataTables-example"> <tr class="odd gradeX"> <td><b>Краткое описание проблемы:</b></td> </tr> <tr class="odd gradeX"> <td><p>' . "".iconv("windows-1251", "utf-8",$r['BRIEF_DESCRIPTION'])."" . '</p> </td> </tr> <tr class="odd gradeX"> <td> <b>Полное описание проблемы:</b> </td> <tr class="odd gradeX"> <td> <p>' . "".iconv("windows-1251", "utf-8",$r['ACTION'])."" . '</p> </td> </tr> </table> </div> <div class="tab-pane fade" id="messages-' . $r['INCIDENT_ID'] .'"> </br> <table class="table table-striped table-bordered table-hover" id="dataTables-example"> <tr class="odd gradeX"> <td> <b>Код закрытия:</b> </td> </tr> <tr class="odd gradeX"> <td> <p>' . "".iconv("windows-1251", "utf-8",$r['CAUSE_CODE'])."" . '</p> </td> </tr> <tr class="odd gradeX"> <td> <b>Полное описание решения:</b> </td> </tr> <tr class="odd gradeX"> <td> <p>' . "".iconv("windows-1251", "utf-8",$r['RESOLUTION'])."" . '</p> </td> </tr> </table> </div> <div class="tab-pane fade" id="active-' . $r['INCIDENT_ID'] .'"> </br> <table class="table table-striped table-bordered table-hover" id="dataTables-example"> <tr class="odd gradeX"> <td> <p>' . "".iconv("windows-1251", "utf-8",$r['UPDATE_ACTION'])."" . '</p> </td> </tr> </table> </div> </div> </div> <!-- /.panel-body --> <!-- /.panel --> </div> <div class="modal-footer"> <button type="button" class="btn btn-default" data-dismiss="modal">Закрыть</button> <a class="btn btn-primary" title="Выгрузить данные в Excel" type="button" href="test.php?test=' . $r['INCIDENT_ID'] . '"/> <i class="fa fa-table fa-fw"></i>Выгрузка данных в Excel v.0.1 </a> <a class="btn btn-primary btn-social btn-twitter" href="mailto:' . $r['ASSIGNEE_NAME'] . ' @mail .ru?subject=' . $sent . $r['INCIDENT_ID'] . ' - ' . $r['NUMBER'] . ' " title="Написать на электронный адрес спициалисту"><i class="fa fa-envelope fa-fw"></i>' . $r['ASSIGNEE_NAME'] . '</a> </div> </div> <!-- /.modal-content --> </div> <!-- /.modal-dialog --> </div> <!-- /.modal --> </div> <!-- .panel-body --></td>' . '<td>' . $r['NUMBER'] . '</td>' . '<td>' . $r['STATUS'] . '</td>' . '<td>' . $date->format('dmY H:i:s') . '</td>' . '<td>' . $date2->format('dmY H:i:s') . '</td>' . '<td>' . "".iconv("windows-1251", "utf-8",$r['FULL_NAME'])."" .'</td>' . '<td><a class="btn btn-primary btn-social btn-twitter" href="mailto:' . $r['ASSIGNEE_NAME'] . ' @mail .ru?subject=' . $sent . $r['INCIDENT_ID'] . ' - ' . $r['NUMBER'] . ' " title="Написать на электронный адрес спициалисту"><i class="fa fa-envelope fa-fw"></i>' . $r['ASSIGNEE_NAME'] . '</a></td>' . '<td>' . "".iconv("windows-1251", "utf-8",$r['BRIEF_DESCRIPTION'])."" .'</td>' . '</tr>'; $end_result = $result1; } echo $end_result; ?> </tbody> </table> </div> <!-- /.table-responsive --> </div> <!-- /.panel-body --> </div> <!-- /.panel --> </div> <!-- /.col-lg-12 --> </div> <!-- /.row --> </div> <!-- /#page-wrapper --> </div> <?php } else { echo '<div class="alert alert-danger">По вашему запросу ничего не найдено.</div>'; } } ?> <!-- /#wrapper --> <!-- Core Scripts - Include with every page --> <script src="js/jquery-1.10.2.js"></script> <script src="js/bootstrap.min.js"></script> <script src="js/plugins/metisMenu/jquery.metisMenu.js"></script> <!-- Page-Level Plugin Scripts - Tables --> <script src="js/plugins/dataTables/jquery.dataTables.js"></script> <script src="js/plugins/dataTables/dataTables.bootstrap.js"></script> <!-- SB Admin Scripts - Include with every page --> <script src="js/sb-admin.js"></script> <!-- Page-Level Demo Scripts - Tables - Use for reference --> <script> $(document).ready(function() { $('#dataTables-example').dataTable(); }); // tooltip demo $('.tooltip-demo').tooltip({ selector: "[data-toggle=tooltip]", container: "body" }) // popover demo $("[data-toggle=popover]") .popover() </script> <!-- Page-Level Demo Scripts - Blank - Use for reference --> </body> </html>

Please help understand how to solve this problem?

  • @intertex // never trust incoming data! Filter everything! $ word = $ _ POST ['search']; The data is not filtered, and this code is a vivid example of a SQL injection-vulnerable code - etki
  • I did not add this piece, I want to make it separately. the problem with data output interests. - intertex

1 answer 1

Use field aliases:

 e1.CONTACT_PHONE as CONTACT_PHONE_e, e1.FULL_NAME as FULL_NAME_e
  • using pseudonyms, my answer returns empty. tried and in MSSQL to drive away request and through the written application. - intertex
  • one
    @intertex, then the statement written above that works fine in the studio is not true - column aliases affect the field names, not the process of getting them. Also verify that in your code you refer to them by altered names. - maxleo
  • @maxleo, by the way, by removing the where clause, the request worked, when compiling the query, there are no errors with where ... - intertex
  • @intertex, apparently you do not have the requested data. This is normal. And in general, use stored procedures. - maxleo
  • I apologize, the structure of the database is huge, the field is not correctly defined. Everything works, thank you. And about the stored procedures in MSSQL? - intertex