Good Morning Dani Web,

I have been struggling for days with a section of code on a hockey pool site that I have built. I am hoping someone here can help.

A little background...

  • This is an NHL pool where each player can be drafted twice - defined in the database as CFHL_A and CFHL_B
  • I am struggling to update four fields required for line-up submission - 26A, 26B, 26AA, 26BB
  • These fields currently are set for week 26 and change each week. To keep my request simple, I have removed the variable and just hard coded the fields above.
  • The code I have works to update 26B, 26AA, and 26BB but for some reason, I cannot get it to update 26A.
  • I will only post the relevant sections of code to keep this post simpler.
  • Any help would be appreciated. If you would like to see the page function, email me at hometownmedia30 AT gmail .com and I will send you the link.

UPDATE CODE near top of page.

//lineup_A
   if( isset($_POST['Submit']) && $_POST['Submit']=='Submit' )
{
    foreach($_POST['lineup_A'] as $playerID=>$v)
    {
        //initialize to empty string and add field=value only if something was submitted
        $update="";

        $lineup_A=mysql_real_escape_string( trim($_POST['lineup_A'][$playerID]) );
        $lineup_AA=mysql_real_escape_string( trim($_POST['lineup_AA'][$playerID]) );
        $lineup_B=mysql_real_escape_string( trim($_POST['lineup_B'][$playerID]) );
        $lineup_BB=mysql_real_escape_string( trim($_POST['lineup_BB'][$playerID]) );

        if( !empty($lineup_A) ) 
        {
            $update.="`26A`='".$lineup_A."'";
        }

        if( !empty($lineup_B) )
        {
            $update.="`26B`='".$lineup_B."'";
        }

        if( !empty($lineup_AA) ) 
        {
            $update.="`26AA`='".$lineup_AA."'";
        }

        if( !empty($lineup_BB) )
        {
            $update.="`26BB`='".$lineup_BB."'";
        }
    }

}

error_reporting (E_ERROR);

//lineup_B
   if( isset($_POST['Submit']) && $_POST['Submit']=='Submit' )
{
    foreach($_POST['lineup_B'] as $playerID=>$v)
    {
        //initialize to empty string and add field=value only if something was submitted
        $update="";

        $lineup_A=mysql_real_escape_string( trim($_POST['lineup_A'][$playerID]) );
        $lineup_AA=mysql_real_escape_string( trim($_POST['lineup_AA'][$playerID]) );
        $lineup_B=mysql_real_escape_string( trim($_POST['lineup_B'][$playerID]) );
        $lineup_BB=mysql_real_escape_string( trim($_POST['lineup_BB'][$playerID]) );

        if( !empty($lineup_A) ) 
        {
            $update.="`26A`='".$lineup_A."'";
        }

        if( !empty($lineup_B) )
        {
            $update.="`26B`='".$lineup_B."'";
        }

        if( !empty($lineup_AA) ) 
        {
            $update.="`26AA`='".$lineup_AA."'";
        }

        if( !empty($lineup_BB) )
        {
            $update.="`26BB`='".$lineup_BB."'";
        }

        if(!empty($update))
        {
            $update = 'UPDATE `stats'.$year.'` SET '. $update . " WHERE `playerID` ='".$playerID."'";
            mysql_query($update) or die( sprintf('Error @Line %d while trying to execute<br/>%s<br/>%s',__LINE__,$update, mysql_error() ) );
        }
    }

}
// Get all the data from the "player" table
error_reporting (E_ERROR);

//lineup_AA
   if( isset($_POST['Submit']) && $_POST['Submit']=='Submit' )
{
    foreach($_POST['lineup_AA'] as $playerID=>$v)
    {
        //initialize to empty string and add field=value only if something was submitted
        $update="";

        $lineup_A=mysql_real_escape_string( trim($_POST['lineup_A'][$playerID]) );
        $lineup_AA=mysql_real_escape_string( trim($_POST['lineup_AA'][$playerID]) );
        $lineup_B=mysql_real_escape_string( trim($_POST['lineup_B'][$playerID]) );
        $lineup_BB=mysql_real_escape_string( trim($_POST['lineup_BB'][$playerID]) );

        if( !empty($lineup_A) ) 
        {
            $update.="`26A`='".$lineup_A."'";
        }

        if( !empty($lineup_B) )
        {
            $update.="`26B`='".$lineup_B."'";
        }

        if( !empty($lineup_AA) ) 
        {
            $update.="`26AA`='".$lineup_AA."'";
        }

        if( !empty($lineup_BB) )
        {
            $update.="`26BB`='".$lineup_BB."'";
        }

        if(!empty($update))
        {
            $update = 'UPDATE `stats'.$year.'` SET '. $update . " WHERE `playerID` ='".$playerID."'";
            mysql_query($update) or die( sprintf('Error @Line %d while trying to execute<br/>%s<br/>%s',__LINE__,$update, mysql_error() ) );
        }
    }

}
// Get all the data from the "player" table
error_reporting (E_ERROR);

//lineup_BB
   if( isset($_POST['Submit']) && $_POST['Submit']=='Submit' )
{
    foreach($_POST['lineup_BB'] as $playerID=>$v)
    {
        //initialize to empty string and add field=value only if something was submitted
        $update="";

        $lineup_A=mysql_real_escape_string( trim($_POST['lineup_A'][$playerID]) );
        $lineup_AA=mysql_real_escape_string( trim($_POST['lineup_AA'][$playerID]) );
        $lineup_B=mysql_real_escape_string( trim($_POST['lineup_B'][$playerID]) );
        $lineup_BB=mysql_real_escape_string( trim($_POST['lineup_BB'][$playerID]) );

        if( !empty($lineup_A) ) 
        {
            $update.="`26A`='".$lineup_A."'";
        }

        if( !empty($lineup_B) )
        {
            $update.="`26B`='".$lineup_B."'";
        }

        if( !empty($lineup_AA) ) 
        {
            $update.="`26AA`='".$lineup_AA."'";
        }

        if( !empty($lineup_BB) )
        {
            $update.="`26BB`='".$lineup_BB."'";
        }

        if(!empty($update))
        {
            $update = 'UPDATE `stats'.$year.'` SET '. $update . " WHERE `playerID` ='".$playerID."'";
            mysql_query($update) or die( sprintf('Error @Line %d while trying to execute<br/>%s<br/>%s',__LINE__,$update, mysql_error() ) );
        }
    }

}
// Get all the data from the "player" table
error_reporting (E_ERROR);

INPUT SECTION (contained within a larger table)

    // Major Line-Up
    echo "</td><td width='40' bgcolor='FFFFFF' align='center'><font face='arial' size='2' color='000000'>"; 
    if ($row['CFHL_A']=="BULL") {
    echo "<input type='checkbox' id='BULL' value='BULL' align='center' size='2' name='lineup_A[".$row['playerID']."]'>";
    } 
    elseif ($row['CFHL_B']=="BULL") {
    echo "<input type='checkbox' id='BULL' value='BULL' align='center' size='2' name='lineup_B[".$row['playerID']."]'>";
    }

    // Major Bench
    echo "</td><td width='40' bgcolor='666666' align='center'><font face='arial' size='2' color='000000'>"; 
    if ($row['CFHL_A']=="BULL") {
    echo "<input type='checkbox' id='X' value='X' align='center' size='2' name='lineup_A[".$row['playerID']."]'>";
    } elseif ($row['CFHL_B']=="BULL") {
    echo "<input type='checkbox' id='X' value='X' align='center' size='2' name='lineup_B[".$row['playerID']."]'>";
    }

    // Major Status 
    echo "</td><td width='40' bgcolor='FFFF66' valign='center' align='center'><font face='arial' size='1' color='CC0000'><b>";
    if ($row['26A']=="BULL") echo "<img src='/images/bar_active.png' width='40' height='12'><br>";
    if ($row['26B']=="BULL") echo "<img src='/images/bar_active.png' width='40' height='12'><br>";

    // Minor Line-Up
    echo "</td><td width='40' bgcolor='CCCCCC' align='center'><font face='arial' size='2' color='000000'>"; 
    if (($row['CFHL_A']=="BULL") and (($row['PROT_A']=="") or ($row['PROT_A']=="N"))){
    echo "<input type='checkbox' id='BULL2' value='BULL2' align='center' size='2' name='lineup_AA[".$row['playerID']."]'>";
    } elseif (($row['CFHL_B']=="BULL") and (($row['PROT_B']=="") or ($row['PROT_B']=="N"))) {
    echo "<input type='checkbox' id='BULL2' value='BULL2' align='center' size='2' name='lineup_BB[".$row['playerID']."]'>";
    }

    // Minor Bench  
    echo "</td><td width='40' bgcolor='666666' align='center'><font face='arial' size='2' color='000000'>"; 
    if (($row['CFHL_A']=="BULL") and (($row['PROT_A']=="") or ($row['PROT_A']=="N"))){
    echo "<input type='checkbox' id='X' value='X' align='center' size='2' name='lineup_AA[".$row['playerID']."]'>";
    } elseif (($row['CFHL_B']=="BULL") and (($row['PROT_B']=="") or ($row['PROT_B']=="N"))) {
    echo "<input type='checkbox' id='X' value='X' align='center' size='2' name='lineup_BB[".$row['playerID']."]'>";
    }

    // Minor Status     
    echo "</td><td width='40' bgcolor='FFFF66' valign='center' align='center'><font face='arial' size='1' color='CC0000'><b>";
    if ($row['26AA']=="BULL2") echo "<img src='/images/bar_active.png' width='40' height='12'><br>";
    if ($row['26BB']=="BULL2") echo "<img src='/images/bar_active.png' width='40' height='12'><br>";
