Hi all

I have a table storing details of events which are submitted by the visitors. I have about 20 keywords stored in a separate table and need the visitor to tick whichever keywords are relevant to the event. The keywords themselves will likely be added to over time.

I can display a table with repeat region to show all the keywords with a checkbox next to each one but I'd like some advice on how I should store the answers. Should I add a field for each keyword into the structure of the events table and if an event is added I'd need to add another field? Ideally I'd like it to be completely dynamic so adding a record to the keywords table will add the checkbox to the submit page and also store the answer somehow into the events table.

My thinking was to use some kind of binary (1,2,4,8,16, etc) and add together all the checked box values and store the total in the table but I think this is too complicated.

Any thoughts would be greatly appreciated.

Many thanks

Jon

Just for reference:
<ul><li><b>What <i>Server Model</i> language?</b> (PHP, ASP, .NET, CFML, JSP)</li><li><b>What database?</b> (Access, MySQL, MS SQL Server,...)</li></ul>

Also, if you are using PHP/MySQL, have you looked at MySQL's <b>SET</b> data type? It might get your brain flowin'. I haven't used it for heavy production, but looked very promising.

Another way is using a table for keywords, a table of users, and a third (middle) table that would allow <i>many-to-many</i> relationships. Basically multiple rows entered per users. But I know you don't want to over-complicate things, and you already have some work done with it so I don't want to put you on a side path.

Hi Chris

Many thanks for your reply. I'm using PHP with MySQL (PHP_MySQL - NOT PHAKT). I'll have a look at the SET command. Your idea about the many-to-many relationships sounds like a good idea. I'm not too far down the road as what I've done so far is just standard stuff.

Any other thoughts you may have would be appreciated.

Thanks again

Jon

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>...using PHP with MySQL (PHP_MySQL - NOT PHAKT). I'll have a look at the SET command. Your idea about the many-to-many relationships sounds like a good idea. I'm not too far down the road as what I've done so far is just standard stuff.<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
As for the multi-table solution, it'll keep your database <i>Normalized</i>. The middle table maybe just be <i>UserID, KeywordID, DateTimeStamp</i>. I use a timestamp for almost everything that someone adds/updates/removes...it's one of those O.C.D. things.

Hi again Chris

Thanks for that. I think I'll go for the solution you've suggested. Could you just give me a clearer idea of how to achieve this? I think I understand but not entirely. Sorry, I really do appreciate your assistance.

Cheers, Jon.

Hi Jon

The idea of the middle table is to reduce the amount of repeated and redundant data in your database, so rather than have 20 fields added into your events table (most of which would be &lt;NULL&gt; ) you have a linking table to store the relationship between each event and keyword.

The linking table would need its own Primary Key and also the PK's from both the events and keywords tables, but as Chris says its a good idea to timestamp each record, you may also want to store the visitors ID (if they have one) so that you know more about the relationship... who created it, who amended it and when etc.

Check out this article from microsoft, it's authors example nicely illustrates the point about <i>many-to-many</i> relationships
msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxcondatanormalization.asp

Live the life you love
Love the life you live

Simon

Edited by - ganseki on 26 Jan 2005 01:34:56

Hi Simon and Chris

Thanks for all your advice. The article at Microsoft.com looks good, I'll go through it in a moment.

Is there a straight forward technique for achieving this in DWMX? I already have the code to insert the record but now I'll need to insert many records into the middle recordset as you've suggested. What's the best way of doing this without, hopefully, messing up the code generated by the insert record behaviour? This is where I'm a little stuck.

Thanks again

Jon

Instead of using the built in Insert Record you've got Commands (from the same menu) which will let you write the Insert statement yourself. Then you can say which bit of data goes into which table and so Insert into more than 1 table. You will need to create the relationship between the records and that will mean that you'll need to retrieve the Primary key of the record you've just inserted so that you can post it into the middle table. I know there's an extension that can do that using ASP and Access (InsertWithIdent) by George Petrov - but I don't know if there's a PHP alternative... Chris may well know?

