[Setting up databases to produce game schedules...]

I take it you just want the Carmel games, yes?

What is you current query?

[Setting up databases to produce game schedules...]

6 minutes ago, Jim R said:

I mean, it prints all three dates,

What 3 dates? You have home games and away games so what is the third - do you also have meet-in-the-middle games?

[Setting up databases to produce game schedules...]

1 minute ago, Jim R said:

...it prints all the games, regardless of who is playing in them.  

Is that a good thing or a bad thing?

(I put in the "WHERE sectional = " bits in because you had it in your query. "8" just happened to be the value I had put in that column in my data.)

[Setting up databases to produce game schedules...]

[Setting up databases to produce game schedules...]

My game table is

CREATE TABLE `game` (
  `game_id` int(11) NOT NULL AUTO_INCREMENT,
  `gamedate` date DEFAULT NULL,
  `home_id` int(11) DEFAULT NULL,
  `away_id` int(11) DEFAULT NULL,
  `home_score` int(11) DEFAULT NULL,
  `away_score` int(11) DEFAULT NULL,
  `tournament_id` int(11) DEFAULT NULL,
  `round` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`game_id`),
  KEY `fk_game_school1_idx` (`home_id`),
  KEY `fk_game_school2_idx` (`away_id`),
  KEY `fk_game_tourn_idx` (`tournament_id`),
  KEY `idx_game_gamedate` (`gamedate`),
  CONSTRAINT `fk_game_school1` FOREIGN KEY (`home_id`) REFERENCES `school` (`school_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_game_school2` FOREIGN KEY (`away_id`) REFERENCES `school` (`school_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_game_tourn` FOREIGN KEY (`tournament_id`) REFERENCES `tournament` (`tournament_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=241 DEFAULT CHARSET=utf8;

 

[Setting up databases to produce game schedules...]

Do you mean you want to add a "WHERE team = '$teamname' "

[Unable to pull a php input array into JQuery]

echo "<td> <input type='text' name='reply[]'class="ansbox" value='$value'></td>";

Have you tried this?

[Setting up databases to produce game schedules...]

It is likely that you are looking for something like this

SELECT team
     , gamedate
     , DATE_FORMAT(gamedate, '%d-%b-%Y') as date
     , opponent
     , hora
FROM (
        SELECT h.schoolname as team
             , gamedate
             , a.schoolname as opponent
             , 'H' as hora
        FROM game g 
             JOIN school h ON g.home_id = h.school_id
             JOIN school a ON g.away_id = a.school_id
        WHERE h.sectional = 8 
          AND a.sectional = 8
        
        UNION ALL
        
        SELECT a.schoolname as team
             , gamedate
             , h.schoolname as opponent
             , 'A' as hora
        FROM game g 
             JOIN school a ON g.away_id = a.school_id
             JOIN school h ON g.home_id = h.school_id
        WHERE h.sectional = 8 
          AND a.sectional = 8
      ) x
ORDER BY team, gamedate;

+-----------+------------+-------------+-----------+------+
| team      | gamedate   | date        | opponent  | hora |
+-----------+------------+-------------+-----------+------+
| School 1  | 2018-11-10 | 10-Nov-2018 | School 13 | H    |
| School 1  | 2018-11-17 | 17-Nov-2018 | School 5  | A    |
| School 1  | 2018-11-24 | 24-Nov-2018 | School 2  | H    |
| School 1  | 2018-12-01 | 01-Dec-2018 | School 7  | H    |
| School 1  | 2018-12-15 | 15-Dec-2018 | School 9  | A    |
| School 1  | 2018-12-22 | 22-Dec-2018 | School 8  | H    |
| School 1  | 2019-01-19 | 19-Jan-2019 | School 11 | A    |
| School 1  | 2019-01-26 | 26-Jan-2019 | School 4  | H    |
| School 1  | 2019-02-02 | 02-Feb-2019 | School 8  | A    |
| School 1  | 2019-02-23 | 23-Feb-2019 | School 6  | H    |
| School 1  | 2019-03-09 | 09-Mar-2019 | School 12 | H    |
| School 1  | 2019-03-30 | 30-Mar-2019 | School 9  | H    |
| School 1  | 2019-04-06 | 06-Apr-2019 | School 6  | A    |
| School 1  | 2019-04-13 | 13-Apr-2019 | School 10 | A    |
| School 1  | 2019-04-20 | 20-Apr-2019 | School 13 | A    |
| School 1  | 2019-04-27 | 27-Apr-2019 | School 7  | A    |
| School 1  | 2019-05-04 | 04-May-2019 | School 11 | H    |
| School 1  | 2019-05-18 | 18-May-2019 | School 12 | A    |
| School 1  | 2019-05-25 | 25-May-2019 | School 10 | H    |
| School 1  | 2019-06-01 | 01-Jun-2019 | School 4  | A    |
| School 1  | 2019-06-08 | 08-Jun-2019 | School 2  | A    |
| School 1  | 2019-06-15 | 15-Jun-2019 | School 5  | H    |

 

[Setting up databases to produce game schedules...]

1 minute ago, Jim R said:

Thoughts on updating it automatically as each score is input?  

Don't. Just enter and store the scores.

Databases should not store derived data. Get the total wins and losses when you need them with a query.

[Setting up databases to produce game schedules...]

Using my data model you can get results like

+---------+--------+------+-------+------+-------+--------+
| School  | Played | Won  | Drawn | Lost | PDiff | Points |
+---------+--------+------+-------+------+-------+--------+          Points awarded
| Cowdrey |      6 |    4 |     2 |    0 |     8 |     14 |             Win  = 3
| Laker   |      6 |    3 |     2 |    1 |     6 |     11 |             Draw = 1
| Jardine |      6 |    1 |     2 |    3 |    -6 |      5 |             Lose = 0
| Grace   |      6 |    0 |     2 |    4 |    -8 |      2 |
+---------+--------+------+-------+------+-------+--------+

This can be  for schools or coaches, either for the current season or going back as far as your data allows.

[pass a php array into js]

Use a hidden field

<?php 
    $rs = [[1,2,3],[2,3,4],[3,4,5]];
    $jrs = json_encode($rs);
    echo "<input id='rs' type='hidden' value='$jrs'>";
?>

then

  <script type="text/javascript">
      $().ready( function() {
          var rs = JSON.parse( $("#rs").val() )
          alert( rs[2][0] )                         // 3
      })
  </script>

 

[Getting a strange error from my query...]

21 minutes ago, Jim R said:

Why doesn’t the query have an idea which table each index of each row came from?

The query needs to know exactly which table each column comes from. It's the output from that query to PHP that doesn't.

[How do I access an array outside of if?]

Check what is in $_POST['pcolors']

if(isset($_POST['pColors'])) {
    var_dump($_POST['pColors']);
}

 

[Setting up databases to produce game schedules...]

As an aside from coaching, you will need to add to your game fixtures table to indicate which games are league games (points contributing to their league position at the end of the season) and which are tournament games. Perhaps a "tournament_id" which would be 0  for league games.

For tournament games you may also want ot indicate which round of the tournament. This would tell you, for example, if a team never got past the first round or if they reach the semis or finals on N occasions.

[Setting up databases to produce game schedules...]

9 hours ago, Jim R said:

and I would want to calculate his overall record

Define "calculate"

[Setting up databases to produce game schedules...]

You wouldn't need to. The school_coach table gives the history of where each coach was and when.

+-----------+------------+-----------+---------+---------+                          +----------+-------+-------+
| school_id | schoolname | sectional | city_id | conf_id |                          | coach_id | fname | lname |
+-----------+------------+-----------+---------+---------+                          +----------+-------+-------+
|         1 | School 1   |      NULL |    NULL |    NULL |                          |        1 | Coach | One   |
|         2 | School 2   |      NULL |    NULL |    NULL |                          |        2 | Coach | Two   |
+-----------+------------+-----------+---------+---------+                          +----------+-------+-------+


                            +-----------------+-----------+----------+--------------+---------------+
                            | school_coach_id | school_id | coach_id | coached_from | coached_until |
                            +-----------------+-----------+----------+--------------+---------------+
                            |               9 |         1 |        1 | 2017-09-01   | 2018-08-31    |
                            |              10 |         2 |        2 | 2017-09-01   | 2018-08-31    |
                            |              11 |         1 |        2 | 2018-09-01   | 9999-12-31    |
                            |              12 |         2 |        1 | 2018-09-01   | 9999-12-31    |
                            +-----------------+-----------+----------+--------------+---------------+

Historical - who coached where on 01-Jan-2018?

SELECT schoolname
     , CONCAT(fname, ' ', lname) as coach
FROM school s
     JOIN school_coach sc 
        ON s.school_id = sc.school_id
           AND '2018-01-01' BETWEEN sc.coached_from AND sc.coached_until
     JOIN coach c USING (coach_id);      
+------------+-----------+
| schoolname | coach     |
+------------+-----------+
| School 1   | Coach One |
| School 2   | Coach Two |
+------------+-----------+

Who is currently coaching where?

SELECT schoolname
     , CONCAT(fname, ' ', lname) as coach
FROM school s
     JOIN school_coach sc 
        ON s.school_id = sc.school_id
           AND CURDATE() BETWEEN sc.coached_from AND sc.coached_until
     JOIN coach c USING (coach_id);  
+------------+-----------+
| schoolname | coach     |
+------------+-----------+
| School 1   | Coach Two |
| School 2   | Coach One |
+------------+-----------+

 

[Getting a strange error from my query...]

String literals in queries need to be enclosed in single quotes. Without quotes SQL assumes they are a column name.

$query = "SELECT * FROM a_schools WHERE sectional = $sectional AND school = '$school' ";

 

[How to pass my form total value to CCAvenue Payment Gateway?]

The answer is - you wouldn't.

You cannot trust any data coming from a user source. The only data you should accept from the form is what was bought (eg product_id) and qty. You would then query your database to get the product prices and do the totalling before passing it to the ayment gateway.

Unless, of course, you would be happy to sell Ferraris for 1 rupee each

[Setting up databases to produce game schedules...]

Correct - there should be a separate coach table. To keep track of who coached when there would be a school_coach table defining when the coach was with the team

Current coaches would have a date value of 9999-12-31 in the coached_until column. This would be updated when the coach leaves.

Remove the coach columns from the school table.

[Setting up databases to produce game schedules...]

This is the SQL to create those tables with relationships as illustrated

CREATE TABLE `city` (
  `city_id` int(11) NOT NULL AUTO_INCREMENT,
  `cityname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `conference` (
  `conf_id` int(11) NOT NULL AUTO_INCREMENT,
  `confname` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`conf_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `school` (
  `school_id` int(11) NOT NULL AUTO_INCREMENT,
  `schoolname` varchar(45) DEFAULT NULL,
  `coachfirst` varchar(45) DEFAULT NULL,
  `coachlast` varchar(45) DEFAULT NULL,
  `sectional` int(11) DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  `conf_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`school_id`),
  KEY `fk_school_city_idx` (`city_id`),
  KEY `fk_school_conference1_idx` (`conf_id`),
  CONSTRAINT `fk_school_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`city_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_school_conference1` FOREIGN KEY (`conf_id`) REFERENCES `conference` (`conf_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `game` (
  `game_id` int(11) NOT NULL AUTO_INCREMENT,
  `gamedate` date DEFAULT NULL,
  `home_id` int(11) DEFAULT NULL,
  `away_id` int(11) DEFAULT NULL,
  `home_score` int(11) DEFAULT NULL,
  `away_score` int(11) DEFAULT NULL,
  PRIMARY KEY (`game_id`),
  KEY `fk_game_school1_idx` (`home_id`),
  KEY `fk_game_school2_idx` (`away_id`),
  CONSTRAINT `fk_game_school1` FOREIGN KEY (`home_id`) REFERENCES `school` (`school_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_game_school2` FOREIGN KEY (`away_id`) REFERENCES `school` (`school_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

[Setting up databases to produce game schedules...]

2 hours ago, Jim R said:

Do you only columns with integers or numeric values show up as Relationship options?

Recommended. Numeric keys are more efficient. Names and other data items should only occur once in a database. IDs are the only items that should be duplicated across tables to form the relationships.

This is what I think you will need (tables and relationships)

What is "sectional"?

[Insert data into mysql]

Doesn't make any difference to the ids what order you sort the records into. The point is that the two ids for any item have a difference of 23

[Setting up databases to produce game schedules...]

I've never heard of Sequel Pro so can't help you there. As you are posting in a MySql forum you might consider using MySql, perhaps.

[Insert data into mysql]

The id sequence of your data (article is id 1 and 24) seems to imply that records are not being written twice (that would give article as ids 1 and 2), rather that the process is being run twice somehow.

[Setting up databases to produce game schedules...]

30 minutes ago, Jim R said:

As for the date, probably not knowing enough about the date function, thinking it would be easier when it came time to printing, since I want to print month abbreviations.  

Store dates as DATE type for maximum functionality (format yyyy-mm-dd). You can output them as you like EG

mysql> SELECT test_date
    ->      , DATE_FORMAT(test_date, '%d-%m-%y') as example1
    ->      , DATE_FORMAT(test_date, '%d-%b-%y') as example2
    ->      , DATE_FORMAT(test_date, '%M %D') as example3
    -> FROM test_b;
+------------+----------+-----------+---------------+
| test_date  | example1 | example2  | example3      |
+------------+----------+-----------+---------------+
| 2019-05-14 | 14-05-19 | 14-May-19 | May 14th      |
| 2019-12-25 | 25-12-19 | 25-Dec-19 | December 25th |
+------------+----------+-----------+---------------+

 

27 minutes ago, Jim R said:

We'll be typing the games directly into the database.  

Not your best idea. I had client who did that with invoices from suppliers. They would type in the supplier names, invoice numbers amounts etc. Some of the suppliers had half-a-dozen different spellings of their names. It made reporting by supplier a nightmare.

Database tables are not spreadsheets.