Magento Forum

Query Magento from outside Magento
 
Camulos
Jr. Member
 
Total Posts:  10
Joined:  2008-07-15
 

Magento works really nice on my server, but I would like to know if a certain product is in the store, without starting up the magento-shop.
Now I have a self-written framework that I would like to communicate with Magento. Because parts of my framework will display some products, it would be nice that the information it the same as the one in Magento. Here the names are the same, the things I would like to know are: Items at stock and price per item.

So this sums up to 2 questions:
- is there is way to query magento for information about an article in the store and let this information be returned ?
- Does anybody know where to find the overview of database construction for Magento? (No, I am not affraid for some nice SQL-query writing)

 
Magento Community Magento Community
Magento Community
Magento Community
 
thE_iNviNciblE
Sr. Member
 
Avatar
Total Posts:  220
Joined:  2008-01-09
Oldenburg
 

hello,

try the new SOAP API ...
Magento Core API

i can’t send you an ERM, this picture is to big…

try the embarcadero ER/Studio

 
Magento Community Magento Community
Magento Community
Magento Community
 
RPGShop
Sr. Member
 
Total Posts:  143
Joined:  2008-06-16
 

Be prepared to be scared… very scared ... smile I hacked my .NET program that used to work on the front of OSCOMMERSE to now work with MAGENTO but it’s a real pain dealing with the model of storing variables of different types into their own databases…

For example… the SQL to search for a product based on name or SKU and return the quantity on hand would be:

$sql "select p.entity_id as products_id, p.sku, s45.value as products_name, s.qty as on_hand
FROM catalog_product_entity p, catalog_product_entity_varchar s45, cataloginventory_stock_item s
WHERE s45.entity_id = p.entity_id and s45.entity_type_id = 4 and s45.attribute_id = 45 and s45.store_id = 0
and s.product_id = s45.entity_id
and s45.value like '%" 
$name "%'
and p.sku like '%" 
$sku "%'";

Humm… That’s actually not bad compared to most queries I had to build. Usually there are a lot of adtional sXX tables I have to join just to pull all the data into one place.

For example here’s a scary one to get order info:

private const string SELECT_GET_ORDER_INFO =
          
"SELECT " +
          
"IFNULL(s.created_at, '') as date_purchased  " +
          
",s.customer_id as customers_id  " +
          
",IFNULL(shipping_amount, 0) as shipping_cost  " +
          
",IFNULL(CONCAT( CONCAT(s331.value, ' '), s332.value), '') as  delivery_name  " +
          
",IFNULL(s334.value, '') as delivery_street_address   " +
          
",IFNULL(s333.value, '') as delivery_suburb   " +
          
",IFNULL(s335.value, '') as delivery_city   " +
          
",IFNULL(s338.value, '') as delivery_postcode   " +
          
",IFNULL(s336.value, '') as delivery_state   " +
          
",IFNULL(s339.value, '') as delivery_country   " +
          
",IFNULL(CONCAT( CONCAT(s331.value, ' '), s332.value), '') as customers_name  " +
          
",IFNULL(s334.value, '') as customers_street_address   " +
          
",IFNULL(s333.value, '') as customers_suburb   " +
          
",IFNULL(s335.value, '') as customers_city   " +
          
",IFNULL(s338.value, '') as customers_postcode   " +
          
",IFNULL(s336.value, '') as customers_state   " +
          
",IFNULL(s339.value, '') as customers_country   " +
          
",IFNULL(s340.value, '') as customers_telephone  " +
          
",c.email as customers_email_address   " +
          
",0 as affiliates_id   " +
          
",IFNULL(s235.value, '') as orders_status  " +
          
",IFNULL(s.updated_at, '') as orders_date_finished   " +
          
",'' as payment_method   " +
          
",0 as credit_applied   " +
          
"FROM " +
          
" sales_order_int s250, " +
          
" sales_order_varchar s235, " +
          
" sales_order s " +
          
"  LEFT OUTER JOIN customer_entity c ON c.entity_id = s.customer_id, " +
          
" sales_order_entity so " +
          
"  LEFT OUTER JOIN sales_order_entity_varchar s340 ON s340.entity_type_id = 12 and s340.attribute_id = 340 and s340.entity_id = so.entity_id   " +
          
" LEFT OUTER JOIN sales_order_entity_varchar s331 ON s331.entity_type_id = 12 and s331.attribute_id = 331 and s331.entity_id = so.entity_id  " +
          
" LEFT OUTER JOIN sales_order_entity_varchar s332 ON s332.entity_type_id = 12 and s332.attribute_id = 332 and s332.entity_id = so.entity_id  " +
          
" LEFT OUTER JOIN sales_order_entity_varchar s333 ON s333.entity_type_id = 12 and s333.attribute_id = 333 and s333.entity_id = so.entity_id  " +
          
" LEFT OUTER JOIN sales_order_entity_varchar s334 ON s334.entity_type_id = 12 and s334.attribute_id = 334 and s334.entity_id = so.entity_id  " +
          
" LEFT OUTER JOIN sales_order_entity_varchar s335 ON s335.entity_type_id = 12 and s335.attribute_id = 335 and s335.entity_id = so.entity_id  " +
          
" LEFT OUTER JOIN sales_order_entity_varchar s336 ON s336.entity_type_id = 12 and s336.attribute_id = 336 and s336.entity_id = so.entity_id  " +
          
" LEFT OUTER JOIN sales_order_entity_varchar s338 ON s338.entity_type_id = 12 and s338.attribute_id = 338 and s338.entity_id = so.entity_id  " +
          
" LEFT OUTER JOIN sales_order_entity_varchar s339 ON s339.entity_type_id = 12 and s339.attribute_id = 339 and s339.entity_id = so.entity_id  " +
          
" LEFT OUTER JOIN sales_order_entity_varchar s341 ON s341.entity_type_id = 12 and s341.attribute_id = 341 and s341.entity_id = so.entity_id  " +
          
"WHERE  " +
          
" s235.entity_type_id = 11 and s235.attribute_id = 235 and s235.entity_id = s.entity_id  " +
          
"AND  s250.entity_type_id = 11 and s250.attribute_id = 250 and s250.entity_id = s.entity_id   " +
          
"AND so.entity_id = s250.value  " +
          
"AND so.parent_id = s.entity_id " ;

Instead of looking at some large diagram for finding out where things are, I usually just search in the “eva_attribute” table for the field name and look in the appropriate table for the product or sales order or customer that you’re dealing with.

James

 
Magento Community Magento Community
Magento Community
Magento Community
 
nicolas46
Sr. Member
 
Total Posts:  210
Joined:  2008-04-09
Toulouse
 

The database diagram :
http://www.magentocommerce.com/wiki/magento_database_diagram

And welcome on board !

 
Magento Community Magento Community
Magento Community
Magento Community
 
thE_iNviNciblE
Sr. Member
 
Avatar
Total Posts:  220
Joined:  2008-01-09
Oldenburg
 

@nicolas46: quite cool… thx alot grin

it’s a bit small ...... hmmm

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top