create recordset from input value

Hi there - I have an items table and a decor table. There will be multiple items that use certain decor sheets. On my items_Insert.php page I have a field called ItemDecorID. If this is populated I want certain fields underneath it to be automatically populated from the decor table, so I will need to bind a recordset to the page containing the decor data WHERE DecorID = the value input into the ItemDecorID field. Can you tell me how to do this?

This is not a proper way to use relational data

The Deco values should be stored only in the decor table, they should not be duplicated in the item table.

instead, you should use a join query to return the related information the decor table.

see this page for more details on SQL joins:
http://www.w3schools.com/sql/sql_join.asp

Hi Jason, I understand your reasoning, however in this instance I want to be able to create an item from the decor sheet data. I have items in the items table that are one off items and items that I want to be able to generate from the decor sheet without having to re enter all that information. Once the information is received I want to be able to manually adjust it so the item entry needs to be autonomous, however being able to initially draw the data across is a big time saver. I've done it before using a 2 step entry process with an insert page then an update page that uses the ID inserted on the insert page to create the recordset that populates the update fields on the update page, however it would be nice if it could be achieved on the same page dynamically.

The whole idea behind using a Relational Database like MySQL is to not have redundant data.

there is no need to create a recordset and insert the redundant data into the items table when you can just use a Join Query when displaying the item info that pulls the data from the decor table.

the way this would work is to creat the decour table and the items table:

items:
itemID - primary key
itemName
itemPrice
itemDecorID - relates to the decors.decorID
etc....

decors
decorID - primary key
decorName
etc....


when you insert a new item, store the decorID value for the decor record that relates to the decors table.

then return the related information on the items display page using a join query:

SELCT *
FROM items
INNER JOIN decor ON items.itemDecorID = decors.decorID

this way the decor values are only defined once and can be used by as many different products as is needed.

it's certainly possible to do it the way you describe, but it is a terrible use of a relational database, and not a good practice.


read up on Data Normalization.

Ok here's the thing. I've literally spent all day today working on this. In an ideal world my items table is as follows:
ItemID
ItemName
ItemDecorID (left join to decor table)
ItemSupplierID (inner join to contact table) *
ItemCurrencyID (inner join to currency table) *
ItemCostPrice *
ItemRetailPrice *
ItemProjectID (inner join to projects table)
etc etc. 35 fields in total may with keys linking to other tables

The items with an asterix should ideally pull across the data from the decor table IF the ItemDecorID does not equal 0. I've set this up already and created a detail page using if statements that generates the correct information based on the entry in the ItemDecorID field. However on my results page I ran into problems. The supplier uses an ID in both the items table and the decor table, the decor table uses a join to connect to the items table, displaying the SupplierName for items that use the decor sheet in a repeat region is causing me problems. Any suggestions to get round this greatfully received. The next problem is that I have the facility to add multiple items from my results table to an order using the eCart Get from Recordset behaviour. This will presumably take an awful lot of hand coding to get this to work if I'm pulling the data from 2 different tables dependant on the decorID entry. Again happy to give it a go if you can point me in the right direction. Otherwise I thought the only and simplest option would be to populate the relevant fields in the items table from the decor sheet. I thought I could also create a multiple update page that would allow you to update the data should the decor sheet change - again not ideal so suggestions welcome.

  displaying the SupplierName for items that use the decor sheet in a repeat region is causing me problems. Any suggestions to get round this greatfully received.  

what type of problems? I need more specific questions in order to give assistance.

Could be that using a nested recordset would be best:
http://www.webassist.com/tutorials/Display-nested-recordsets

Adding the item to the cart should not take to much hand coding, create the joined recordset, then use the add to cart from recordset behavior and bind the cart columns to the corresponding column from the recordset.

Sorry I'll try and be more specific. I am using my items_Results page a bit like a shopping cart. Each item has a check box next to it which enables me to place multiple items on an order - all very clever thanks to you kind people! I want the item to display the following info in the repeat region:
ItemImage ItemName ItemSupplier ItemPrice etc etc
As mentioned I have prepared an if statement as follows: IF ItemDecorID == 0 retrieve ItemImage ItemName ItemSupplierID etc ELSE IF ItemDecorID != 0 retrieve DecorImage DecorName DecorSupplierID etc (obviously this isn't the syntax used).
All fine as it retrieves this information from the Items recordset which has a join on it to the decor table. I have an inner join that joins my contacts table to my items table via the ItemSupplierID, so the ItemSupplierName is also available in the items table, however because the decor table is also joined to the items table via a join through the ItemDecorID I only have the DecorSupplierID available and not the DecorSuppilerName which I need to display the data correctly. This is where my problem started yesterday - if you could give me an answer to that it would certainly help me on my way.
Thanks for your help.

The answer is to use joined queries like originally suggested, or to use a nested recordset:

http://www.webassist.com/tutorials/Display-nested-recordsets

Hi Jason, I'd be really grateful if could help me with my SQL query. I have this:
SELECT * FROM items INNER JOIN contacts ON items.ItemSupplierID=contacts.ContactID INNER JOIN decor ON items.ItemDecorID=decor.DecorID INNER JOIN contacts ON decor.DecorSupplierID=contacts.ContactID
which returns the error message:
MySQL Error: 1066 Not unique table/alias: 'contacts'.
Sorry I know there must be a way around this, but I can't seem to find it. I'm not using DataBridge, still haven't migrated from the old DataAssist. If this isn't an easy solution I'll book in for a premium support ticket. Thanks.

you are joining the contacts table twice.


you need to use an alias for the second one:
INNER JOIN contacts as C2 ON decor.DecorSupplierID=C2.ContactID