Personally I would use Stored Procedures(but then I'm a SQL Server type), separating the presentational stuff from the backend workings - this should give you performance increases too as you'll be making fewer trips to the database (version 5 of MySql supports Stored Procedures)

Live the life you love
Love the life you live

Simon

Hi Simon

Thanks for the tips. I don't have MySQL 5 on the server so stored procedures are not an option. And I'm still using Dreamweaver MX, not MX 2004. I have 2004 but just haven't installed it yet as I'm always working on sites and don't want any problems.

Can you confirm that 'Commands' are available in MX as I can't find them or was this a new feature of MX2004? Maybe now is the time to install it?

Cheers

Jon

Edited by - jstanton on 26 Jan 2005 15:37:32

Edited by - jstanton on 26 Jan 2005 15:37:50

Ahh, my bad PHP doesn't have that option <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

I checked in ASP and Commands can be found in the Application panel, Bindings... click on the + symbol and just underneath Recordset (Query) is Command (Stored Procedure)


Live the life you love
Love the life you live

Simon

I assume your users are already registered into the database*. Assuming the users data is entered already, and you have a keywords table ready (generates checkboxes)...
<ul>
<li>1.) <b>Checkboxes must share an array name</b> as their ID/Name attributes (ie. <i>keywords[]</i>...notice the empty brackets)</li>
<li>2.) <b>Checkboxes must have values attribute be their ID's</b> from the database table that generates them.</li>
<li>3.) You can use the INSERT Record SB (Server Behavior) to start the INSERT code, but it will get edited, so it won't show up in your SB window.** (If you know PHP by hand, then your code could be smaller for that simple SQL statement.</li>
<li>4.) <b>generate a conditional loop</b>, in PHP, on the page that will process the form (same page or other), might be a <b><i>foreach</i></b>, to loop through the <i>keywords[]</i> array and run the (new) INSERT sql statement each loop: so it will INSERT the UserID, and KeywordID</li>
<li>5.) Then redirect or carry on as normal.</li>
</ul>


*PHP's <b>mysql_insert_id()</b> function (us3.php.net/manual/en/function.mysql-insert-id.php) shows what line of code should come after an INSERT statement to auto-grab the last (auto) record ID, if needed.

**SBB, Server Behavior Builder can help you make your own custom SB to show up in the window, but that's reading for another day.



I really hope this helps. I will be making a request for a Premium Article be made to cover all this, since this is (one of) the most common requests/questions. It should be an FAQ, but I think that would be a long FAQ. <img src=../images/dmxzone/forum/icon_smile_tongue.gif border=0 align=middle> <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>



As for <b>extension(s)</b>, the most famous would be InterAKT's <i>ImpAKT & NeXTensio</i> pack (www.interaktonline.com/Products/Dreamweaver-Extensions/ImpAKT/Features/).

Hi Chris

Excellent post and one that I will find very useful. I think I've actually managed to accomplish what I require but by a completely different technique (you hold your head in your hands!). I've used

while(list($key,$value) = each($HTTP_POST_VARS)) {

to get all the post variables. I've then separated out the ones from the tick boxes and put them into a single variable separated by commas. I've also got the words using the same technique so my record now contains two fields, one for keyword ID and one for the keyword itself (needed for visitors to search for it).

I'm sure it's not the best way of doing it but it works. I've now got 3,6,11 in one field and shop,friend,cafe in another.

I've now got to write the code (hopefully pretty easy) to do the reverse, ie. take '3,6,11' and turn it into $string[0] = 3, $string[1] = 6, $string[2] = 11.

Is there a PHP command to do this or an easy way that you know?

Thanks again. A premium article showing how to do the many-to-many method you've mentioned would be very useful as I'd really like to do that in preference to my method it's just that it's working and my head is hurting!

Cheers, Jon.

It doesn't sound bad.

I don't know of a PHP command offhand, check <b>www.php.net</b> and search the <b>functions</b>, it will really blow your mind at all the good user comments, they usually will have the right code/answer to pulling something like that off.

<img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>