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

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

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

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

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

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.

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

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

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