shaddf

March 22, 2017

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?

March 21, 2017

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

March 21, 2017

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.

March 19, 2017

You would have to identify just the rows that have changed and update them, so it means finding the players that have been changed and using the match id and old player id to update that specific row to have the new player id.

what if all have been changed?how can i adress thascenariot?

March 19, 2017

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'];

March 17, 2017

This is not a way to do it. I'm not sure of exactly what your reasons for doing what you are doing.

The problem is that in your statement you are going to try and update every record for a particular matchid to (for example) have a playerid of 1. What you probably want to do is just have a list of player ID's and want to make sure that your set of records match. Update isn't the way to do it. It would probably be better and a lot easier to delete the existing records and insert new records.

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?

March 16, 2017

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'

March 2, 2017

"Grouping by" something means combining all the records with the same values together. That means there will be only one row per Team_catId.

You do not want to do a GROUP BY.

What kind of results are you trying to get?

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

March 2, 2017

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

February 25, 2017

you should validate each input separately and set up a specific error message for each input so that the user doesn't need to guess which value(s) was(were) not selected. you would validate any empty values first, before testing if the first two values are not the same.

if you use an array of arrays to hold the error messages, with the main array index being the key/section value (0, 1, 2, ...) and the secondary array index being the select name, you can either output all the errors as a group at the start of each section or you can output each individual error at the select menu it corresponds to.

you would also want to validate all the sections of data at once (no break; in the loop) so that you could display all the errors at once. by breaking out of the loop upon the first error, the user will have to fix the error(s) with one section before seeing if there are more errors later in the submitted data.

if you set up an array that defines the form fields and the label for each, you can use this to produce your table heading and to dynamically validate the data and dynamically set up the error messages.

i also hope you are dynamically producing the select menus, which would allow you to select any existing option choices should there be validation errors so that the user doesn't have to re-select everything when there is a validation error.

could you tweak the code to this end .

February 25, 2017

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;}
 
}

February 24, 2017

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?

February 19, 2017

According to your data, none of the teams in the matches table have a corresponding team_catid in the catID table. Is this a possible situation?

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 );

February 19, 2017

Which is it?

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

February 19, 2017

Which is it?

it is the one teamid that is for the home team(h_id) Vs away team(a_id) in a game(matches table)

February 18, 2017

A prerequisite to writing a working query is knowledge of the table structures and relationships.

So how about providing that information?

teamcat table

+-----------+-----------+

| TeamID | Team_catId |

+-----------+-----------+

| 1 | 23 |

| 2 | 34 |

| 3 | 45 |

+-----------+-----------+

matches table

+-----------+-----------+------+------+--------------+------+--------------+

+-----------+-----------+------+------+--------------+------+--------------+

| 0 | 0 | 11 | 6 | Seowa fc | 7 | Seowa U-14 | 12

| 0 | 0 | 12 | 7 | Seowa U-14 | 6 | Seowa fc | 13

| 0 | 0 | 13 | 8 | Teowa fc | 6 | Seowa fc | 14

+-----------+-----------+------+------+--------------+------+-------------

insert into match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)values('14','6','7','8','9',

CASE WHEN a_id = teamcat.TeamID THEN Teamcat_id END ,

CASE WHEN teamcat.TeamID = h_id THEN Teamcat_id=23

ELSE teamcat_id= 0 END );

iam trying to do this:

if the awayteam id a_id or hometeam id h_id exists in teamcat table then set Teamcat_id to the value of Teamcat_id for this id into the summary table else if empty enter 0.

Also how can i account for a situation where a game both the away team id and home team id exists in teamcat table i.e 1 vs 2. Will two recods be recorded in the team summary table to account for the two different categories??

February 18, 2017

But the only table reference by that query is "match_summ" (the one you are inserting into).

As mentioned, what are you trying to do?

how can I insert into a table given three field values are known but the last is dependent on the team category to which the home team or away team belong to. in match_summ table;

MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID, are known and straight forward,but the teamcatid is based on if(hometeamid{is in table a,then set teamcatid=the catid in table a})

February 17, 2017

Regarding CASE statements, yes you can use them in an insert.

BUT whether the one you propose is feasible is impossible to say without knowing what you are trying to accomplish. Where do "home_id", "away_id" and "l_team_id" come from in that statement?

home_id is the home team team in the matches table and away_id is the away team in the matches table(the new generated matchID record).l_team_id is the team_id from the team-categorysummary(teamId int,categoryid int) table

