php/sql purchase order display

[atomicrabbit]

I'm coding something that will display purchase orders.

 

My database table looks like this:

table named 'orders' with the following columns:

- id

- po (purchase order number stored here)

- company name

- product (references a product id from products table)

- quantity

- paid (amount paid)

- date

 

I don't know which is better, in terms of how it's stored in the MySQL database:

 

a) should I create a new entry for each product that is part of a single purchase order. I.e.

ID   PO     COMPANY       PRODUCT     QUANTITY     PAID    DATE
1    24355  ABC Company   5           10           0.00    2008.09.04
2    24355  ABC Company   3           2            0.00    2008.09.04
3    35464  Acme Co.      1           100          50.00   2008.09.04

 

Then when I want to display a purchase order, just query the db for all entries with a specific purchase order number and list them

 

OR

 

b) each purchase order will only use 1 row and the products and quantities will be separated by commas

ID   PO     COMPANY       PRODUCT     QUANTITY     PAID    DATE
1    24355  ABC Company   5,3         10,2         0.00    2008.09.04
2    35464  Acme Co.      1           100          50.00   2008.09.04

 

then I just explode the products and quantities into their own arrays to output it. I guess if I use option b) I won't need the ID column

[Psycho]

Go with Option C

 

Create one table for purchase order data and a second table for the product data

 

PO Table would have fields such as:

id, PO #, Company, Paid, & date

 

Product table would have fields such as:

id, po_id, quantity

 

When yu have a many-to-one relationship you need to have separate tables and the data needs to be captures in the appropriate table. For example if you want to capture the cost for a product it would be captures in the product table. But, if youhave a PO cost for shipping or taxes it should be captures at the PO level.

 

You can then get any data you need by joining tables in your query. With the fields I had above if I needed to get the details of a purchase order I would run the following query

 

SELECT *

FROM purchase_orders

JOIN products on purchase_orders.id = products.po_id

 

 

[atomicrabbit]

well I can't name the table "products" because I already have a products table that holds all the products. So let's say I name it "product_orders"

 

so id in product_orders would equal the id from the actual products table where all the products are stored and po_id would be the id from the purchase_orders table? right?

 

now sorry if this is a stupid question, but I've never used JOIN before. What would that sql query return?

[atomicrabbit]

ok i figured the sql syntax out... thanks mjdamato.

[cooldude832]

this option C also gives you the ability to manage you products table against orders (for inventory levels)

 

Just don't do any inserting into the table until the order is finalized (keep stuff in the cart) 

 

Also the table might get a bit messy (overhead) because you are doing a ton of Insert, Update, Delete queries on it so watch how you write queries.

[Psycho]

so id in product_orders would equal the id from the actual products table where all the products are stored and po_id would be the id from the purchase_orders table? right?

 

You can do that first part (so id in product_orders would equal the id from the actual products table[/i]), but you will need to make sure that the id in product_orders is not unique.

 

As for JOINS - well that is the true power of databases. It can take some time to understand them and, even then, there will be times where you will scratch your head to figure out the right query in difficult situations. But, whatever you do, don't use looping queries as I see many people do because they don't understnad how to do JOINS. Looping queries (i.e. do one query to get the parent records and then loop through the results to run individual queries on another table using the parent info) are extreemly inefficient and a hugh overhead.

[cooldude832]

a good tip for learning how to JOIN properly is when you design your query think of what you want each "row" to be if you plan on having more than 1 row

 

In your case each "row" would probably be the individual items

 

So you want to try and GROUP BY products_purchased.id

 

example

<?php
$q = "SELECT
orders.PO as PO_Num,
orders.Date as PO_Date,
items.qty as quantity,
prodcuts.name as product_name

FROM
`orders`

LEFT JOIN `prodcuts_purchases` as items ON(items.PO = orders.PO)

LEFT JOIN `products`  ON(items.itemid = products.id)

WHERE orders.PO = '".mysql_real_escape_string($_GET['PO'])."'

GROUP BY items.id
";
?>

[atomicrabbit]

@cooldude832: I'm not making a shopping cart-like site. It is simply for a company's clients to view purchase orders. The purchase orders/invoices will be generated on their own P.O. software, then re-entered into this website manually so their clients can view it at any time. This is what they want at this point in time.

 

btw, that select statement is a monster . I'm going to read it after posting this thread and try to fully understand it. Why are you using left join? why not INNER JOIN? Based on what I read about inner, left and right join, the left and right join match even if the opposite is empty -- I don't fully understand it yet.

 

@mjdamato: yeah I know what you mean about looping queries. I don't do that because I rarely need to even without using join. But in one case in this project, one of the fields in the products table references an id of another table that holds static types of wood. In this case, I just load those values into a global array before anything is processed and then just use something like $typeArray[$row['type_id']]. This way, I don't have to re-query the database (even if it's just a join query), whenever I need the type.

 

EDIT: just a question about the JOIN statement... I notice that if I do a join with two tables that have an id field, it replaces the id field from the first table with the second table. Is the only way around this to select ONLY the fields that I need? i.e.

SELECT table1.*, table2.field2, table2.field3, table2.field4 FROM table1 ....

[cooldude832]

that tablename.* syntax is aviable still while joining however I prefer to select what  i need instead of everthing thus I have a fairly lengthy selection section.

 

 

As for LEFT RIGHT INNER or STRAIGHT it depends on the nature of the query which you should research.

 

 

[atomicrabbit]

well in this specific case, and when using join I need everything from one table and only specific fields from the second joined table.

[cooldude832]

well in this specific case, and when using join I need everything from one table and only specific fields from the second joined table.

 

Just watch out for field names duplicating in other tables being selected

 

use the select fieldname as Somethingelse to help

 

 

[atomicrabbit]

just curious... when uploading an image to my product page, is it better to upload to a folder on my server or store the image in the mysql database?

[cooldude832]

really up to you.

 

I've become an anti flat file person so I store nearly everything in MySQL now.

 

Just use proper methods and you will be fine

[atomicrabbit]

won't it make the database huge over time though?

[Psycho]

As he said it is your call. Personally I would not store images in the database, only the path.

[BlueSkyIS]

As he said it is your call. Personally I would not store images in the database, only the path.

 

Agreed.

[BlueSkyIS]

won't it make the database huge over time though?

 

yes. and if you lose the database you lose all of your images too. why anyone would store images in a database is beyond my comprehension.

[atomicrabbit]

that's what I prefer as well. It will keep the database smaller. Thanks.

[atomicrabbit]

yes. and if you lose the database you lose all of your images too. why anyone would store images in a database is beyond my comprehension.

just to be the devil's advocate, depending on the subject of the site, if you lose the data in the database and the images are stored in a folder on the server, you'll just have a bunch images with no information to use them with... so in that sense, it doesn't really matter if it's stored in the db or in a folder.

 

In my case the images are products and if I had the images stored in a folder and lost the db that linked the image paths to data, the data is MUCH more important than the images and the images could probably be easily replaced, but not the data.

 

But again, it will be harder to backup the mysql database with tons of images stored in it -- well, it would take more time at least.