Struggling with an update command

Question

ceeandcee30 5 Newbie Poster

7 Years Ago

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

php

2 Contributors
12 Replies
2K Views
2 Days Discussion Span
Latest Post 7 Years Ago Latest Post by ceeandcee30

diafol

7 Years Ago

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?

diafol

7 Years Ago

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.

Edited 7 Years Ago by diafol

diafol

7 Years Ago

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

Reply to this topic

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.

ceeandcee30 5 Newbie Poster · Answer 1 · 2017-09-16T14:21:49+00:00

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

ceeandcee30 5 Newbie Poster · Answer 2 · 2017-09-17T00:27:19+00:00

Lets start with this...

Link to page...
http://hometownhockey.ca/fantasy/admin/data/lineups/cfhl/tmi/Index.php

diafol · Answer 3 · 2017-09-17T04:10:04+00:00

The link asks for a username and pwd.

ceeandcee30 5 Newbie Poster · Answer 4 · 2017-09-17T12:59:57+00:00

My apologies...

This link should work...
http://hometownhockey.ca/fantasy/cfhl/tmi/

ceeandcee30 5 Newbie Poster · Answer 5 · 2017-09-17T13:10:06+00:00

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!

ceeandcee30 5 Newbie Poster · Answer 6 · 2017-09-17T13:15:12+00:00

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

ceeandcee30 5 Newbie Poster · Answer 7 · 2017-09-17T13:19:49+00:00

ceeandcee30 5 Newbie Poster

7 Years Ago

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

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with pdf files.

playerdb.pdf (196.16 KB)

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with pdf files.

stats1617.pdf (206.72 KB)

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with pdf files.

gamesplayed.pdf (189.61 KB)

ceeandcee30 5 Newbie Poster · Answer 8 · 2017-09-18T13:36:32+00:00

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

ceeandcee30 5 Newbie Poster · Answer 9 · 2017-09-18T17:27:10+00:00

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