Data Inserting into Wrong Columns/Players in Database after Submitting. PHP Sports League

First post here. Just found this place - happy to be apart of it.

TLDR: My data is inserting to wrong columns in database. Has to do with my count feature not working properly for a radio input. So when user submits form, the data is being inserted for the wrong players. Read below for more in depth detail. My code is at the end. Tried asking this on stack overflow and it was closed due to it being “duplicate”. You can find it here - i couldnt get anything of value though: https://stackoverflow.com/questions/35856766/php-inserting-multiple-checkbox-and-textbox-arrays-into-mysql-database

I have a database that stores a bunch of stats and players for a sports league. Pretty basic.

I have a form that allows users to submit all the game data that goes into the database.

In the form is a section where I allow users to dynamically add goal scorers. For each goal scored, they click on an “add” button and it adds a whole line. These dynamic options all share the same name, count as their own individual records.

It works fine with select options, and inputs (integers), however when I add an input type radio, the functionality only half works (for radio option only).

For example, when I click “add goalie”, the dynamic row pops up. I then select team, player and type in (integer value) how many goals he let in.

I can add as many goalies as I wish, and after submitting, all their goals against will stick to their designated goalie. Meaning, if I had goalie A letting in 2 goals, and goalie B letting in 3 goals, that’s how it will insert into my database. Hope that makes sense. Basically all unique rows and my form understands that.

The problem is when I add a fourth option as an input with type radio. This is just one radio that the user has to select, for whomever finished the game. So out of all the goalies played, only one will finish the game since it’s a radio not a checkbox. The one who finishes, gets credited with either a win or a loss. The rest don’t get anything.

I applied my same technique by putting my query into a for loop, but what happens with my radio is only the first goalie gets credited with anything. If I had four goalies and selected “finished” on the last one, it would apply the radio stats to the first goalie instead.

I think I know my problem and it has to do with the count for my radio. The reason why the input before it for goals against worked, was because since it was a number I could put it into an array to call in my SQL1. Called it “:goalsagainst” (See $data1).

But with radio it’s not an integer so I didn’t know how to add it into my data array. So I left it out of the data array, and tried adding the $count in my if statement instead.

This got me close but still off since it’s crediting the wrong player.

Can anyone help me out? Still learning so this was best way I could explain , with walls and walls of text. Is there a better way I can use my if statement, or maybe somehow combine the if statement with the for loop it’s within?

Here’s two parts of my code. First part below is my JavaScript - showing what I am appending into the document. This is also how I make it dynamic, using ajax as well

Then the second part, showing my PHP. Showing two queries here: First one as an example of what works with my input integer option. And then the second snippet is showing the code I’m stuck on - for the forth radio input. Thank you in advance, hope all this helped and wasn’t too complicating. Much appreciated.

//PART #1........javascript - adding the dynamic options

$(document).on('click', '.addgoalies', function(){     //when user clicks 'add', all this below will be added to html
 count++;      //counter is added by 1 each time 'add' is clicked
 var html = '';
  
    html += '<div class="goal"><select name="teamGoalie[]" class="teamzGoalie" data-playerz="'+count+'" ><option value="">Team</option><?php echo fill_select_box($pdo, "0"); ?></select>'; 
    html += '<select class="goal" name="playerGoalie[]" id="playermenu'+count+'"><option value="">Player</option></select>'; 
    html += '<input type="number" name="goalsAgainst[]" placeholder="0" class="tendy" id="goaliemenu'+count+'">'; 
    html += '<input type="radio" name="statusGoalie[]" value="finish" class="status" id="goaliemenufinish'+count+'"><label for "finish">FINISH</label>'; 
    html += '<button type="button" name="remove" class="remove"><span class="glyphicon glyphicon-minus">REMOVE</span></button></div>';
 $('.wrapper3').append(html);
});



 //PART #2........the working php query, and then the one i'm stuck on

<?php

   $goalieresult = $_POST['statusGoalie'];
  $playerGoalie = $_POST['playerGoalie'];
  $goalsagainst = $_POST['goalsAgainst'];
  

 for($count = 0; $count < count($playerGoalie); $count++){
      $data1 = array(
          ':goalsagainst'=>$goalsagainst[$count],
          ':playerid'=>$playerGoalie[$count]
      );
      $sql1 = "UPDATE goalies SET                   
      goals_ag = goals_ag+:goalsagainst
      WHERE id = :playerid";
      $statement1 = $pdo->prepare($sql1);
      $statement1->execute($data1);   //execute statement
  }

  //////////////////////////////////////////////////////////////
  
  for($count = 0; $count < count($playerGoalie); $count++){
    $data2 = array(

        ':playerid'=>$playerGoalie[$count]
    );
    if($goalieresult[$count] == 'finish'){      
    $sql2 = "UPDATE goalies SET                   
    Wins = Wins+1
    WHERE id = :playerid";
    $statement2 = $pdo->prepare($sql2);
    $statement2->execute($data2);   //execute statement
   }
  }
  

  ?>
1 Like

I assume the intent is only one goalie can be marked as a finisher.

Radio is the correct type, however what you should do is instead push the value as count, and not as an array; there is 1 radio among your entire form.

html += '<input type="radio" name="finishGoalie" value="'+count+'" class="status" id="goaliemenufinish'+count+'"><label>FINISH</label>';

Note that this will now require that count = index; that is to say, teamGoalie[3] must be the row in which finishGoalie’s value = 3. Also keep in mind that indexes start at 0. so teamGoalie[3] is the fourth row. You can’t delete rows in this scenario without doing fiddly bits with the form.

