Item > Category help

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
WorldCom
Forum Commoner
Posts: 45
Joined: Sat Jun 24, 2006 8:14 am
Location: Ontario, Canada

Item > Category help

Post by WorldCom »

Hi all,
I have a database that consists of items, description etc.
For each item I need to have one or more payment processor for it.
eg Credit Card, Check, Money Order etc.

The payment processors need to be user configured so they can have as many of few as necessary + the ability to delete them. I currently have 2 tables set up for this.
Table 1: Items
| Unique ID | Name | ..... other info |

Table 2: Processors
| Unique ID | Name |

I've already done the coding for both the Items and Processors adding/editing/deleting.

I just can't get my head around this. I need to have the Processor ID in the item table somehow. My thoughts were to add like 10 columns to Items and then populate them with the Processor IDs ... but .. this would limit me to 10 only. Not that there may be more than 10 processors, but say there is a particular item that say accepted India Rubbles (I think thats right :)) ... so they may need to add Rubles to the processors yet only use it for one item. I wanted the flexibility of having virtually unlimited Processors.

I was also thinking of have a 3rd table that links Item ID > Processor ID. But is this the best way or is there maybe something more efficient?
Thanks for any help/ideas/ appreciated :)
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: Item > Category help

Post by mattpointblank »

Your latter idea is right - a third table.

Say your database structure is like this:

table: products
- productID
- productName
- productDescription

table: processors
- processorID
- processorName

You now need a third table:

table: productProcessors
- productProcessorID
- productID
- processorID

So a product can then have as many processors as it needs - you could have data in the latter table stored like:

productProcessorID: 1
productID: 456
processorID: 1

productProcessorID: 2
productID: 456
processorID: 8

productProcessorID: 3
productID: 456
processorID: 78523

etc.

You can then use mysql JOIN to attach the relevant rows to your tables when querying. Simple!
WorldCom
Forum Commoner
Posts: 45
Joined: Sat Jun 24, 2006 8:14 am
Location: Ontario, Canada

Re: Item > Category help

Post by WorldCom »

Thanks a lot.
Have you got a quick example of the join function I could use??
This would help me a lot :)
mattpointblank
Forum Contributor
Posts: 304
Joined: Tue Dec 23, 2008 6:29 am

Re: Item > Category help

Post by mattpointblank »

SELECT * FROM products
INNER JOIN productProcessors ON products.productID = productProcessors.productID


Syntax is like:

INNER JOIN name_of_table_to_join ON row_from_main_table = corresponding_row_from_joined_table

There are different types of join - Read this for more info: http://www.tizag.com/mysqlTutorial/mysqljoins.php
WorldCom
Forum Commoner
Posts: 45
Joined: Sat Jun 24, 2006 8:14 am
Location: Ontario, Canada

Re: Item > Category help

Post by WorldCom »

Thanks again for the help. I never did use the join function since I had to loop through the processors later in the script. But after battling with checkboxes, it works :)
Post Reply