close ad
Install the LAtest Updates to Work with CC 2017 and CC 2018
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

suggestion for a better way of doing a multiple insert

Thread began 9/24/2011 9:28 am by info97301 | Last modified 10/03/2011 9:32 am by info97301 | 1298 views | 8 replies |

info97301

suggestion for a better way of doing a multiple insert

Hi

I have created a page where multiple 'post holders' can be added. However, there are approx 2,000 members from which to choose to assign to any post. I've put these in a drop-down list (which was the process I've used successfully for adding individual post-holders)

However, I'm not really surprised that it doesn't work - the server times out trying to retrieve all the data (presmably, 2000 members * 20 officer posts requires a lot of processing).

Instead of a repeated menu of members, can anyne suggest a better way of doing it?

Many thanks in anticipation.

Chris

Sign in to reply to this post

CraigRBeta Tester

Saw there were no replies to this.

Not quite sure what you are trying to do, can you show your database structure/upload a page/provide a link ? to help explain further ?

Thanks

Sign in to reply to this post

info97301

Hi Craig

Thanks for the interest.

It's only on localhost at the moment, however, I will try to explain;

Currently, I have a page to 'add officers' - there are 20 'posts', such as treasurer, secretary, xxx cttee chairman, yyy cttee chairman, etc.

On the existing page, I select the appropriate year, post and post-holder - from about 2,000 members. As the list of members is a drop-down menu, as you start to type the surname the menu jumps to the first corresponding match, which makes trawling through 2000 members much easier! When the post is added, the form returns the user to the same page to add the next officer.

I would like to have a multiple insert page, where you select the appropriate year at the top of the form and the 20 available posts are displayed in the first column - with a corresponding drop-down list for each potential post holder.

This is where I run into trouble - the multiple insert form appears to be created properly, with all the repeat selections in place - but when I try to test the page, the computer gives up, presumably because it needs to populate drop-down lists for 20 posts with 2000 members.

I wondered if there was a way that I could maybe have the recordset and start typing the surname of the post-holder without the need for a repeated drop-down menu, as this is what appears to be causing the problem. An array of some sort? Don't know!!

I'm using a MySQL database. the appropriate tables are:

years (YrID int, YearName nvarchar e.g. 2010-2011)
posts (PostID int, postname nvarchar)
members (memID int, surname, firstname etc)
postholders (phID int, phYrID int, phPostID int, phMemID int)

Any ideas much appreciated - whilst the single entry method works, a multiple entry page would simplify things for users.

Cheers

Chris

Sign in to reply to this post

CraigRBeta Tester

If i'm interpreting this correctly, you want a list of 20 posts down the page, with a select list next to each post, containing all of the members.

submitting the form will append 20 rows to the postholders table ?

If that is the case, then the insert should occur in a fraction of a second, and is not the cause of your timeout.

to test, i reproduced your database tables and a multiple insert page.

you will need to rename the fields and connection parameters, but it should give you what you need.

page is a bit ugly, with no formatting, but should get you going in the right direction

Attached Files
posts.zip
Sign in to reply to this post

info97301

Hi Craig

thanks - but it isn't the insert that's the problem, and I have created the page with the multiple inserts on - the page just won't load fully - and I can only think this is caused by having 20,000 members in a dropdown list repeated 20 times.

It works fine if there are only a few members (the page loads instantly if I restrict it to about 20 members), but on the full list the page loads slowly, with each post field being populated until it 'gives up', before the full list of available posts is completed.

Cheers

Chris

Sign in to reply to this post

CraigRBeta Tester

I see what you mean.

to satisfy my curiosity, i populated my members table with 3000 names and 20 posts and uploaded the page to my testing server.

it takes about 14 seconds to load the page.

when i checked the page size, it is around 3843 kb, which is pretty big.

I guess that with 2000 names, the load time will be shorter, but still quite slow,

Only thing I can think of is to split the posts into 2 or 3 groups, which will reduce the page size accordingly. not perfect, but quicker than 1 at a time

there may be an option to increase the timeout with your host, if this is an issue, but the page load will still be slow.

Sign in to reply to this post

info97301

Hi Craig

Many thanks for looking, anyway.

I had wondered if it was possible to avoid a drop-down list completely, and 'magically' have matching members names (from the recordset) appear as you started typing!

Cheers
Chris

Sign in to reply to this post

CraigRBeta Tester

there is a spry/javascript feature which creates an autosuggest rather like on google, but you still need the underlying recordset.

you may need to consider a different approach, such as building up an array using a recordset for members and one for posts, adding these items to the array, then storing the whole array in one go.

Don't know if this is of any real benefit though

Sign in to reply to this post

info97301

I think the auto suggest thing will be what's required - I can experiment with that now I know it's available.

many thanks, once more
Cheers
Chris

Sign in to reply to this post

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...