Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

A database issue.  Help appreciated. 
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Hi,

I have created a custom script which pull some information out of the database about orders in a custom way to generate a packing list (csv).

Anyway, the specifics of that are unimportant - the script has been working fine for a few days, but suddenly some orders are not appearing, and I have isolated the problem down to the fact that on certain occasions, no data is created in the “sales_flat_quote_item” table about the order.  (I use this table in mysql query to extract information).

Does anyone have any idea WHY this table would not be updated, or what circumstances would cause this?  Guest checkout perhaps? 

I am missing information in my file now and I dont know how to go about resolving this. 

I can post my SQL command if that helps ?.

Thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

This is my SQL Query....

$itemquery =     "SELECT sales_flat_order_item.order_id AS orderid,
                        sales_flat_order_item.product_type,
                        sales_flat_order_item.quote_item_id,
                        sales_flat_order_item.parent_item_id AS parentid,
                        sales_flat_order_item.sku AS itemcode,
                        sales_flat_order_item.name AS itemname,
                        sales_flat_order_item.qty_ordered AS qty_ordered,
                        sales_flat_order_item.qty_shipped AS qty_shipped,
                        sales_flat_quote_address.email AS email,
                        sales_flat_quote_address.quote_id AS quote_id,
                        sales_flat_quote_address.prefix AS title,
                        sales_flat_quote_address.firstname AS firstname,
                        sales_flat_quote_address.lastname AS lastname,
                        sales_flat_quote_address.street AS address,
                        sales_flat_quote_address.city AS city,
                        sales_flat_quote_address.region AS region,
                        sales_flat_quote_address.country_id AS country,
                        sales_flat_quote_address.postcode AS postcode,
                        sales_flat_quote_address.telephone AS telephone,
                        sales_flat_quote_address.shipping_description AS shipping_instructions
                        FROM sales_flat_order_item
                        INNER JOIN sales_flat_quote_item
                        ON sales_flat_order_item.quote_item_id = sales_flat_quote_item.item_id
                        INNER JOIN sales_flat_quote_address
                        ON sales_flat_quote_item.quote_id = sales_flat_quote_address.quote_id
                        WHERE sales_flat_order_item.order_id = 
$orderid
                        AND sales_flat_quote_address.address_type = 'shipping' 
                        AND sales_flat_order_item.product_type <> 'configurable'
                        AND sales_flat_order_item.qty_shipped < sales_flat_order_item.qty_ordered
 
Magento Community Magento Community
Magento Community
Magento Community
 
mzentrale1
Guru
 
Total Posts:  731
Joined:  2007-12-06
Stuttgart, Germany
 

Hi,

why are you usinge the quote tables? your scripts take affects during the checkout? A quote is only “up to date” within the checkout. After the customer placed the order the quote is converted to a order and is only available for reports in backend and not updated any more.
You should use the order for your purpose, not the quote.

Cheers

Stefan

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Yeah its not ideal, but the results during testing have been just as effective.

Basically I cant figure out where/how else to get the name, address etc of the customer easily.  I am sure its in the database elsewhere, but as we allow guest and user checkout it didnt seem as clearly obvious as this method.

Can you suggest how else this might be achieved?

Thanks for you reply btw.  its appreciated.

 
Magento Community Magento Community
Magento Community
Magento Community
 
mzentrale1
Guru
 
Total Posts:  731
Joined:  2007-12-06
Stuttgart, Germany
 

Hi,

take a look at the phpdoc - exp. Mage_Sales Module. Within order model there are methods to get invoice and shipment address.

F.e.

$order Mage::getModel('sales/order')->load($orderId);
$billing $order->getBillingAddress();
echo 
$billing->getCity();
//or
$shipping $order->getShippingAddress();
echo 
$shipping ->getCity();

Cheers

Stefan

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Thanks Stefan,

To be honest I am not very clued-in on developing magento modules or OO coding, I’m a top-down PHP coder and so i created a “standalone” script which pulls information from the database based on an orderid.  Obviously there are a few problems in this method.

I know I really need to invest some time into making this feature integrated with Magento though, but I have no idea where to start.  I take it your method above cant be used by including a few key magento files into my script at least smile So I’d have to invest time in going back to the drawing board to make it a proper module.

Thanks for the help anyway, I’d still like to know “why” certain quotes are not converted to sales, even when the sale has gone through though, there must be a reason for this to occur.  A setting in the cart or a condition that would cause this anomoly to present itself.  In theory there should be a quote for every single order - and so my method, as crude as it is should kinda work.

C

 
Magento Community Magento Community
Magento Community
Magento Community
 
mzentrale1
Guru
 
Total Posts:  731
Joined:  2007-12-06
Stuttgart, Germany
 

Hi,

there could be many quotes by customers. F.e.  a customer adds products to cart, checkout and didn’t set the order, so theres a quote in your table.

If you want to use magento models in your own script you can use this code - copy it to the root of magento:

require_once MAGENTO.'/app/Mage.php';
Mage::app();
//$orderId is a order id - numeric choose an order in admin and copy the order_id
$order Mage::getModel('sales/order')->load($orderId);
$billing $order->getBillingAddress();
echo 
$billing->getCity();
//or
$shipping $order->getShippingAddress();
echo 
$shipping ->getCity();

You can fetch attributes with getter functions.

Hope that helps a bit

cheers

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Zentrale, thanks so much for this, works like a charm smile You mentioned a phpdoc earlier in your posts, is that where I would find all the available methods available to me?  Where would I find that, been searching but cant find it.

Thanks again mate, saved a lot of hassle!!

C

 
Magento Community Magento Community
Magento Community
Magento Community
 
mzentrale1
Guru
 
Total Posts:  731
Joined:  2007-12-06
Stuttgart, Germany
 

Hi,

phpdoc -> http://docs.magentocommerce.com/.

Cheers

 
Magento Community Magento Community
Magento Community
Magento Community
 
IrishC
Sr. Member
 
Avatar
Total Posts:  89
Joined:  2008-01-29
 

Hey mate, sorry to bother you again…

I’ve made a little head way in getting some of the values I need, and as I go through this process, I realise that in theory I should be able to almost drop most of my over complicated SQL query in favour of extracting information from objects as above (just by inputting an order number). However, even after looking at that documentation, I am really unclear as to “how” I get the various fields I want.  There doesnt seem to be a defined list of methods I can use after I’ve created an object with Mage::getModel(’sales/order’)->load($orderid); command.

For instance, I’d have thought “echo $shipping ->getEmail();” would have returned the customer email but it doesnt.

I guess whilst I am also at it I might aswell also use this “proper” way to get the item details (sku, quantity, name) etc, but as before, I don quite understand how to nagivate through that documentation to workout which classes and methods are available to me.

Thanks again.

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