Select option from custom table

I have two custom tables, one for players and another for the honours they have achieved in a club. I am trying to use a list box using a field from the players table with a list of players to select the one I want to use to create a record in the honours table. I have a script in the resource that limits which players can be selected for which honour depending upon the player’s gender.

I am using formit and migxloopcollection as a select tool. However, when I try to create an honours record the name does not get selected, a 0 is input into the playername field.

The migxloopcollection select tplOption was suggested by halftrainedharry some months ago in another topic.

The resource code is.

[[!FormIt?
   &hooks=`formit2db,redirect`
   &preHooks=`db2formit`
   &prefix=`agc_`
   &packagename=`honours`
   &classname=`honposition`
   &tablename=`positionagc`
   &redirectTo=`5`
   &store=`1`
   &validate=`nospam:blank,
      playername:required,
      position:required,
      yearwhen:required`
]]

<h2>Add Position Form</h2>
[[!+fi.successMessage]]
[[!+fi.validation_error_message:notempty=`<p>[[!+fi.validation_error_message]]</p>`]]

<form name="positiondet" action="[[~[[*id]]]]" method="post" class="form">
    <input type="hidden" name="nospam" value="" />

<select name="position" id="position" value="[[!+fi.position]]" onchange="showHonsForm()">
            <option value="0">Select position...</option>
            <option value="1" [[!+fi.position:FormItIsSelected=`Mens Captain`]]>Mens Captain</option>
            <option value="2" [[!+fi.position:FormItIsSelected=`Ladies Captain`]]>Ladies Captain</option>
            <option value="3" [[!+fi.position:FormItIsSelected=`President`]]>President</option>
            <option value="4" [[!+fi.position:FormItIsSelected=`Vice President`]]>Vice President</option>
</select>
<div id="male" style="display:none">
<select name="playername" id="playername" value="[[!+fi.playername]]">
<option value="">Select member ...</option>
[[!migxLoopCollection?
&prefix=`agc_`
&usecustomprefix=`agc_`
&packageName=`honours`
&classname=`player`
&selectfields=`fullname,gender`
&tpl=`tplOption`
&sortConfig=`[{"sortby":"surname"}]`
&where=`{"gender":"1"}`
&selected=`[[!+fi.playername]]`
]]
</select>
</div>
<div id="female" style="display:none">
<select name="playername" id="playername" value="[[!+fi.playername]]">
<option value="">Select member ...</option>
[[!migxLoopCollection?
&prefix=`agc_`
&usecustomprefix=`agc_`
&packageName=`honours`
&classname=`player`
&selectfields=`fullname,gender`
&tpl=`tplOption`
&where=`{"gender":"2"}`
&selected=`[[!+fi.playername]]`
]]
</select>
</div>
<div id="bothgender" style="display:none">
<select name="playername" id="playername" value="selected">
<option value="0">Select member ...</option>
[[!migxLoopCollection?
&prefix=`agc_`
&usecustomprefix=`agc_`
&packageName=`honours`
&classname=`player`
&selectfields=`fullname,gender`
&tpl=`tplOption`
&selected=`[[!+fi.playername]]`
]]
</select>
</div>
    <label for="yearwhen">
        Year Captain: 
        <span class="error">[[!+fi.error.yearwhen]]</span>
    </label>
    <input style="width: 100px;" type="int" name="yearwhen" id="yearwhen" value="[[!+fi.yearwhen]]" />
    <br class="clear" />

    <div class="form-buttons">
        <input type="submit" value="Enter captain details" />
    </div>
</form>

