2

php loop is skipping or missing some results of mysql query, the same query is bringing 181 results in PhpMyAdmin and even in sqlYOG where as the loop is bringing only 175

$queryos = "SELECT * FROM OSCSBranch AS a 
LEFT JOIN
(SELECT ItemCode AS icode FROM NonFood
) AS q ON a.ItemCode = q.icode
WHERE a.BranchId = '$bid' AND a.BType = 'O' AND DATE(a.BDate) = '$date'"; 

$osquery = mysqli_query($conn, $queryos);
if(!$osquery)
{
die("QUERY FAILED OS " . mysqli_error($conn));    
} 

while($row = mysqli_fetch_assoc($osquery)){
$total[$row['ItemCode']] = $row['TotalQuantity'];
}
Yasar Khalid
  • 71
  • 2
  • 10
  • MAY BE SOME RECORD ARE DUPLICATED AND DUE TO `$total[$row['ItemCode']]` THE NEW RECORD OF SAME ID IS REPLACINGTO THE OLDER ONE – Alive to die - Anant Jan 07 '17 at 07:37
  • @anant the `($row->num_rows;)` i also tried the count variable it is also bringing me 175 results, this is weird as it only happened this time is it a bug? – Yasar Khalid Jan 07 '17 at 07:40

1 Answers1

1

It seems like some records have same 'ItemCode'.

So due to $row['ItemCode'] = ....

the new data of same ItemCode is replacing the older value again and again and due to that you are getting 175 record instead of 181

So you can do it like below:-

Either:-

$total[] = $row['TotalQuantity']; will give you like array(0=>array('name'=>'a',....)....)

Or

$total[$row['ItemCode']][] = $row['TotalQuantity']; //will  give you like array('item201'=>array(0=>array(),1=>array(),...),....) (an example)
Alive to die - Anant
  • 70,531
  • 10
  • 51
  • 98
  • the `$total` array is i guess not the issue as the results that are fetched were check by `$count = 0;` `while($row = mysqli_fetch_assoc($osquery)){` `$total[$row['ItemCode']] = $row['TotalQuantity'];` `$count++;` `} ` `echo $count;` this is also bringing 175, and num_rows is also brining 175 – Yasar Khalid Jan 07 '17 at 07:44
  • @YasarKhalid are you sure that same query with hardcoded value gave 181 record ? while same query with same values via php giving 175 record? – Alive to die - Anant Jan 07 '17 at 07:45
  • yes i am sure about it @anant only 5 items are missing and i am unable to figure it out why – Yasar Khalid Jan 07 '17 at 07:46
  • @YasarKhalid do one thing :- before `$osquery = mysqli_query($conn, $queryos);` write `echo $queryos;die;` and then copy that query and run it to PhpMyAdmin or sqlYOG and check how many records are coming – Alive to die - Anant Jan 07 '17 at 07:49
  • @YasarKhalid glad to help you .:):) – Alive to die - Anant Jan 07 '17 at 07:58