For Each Problem

**chris_stfc** · Jun 22, 2007 01:34

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

**halfer** · Jun 22, 2007 02:32

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.

**chris_stfc** · Jun 22, 2007 02:59

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

**halfer** · Jun 22, 2007 03:16

What happens if only one goal is scored in the match? Or none? Three or more?

**chris_stfc** · Jun 22, 2007 05:17

Hi Halfer,

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

Thanks

Chris

**halfer** · Jun 22, 2007 05:48

So if only one goal can be scored, how can you record two?

**chris_stfc** · Jun 22, 2007 07:34

Each scorer scores a goal. I know it sounds wrong! but it is meant to be like that!

Thanks

**byron3@earthlink** · Jun 22, 2007 11:05

Originally Posted by chris_stfc

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

Originally Posted by chris_stfc

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

Originally Posted by chris_stfc

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 ).

**chris_stfc** · Jun 22, 2007 12:39

Thanks. I'll take a look at that ;-)

**chris_stfc** · Jun 22, 2007 14:23

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>

**byron3@earthlink** · Jun 22, 2007 15:06

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]".

**chris_stfc** · Jun 23, 2007 03:56

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

**byron3@earthlink** · Jun 23, 2007 09:30

Originally Posted by chris_stfc

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.

Originally Posted by chris_stfc

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.

User Tag List

Thread: For Each Problem

Thread Tools

Display

For Each Problem

Bookmarks

Bookmarks

Posting Permissions