0

this query is written for sale report...but when i tried to run on phpmyadmin it gives an error of "subquery return more than 1 row"..what will be the alternative or changings will be required?

 <?php 
            if(isset($_POST['search_category'])){  
              $records ="SELECT DISTINCT category , 
      (SELECT COUNT(products.name) FROM products where category_id=categories.id) AS total_products,
      ( 
          SELECT SUM(quantity)  FROM productstock a
          LEFT JOIN products b ON a.product_id=b.id
          LEFT JOIN categories c ON b.category_id=c.id
          where c.deleted=0 
          GROUP BY category_id
      ) AS available_stock, 
      SUM(product_qty*orignalCost) AS SaleWise_cost,
      SUM(product_qty*saleprice) AS SaleWise_price,
      SUM(product_qty*saleprice) AS total_sale , 
      SUM((product_qty*saleprice)-(product_qty*orignalCost)) AS profit
               FROM categories
      INNER JOIN products ON  categories.id =  products.category_id
      INNER JOIN sales ON  sales.product_id = products.id
      INNER JOIN productstock ON  productstock.product_id = products.id
               WHERE categories.deleted=0
               GROUP BY category_id;";  
                                      
              while($data = mysqli_fetch_array($result)) 
              {         
          ?>  
            <tr>
            <td><?php echo $data['category']; ?></td> 
            <td><?php echo $data['total_products']; ?></td>   
            <td><?php echo $data['available_stock']; ?></td>   
            <td><?php echo $data['SaleWise_cost']; ?></td>  
            <td><?php echo $data['SaleWise_price']; ?></td>  
            <td><?php echo $data['total_sale']; ?></td>  
            <td><?php echo $data['profit']; ?></td>  
            </tr> 
            <?php
              }
               
           }
            ?>
           </table>
Bakhtawar Ashraf
  • 13
  • 1
  • 6
  • Why are you using `mysqli_multi_query`? **Never use `mysqli_multi_query()`!!!** – Dharman Aug 13 '21 at 12:17
  • i tried to make only one query but it gives an error so the only solution left behind is mysqli_multi_query... – Bakhtawar Ashraf Aug 13 '21 at 12:28
  • No, `mysqli_multi_query` is never the solution, it is the source of problems. What was the error you got? – Dharman Aug 13 '21 at 12:29
  • actually i want to show the both queries data in one table... – Bakhtawar Ashraf Aug 13 '21 at 12:31
  • I know that. This is why there exists `UNION`. What was the error that prevented you from using `UNION`? – Dharman Aug 13 '21 at 12:31
  • as the first query fetching data for all colums except available_stock...2nd query fetching data for available_stock.....but on result for first query it shows undefined index Available _stock and for 2nd query it shows the value for available_stock and for remaining colums it shows undefined index – Bakhtawar Ashraf Aug 13 '21 at 12:35
  • How can you have undefined index? Please show the code where you do it in a single query. – Dharman Aug 13 '21 at 12:37
  • can we add the 2nd query into the first query assigning alias as available_stock? i tried with this query but it gives an error "subquery return more than 1 row" – Bakhtawar Ashraf Aug 13 '21 at 12:46
  • Please create a proper [mcve]. As this question has nothing to do with PHP by the looks of it, you can remove all PHP and HTML code and show the minimal example of the query, your data and the expected outcome. – Dharman Aug 13 '21 at 12:49

0 Answers0