Magento 1 Sql query for product name, sku and quantity

Question

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.

Answer 1

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;
            }
          }
        }
      }  
      ?>

Answer 2

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