Which one? PHP or MySQL

[dak888]

I'm pretty new to this but I'll try to explain my situation and what I'm trying to accomplish.

The scenerio:
I've built a query that will return customer order information including name, address, products ordered, etc.... Works great and returns the results I want.

The problem: If a product has multiple options (ie. color, size, etc...) my query returns a new row for each item option which could create a lot of confusion for the eventual end user which is not the desired result. I need a way for either MySQL or PHP (on the output page) to recognize that multiple options belong to one item and to display them on the same row.

Now each item option can be associated to the item through a unique itemID/optionID but I just can't figure out how to make them display on one row.

Is this something I can accomplish with MySQL or do I need to somehow format it with PHP when it's output on the page?

Any direction as to the best approach would be appreciated.

Thanks,

DaK

[studiokraft]

Hello,

Can you describe your MySQL database structure in a little more detail?

It sounds like you are using a structure similar to OSCommerce, which stores basic product information in one table, and product options (color, size, etc.) in another table. Order information and products on each order are also stored in separate tables, as well as the options chosen for each product for each order. It can get overwhelming.

Without knowing the overall structure, I could recommend that you look into the "SELECT DISTINCT" and "GROUP BY" features of MySQL, which may give you the results that you are after.

For OSCommerce, the best and most reliable way for reporting and order display is to pull basic information about the order from the DB first and then pull the supplemental information (products, product options) from the DB second using PHP. Therefore the answer to your question would be "Both".

[dak888]

LOL, both....that figures I guess. Yes, it does sound as if the database structure is similar to OSCommerce. The query spans across 5 different tables to get all the required information. Items for the order are in one table "ORDER_ITEMS" while the individual item options are in the "ORDER_OPTIONS" table.

Perhaps displaying the query will help shed some light:

SELECT ORDER_INFO.InvoiceNumber, ORDER_INFO.OrderID, ORDER_SHIP_INFO.SOrderID, ORDER_ITEMS.IOrderID, ORDER_PASSTHROUGH.POrderID, ORDER_SHIP_INFO.SShipName, ORDER_SHIP_INFO.SStreet1, ORDER_SHIP_INFO.SStreet2, ORDER_SHIP_INFO.SCity, ORDER_SHIP_INFO.SStateOrProvince, ORDER_SHIP_INFO.SPostalCode, ORDER_SHIP_INFO.SCountry, ORDER_SHIP_INFO.ShipMethod, ORDER_ITEMS.IFullSKU, ORDER_ITEMS.IDescription, ORDER_ITEMS.IQuantity, ORDER_ITEMS.ItemID, ORDER_OPTIONS.OItemID, ORDER_OPTIONS.OOptionName, ORDER_OPTIONS.OOptionValue, ORDER_PASSTHROUGH.PPassValue, ORDER_OPTIONS.OOptionID
FROM ((((ORDER_INFO
LEFT JOIN ORDER_SHIP_INFO ON ORDER_SHIP_INFO.SOrderID=ORDER_INFO.OrderID)
LEFT JOIN ORDER_ITEMS ON ORDER_ITEMS.IOrderID=ORDER_SHIP_INFO.SOrderID)
LEFT JOIN ORDER_PASSTHROUGH ON ORDER_PASSTHROUGH.POrderID=ORDER_ITEMS.IOrderID)
LEFT JOIN ORDER_OPTIONS ON ORDER_OPTIONS.OItemID=ORDER_ITEMS.ItemID)
WHERE ORDER_INFO.InvoiceNumber='orderNumber'

Anyways, I'll read more about the SELECT DISTINCT and GROUP BY features.

Thanks,

DaK

[steve0]

I only quickly looked at the query..

A question I had..
Are your product options (size, color, etc) all in one table?
Is there a way to differentiate the options by type?

If there was.. then you could always JOIN the ORDER_OPTIONS
multiple times as aliases to create the columns for the options.

[dak888]

Well, there are actually two options tables but I only use one in this query. The first options table holds options for all the products on the site. The second options table ORDER_OPTIONS only contains the options selected by customers that placed orders. So the answer to you question is yes, technically.

ORDER_OPTIONS.OItemID=ORDER_ITEMS.ItemID matches the correct option to its item. In the case of an item that has multiple options, they will all share the same ID number. So I'll get several rows of the same sku, description, etc that are all for the same item, just different options in the last 2 columns (option name and option value).

I was thinking that perhaps an if/else in a do/while loop with PHP might help me in this situation. (if there are multiple instances of xID then write the table this way, else write it this way...)I've written plenty of these in VBA but have not attempted one in PHP yet...if it's even the right direction to go.

