0

I am using Magento 1. I want to fetch product names, SKUs, and stock quantity with SQL queries. If anyone knows please let me know. Thanks in advance.

2 Answers 2

0

This is query you can use and modify, here increment_id is the order no. or order id. Please try this query and modify accordingly.

$detail_query_2="SELECT 
a.product_id,a.weight,a.sku,a.name,a.qty_ordered,base_price,price FROM 
rpt_sales_order_item AS a INNER JOIN rpt_sales_order AS b ON a.order_id=b.entity_id 
AND b.increment_id = '$ids'";

Edit Adding more code so that you can edit it easily.

 <?php
      // $detail_query_2="SELECT a.product_id,a.weight,a.sku,a.name,a.qty_ordered,base_price,price FROM rpt_sales_order_item AS a WHERE a.order_id='$ids'";
      //"select distinct * from m24.rpt_sales_order_item as a inner join m24.rpt_sales_order as b on a.order_id=b.entity_id and b.increment_id = '000000164'
      $detail_query_2="SELECT a.product_id,a.weight,a.sku,a.name,a.qty_ordered,base_price,price FROM rpt_sales_order_item AS a INNER JOIN rpt_sales_order AS b ON a.order_id=b.entity_id AND b.increment_id = '$ids'";
      //echo $detail_query_2;exit;
      $sql_detail_2=mysqli_query($con,$detail_query_2) or die(mysqli_error($con));

      if(@mysqli_num_rows($sql_detail_2))
      { 
        $total_amount=0;
        $cnt2=@mysqli_num_rows($sql_detail_2);
        while($rt_details_2=mysqli_fetch_array($sql_detail_2,MYSQLI_ASSOC))
        {
          $ksb_var= $rt_details_2['product_id'];
          //echo "discount amount".$discount_amount;
          $ksb_query="SELECT CONCAT('https://cdn.raptorsupplies.com/pub/media/catalog/product/300x300',a.value) AS image FROM rpt_catalog_product_entity_media_gallery AS a INNER JOIN rpt_catalog_product_entity_media_gallery_value_to_entity AS b ON a.value_id=b.value_id WHERE b.entity_id='$ksb_var'";
          $ksb_sql_detail=mysqli_query($con,$ksb_query) or die(mysqli_error($con));
          if(@mysqli_num_rows($ksb_sql_detail))
          { 
            $ksb_cnt=@mysqli_num_rows($ksb_sql_detail);
            
            while($ksb_detail=mysqli_fetch_array($ksb_sql_detail,MYSQLI_ASSOC))
            {            
              #print_r($rt_details);exit;
              $sub_total=$rt_details_2['price']*$rt_details_2['qty_ordered'];
              echo '<div class="order-details">
              <div class="product-image"><img src="'.$ksb_detail['image'].'"></div>
              <div class="prouct-name">
              <h5>'.$rt_details_2['name'].'</h5>';
              //<span class="quantity">SKU: <span><strong>'.$rt_details_2['sku'].'</strong></span></span><br> 
              echo '<span class="quantity">Quantity: <span><strong>'.number_format($rt_details_2['qty_ordered']).'</strong></span></span><br>
              <span class="quantity"><strong>'.$currencySymbol.number_format($rt_details_2['price'],2).'</strong><span>/unit</span></span>';
              // <br>
              // <span class="quantity"><span>Subtotal:</span><strong> '.$currencySymbol.number_format($sub_total,2).'</strong></span>
              echo '</div>
              </div>';
              $total_amount += $sub_total;
            }
          }
        }
      }  
      ?>
2
  • Borther I want to fetch all products in magento 1 with name and sku
    – Yousaf
    May 7, 2021 at 18:08
  • I added more code, it have more sql queries, it may help. May 7, 2021 at 18:13
0

Here you go:

select e.sku, cpev.value as name, csi.qty from catalog_product_entity as e inner join catalog_product_entity_varchar as cpev on e.entity_id=cpev.entity_id inner join cataloginventory_stock_item as csi on e.entity_id=csi.product_id where cpev.attribute_id in (select attribute_id from eav_attribute where attribute_code='name' and entity_type_id=4);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.