PHP Parsed to a HTML Table (for layout), customised MySQL query

[j.smith1981]

This is quite a complex script in all but I am a tad bit stuck with, understand somewhat whats going on but I thought I would ask about this.

 

We are using the x cart system from qualitive team (excuse the spelling), as their existing orders page isnt what we require at all.

 

I have reformatted their Picking slips into an invoice like layout, this is quite a complex SQL query but here goes:

 

There's obviously joins on each part.

 

xcart_orders (with orderid being the primary key).

 

The pick list then requires all customer details so there's a join on the customerid to the same column on:

 

xcart_customers with a WHERE statement: WHERE type='P' (still with me?)

 

There's another join, the problem is.

 

When I have the entire SQL.

 

using:

 

xcart_customers

xcart_order_details

xcart_additionalfield_values (for cost prices)

 

The problem is, if a user orders more than 1 item, say 2 different items.

 

I have done a standard query including all the required fields but when I run the PHP script it comes up with 2 pick lists, for the same order number if a customer has ordered more than 1 item in the same session id (at the same order).

 

So basically 2 pick sheets appear if 2 products have been ordered, the pick sheets have the same orderid.

 

Is there any good advice about including the 2 products (or how ever many a user orders), for including the multiple products rowed down on the same pick list?

 

Otherwise if a user was to order 20 items we'd get 20 pick list sheets with the same orderid, not exactly the most cost effective and timed system ever.

 

Can someone help me?

 

Regards,

Jeremy.

[j.smith1981]

Tried including this in the above post but to get an idea of what the SQL looks like, here's the following, renaming the columns in the SQL makes it somewhat easier to understand when putting it into the report format if you like for the guys in our warehouse to pick the stock

 

Here's the SQL:

 

SELECT CONCAT(DATE_FORMAT(FROM_UNIXTIME(A3.date), '%e/%c/%Y'), ' ' ,DATE_FORMAT(FROM_UNIXTIME(A3.date), '%H:%i')) AS order_date, -- Displays date and time in 24hr format

-- Order information
A1.orderid AS orderid,

A3.status AS status,

-- Profile

A5.login AS username,

A5.title AS title,

A5.b_firstname AS f_name,  -- Billing but using it as first name

A5.b_lastname AS l_name, -- Billing but using it as last name

-- Profile Billing
A5.b_address AS b_address,

A5.b_city AS b_city,

A5.b_zipcode AS b_pcode,

-- Profile Shipping
A5.s_address AS s_address,

A5.s_city AS s_city,

A5.s_zipcode AS s_pcode,

A5.phone AS phone,

A5.email AS email,


-- Products line
A4.value AS adventcode,

A1.product AS descr,

A1.amount AS qty,

-- Order notes
A3.customer_notes AS cust_notes


-- A1.product, A1.amount AS qty, DATE_FORMAT(FROM_UNIXTIME(A3.date), '%e/%c/%Y') AS order_date
FROM xcart_order_details A1
LEFT JOIN xcart_pricing A2 ON A1.productid = A2.productid
LEFT JOIN xcart_orders A3 ON A1.orderid = A3.orderid
LEFT JOIN xcart_extra_field_values A4 ON A1.productid = A4.productid
LEFT JOIN xcart_customers A5 ON A3.login = A5.login
WHERE A2.membershipid = '2' AND A3.status = 'P' AND  A4.fieldid = '3'
ORDER BY A5.login AND A1.orderid ASC

[j.smith1981]

Sorry just for further reading.

 

There's 2 basic components needed.

 

The first being the top (header I would call it), which includes the Customers Billing and Shipping information.

 

Then the products that are being ordered by that orderid (a mysql GROUPBY wouldnt work as it would put all orders by that customer together not what we want, if a user of the site was to order 1 item then another later on going through a processed status (WHERE status='P') it would put all orderids together.

 

What I need it to do is fill down rows with each product for that order id in the top (header section).

 

Then go down and select the cust_notes details so a user might want to put 'If no one answers the door please put parcel around the back'.

 

This bottom would appear at the bottom of course.

 

How is this acheivable?

[j.smith1981]

Would this be possible in a subquery perhaps?

 

Just racking my brain thinking of how to do this.