DaK

[DrTandem1]

If you need a data base to use with PHP, also use MySQL. PHP and MySQL are not either/or, they work together. If you are simply trying to add options to a base selection, you don't need a database. You simply register the variables for the session from page to page.

Using logic (if/else) in PHP, it should be fairly easy to populate fields through a number of choices. If for some reason you need to use the data on a return visit, then you do need a database. However, if you are simply transferring data to a shopping cart or a form, you do not.

[dak888]

I understand that PHP and MySQL work together. The point of my title is where is the best place to implement what I'm trying to accomplish. Is there some sql command that I'm missing that will accomplish what I want, or do I "need" to use PHP to format the output?

That's the point of the title. I'm going to experiment with if/else in PHP unless someone has a better suggestion.

Thanks,

DaK

[jganders]

First of all, I'd recommend using aliases for tables and formatting the SQL.
You will find it much more readable and easier to add new columns, etc..
I was an Oracle Corp consultant for three years but have been focusing
on mySQL the last 3 years.
This would be my suggestion for the reformatted SQL you posted.

SELECT ord.InvoiceNumber,
ord.OrderID,
ship.SOrderID,
itm.IOrderID,
pass.POrderID,
ship.SShipName,
ship.SStreet1,
ship.SStreet2,
ship.SCity,
ship.SStateOrProvince,
ship.SPostalCode,
ship.SCountry,
ship.ShipMethod,
itm.IFullSKU,
itm.IDescription,
itm.IQuantity,
itm.ItemID,
Opts.OItemID,
Opts.OOptionName,
Opts.OOptionValue,
pass.PPassValue,
Opts.OOptionID
FROM ((((
ORDER_INFO ord
LEFT JOIN ORDER_SHIP_INFO ship ON ship.SOrderID=ord.OrderID)
LEFT JOIN ORDER_ITEMS itm ON itm.IOrderID=ship.SOrderID)
LEFT JOIN ORDER_PASSTHROUGH pass ON pass.POrderID=itm.IOrderID)
LEFT JOIN ORDER_OPTIONS opts ON Opts.OItemID=itm.ItemID)
WHERE ord.InvoiceNumber='orderNumber'

As for getting the multiple options in one row. mySQL has the
GROUP_CONCAT function. Check it out. This function will concatenate
all the diff options into a comma delim field in the result.
Without seeing an ERD, or at least the DDL, for your database it's hard for me to comment on whether your SQL will work for the purpose you've stated.
For instance, ord.Orderid is probably a field in order_items and
order_options, maybe other tables. So why are you doing a left join from
order_options on order_item, unless order_options is really order_item_options?
What about having 4 nested joins? I've never seen this.
I suspect your query, even if it works, if inefficient, will slow down as your database
grows.

[DrTandem1]

If all he wants to do is post results of a form into something he can easily read, he doesn't need a database.

[Orion]

the display is in html... so it depends on what html code the php script is using to markup the results from the db querie.

if you're displaying in <input> or <select> fields you need to use either a) CSS or b) tables to allow those elements to display in a browser side by side.

[jganders]

yup. if he only has 5 items and 3 customers he prolly doesnt need a silly old database. but you guys are missing the point. this is a database discussion not an html discussion. dak888 does not have an html display problem. He is trying to construct an efficient SQL SELECT that will display multiple available options. If the database relationships are setup correctly, when new options are added to his product line, he wont have to change any html! The query will take care of it all! A carefully planned database will totally minimize or eliminate page maintenance when the product lines change!

[dak888]

Hello all,

The database is already built by our third party shopping cart software. I'm just making a query to display some information we need to extract from it. I'm don't understand why some are getting so up tight about a database being involved. The information is all there, shouldn't I use it??? Maybe I'm missing something...

Also, since I'm new to both PHP and MySql, my boss was kind enough to purchase the Adobe Developers tools to help me out. I used the query builder in the tool set to build the query so if it's inefficient, I"m afraid I'm oblivious to it.

Jganders, I'll read a bit more about the Group_Concat.

Thanks,

DaK

[dak888]

Jganders, Order.id is technically in every table but it's named differently in each table (I didn't set this up, lol).
Order.id = SOrder.id = POrder.id = IOrder.id = etc... They are all the same information but named differently in each table. That's how I associated the information from each table to make sure I was pulling the correct info for each order.

Forgive me as I'm a bit ignorant to all this.

Thanks,

DaK

