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