<script type="text/javascript">
    function showHonsForm() {
        var selopt = document.getElementById("position").value;
        if (selopt == 1) {
            document.getElementById("male").style.display = "block";
            document.getElementById("female").style.display = "none";
            document.getElementById("bothgender").style.display = "none";
        }
        if (selopt == 2) {
            document.getElementById("female").style.display = "block";
            document.getElementById("male").style.display = "none";
            document.getElementById("bothgender").style.display = "none";
        }
        if (selopt == 3||selopt == 4) {
            document.getElementById("bothgender").style.display = "block";
            document.getElementById("male").style.display = "none";
            document.getElementById("female").style.display = "none";
        }
        if (selopt == "0") {
            document.getElementById("male").style.display = "none";
            document.getElementById("female").style.display = "none";
            document.getElementById("bothgender").style.display = "none";
        }
    }
</script>

The tplOption is:

<option value="[[+fullname]]" [[+fullname:eq=`[[+property.selected]]`:then=` selected="[[+fullname]]"`]]>[[+fullname]]</option>

and the schema is:

<?xml version="1.0" encoding="UTF-8"?>
<model package="honours" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="player" table="playerdetails" extends="xPDOSimpleObject">
		<field key="timeset" dbtype="varchar" phptype="string" precision="25" null="false" default="" />
		<field key="title" dbtype="varchar" phptype="string" precision="10" null="false" default="" />
		<field key="initial1" dbtype="varchar" phptype="string" precision="10" null="false" default="" />
		<field key="initial2" dbtype="varchar" phptype="string" precision="10" null="false" default="" />
		<field key="initial3" dbtype="varchar" phptype="string" precision="10" null="false" default="" />
		<field key="surname" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
		<field key="fullname" dbtype="varchar" phptype="string" precision="50" null="false" default="" />
		<field key="gender" dbtype="int" phptype="string" precision="1" null="false" default="" />
                <composite alias="captain" class="captain" local="fullname" foreign="playername" cardinality="many"
        owner="local"/>
	</object>
        <object class="honposition" table="positionagc" extends="xPDOSimpleObject">
		<field key="playername" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
                <field key="position" dbtype="int" phptype="string" precision="1" null="false" default="" />
		<field key="yearwhen" dbtype="int" phptype="integer" precision="10" null="false" default="0" />
 <aggregate alias="player" class="player" local="playername" foreign="fullname" cardinality="one" owner="foreign" />
	</object>
         <object class="competition" table="competitionagc" extends="xPDOSimpleObject">
		<field key="compname" dbtype="varchar" phptype="string" precision="100" null="false" default="" />
                <field key="comptype" dbtype="int" phptype="string" precision="1" null="false" default="" />
		<field key="compdesc" dbtype="text" phptype="string" precision="500" null="false" default="0" />
	</object>
</model>

I cannot understand why a 0 is entered into the playername field instead of the “fullname” from the player table

I think the problem is, that you have 3 select-fields in your form with the same name

<select name="playername" id="playername" value="">

playername has the correct value if you make a selection in the last of these 3 select-tags. Otherwise it gets the value 0, because this is the value of the last <select> when the first option is selected.

<option value="0">Select member ...</option>

Thanks halftrainedharry

Do I need to have a different “name” and “id” for each select-field?

if you set the id - attribute at an html - element, each one has to be unique.
https://www.w3schools.com/html/html_id.asp#:~:text=The%20id%20attribute%20specifies%20a,element%20with%20the%20specific%20id.

For the names of your selects you could use playername1, playername2 or put them into an array with playername[]

Thanks Bruno.
I shall look into it.

This is one possibility. But then you have to adjust the formit2db-hook to make it work.

Another possibility is to have only one select for all the cases and then change the options with javascript when the value of position changes.

Just as a side note:
When you define a relationship between database tables, you usually use the primary key (and not a field like “playername”).

<object class="honposition" table="positionagc" extends="xPDOSimpleObject">
	...
	<field key="player_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" />
	<aggregate alias="player" class="player" local="player_id" foreign="id" cardinality="one" owner="foreign" />
</object>

This allows you for example to change the name in one table without breaking the relation or to discern between two people with the same name. To display the names that relate to the honours, you then just have to join the two database tables.