February 17, 2017

iam trying to insert a summary into a table.Is it possible to write a case condition inside an insert statement like so:

insert into match_summ(MatchID ,match_venueID,stadium_LocationID,SeasonID,CompetitionID,Team_catId)values('5','6','7','8','9',
          CASE WHEN homeid = l_team_id THEN teamcat_id=1 END ,
        CASE WHEN l_team_id = awayid THEN teamcat_id=2 
            ELSE teamcat_id= 0 END );

Is what iam trying to do possible??What is the best way to do it???

February 17, 2017

Then alter the solution, adapting it to your needs.

that is were iam at a loss,how to make it broader like the betting sites do it:showing the home and away form inclusively

February 13, 2017

I have already given you a link to a solution to this in your previous topic here.

If you don't read replies then you are just wasting our time.

i did read your answer but his time i need some additional data from that same DB table as you can see.I need the home results summary for each team as well as its away results form summary.thanks

February 9, 2017

how can i get a table like so: from a database table with teams and goals scored

Season 2016/17

Premier League 2016/17

Team Information Home Away Total Points Information

P W D L F A W D L F A W D L F A +/- Pts

Manchester City 7 3 0 0 9 2 3 0 1 9 5 6 0 1 18 7 11 18

February 7, 2017

i have this link

Stottoma-BC-textiles/first-bale/-t6/-c2/

and this regex

RewriteRule ^.*-([a-z]+)/-t([0-9]+)/-c([0-9]+)/?$ /index.php?Page=$1&baleId=$2&CategoryId=$3 [L]

where am going wrong with the regex iam getting not found error

February 4, 2017

how can i leave out all the null rows from this result of query below;iam trying to get the list of player names and their playing position

mysql> select f0.player_id as P_id,concat (f0.Fname,' ',f0.Lname)as pname,f1.position_id,f2.position from( select *  from Soka_player_details_tbl) as f0 left join (select * from Soka_players_team_tbl where Soka_players_team_tbl.SeasonID=(select SeasonID from tbl_season where Publish='1')and Team_catId= '1'  )as f1 on f1.player_id=f0.player_id left join (select * from Soka_position_tbl )as f2 on f1.position_id=f2.position_id order by position_id asc;
+------+----------------------------------------------------+-------------+--------------+
| P_id | pname                                              | position_id | position     |
+------+----------------------------------------------------+-------------+--------------+
|    5 |  Performance nutritionist  Assistant manager       |        NULL | NULL         |
|    8 |  Kit manager  Equipment manager                    |        NULL | NULL         |
|    6 |  Head of performance  Head of medical services     |        NULL | NULL         |
|    4 |  Masseur  Assistant kit manager                    |        NULL | NULL         |
|    7 |  Assistant fitness coach Under-21s assistant coach |           1 | Goal keepers |
|    1 |  Manager  First-team coach                         |           1 | Goal keepers |
|    2 |  Goalkeeping coach  Fitness coach                  |           2 | Defenders    |
|    3 |  Club doctor  First-team physiotherapist           |           3 | Midfielders  |
|    9 |  Football analyst  Academy manager                 |           4 | Forwards     |
+------+----------------------------------------------------+-------------+--------------+
9 rows in set (0.00 sec)

February 4, 2017

how can I get a full table from this result set

+-----------+-----------+------+------+--------------+------+--------------+
| HomeScore | AwayScore | g_id | a_id | h_name       | h_id | a_name       |
+-----------+-----------+------+------+--------------+------+--------------+
|         0 |         0 |   11 |    6 | Seowa fc    |    7 | Seowa U-14 |
|         0 |         0 |   12 |    7 | Seowa U-14 |    6 | Seowa fc    |
|         0 |         0 |   13 |    8 | Teowa fc    |    6 | Seowa fc    |
+-----------+-----------+------+------+--------------+------+-------------

Sign In

shaddf

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by shaddf

setting results

look up table

check constraint

multiple updates

how to load a select

multiple updates

multiple updates

grouping in query

grouping in query

validating data from arrayy

validating data from arrayy

improve approach

inserting using case else

inserting using case else

inserting using case else

inserting using case else

inserting using case else

inserting using case else

inserting using case else

getting a full detail soccer table

getting a full detail soccer table

getting a full detail soccer table

rewrite regex

leaving out null rows

getting a soccer table

Browse

Activity

Important Information