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 ";