Compound Search
Posted: Wed Jan 04, 2012 10:19 am
Hi all
I am a complete newbie to all things SQL and PHP so please forgive the ignorance. I found this website and have managed to setup a basic database on my Ubuntu server accordingly (great tutorial by the way). However, I am not sure whether or not my ultimate goal is feasible and if so how to achieve it.
The goal is to have a system that will allow for an easy search facility to locate all products that are assembled using similar constituent component parts.
For example, the basic structure would be that shown here:
Each product can have many components and each has their own relevent details. What I need is the ability to return all products where all entered component details match. The issue here is I need to be able to specify multiple components and only return products where all these components match.
Example: (forgive the pseudo code style of writing)
search criteria : SELECT Products WHERE ComponentA.PartNumber = T AND ComponentB.PartNumber = U etc
There is no specific number of components per search and so, I may want to list all products where only ComponentA.PartNumber = T or Component[A - G].PartNumber = [T - Z]
The form would ideally start with a single PartNumber to search for and have an "add new search criteria" button which would simply append additional PartNumber to the query. The Description and the PartNumber are related in that you would only ever see a set number of PartNumbers for a given Description. In this case, Part Numbers from a brand of IC, say Siemens, will differ from Philips. I had looked into valuelists to provide a predefined selection of Descriptions and so when performing the query, the criteria would need to reflect this - (Description = DescriptionA AND PartNumber = PartNumberA)
I am completely aware that the structure of the database I have chosen may not be appropriate and so am open to suggestions as to how I should rearrange the stored data.
All components can exist on their own but several combined together will make up the product. Once all the data has been entered into the database for all Components, I should then be able to see what products have similar components fitted and thus source these products for their individual components in a bid to repair others.
I suppose you could look at this as a sort of catalogue for spare parts. The ProductID field within the Component table is used to relate an individual component to a specific product. The result should be a list of products, displaying the Serial Numbers of the Products and the Version for each component matched.
If anyone knows of a way to implement this and whether or not I am going about it the right way, I would be very grateful.
Andy
I am a complete newbie to all things SQL and PHP so please forgive the ignorance. I found this website and have managed to setup a basic database on my Ubuntu server accordingly (great tutorial by the way). However, I am not sure whether or not my ultimate goal is feasible and if so how to achieve it.
The goal is to have a system that will allow for an easy search facility to locate all products that are assembled using similar constituent component parts.
For example, the basic structure would be that shown here:
Each product can have many components and each has their own relevent details. What I need is the ability to return all products where all entered component details match. The issue here is I need to be able to specify multiple components and only return products where all these components match.
Example: (forgive the pseudo code style of writing)
search criteria : SELECT Products WHERE ComponentA.PartNumber = T AND ComponentB.PartNumber = U etc
There is no specific number of components per search and so, I may want to list all products where only ComponentA.PartNumber = T or Component[A - G].PartNumber = [T - Z]
The form would ideally start with a single PartNumber to search for and have an "add new search criteria" button which would simply append additional PartNumber to the query. The Description and the PartNumber are related in that you would only ever see a set number of PartNumbers for a given Description. In this case, Part Numbers from a brand of IC, say Siemens, will differ from Philips. I had looked into valuelists to provide a predefined selection of Descriptions and so when performing the query, the criteria would need to reflect this - (Description = DescriptionA AND PartNumber = PartNumberA)
I am completely aware that the structure of the database I have chosen may not be appropriate and so am open to suggestions as to how I should rearrange the stored data.
All components can exist on their own but several combined together will make up the product. Once all the data has been entered into the database for all Components, I should then be able to see what products have similar components fitted and thus source these products for their individual components in a bid to repair others.
I suppose you could look at this as a sort of catalogue for spare parts. The ProductID field within the Component table is used to relate an individual component to a specific product. The result should be a list of products, displaying the Serial Numbers of the Products and the Version for each component matched.
If anyone knows of a way to implement this and whether or not I am going about it the right way, I would be very grateful.
Andy