SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    For Each Problem

    For Each Problem

    Hi Guys,

    Finally getting my head around this. I’m trying to create a script where I’m using a foreach statement to post various data in the database. And using the script below this works:

    PHP Code:
    <?php

    if(isset($_REQUEST['Submit']))
    {
        
    // Get global values to post into database
      
    $team_id $_GET['team_id'];
      
    $lastinsert '1';
      
    // Values to be inserted into reports table 
      
    $player_id $_POST['player_id'];

    // IF i'm not getting data then insert into various tables
    if(!isset($_GET['report_id']))
    {
    // Values to be inserted into goals table                     
    foreach($_REQUEST['r'] as $row

        
    mysql_query("INSERT INTO `stats` ( `player_id`,`team_id`, `report_id` ) VALUES ('".$row['player_id']."', '".(int)$_GET['team_id']."', '".$lastinsert."')") OR DIE(mysql_error());
    }

    }
      else
    {
      
    $query_one mysql_query("Update `stats` set player_id='$player_id', team_id='$team_id', report_id='$report_id' where report_id=".$_GET['report_id']);
      
    $msg "Updated";
     }

    //End of POST submit

    if(isset($_GET['report_id']))

    {
      
    //GET data from Reports Table
      
    $query_one mysql_query("Select * From goals where report_id=".$_GET['report_id']);
      
        
    $report_id $row['report_id'];
        
    $player_id $row['player_id'];
        
    $team_id $row['team_id'];
    }

    ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
    <head>
    <title>Untitled Document</title>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    </head>

    <body>
    <form name="reports" method="post" action="">
    <?php echo $query_one?>
      <table width="100%" border="0" cellspacing="1" cellpadding="1">
        <tr> 
          <td width="14%"><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><strong>Goalkeeper//:</strong></font></td>
          <td width="86%"> <span class="next"> 
            <?php $p_id $player_id $player_id ''?>
            <select class="input" name="r[1]player_id " size="1"  style="width: 145" tabindex="1">
              <option value="" <?php echo ($p_id == '' 'selected="selected"' ''); ?>>Goalscorer</option>
              <?php 
                $type_array
    =get_player_names(); 
                foreach (
    $type_array as $players
               { 
                  print(
    "<option value=\"".$players['player_id']."\"" 
                  
    . ($p_id == $players['player_id'] ? 'selected="selected"' ''
                  . 
    ">".$players['player_name']."</option>\n"); 
              } 
         
    ?>
            </select>
            </span> </td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td><span class="next">
            <?php $p_id $player_id $player_id ''?>
            <select class="input" name="r[2]player_id" size="1"  style="width: 145" tabindex="1">
              <option value="" <?php echo ($p_id == '' 'selected="selected"' ''); ?>>Goalscorer</option>
              <?php 
                $type_array
    =get_player_names(); 
                foreach (
    $type_array as $players
               { 
                  print(
    "<option value=\"".$players['player_id']."\"" 
                  
    . ($p_id == $players['player_id'] ? 'selected="selected"' ''
                  . 
    ">".$players['player_name']."</option>\n"); 
              } 
         
    ?>
            </select>
            </span></td>
        </tr>
      </table>
      <input type="submit" name="Submit" value="Submit" class="button">
      <input type="reset" name="Reset" value="Reset" class="button">
    </form>
    </body>
    </html>


    However I have two questions

    1)How do I use foreach to get the row for each of the dropdowns where report_id=1 from the database (2 records) and place the first player_id value in the database in the first dropdown, and the second player_id value in the second dropdown?

    2) Once they are then placed in each dropdown how can I then UPDATE this data for each row in the database accordingly?

    Thanks

    Chris

  2. #2
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What you are trying to do is entirely achievable, but I'm not 100% clear what your script does. If you could describe succinctly what it does, then someone may be able to advise.

  3. #3
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    Sorry, sometimes my explanation of things aint too hot! ;-)

    I'm trying to create a system whereby I enter two goalscorers from a football match into a database

    Here's What I have done
    Create a script which contains two dropdown menus. When I select a value from each of the dropdown's it's then saved into the Mysql database as a row upton (INSERT) submit ie:



    Now here's what I want to do
    Now I may need to edit the details I have entered in the database, so in my script I want to be able to:

    1) Find all records in my database where the report_id in the database = '1' (two records)

    2) Get the values from the player_id columns for each of the two rows in the database table and default them the dropdown value I selected in the first place when I ran my insert query

    3) I then want to be able to change this data. So if I select a different value in the first dropdown an UPDATE query is run to change the value for the row i'm changing in the database.

    Many Thanks

    Chris

  4. #4
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What happens if only one goal is scored in the match? Or none? Three or more?

  5. #5
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Halfer,

    This sounds wierd, but that won't happen (as soon as a goal is scored the match is over!)

    Thanks

    Chris

  6. #6
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    23
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So if only one goal can be scored, how can you record two?

  7. #7
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Each scorer scores a goal. I know it sounds wrong! but it is meant to be like that!

    Thanks
    Last edited by chris_stfc; Jun 22, 2007 at 07:58.

  8. #8
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chris_stfc View Post

    Now here's what I want to do
    Now I may need to edit the details I have entered in the database, so in my script I want to be able to:

    1) Find all records in my database where the report_id in the database = '1' (two records)
    PHP Code:
    /* You only need the single quotes if 1 is stored as a string value. */
    /* Replace the [database] with the name of your databse */
    $query "select * from [database] where report_id = '1'"
    Quote Originally Posted by chris_stfc View Post
    2) Get the values from the player_id columns for each of the two rows in the database table and default them the dropdown value I selected in the first place when I ran my insert query.
    When you get the resultset from the above query it will contain two records, each will contain the player_id value. Use a function like the one below to display the select code.

    PHP Code:
    function DisplayTeamIDSelect ( array $Teams_r$Sel_Name$Selected "" ) {
        echo 
    "<select name='$Sel_Name'>"// Open select
        
    foreach ( $Teams_r as $name ) { Iterate through teams
            
    if ( $Selected == $name ) { // Does the selected team = our current option ?
                
    echo "<option selected = 'selected'>$name</option>"// Echo selected
            
    } else {
                echo 
    "    <option>" .$name"</option>\n";
            } 
    //End if
        
    // End foreach
        
    echo "</select>\n"// Close select
    // End function 
    Quote Originally Posted by chris_stfc View Post
    3) I then want to be able to change this data. So if I select a different value in the first dropdown an UPDATE query is run to change the value for the row i'm changing in the database.
    You need some way of distinguishing between inserting and updating data, a couple of different approaches would be to either set an edit flag upon loading this data into your form, or using two different submit buttons ( one for add and one for update ).
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  9. #9
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks. I'll take a look at that ;-)

  10. #10
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've had a good look and fiddled, and to be blatently honest I have not got a clue about how that code works. Thanks for helping though!

    I thought something like this would do the trick, but the UPDATE query does not work on the foreach.

    It simply takes the last value selected from r[2] and updates/overwrites all the records in the database where report_id=1 with the value selected and ignores any values I changed in r[1]



    So for example in this table if I was to run my update query on it and change r[2] to player_id=4 r[1] becomes the same value (4) in the table nomatter if r[1] is set to player_id=8

    PHP Code:
    <?php 

    if(isset($_REQUEST['Submit'])) 

         
    // Values used for all three tables 
      
    $team_id $_GET['team_id']; 
      
    $lastinsert '1'
       
    // Values to be inserted into reports table 
      
    $player_id $_POST['player_id']; 

    // IF i'm not getting data then insert into various tables 
    if(!isset($_GET['report_id'])) 

    // Values to be inserted into goals table                      
    foreach($_REQUEST['r'] as $row

        
    mysql_query("INSERT INTO `stats` ( `player_id`,`team_id`, `report_id` ) VALUES ('".$row['player_id']."', '".(int)$_GET['team_id']."', '".$lastinsert."')") OR DIE(mysql_error()); 



      else 

    foreach(
    $_REQUEST['r'] as $row
       { 
      
    $query_one mysql_query("Update `stats` set player_id='".$row['player_id']."', team_id='$team_id', report_id='".$lastinsert."' where report_id=".$_GET['report_id']); 
       } 


    //End of POST submit 

    if(isset($_GET['report_id'])) 


      
    //GET data from Reports Table 
      
    $query_one mysql_query("Select * From goals where report_id=".$_GET['report_id']); 
       
        
    $report_id $row['report_id']; 
        
    $player_id $row['player_id']; 
        
    $team_id $row['team_id']; 


    ?> 
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
    <html> 
    <head> 
    <title>Player Stats</title> 
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> 
    </head> 

    <body> 
    <form name="stats" method="post" action=""> 
      <table width="100%" border="0" cellspacing="1" cellpadding="1"> 
        <tr> 
          <td width="14%">Player One:</td> 
          <td width="86%"> 
            <?php $p_id $player_id $player_id ''?> 
            <select class="input" name="r[1]player_id " size="1"  style="width: 145" tabindex="1"> 
              <option value="" <?php echo ($p_id == '' 'selected="selected"' ''); ?>>Player One</option> 
              <?php 
                $type_array
    =get_player_names(); 
                foreach (
    $type_array as $players
               { 
                  print(
    "<option value=\"".$players['player_id']."\"" 
                  
    . ($p_id == $players['player_id'] ? 'selected="selected"' ''
                  . 
    ">".$players['player_name']."</option>\n"); 
               } 
              
    ?> 
            </select> 
            </td> 
        </tr> 
        <tr> 
          <td>Player Two:</td> 
          <td> 
            <?php $p_id $player_id $player_id ''?> 
            <select class="input" name="r[2]player_id" size="1"  style="width: 145" tabindex="1"> 
              <option value="" <?php echo ($p_id == '' 'selected="selected"' ''); ?>>Player Two</option> 
              <?php 
                $type_array
    =get_player_names(); 
                foreach (
    $type_array as $players
               { 
                  print(
    "<option value=\"".$players['player_id']."\"" 
                  
    . ($p_id == $players['player_id'] ? 'selected="selected"' ''
                  . 
    ">".$players['player_name']."</option>\n"); 
              } 
         
    ?> 
            </select> 
            </span> 
        </tr> 
      </table> 
      <input type="submit" name="Submit" value="Submit" class="button"> 
      <input type="reset" name="Reset" value="Reset" class="button"> 
    </form> 
    </body> 
    </html>

  11. #11
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to distinguish between the two records with the same report number, either by your stat_id or your player_id. In the record set you use as an example stat_id and player_id are the only columns that do not contain matching info. Your where statement needs to say "and player_id = [Player_ID_To_Change]" or "and stat_id = [Stat_ID_To_Change]".
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.

  12. #12
    SitePoint Evangelist
    Join Date
    Apr 2007
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Byron,

    Fist of all thank you for your help.

    Now this update line:

    PHP Code:
    foreach($_REQUEST['r'] as $row)    
    {   
    $query_one mysql_query("Update `stats` set player_id='".$row['player_id']."', team_id='$team_id', report_id='".$lastinsert."' where report_id=".$_GET['report_id']);    

    You state that I need to say Where player_id= or stat_id= rather than WHERE report_id=".$_GET['report_id']);

    And this is where i'm confused. I would have thought it would have taken each of the r[] values. Found all entries in the database where report_id=1 and updated them. If I change my statement to update using player_id some players in the table may have the same number and stat_id would only update one record in the database I would have thought?



    Thanks

  13. #13
    SitePoint Evangelist
    Join Date
    Aug 2005
    Posts
    453
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chris_stfc View Post
    I've had a good look and fiddled, and to be blatently honest I have not got a clue about how that code works. Thanks for helping though!
    Chris that function will display you a select box, the first parameter is an array of option values, the second parameter is a name for the select so that you know what post or get value it is associated with, the third parameter is the value of the option in the list that will be selected upon display. The third parameter is optional so simply omitting will display your select with nothing selected.

    Quote Originally Posted by chris_stfc View Post
    I thought something like this would do the trick, but the UPDATE query does not work on the foreach.

    It simply takes the last value selected from r[2] and updates/overwrites all the records in the database where report_id=1 with the value selected and ignores any values I changed in r[1]

    So for example in this table if I was to run my update query on it and change r[2] to player_id=4 r[1] becomes the same value (4) in the table nomatter if r[1] is set to player_id=8
    Change r[1] with your update and use "where report_id = '1' and player_id ='3'". Change r[2] with your update and use "where report_id = '1' and player_id = '7'. As stated in my previous post you must distinguish between the two records in report 1 with one of the two fields.
    Computers and Fire ...
    In the hands of the inexperienced or uneducated,
    the results can be disastrous.
    While the professional can tame, master even conquer.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •