One form, insert into multiple rows in DB

richardbotw

Hello all,

I've been designing a web-site, and on one of my pages I have a form. And one it, there are 14 database-driven drop-down lists which featue the names of the players in my soccer team. The idea is that after every game, the match details are submitted, as well as the names of the players who played in the game.

Therefore, once the user has selected who played from the drop-down list, on the submission of the form, I want the data to be inserted into fourteen different rows in the database.

I've been using the very limiting PHP Login Suite plugin for Dreamweaver MX so far, therefore if there is any excess junk coce, that's why... I've tried to "edit" the code myself to see if changing it will work... but it doesn't. Any help would be appreciated so that form fields PlayerAdd1 through the Player PlayerAdd14 successfully populate the 14 rows in the DB... Thank you so much - if you can! 😕

$editFormAction = $HTTP_SERVER_VARS['PHP_SELF'];
if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
  $editFormAction .= "?" . $HTTP_SERVER_VARS['QUERY_STRING'];
}

if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "player")) {
  $insertSQL = sprintf("INSERT INTO fixtureplayer (FixtureID, PlayerID, Appearance, SubAppearance) VALUES (%s, %s, %s, %s)",
  GetSQLValueString($HTTP_POST_VARS['FixtureID1'], "int"),
  GetSQLValueString($HTTP_POST_VARS['playeradd1'], "int"),
  GetSQLValueString($HTTP_POST_VARS['Appearance1'], "int"),
 GetSQLValueString($HTTP_POST_VARS['SubAppearance1'], "int"));

// the bit I added (without success)

  $insertSQL = sprintf("INSERT INTO fixtureplayer (FixtureID, PlayerID, Appearance, SubAppearance) VALUES (%s, %s, %s, %s)",
  GetSQLValueString($HTTP_POST_VARS['FixtureID2'], "int"),
  GetSQLValueString($HTTP_POST_VARS['playeradd2'], "int"),
  GetSQLValueString($HTTP_POST_VARS['Appearance2'], "int"),
 GetSQLValueString($HTTP_POST_VARS['SubAppearance2'], "int"));

// end of the bit I added (without success)

  mysql_select_db($database_connDB, $connDB);
  $Result1 = mysql_query($insertSQL, $connDB) or die(mysql_error());

  $insertGoTo = "match_detail2.php";
  if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $HTTP_SERVER_VARS['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

ahundiak

The basic problem is that you are not inserting the first record before you build the second record:

$sql = "INSERT ... player1"
mysql_query($sql);
$sql = "INSERT ... player2"
mysql_query($sql);

richardbotw

Hey thanks for that, after some playing around I got it working fine. I've got another dillema though.

I'm trying to write a query, where:

If column "PlayerID" = 1 (for example)
AND column "Appearance" = 1
And column "GoalsScored" <> 0

Then, return all the results where PlayerID 1 played in the game and scored a goal.

The idea is to create a "total" of the goals-scored, for a statistics page. At the moment I'm using the following code (stripped down the other fields in the query for simplicity)

mysql_select_db($database_connDB, $connDB);
$query_rsPlayer1Goals = "SELECT fixtureplayer.PlayerID, fixtureplayer.Appearance, fixtureplayer.GoalsScored FROM fixtureplayer WHERE ( (fixtureplayer.PlayerID = 1) and (fixtureplayer.Appearance = 1) and (fixtureplayer.GoalsScored <> 0))";
$rsPlayer1Goals = mysql_query($query_rsPlayer1Goals, $connDB) or die(mysql_error());
$row_rsPlayer1Goals = mysql_fetch_assoc($rsPlayer1Goals);
$totalRows_rsPlayer1Goals = mysql_num_rows($rsPlayer1Goals);

Therefore, if I use

<?php echo $totalRows_rsPlayer1Goals ; ?>

..this only returns the number of records where the above criteria are met. I need however, to TOTAL the results in the GoalsScored column - but I haven't got a clue how to do this, can anyone help if possible?

Would be much appreciated.

Cheers

BooRadLey

mysql_select_db($database_connDB, $connDB);
$query_rsPlayer1Goals = "SELECT fixtureplayer.PlayerID, fixtureplayer.Appearance, fixtureplayer.GoalsScored FROM fixtureplayer WHERE ( (fixtureplayer.PlayerID = 1) and (fixtureplayer.Appearance = 1) and (fixtureplayer.GoalsScored <> 0))";
$rsPlayer1Goals = mysql_query($query_rsPlayer1Goals, $connDB) or die(mysql_error());
$totalRows_rsPlayer1Goals = mysql_num_rows($rsPlayer1Goals);
//loop through results adding the GoalsScored column to a variable
$total_goals = 0;
while($row_rsPlayer1Goals = mysql_fetch_assoc($rsPlayer1Goals)) {
    $total_goals = $total_goals + $row_rsPlayer1Goals['GoalsScored'];
}
// echo this result to display goals scored
echo $total_goals;

Hope this helps.

Brad

richardbotw

Hi Brad, thanks for that - I've inserted the code you provided me with, but it only seems to echo the

$total_goals = 0;

part of the code, without seemingly doing the loop.

For example, for one player it should give the result "5", as he has scored that many goals this season, however it echoes "0".

Any ideas? I can see the logic of what you've done but it just doesnt seem to work.