Then your query becomes

//no for loop.
    $sql2 = "UPDATE goalies SET                   
    Wins = Wins+1
    WHERE id = :playerid";
    $statement2 = $pdo->prepare($sql2);
    $statement2->execute(array('playerid' => $playerGoalie[$_POST['finishGoalie']]));

(I’ve combined what you might put into a few separate lines there, but hopefully it still makes sense.)

Welcome to the forum. +1 for starting off with learning with prepared statements. :wink:

1 Like

wow very creative! Sincerely appreciate it! Made sure my count indexes were lined up properly too through inspect… Unfortunately it didn’t work, now instead of wrong goalie getting stats, nobody gets anything :frowning:

I tried various combos, with AND without the for loops. Couldn’t get anything to budge :confused: Going to keep trying a few things, if you have any ideas please let a brother know :smiley:

would generally indicate to me that the PHP page is fouling somewhere and kicking out a 500 error before it tries to run. If you go directly to that php page in your browser, what happens? (Well, thats a silly question Marc, you’ve coded that second part of the query so it will fail if the form values arent present… slaps own wrist)

Check the Network tab, is the fetch request returning 200 or 500?

Hey m_hutley, tried this morning and no 500’s! Only 200’s unfortunately. Sorry for late reply, been hammering away all day. FOUND A SOLUTION THOUGH! Wanted to share it with you and hear your thoughts :slight_smile:

Just to recap - For ONE goalie entry, a variation of your method worked. My original method that I shared also worked. As did a bunch of other suggestions that chatGPT gave me.

Some of these methods worked with two goalies, but if I left one of them unselected(which will always happen since it’s a radio and only one radio can be selected) it would glitch out. Only way It would work was if BOTH goalies had a selection on radio.

So that was cool! Figured out where the problem was coming from I think. Despite my best efforts I couldn’t get my form to ignore the goalies that didn’t have anything selected. Tried a bunch of different stuff and I’ll share it if you’re interested, but was not successful. Mainly because like i said, it’s radio so only one goalie can ever be selected.

So I had another idea. Instead of radio, I switched it to checkbox. And I also changed the value from “finish” or “count”, and changed it to a numerical value (1, 2, 3, etc).

I made three inputs: first one labeled “FINISHED” with a value of 1. Second labeled “STARTED” with a value of 0, and the third labeled “BOTH” with value of 1.

“Both” and “finished” will be awarded the wins and losses. Hence why their value is 1. If user selects “started”, the value is 0 so they get nothing.

I also added some JavaScript to make the checkbox function like a radio. So for each goalie, one of them always has to be selected.

By doing this, it allowed me to use the exact same query as my goals against input. Where i can make use it in my ‘data2 array’ by referring to it as ’ :finish '. And since each goalie has to select finished, started or both, it worked perfectly!

Here is my updated JS:

$(document).on('click', '.addgoalies', function(){     //when user clicks 'add', all this below will be added to html
  count++;      //counter is added by 1 each time 'add' is clicked
  var html = '';
   
        html += '<div class="goal"><select name="teamGoalie[]" class="teamzGoalie" data-playerz3="'+count+'" ><option value="">Team</option><?php echo fill_select_box($pdo, "0"); ?></select>';   
        html += '<select class="goalzGoalie" name="playerGoalie[]" id="playermenu3'+count+'"><option value="">Player</option></select>'; 
        html += '<input type="number" name="goalsAgainst[]" placeholder="0" id="goaliemenu'+count+'">'; 
        html += '<fieldset><input type="checkbox" name="statusGoalie[]" value="0" id="goaliemenufinish'+count+'"><label for "0">STARTED</label>'; 
        html += '<input type="checkbox" name="statusGoalie[]" value="1" id="goaliemenufinish2'+count+'"><label for "1">FINISHED</label>'; 
        html += '<input type="checkbox" name="statusGoalie[]" value="1" id="goaliemenufinish3'+count+'" checked><label for "1">BOTH</label></fieldset>'; 
        html += '<button type="button" name="remove" class="remove"><span class="glyphicon glyphicon-minus">REMOVE</span></button></div>';
  $('.wrapper3').append(html);
});

Here is my added JS piece that tells the checkboxes to function like a radio:

$(document).on('change', 'input[type="checkbox"]', function(){
  $(this).parents('fieldset').find('input[type="checkbox"]').not(this).prop('checked', false);
  if($(this).parents('fieldset').find('input[type="checkbox"]:checked').length == 0){
    $(this).prop('checked', true);
  }
});

And here is my updated PHP - only showing the modified checkbox query:

   $goalieresult = $_POST['statusGoalie'];
  $playerGoalie = $_POST['playerGoalie'];
  $goalsagainst = $_POST['goalsAgainst'];

for($count = 0; $count < count($playerGoalie); $count++){
        $data2 = array(
    
            ':playerid'=>$playerGoalie[$count],
            ':finish'=>$goalieresult[$count]
        );
        
        $sql2 = "UPDATE goalies SET                   
        Wins = Wins+:finish
        WHERE id = :playerid";
        $statement2 = $pdo->prepare($sql2);
        $statement2->execute($data2);   //execute statement
      }

Here is a screenshot of what my submit screen looks like just for reference. Using two goalies in this example via the checkbox method. The ‘REM’ is short for remove. The white boxes are the ‘goalsAgainst’ section, and do have a number that user can input in there, but have been too lazy to fix the styling in CSS hahaha :

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.