duanlidi1051 2016-09-12 01:27
浏览 26

MySQL PHP查询中的奇怪数据问题

I am making a query in PHP file as below. I get the data but its not complete. I have about 91 records in mysql but As soon as I add label column in query I just get about 80 record and when I add url column in query it get about 50 records.

I call it weird because as soon as I remove some content from Label columns in Database I get to see from 80 to 83 records.

$sql = "SELECT title, GROUP_CONCAT(title,'|', label,'|', id,'|', grp  SEPARATOR ';') prods FROM docLibrary3 GROUP BY title ";

while($row = mysqli_fetch_array($query)){

 $prods = explode(';',$row['prods']);
 echo '----'.$idx.'----<br>';

 foreach ($prods as $key => $value) {
   $expValue = explode('|',$value);
   echo $expValue[2].":: ".$expValue[3]."<br>";
 };

 $idx++;
};

Here how the out looks

----16----
58:: 08
59:: 09
60:: 11
61:: 12
62:: 15
63:: 02
64:: 1
65:: 13
57:: 07
56:: 06
48:: 02,11
49:: 01,12
50:: 12
51:: 01,12,15
52:: 013
53:: 02,11
54:: 01
:: 
----17----
75:: 01,02,07,08,11,12,13,15
74:: 02,07
73:: 09,10
72:: 11
71:: 02,06,08
70:: 01,02,11,12,13,15
69:: 01,02,11,12,13,16
68:: 01,02,11,12,13,15
67:: 01,02,11,12,13,15
66:: 01,02,11,12,13,15
----18----
85:: 01,02,11,12,13,15
86:: 01,02,11,12,13,15
87:: 01,02,11,12,15
88:: 03,04
89:: 03,04
90:: 01,06,07,08
84:: 01,02,11,12,13,15
83:: 01,02,11,12,15
82:: 01,02,11,12,13,15
:: 

55 and 91 record doesn't print but if I remove some content from label column from any record then some character shows in 55 and 91 record. First I though there may be some special character issue but then found that it was not. It become verse when I add url column in query.

Also not sure why record are not sorting by id column even when I add ORDER BY id in query.

UPDATED: Here is the code that build nested array and print json - works fine but with missing data. I think something is going on in foreach loop or may be in data table

    $rows = array();
$idx = 0; 

  $sql = "SELECT title, GROUP_CONCAT(title,'|', label,'|', id,'|', grp,'|', url  SEPARATOR ';') prods FROM docLibrary3 GROUP BY title ";

  $query = mysqli_query($con, $sql); 

  while($row = mysqli_fetch_array($query)){
           $prods = explode(';',$row['prods']);
          foreach ($prods as $key => $value) {

            $expValue = explode('|',$value);
            echo $expValue[0].":: ".$expValue[3]."<br>";

            $rows[$idx]['title'] = $expValue[0];
            $rows[$idx]['subList'][] = array('label' => $expValue[1], 'url' => $expValue[2], 'grp' => $expValue[3]);

          };

          $idx++;
      };
  $siteData['list'] = $rows;
  var_export($rows, true);
  header('Content-Type: application/json');
  echo json_encode($siteData, JSON_PRETTY_PRINT);

UPDATE 2: Trying to set group_concat_max_len but thats not working too

$sql = "SET SESSION group_concat_max_len=3423543543"
  ."SELECT title, GROUP_CONCAT(title,'|', label,'|', id,'|', grp,'|', url  SEPARATOR ';') prods FROM docLibrary3 GROUP BY title ";
  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 uniapp uview http 如何实现统一的请求异常信息提示?
    • ¥15 目详情-五一模拟赛详情页
    • ¥15 有了解d3和topogram.js库的吗?有偿请教
    • ¥100 任意维数的K均值聚类
    • ¥15 stamps做sbas-insar,时序沉降图怎么画
    • ¥15 买了个传感器,根据商家发的代码和步骤使用但是代码报错了不会改,有没有人可以看看
    • ¥15 关于#Java#的问题,如何解决?
    • ¥15 加热介质是液体,换热器壳侧导热系数和总的导热系数怎么算
    • ¥100 嵌入式系统基于PIC16F882和热敏电阻的数字温度计
    • ¥15 cmd cl 0x000007b