mySQL query to retrive products and its attributes from customers' basket?

Hi there

I need some help with mySQL

I had an order in online shop where money had been received (its lying on a bank account), but the order has not reached the system. So, I have no idea what customer ordered. It happend twice already

What I need is a mySQL query I can process to retrive the data from phpMyAdmin. This is what I have so far:

*SELECT cb.customers_id, c.customers_firstname, c.customers_lastname, ab.entry_street_address, ab.entry_suburb, ab.entry_postcode, ab.entry_city, c.customers_email_address, c.customers_telephone, p.products_id, p.products_model, cb.customers_basket_quantity, p.products_price
FROM customers_basket cb, customers c, products p, address_book ab
WHERE cb.customers_id = XXXX
AND c.customers_id = cb.customers_id
AND c.customers_default_address_id = ab.address_book_id
AND p.products_id = SUBSTRING_INDEX(cb.products_id, ':', 1)
*

and it works perfectly. However one products is with attributes (colours). The question is: how to change the query so it retrives product attributes as well?

any help?

I am still trying to figure it out by myself, but hint or help could do a lot...

anybody?

You have a few options:

a) continue hacking away at queries (perhaps consider joining the query to the other database table customers_basket_attributes)
or
b) install the very simple Encrypted Master Password addon and simply login as the customer and look at their cart. Maybe even go so far as to actually complete the order for them so that the order shows up in your admin area ... That would be much more efficient in the long run.

Thanks for the reply DrByte

I'll try to use first hint first.

Is this Encrypted Master Password addon safe to use? It could be very helpfull to have master password, as this kind of "lost order situation" propably repeat in the future.

Cheers

It's as safe as the password you select.

I think I will stick to phpMyAdmin (although, I haven't figured out how to query the attributes, yet), since I have no idea how to backup a shop. I don't want to risk yet and suffer unempoyment :D

If anyone has an idea what this query could be? This is what I have so far:

SELECT cb.customers_id, c.customers_firstname, c.customers_lastname, ab.entry_street_address, ab.entry_suburb, ab.entry_postcode, ab.entry_city, c.customers_email_address, c.customers_telephone, p.products_id, p.products_model, cb.customers_basket_quantity, p.products_price, pd.products_id, pd.products_name

FROM customers_basket cb, customers c, products p, address_book ab, products_description pd

WHERE cb.customers_id = 299
AND c.customers_id = cb.customers_id
AND cb.products_id = pd.products_id
AND c.customers_default_address_id = ab.address_book_id
AND p.products_id = SUBSTRING_INDEX(cb.products_id, ':', 1)

Right. I got it at the end. The query I wrote in my earlier posts works fine but doesn't give back any product attributes, ie colours, sizes

Here is the one that will give you attributes as well:

**
SELECT cb.customers_id, c.customers_firstname, c.customers_lastname, ab.entry_street_address, ab.entry_suburb, ab.entry_postcode, ab.entry_city, c.customers_email_address, c.customers_telephone, p.products_id, p.products_model, cb.customers_basket_quantity, p.products_price, pd.products_id, pd.products_name, cba.products_id, cba.products_options_value_id, pov.products_options_values_id, pov.products_options_values_name

FROM customers_basket cb, customers c, products p, address_book ab, products_description pd, customers_basket_attributes cba, products_options_values pov

WHERE cb.customers_id = 299
AND c.customers_id = cb.customers_id
AND cb.products_id = pd.products_id
AND cba.products_id = cb.products_id
AND cba.products_options_value_id = pov.products_options_values_id
AND c.customers_default_address_id = ab.address_book_id
AND p.products_id = SUBSTRING_INDEX(cb.products_id, ':', 1)
**

Maby it's not very elegant but it works

safeg:

since I have no idea how to backup a shop. I don't want to risk yet and suffer unempoyment
With all due respect, you owe it to yourself to figure out that step FIRST.
If you're running a business with NO BACKUPS, you are just asking for trouble. Be sure to tell your employer that they have no backups if the site were to go down. I'm sure they'll give you lots of time to learn these important basics.

DrByte

I know how it sounds. I was supposed to work for my employer for a short period of time, only for building up online products. From time to time I had been given simple tasks and rarely more difficult ones. I was supposed to be working for 4 weeks only but I've been here for a year. They've decided recently that I could actually be very usefull, and gave me more tasks to do. That's why I've been asking those questions around. They used to hire a company for hosting and maintaining website.

I am sure they have a backup. I hope so. The knowledge about such things as backup I need for myself, as if I am going to install anything in the future ie add on, I want to do the backup myself, so I my do it in peace of mind.

And you are right. Thanks for advice