Thanks for the advice halftrainedharry.
I shall look into this.
I shall be looking at reorganising my form layout to select the player before the “honour”. The “honour” to be selected depends upon the gender of the player. How can I incorporate the player selection with getting the player’s gender, or has this to be done in two stages with snippets and a query?

Regarding the above reply of mine, can gender be selected using the tplOption that I am using?

You could use a data attribute.
Add data-gender="[[+gender]]" to your chunk tplOption

<option value="[[+fullname]]" data-gender="[[+gender]]" [[+fullname:eq=`[[+property.selected]]`:then=` selected="[[+fullname]]"`]]>[[+fullname]]</option>

When you select a name, you should be able to read the gender in javascript with code like this:

With jQuery

$('#playername').change(function(){
	console.log("Gender = " + $(this).find(':selected').data('gender'));
});

With plain JS

function logGender(){
	var player_select = document.getElementById("playername");
	var selected_option = player_select.options[player_select.selectedIndex];
	console.log("Gender = " + selected_option.getAttribute('data-gender'));
}

Thanks for the feedback halftrainedharry. I shall certainly try it and report back when I get the time. Thanks again.

I am making progress and getting a bit further. However, the plain JS solution did not appear to work, I think due to the “selectedIndex”. Could this be because the selections are being generated by migxloopcollection?

Could a “myselect” be used in the code instead?

function copy() {
document.getElementById(“label”).innerHTML = document.getElementById(“mySelect”).value
}

I’m confused! Do you even have an element with the ID “myselect”? Does your <select> still have the ID “playername”?

<select name="playername" id="playername">

Maybe you can study this example code for HTMLSelectElement.selectedIndex.

Sorry halftrainedharry. My mistake, the plain JS solution works fine. I had not put the correct id to the select.
Thanks once again.

I am back on the project and my next problem is editing a record from one of the custom tables.
I want to use a list box to select the record to be edited. I am trying to use the suggestion from “halftrainedharry” that I have used to select a record in another part of the project. I am selecting a record based on a person’s “fullname” field and using that in a migsloopcollection snippet together with a tplMemberEditOption as follows:

The migxloopcollection is:

<select name="selfullname" id="selfullname" onchange="logPlayer()">
<option value="0">Select member ...</option>
[[!migxLoopCollection?
&prefix=`agc_`
&usecustomprefix=`agc_`
&packageName=`honours`
&classname=`Playerdetails`
&selectfields=`fullname`
&tpl=`tplMemberEditOption`
&sortConfig=`[{"sortby":"surname"}]`
]]
</select>

The tplMemberEditOption is:

<option value="[[+fullname]]" data-fullname="[[+fullname]]" [[+fullname:eq=`[[+property.selected]]`:then=` selected="[[+fullname]]"`]]>[[+fullname]]</option>

The select is working fine.

I am then using the following script to extract the “fullname” from the select. The script is:

<script>
function logPlayer() {
	var player_select = document.getElementById("setfullname");
	var selected_option = player_select.options[player_select.selectedIndex];
        var sel_player = selected_option.getAttribute('data-fullname');
	console.log("Player name = " + selected_option.getAttribute('data-fullname'));
        return sel_player;
}
</script>

My problem is how to use the FormIt with the db2formit and the &where. When I use the following the corresponding record is not showing in the form.

[[!FormIt? 
&hooks=`formit2db,redirect` 
&preHooks=`db2formit`
&prefix=`agc_`
&packagename=`honours`
&tablename=`playerdetails`
&classname=`Playerdetails`
&fieldname=`fullname`
&where=`{"fullname":"sel_player"}`
&validate=`nospam:blank,
      title:required,
      surname:required,
      initial1:maxlength=^1^,
      initial1:required,
      gender:required`
]]

After the select I am using a chunk with the form details as follows:

[[!+fi.successMessage]]
[[!+fi.validation_error_message:notempty=`<p>[[!+fi.validation_error_message]]</p>`]]

<form name="playerdet" action="[[~[[*id]]]]" method="post" class="form">
    <input type="hidden" name="nospam" value="" />
    <label for="title">
        Title:
        <span class="error">[[!+fi.error.title]]</span>
    </label>
        <select name="title" value="[[!+fi.title]]">
            <option value="">Select title...</option>
            <option value="Mr" [[!+fi.title:FormItIsSelected=`Mr`]]>Mr</option>
            <option value="Mrs" [[!+fi.title:FormItIsSelected=`Mrs`]]>Mrs</option>
            <option value="Ms" [[!+fi.title:FormItIsSelected=`Ms`]]>Ms</option>
            <option value="Rev" [[!+fi.title:FormItIsSelected=`Rev`]]>Rev</option>
        </select>
<!--    <input style="width: 40px" type="varchar" name="title" id="title" value="[[!+fi.title]]"/>-->
    <label for="initial1">
        First Initial
        <span class="error">[[!+fi.error.initial1]]</span>
    </label>
    <input style="width: 30px; text-transform:uppercase;" type="varchar" name="initial1" size=1 onKeyup="autotab(this, document.playerdet.initial2)" maxlength=1 id="initial1" oninput="this.value = this.value.toUpperCase()" value="[[!+fi.initial1:ucase]]" />
    <label for="initial2">
        Second Initial
        <span class="error">[[!+fi.error.initial2]]</span>
    </label>
    <input style="width: 30px; text-transform:uppercase;" type="varchar" name="initial2" size=1 onKeyup="autotab(this, document.playerdet.initial3)" maxlength=1 id="initial2" oninput="this.value = this.value.toUpperCase()" value="[[!+fi.initial2:ucase]]" />
    <label for="initial3">
        Third Initial
        <span class="error">[[!+fi.error.initial3]]</span>
    </label>
    <input style="width: 30px; text-transform:uppercase;" type="varchar" name="initial3" id="initial3" size=1 oninput="this.value = this.value.toUpperCase()" value="[[!+fi.initial3:ucase]]" />
    <label for="surname">
        Surname:
        <span class="error">[[!+fi.error.surname]]</span>
    </label>
    <input type="varchar" name="surname" id="surname" value="[[!+fi.surname]]" />
    <label for="gender">
        Gender:
        <span class="error">[[!+fi.error.gender]]</span>
    </label>
        <select name="gender" value="[[!+fi.gender]]">
            <option value="">Select gender...</option>
            <option value="Male" [[!+fi.gender:FormItIsSelected=`Male`]]>Male</option>
            <option value="Female" [[!+fi.gender:FormItIsSelected=`Female`]]>Female</option>
        </select>
     <span class="error">[[!+fi.error.fullname]]</span>
    <input type="hidden" name="fullname" value="[[!+fi.fullname]]" />

    <br class="clear" />

    <div class="form-buttons">
        <input type="submit" name="submit" id="submit" value="Update member" />

    </div>
</form>

Sorry about all the detail but I don’t know where my approach is correct and whether I should be doing something before the FormIt call.

Any help would be appreciated.

First of all. When editing database records use the primary key (the column id) and not the column fullname.
For example in your <option>, set the value to the id. You also don’t need a data-attribute when it is set to the same value as the value-attribute.

<option value="[[+id]]" [[+id:eq=`[[+property.selected]]`:then=` selected="selected"`]]>[[+fullname]]</option>

You call logPlayer() when the value of the <select> changes, but you then just log the name and return an element. You have to either reload the whole page with the selected player-id as a parameter or make an Ajax-request. The preHook db2formit has to run again to select the correct row from the database.

Thanks halftrainedharry. Your response is as quick as ever.

I shall now look into how to reload the page and/or learn how to do an Ajax-request to trigger the db2formit.

I’ll keep you posted.