[jganders]

Ok. This is the way I see it.
SHIP is a child of ORDER but rows may not be present for OrderID because the order may not have shipped. You need the outer join cuz SHIP rows may not be present.
PASS is a child of ORDER but rows may not be present for OrderID so you need the outer join.
ITEM is a child of ORDER and *will* exist so you just need an implicit inner join.
OPTS is a child of ITEM. If options exists they need an inner join. Options not chosen
for the item must not be displayed. Therefore, an outer join is inappropriate.

SELECT ord.OrderID,
       ship.SShipName,
       ship.SStreet1,
       ship.SStreet2,
       ship.SCity,
       ship.SStateOrProvince,
       ship.SPostalCode,
       ship.SCountry,
       ship.ShipMethod,
       itm.IFullSKU,
       itm.IDescription,
       itm.IQuantity,
       itm.ItemID,
       group_concat(Opts.OOptionName) option_names
  FROM ORDER_INFO ord
       LEFT JOIN ORDER_SHIP_INFO ship 
         ON ship.SOrderID=ord.OrderID     
       LEFT JOIN ORDER_PASSTHROUGH pass 
         ON pass.POrderID=itm.IOrderID,
       ORDER_ITEMS itm,
       ORDER_OPTIONS opts
 WHERE ord.OrderID = itm.IOrderID
   AND itm.ItemID = Opts.OItemID 
 GROUP BY 
       ord.OrderID,
       ship.SShipName,
       ship.SStreet1,
       ship.SStreet2,
       ship.SCity,
       ship.SStateOrProvince,
       ship.SPostalCode,
       ship.SCountry,
       ship.ShipMethod,
       itm.IFullSKU,
       itm.IDescription,
       itm.IQuantity,
       itm.ItemID

The 'option_names' column result should be a comma delim list of selected options for each order/item.
Note that you dont need the order ID duplicated 4 times. Just grab it once.
See how the Opts.OOptionName is the only column not in the GROUP BY? All the other columns will be the same value for a given OrderID and ItemId. Because of this you will only get them once.

[dak888]

Hey jganders,

You have solved the problem with the Group_Concat. Took me a bit to wrap my head around it but here is how the ending query looked:

SELECT ORDER_INFO.InvoiceNumber,
ORDER_PASSTHROUGH.POrderID,
ORDER_SHIP_INFO.SShipName,
ORDER_SHIP_INFO.SStreet1,
ORDER_SHIP_INFO.SStreet2,
ORDER_SHIP_INFO.SCity,
ORDER_SHIP_INFO.SStateOrProvince,
ORDER_SHIP_INFO.SPostalCode,
ORDER_SHIP_INFO.SCountry,
ORDER_SHIP_INFO.ShipMethod,
ORDER_ITEMS.IFullSKU,
ORDER_ITEMS.IDescription,
ORDER_ITEMS.IQuantity,
ORDER_PASSTHROUGH.PPassValue,
ORDER_OPTIONS.OItemID,
GROUP_CONCAT(OOptionValue)
FROM ((((ORDER_INFO LEFT JOIN ORDER_SHIP_INFO ON ORDER_SHIP_INFO.SOrderID=ORDER_INFO.OrderID)
LEFT JOIN ORDER_ITEMS ON ORDER_ITEMS.IOrderID=ORDER_SHIP_INFO.SOrderID) LEFT JOIN ORDER_PASSTHROUGH ON ORDER_PASSTHROUGH.POrderID=ORDER_ITEMS.IOrderID)
LEFT JOIN ORDER_OPTIONS ON ORDER_OPTIONS.OItemID=ORDER_ITEMS.ItemID)
WHERE ORDER_INFO.InvoiceNumber=orderNumber
GROUP BY OItemID


Man, I really appreciate you pointing me to that. Works perfectly and is exactly what I was looking for!!!! I ended up being able to clean a bunch of crap out that I didn't need. Thank you for pointing me in that direction as well. I'm getting it, slowly, but I'm getting it.

DaK

[jganders]

I would have absolutely predicted that you'd get an SQL error by not putting every field except GROUP_CONCAT(OOptionValue) into the GROUP BY clause. You only have OItemID there now. But, hey, if it works, I wont argue.

[dak888]

LOL. I dunno, all I can say is that it works.

Thanks again to everybody that provided input.

DaK

[comp1]

Hi friend

There is only one difference between MySQL and PHP versions:-

The mysql.com download is based on the latest MySQL Client libraries. No other differences exist. Both downloads contain the same PHP functions.