close ad
WARNING: Do Not Install the DREAMWEAVER CC 2017 or 2018 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

create recordset from input value

Thread began 4/23/2014 3:11 pm by dallySP | Last modified 5/12/2014 6:43 am by dallySP | 392 views | 10 replies |

dallySP

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?

Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post

dallySP

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.

Sign in to reply to this post

Jason ByrnesWebAssist

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.

Sign in to reply to this post

dallySP

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.

Sign in to reply to this post

Jason ByrnesWebAssist

  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.

Sign in to reply to this post

dallySP

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.

Sign in to reply to this post

Jason ByrnesWebAssist

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

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

Sign in to reply to this post

dallySP

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.

Sign in to reply to this post

Jason ByrnesWebAssist

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

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...