Jump to content

shaddf

Members
  • Posts

    102
  • Joined

  • Last visited

shaddf's Achievements

Regular Member

Regular Member (3/5)

0

Reputation

  1. i have this table select MatchID,HomeScore,AwayScore from tbl_matches ; +---------+-----------+-----------+ | MatchID | HomeScore | AwayScore | +---------+-----------+-----------+ | 11 | 1 | 1 | | 12 | 3 | 3 | | 13 | 3 | 0 | | 14 | 3 | 0 | | 15 | 1 | 0 | | 16 | 1 | 0 | | 17 | 0 | 0 | | 18 | 0 | 0 | | 19 | 0 | 1 | | 20 | 0 | 0 | | 21 | 0 | 0 | | 22 | 1 | 1 | | 23 | 0 | 0 | | 24 | 0 | 0 | | 25 | 0 | 0 | | 26 | 0 | 0 | | 27 | 0 | 0 | | 28 | 0 | 0 | | 29 | 1 | 0 | | 30 | 0 | 0 | | 31 | 0 | 0 | +---------+-----------+-----------+ 21 rows in set (0.00 sec) how can i set the unentered result .for now it is like all the games wether played or not are at 0:0.i need to be able to get accurate table log of all the games such that if result is not set this can be identified and not set to 0:0. should i set the default to Null,or not null for the HomeScore and awayscore?
  2. I have this player registration table select player_id,Fname,Lname,Gender,Shirt_no FROM player_details; +-----------+---------------------------+-----------------------------+--------+----------+ | player_id | Fname | Lname | Gender | Shirt_no | +-----------+---------------------------+-----------------------------+--------+----------+ | 1 | Manager | First-team coach | NULL | NULL | | 2 | Goalkeeping coach | Fitness coach | NULL | NULL | | 3 | Club doctor | First-team physiotherapist | NULL | NULL | | 4 | Masseur | Assistant kit manager | NULL | NULL | | 5 | Performance nutritionist | Assistant manager | NULL | NULL | | 6 | Head of performance | Head of medical services | NULL | NULL | | 7 | Assistant fitness coach | Under-21s assistant coach | NULL | NULL | | 8 | Kit manager | Equipment manager | NULL | NULL | | 9 | Football analyst | Academy manager | NULL | NULL | | 10 | Mulan | Babu | M | 23 | +-----------+---------------------------+-----------------------------+--------+----------+ 10 rows in set (0.00 sec) and this summary table select player_id,Team_catId,SeasonID from Soka_players_team_tbl; +-----------+------------+----------+ | player_id | Team_catId | SeasonID | +-----------+------------+----------+ | 1 | 1 | 12 | | 2 | 1 | 12 | | 3 | 1 | 12 | | 4 | 1 | 12 | | 5 | 2 | 12 | | 6 | 2 | 12 | | 7 | 3 | 12 | | 8 | 3 | 12 | | 9 | 3 | 12 | | 10 | 4 | 12 | +-----------+------------+----------+ if the seasonid changes,I have to reenter all the playeridsfor a particular teamcategory for every new season. for instace i will have to repeat ids 1-10 for a new seasonid 13. Iam afraid this will make the table grow very huge for all the different team categories,considering the seasons have to change,and i have to tell the players i had in a previous season how can i redesign this to avoid this in the future??
  3. select player_id,Gender,Shirt_no FROM player_details; +-----------+--------+----------+ | player_id | Gender | Shirt_no | +-----------+--------+----------+ | 10 | M | 34 | | 11 | M | 12 | | 12 | M | 13 | | 13 | M | 34 | +-----------+--------+----------+ 13 rows in set (0.00 sec) select player_id,Team_catId from players_team; +-----------+------------+ | player_id | Team_catId | +-----------+------------+ | 10 | 1 | | 12 | 2 | | 11 | 3 | | 13 | 1 | +-----------+------------+ i would like to put a check constraint on the shirt_no field,such that no player in same team category has same shirt number i have tried this: CREATE TABLE player_details ( player_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, Fname VARCHAR(60) NOT NULL, Gender ENUM('M','F'), Shirt_no tinyint(2), PRIMARY KEY (player_id ), CONSTRAINT Shirt_number_taken CHECK (NOT EXISTS -- reference to second table (SELECT * FROM Soka_players_team_tbl AS M1 WHERE M1.player_id = Soka_player_details_tbl.player_id )) ); but it is not working.
  4. what if all have been changed?how can i adress thascenariot?
  5. i have this sql query result: +--------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------+------+-----+---------+-------+ | Gender | enum('M','F') | YES | | NULL | | +--------+---------------+------+-----+---------+-------+ 1 row in set (0.00 sec) how can i loaad the enum type field into a select.I have tried this but it shows nothing $this->mGender = Cag::GetGender(); and in php script: echo $obj->mGender['Type'];
  6. I thought about that, but what about the scenario were I would like to update only the line up i.e the player id for a particular game(it could be many player ids and not one).And a new list of player names has been selected.I would not want to re enter their statistics afresh but only for the changed player ids in order to reset the lineup .look at this table: select player_id, MatchID,minutes,assists from soka_player_statistics_tbl; +-----------+---------+---------+---------+ | player_id | MatchID | minutes | assists | +-----------+---------+---------+---------+ | 1 | 29 | NULL | NULL | | 2 | 29 | NULL | NULL | | 3 | 29 | NULL | NULL | | 4 | 29 | NULL | NULL | | 5 | 29 | NULL | NULL | | 6 | 29 | NULL | NULL | | 8 | 29 | NULL | NULL | | 9 | 29 | NULL | NULL | +-----------+---------+---------+---------+ I would like to be updating only the player ids without changing the other data. Is there a way I can use case statement here?
  7. i have this table structure and an array of new records, how can i run an update on all player ids with new ones for a particular matchid select player_id, MatchID,minutes from soka_player_statistics_tbl; +-----------+---------+---------+ | player_id | MatchID | minutes | +-----------+---------+---------+ | 1 | 29 | NULL | | 2 | 29 | NULL | | 3 | 29 | NULL | | 4 | 29 | NULL | | 5 | 29 | NULL | | 6 | 29 | NULL | | 7 | 29 | NULL | | 8 | 29 | NULL | | 9 | 29 | NULL | +-----------+---------+---------+ I have tried this : update soka_player_statistics_tbl set player_id=inPlayerId where MatchID=inMatchId; but if i run in an array from php to update matchID 29 with new player ids,i get an ERRNO: 256 TEXT: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-29' for key 'PRIMARY'
  8. iam trying to get Alfred| aram |first both these to display in category first. so that i can have a heading FIRST CATEGORY and then list the names below it.when using php to display the data. HOW CAN I GET THIS RECORDSET IN SQL FOR THAT END RESULT
  9. how can I make this query bring results grouped by Team_cat? here it brings all results select f0.*,positions.job_title,Cat.Team_catId ,Cat.Team_cat from soka_staff_details_tbl as f0 join soka_staff_job_tbl as f1 on f1.staff_id=f0.staff_id JOIN soka_job_title_tbl AS positions ON positions.job_id = f1.job_id JOIN Soka_team_category_tbl AS Cat ON Cat.Team_catId = f1.Team_catId where Cat.Team_catId <> '1' ; +----------+--------+---------------+-------+-------------+--------+----------+-----------------+---------------+-----------+-------------+-------+-----------+------+-----------+---------+---------------------+--------------------+------------+----------+ | staff_id | Fname | Lname | D_O_B | Nationality | Gender | D_O_JOIN | playedfor_clubs | Managed_clubs | Telephone | Mobilephone | email | thumbnail | img | biography | honours | date_created | job_title | Team_catId | Team_cat | +----------+--------+---------------+-------+-------------+--------+----------+-----------------+---------------+-----------+-------------+-------+-----------+------+-----------+---------+---------------------+--------------------+------------+----------+ | 1 | Alfred | reza | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2017-03-01 15:10:29 | Manager | 2 | FIRST | | 2 | Aram | Kauma | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2017-03-01 15:10:29 | First-team coach | 2 | FIRST | +----------+--------+---------------+-------+-------------+--------+----------+-----------------+---------------+-----------+-------------+-------+-----------+------+-----------+---------+---------------------+--------------------+------------+----------+ 2 rows in set (0.98 sec) but here it brings 1 record select f0.*,positions.job_title,Cat.Team_catId ,Cat.Team_cat from soka_staff_details_tbl as f0 join soka_staff_job_tbl as f1 on f1.staff_id=f0.staff_id JOIN soka_job_title_tbl AS positions ON positions.job_id = f1.job_id JOIN Soka_team_category_tbl AS Cat ON Cat.Team_catId = f1.Team_catId where Cat.Team_catId <> '1' GROUP BY Cat.Team_catId; +----------+--------+---------------+-------+-------------+--------+----------+-----------------+---------------+-----------+-------------+-------+-----------+------+-----------+---------+---------------------+-----------+------------+----------+ | staff_id | Fname | Lname | D_O_B | Nationality | Gender | D_O_JOIN | playedfor_clubs | Managed_clubs | Telephone | Mobilephone | email | thumbnail | img | biography | honours | date_created | job_title | Team_catId | Team_cat | +----------+--------+---------------+-------+-------------+--------+----------+-----------------+---------------+-----------+-------------+-------+-----------+------+-----------+---------+---------------------+-----------+------------+----------+ | 1 | Alfred | reza | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 2017-03-01 15:10:29 | Manager | 2 | FIRST | +----------+--------+---------------+-------+-------------+--------+----------+-----------------+---------------+-----------+-------------+-------+-----------+------+-----------+---------+---------------------+-----------+------------+----------+ 1 row in set (0.11 sec) yet it has to bring two under Team_cat FIRST
  10. could you tweak the code to this end .
  11. i have a set of selects in html form and would like to validate them based on : if the first select is not null and the value is not equal to the selection of the second select,and the remaing selects are not empty or null,then pass else fail. how can i do that? here is my html <tr> <td valign="middle" align="left"> <label for="tags"> <select class="t1-select" name="home[0]"> <option value="">Select team</option> <option value="6">Seb FC</option> </select> </label> </td> <td valign="middle" align="left"> <label for="tags"> <select class="t1-select" name="away[0]"> <option value="">Select team</option> <option value="6">Sebo FC</option> </select> </label> </td> <td valign="middle" align="left"> <select class="t1-select" name="starthour[0]" style="width:55px"> <option value="00">00</option><option value="01">01</option><option value="02">02</option><option value="03">03</option><option value="04">04</option><option value="05">05</option><option value="06">06</option><option value="07">07</option><option value="08">08</option><option value="09">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option></select></td> <td valign="middle" align="left"> <select class="t1-select" name="startminute[0]" style="width:55px"> <option value="00">00</option><option value="01">01</option><option value="02">02</option><option value="03">03</option><option value="04">04</option><option value="05">05</option><option value="06">06</option><option value="07">07</option><option value="08">08</option><option value="09">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option><option value="53">53</option><option value="54">54</option><option value="55">55</option><option value="56">56</option><option value="57">57</option><option value="58">58</option><option value="59">59</option><option value="60">60</option></select> </td> <!--<td valign="middle" align="left"> <select class="t1-select"name="endhour[0]"style="width:55px"> <option value="00">00</option><option value="01">01</option><option value="02">02</option><option value="03">03</option><option value="04">04</option><option value="05">05</option><option value="06">06</option><option value="07">07</option><option value="08">08</option><option value="09">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option></select> </td> <td valign="middle" align="left"> <select class="t1-select"name="endminute[0]"style="width:55px"> <option value="00">00</option><option value="01">01</option><option value="02">02</option><option value="03">03</option><option value="04">04</option><option value="05">05</option><option value="06">06</option><option value="07">07</option><option value="08">08</option><option value="09">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option><option value="53">53</option><option value="54">54</option><option value="55">55</option><option value="56">56</option><option value="57">57</option><option value="58">58</option><option value="59">59</option><option value="60">60</option></select> </td>--> <td valign="middle" align="left"> <label for="tags"> <select class="t1-select" id="user_venue" name="venue[0]" style="width:55px"> <option value="">Select Venue</option> <option value="H">(H)HOME</option><option value="A">(A)AWAY</option><option value="N">(N)NEUTRAL</option> </select> </label> </td> <td valign="middle" align="left"> <label for="tags"> <select class="t1-select" id="user_stad" name="stadium[0]"> <option value="">Select Stadium</option> <option value="1">Nambo Stadium</option> </select> </label> </td> </tr> <tr> <td valign="middle" align="left"> <label for="tags"> <select class="t1-select" name="home[1]"> <option value="">Select team</option> <option value="6">Seb FC</option> </select> </label> </td> <td valign="middle" align="left"> <label for="tags"> <select class="t1-select" name="away[1]"> <option value="">Select team</option> <option value="6">Sebo FC</option> </select> </label> </td> <td valign="middle" align="left"> <select class="t1-select" name="starthour[1]" style="width:55px"> <option value="00">00</option><option value="01">01</option><option value="02">02</option><option value="03">03</option><option value="04">04</option><option value="05">05</option><option value="06">06</option><option value="07">07</option><option value="08">08</option><option value="09">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option></select></td> <td valign="middle" align="left"> <select class="t1-select" name="startminute[1]" style="width:55px"> <option value="00">00</option><option value="01">01</option><option value="02">02</option><option value="03">03</option><option value="04">04</option><option value="05">05</option><option value="06">06</option><option value="07">07</option><option value="08">08</option><option value="09">09</option><option value="10">10</option><option value="11">11</option><option value="12">12</option><option value="13">13</option><option value="14">14</option><option value="15">15</option><option value="16">16</option><option value="17">17</option><option value="18">18</option><option value="19">19</option><option value="20">20</option><option value="21">21</option><option value="22">22</option><option value="23">23</option><option value="24">24</option><option value="25">25</option><option value="26">26</option><option value="27">27</option><option value="28">28</option><option value="29">29</option><option value="30">30</option><option value="31">31</option><option value="32">32</option><option value="33">33</option><option value="34">34</option><option value="35">35</option><option value="36">36</option><option value="37">37</option><option value="38">38</option><option value="39">39</option><option value="40">40</option><option value="41">41</option><option value="42">42</option><option value="43">43</option><option value="44">44</option><option value="45">45</option><option value="46">46</option><option value="47">47</option><option value="48">48</option><option value="49">49</option><option value="50">50</option><option value="51">51</option><option value="52">52</option><option value="53">53</option><option value="54">54</option><option value="55">55</option><option value="56">56</option><option value="57">57</option><option value="58">58</option><option value="59">59</option><option value="60">60</option></select> </td> <td valign="middle" align="left"> <label for="tags"> <select class="t1-select" id="user_venue" name="venue[1]" style="width:55px"> <option value="">Select Venue</option> <option value="H">(H)HOME</option><option value="A">(A)AWAY</option><option value="N">(N)NEUTRAL</option> </select> </label> </td> <td valign="middle" align="left"> <label for="tags"> <select class="t1-select" id="user_stad" name="stadium[1]"> <option value="">Select Stadium</option> <option value="1">Nambo Stadium</option> </select> </label> </td> </tr $hteam = $_POST['home']; $ateam = $_POST['away']; $starthour = $_POST['starthour']; $startminute = $_POST['startminute']; $venue = $_POST['venue']; $stadium = $_POST['stadium']; foreach($hteam as $key => $d ) { if((!empty($d)&& !empty($ateam[$key])&& ($ateam[$key]!= $d))/*&& empty($venue[$key])&& empty($stadium[$key])*/){ $mErrorMessage = 'Set full match details'; break;} }
  12. declare l_team_id int; DECLARE cur1 CURSOR FOR SELECT * FROM t_summ; DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1; SET l_last_row_fetched=0; OPEN cur1; cat_loop: LOOP FETCH cur1 INTO l_team_id,l_teamcat_id; /*work with the data*/ IF l_last_row_fetched=1 THEN /* No more rows*/ LEAVE cat_loop; END IF; if l_team_id = inHometeamId || l_team_id = inAwayteamId THEN insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,l_teamcat_id); elseif l_team_id = inAwayteamId THEN insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,l_teamcat_id); else insert into mvenue (MatchID ,match_venueID,stadium_LocationID,LeagueSeasonID,CompetitionID,Team_catId)values(inm_id,inVenueId,instadiumId,inseasonId,inCompId,'0'); end if; END LOOP cat_loop; CLOSE cur1; SET l_last_row_fetched=0; how can i improve on the above to make it database friendli and efficient? how can i modify it to use case statementand leave out the cursor?
  13. is this syntax correct insert into match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)values('14','6','7','8','9', select CASE WHEN teamcat.TeamID=a_id THEN Teamcat_id=teamcat.Teamcat_id END , CASE WHEN teamcat.TeamID = h_id THEN Teamcat_id=teamcat.Teamcat_id ELSE teamcat_id= 0 END FROM teamcat );
  14. as the table grows of course it will have them,and with that in mind ,ijust need how to write the query the correct wayusing insert
  15. it is the one teamid that is for the home team(h_id) Vs away team(a_id) in a game(matches table)
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.