PHP: looking up values in DB and converting before inserting data

This might be easier to do with javascript but but I happen to be restricted solely to MySQL, PHP, and Apache (as well as HTML and CSS obviously) for this project, and I’m not good at PHP

I’ve basically finished the whole thing but came a cross a small problem.

The user creates a sport event and to do so the user chooses two opposing teams with the help of two html dropdowns (both dropdowns display the same array of teams that are fetched from my ‘teams’ table located in my database). The names of the teams are displayed and selected on the page, and therefore stored as string variables in PHP once the user submits the form.

Here’s the catch, the two teams the user selects are then to be saved in a table which is meant to contain the IDs of the two teams, rather than their names (makes it easier to enforce integrity constraints). But I would have to query the db again to obtain the IDs from the ‘teams’ table and then store this in the table I’m interested in.

What makes this a tad more complicated is the fact that along with the two team-dropdowns, in the aforementioned html form, the user inputs a lot of other data regarding the event which is stored together with the two teams; this data is supposed to be inserted into the same database table as is, everything but the two teams is supposed to be inserted as is.

My initial thoughts were to add a subquery next to the two value placeholders I would use to insert the teams-data:

$query = "INSERT INTO sport_events
          (event_date, start_time, end_time, home_team, visiting_team) values
          (?, ?, ?, (select ID from teams where team_name = ?), (select ID from teams where team_name =?))";

this returns an error however:

Subquery returns more than 1 row

which makes no sense. if I try to insert the same tuple into the database manually (using these two subqueries in mysql) everything is fine:

('xyz_date', 'xyz_time', 'xyz_time', (select ID from teams where team_name = 'Manchester United'), (select ID from teams where team_name = 'Arsenal'))

if I try to query the database using either one of the two subqueries, the result is always one row as the team_name is always unique (below is the query):

select ID from teams where team_name = 'Manchester United';

EDIT: here’s the code

How the teams are fetched and displayed in two html dropdowns:

        $teams = array();
/*populate an array with the teams from the database*/
        while ($row = mysqli_fetch_array($response)) {
          array_push($teams, $row['team_name']);
        }
        echo '<p>Home Team: <select name="home_team" value ="">';
/*unload the teams into the html element - first dropdown*/
        foreach ($teams as $team) {
          echo '<option>' . $team . '</option>';
        }
        echo '</select></p>';
/*unload the teams into the html element - second dropdown*/
        echo '<p>Visiting Team: <select name="visiting_team" value ="">';
        foreach ($teams as $team) {
          echo '<option>' . $team . '</option>';
        }
        echo '</select></p>';

the five pieces of data inserted by the user into the form are then checked for null-values (example of one below)

if(isset($_POST['submit'])) {

if(empty($_POST['home_team'])) {
        $data_missing[] = 'Home Team';
      }
      else {
        $h_team = trim($_POST['home_team']);
      }
...

and then this is as far as the data gets

if(empty($data_missing)){
        require_once('mysqli_connect.php');
        $query = "INSERT INTO sport_events
          (event_date, start_time, end_time, home_team, visiting_team) values
          (?, ?, ?, (select ID from teams where team_name = ?), (select ID from teams where team_name =?))";

        $stmt = mysqli_prepare($dbc, $query);

        mysqli_stmt_bind_param($stmt, "sssii", $e_date, $beg_at,
        $end_at, $h_team, $v_team);

        mysqli_stmt_execute($stmt);

        $affected_rows = mysqli_stmt_affected_rows($stmt);

P.S. using MySQL 10.4.14 connected to the XAMPP 3.2.4 using Apache 2.4 and PHP 7.2.34

Answer

You can set a value for an <option>, and then it’s the value which is submitted to the server with the form.

So if you put the ID of your team into each option when you are rendering the HTML, then you’ll get the selected ID back again when the form is submitted.

e.g.

<option value="1">Anywhere United</option>
<option value="2">SomewhereElse Rangers</option>
<option value="3">Lonely Wanderers</option>

More info: https://developer.mozilla.org/en-US/docs/Web/HTML/Element/option

The current SQL used to select the teams isn’t shown, but I would guess the SQL query to do this needs to be something like:

SELECT ID, team_name FROM teams

and then the PHP to read it:

$teams = array();
/*populate an array with the teams from the database*/
while ($row = mysqli_fetch_assoc($response)) {
  array_push($teams, $row);
}
echo '<p>Home Team: <select name="home_team" value ="">';


/*unload the teams into the html element - first dropdown*/
foreach ($teams as $team) {
  echo '<option value="' . $team['ID'] . '">' . $team['team_name'] . '</option>';
}
echo '</select></p>';


/*unload the teams into the html element - second dropdown*/
echo '<p>Visiting Team: <select name="visiting_team" value ="">';
foreach ($teams as $team) {
  echo '<option value="' . $team['ID'] . '">' . $team['team_name'] . '</option>';
}
echo '</select></p>';

Advertisement

Answer