Member Avatar for diafol

The code looks like there is a lot of duplication and could be simplified considerably (from a quite scan). You're also using deprecated mysql functions and deprecated HTML. You are using PHP to output all the HTML - very difficult to maintain - it may be easier to have HTML as markup and just include the odd bit of php here and there. Sorry do not have a solution. Could you give the schemas of your DB tables?

Thanks Diafol. How do I give you the schemas of the DB tables?

Member Avatar for diafol

The most useful way for us would be an export (copy and paste here). Have a look at this link to see:

https://dba.stackexchange.com/questions/15537/how-to-display-database-structure-in-phpmyadmin

This means we can set up a copy of your relevant tables and test them. Then when we know how the data is set up, we can look into the code to see what you're trying to do. As I said, the code looks kinda over-complicated.

Also a screenshot of the table in your webpage could be useful. I'm assuming there are multiple players.

Member Avatar for diafol

The link asks for a username and pwd.

I followed that link and have attached the schemas of the DB tables as requested.

Also, my db query is below.

$GETQ="Select
playerdb.playerID,
playerdb.Last,
playerdb.`First`,
playerdb.Pos,
playerdb.`No`,
playerdb.Team,
playerdb.CFHL_A,
playerdb.CFHL_B,
playerdb.PROT_A,
playerdB.PROT_B,
stats1617.playerID,
stats1617.GP1617_wk26,
stats1617.PTS1617_wk26,
stats1617.26A,
stats1617.26AA,
stats1617.26B,
stats1617.26BB,
gamesplayed.Team,
gamesplayed.26

From
playerdb

Inner Join stats1617 ON stats1617.playerID = playerdb.playerID
Inner Join gamesplayed ON playerdb.Team = gamesplayed.Team

Where
playerdb.CFHL_A = 'BULL' OR
playerdb.CFHL_B = 'BULL'

Group By
stats1617.playerID

Order By
field(`playerdb`.`Pos`,'LW','C','RW','D','G'), `PTS1617_wk26` DESC, `GP1617_wk26` ASC";

$result = mysql_query($GETQ)
or die(mysql_error());  

Thanks in advance!

Just realized that the site will not allow me to attach an .sql file. How do I send it to you?

I have attached a pdf printout of the structure of the three tables. Hope this helps.

Thank you for your help. I have solved this question. Much appreciated.

Member Avatar for diafol

Sorry for the delay. Had some personal stuff to see to. Glad you got it sorted.

Nothing to apologize for...your priorities are in the right order! I appreciate your help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.