FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Help needed with getting table to auto update via html form





welshsteve
Hi everyone, I've got a script which is supposed to automatically update a league table when scores are entered.

Here's the html form.

Code:
<form method="post" action="sl_sendscore_p1_go.php">
   Home Team:
   <select name="teamhome" id="teamhome">
      <option value="Railway Inn B">Railway Inn B</option>
      <option value="Hodcarrier A">Hodcarrier A</option>
      <option value="St Patricks Club A">St Patricks Club A</option>
      <option value="Greyhound B (Simple Simon)">Greyhound B (Simple Simon)</option>
      <option value="Wheatsheaf C">Wheatsheaf C</option>
      <option value="The New Inn">The New Inn</option>
      <option value="Radford Semele Sports and Social">Radford Semele S&amp;S</option>
      <option value="Whitnash Tavern A">Whitnash Tavern A</option>
      <option value="Sunny Jet">Sunny Jet</option>
      <option value="Whitnash Tavern B">Whitnash Tavern B</option>
   </select>
   Home Score: <input type="text" class="fieldtxtsml" size="3" name="scorehome" id="scorehome" />

   Away Team:                   
   <select name="teamaway" id="teamaway">
      <option value="Railway Inn B">Railway Inn B</option>
      <option value="Hodcarrier A">Hodcarrier A</option>
      <option value="St Patricks Club A">St Patricks Club A</option>
      <option value="Greyhound B (Simple Simon)">Greyhound B (Simple Simon)</option>
      <option value="Wheatsheaf C">Wheatsheaf C</option>
      <option value="The New Inn">The New Inn</option>
      <option value="Radford Semele Sports and Social">Radford Semele S&amp;S</option>
      <option value="Whitnash Tavern A">Whitnash Tavern A</option>
      <option value="Sunny Jet">Sunny Jet</option>
      <option value="Whitnash Tavern B">Whitnash Tavern B</option>
   </select>
   Away Score: <input type="text" class="fieldtxtsml" size="3" name="scoreaway" id="scoreaway" />

   <input type="submit" name="btnSubmit" id="btnSubmit" class="btn" value="Submit Score" />
   <input type="reset" name="btnReset" id="btnReset" class="btn" value="Reset Form" />
</form>


And here's the script
Code:

<?php
$con = mysql_connect("SERVER","USER","PASS");
if (!$con)
  {
  die('Could not connect: ' . mysql_error() . '<br /><br />Please contact your web developer');
  }

mysql_select_db("DATABASE", $con);

/* variables received from the form */
$teamhome = mysql_real_escape_string($_POST['teamhome']);
$teamaway = mysql_real_escape_string($_POST['teamaway']);
$scorehome = mysql_real_escape_string($_POST['scorehome']);
$scoreaway = mysql_real_escape_string($_POST['scoreaway']);

$newhomediff = $scorehome - $scoreaway;
$newawaydiff = $scoreaway - $scorehome;

if($scorehome > $scoreaway)
{
  $sql = "UPDATE sl_tab_dp SET Pld=Pld+1,W=W+1,F=F+".$scorehome.",A=A+".$scoreaway.",Diff=Diff+".$newhomediff.",Pts=Pts+2 WHERE Team='$teamhome'";
  $res = mysql_query($sql);
  $sql2 = "UPDATE sl_tab_dp SET Pld=Pld+1,L=L+1,F=F+".$scoreaway.",A=A+".$scorehome.",Diff=Diff+".$newawaydiff." WHERE Team='$teamaway'";
  $res2 = mysql_query($sql2);
}
elseif($scoreaway > $scorehome)
{
  $sql = "UPDATE sl_tab_dp SET Pld=Pld+1,W=W+1,F=F+".$scoreaway.",A=A+".$scorehome.",Diff=Diff+".$newawaydiff.",Pts=Pts+2 WHERE Team='$teamaway'";
  $res = mysql_query($sql);
  $sql2 = "UPDATE sl_tab_dp SET Pld=Pld+1,L=L+1,F=F+".$scorehome.",A=A+".$scoreaway.",Diff=Diff+".$newhomediff." WHERE Team='$teamhome'";
  $res2 = mysql_query($sql2);
}
else
{
  $sql = "UPDATE sl_tab_dp SET Pld=Pld+1,D=D+1,F=F+".$scorehome.",A=A+".$scoreaway.",Pts=Pts+1 WHERE Team='$teamhome'";
  $res = mysql_query($sql);
  $sql2 = "UPDATE sl_tab_dp SET Pld=Pld+1,D=D+1,F=F+".$scoreaway.",A=A+".$scorehome."Pts=Pts+1 WHERE Team='$teamaway'";
  $res2 = mysql_query($sql2);
}

echo '<p>Table Updated. <a href="sl_sendscore_p.php">Add Another Score</a></p>';
echo '<p><a href="../sl_tab_dp.php">View Table</a></p>';
echo '<p><a href="../index.php">Home</a></p>';

mysql_close($con);


?>


If the match is a draw i.e. $scorehome and $scoreaway are the same value, it updates the "Home Team" details, but ignores the "Away Team".

Therefore the following part of the code isn't working

Code:

else
{
  $sql = "UPDATE sl_tab_dp SET Pld=Pld+1,D=D+1,F=F+".$scorehome.",A=A+".$scoreaway.",Pts=Pts+1 WHERE Team='$teamhome'";
  $res = mysql_query($sql);
  $sql2 = "UPDATE sl_tab_dp SET Pld=Pld+1,D=D+1,F=F+".$scoreaway.",A=A+".$scorehome."Pts=Pts+1 WHERE Team='$teamaway'";
  $res2 = mysql_query($sql2);
}



Can anybody shed some light on why this is the case. I've tried changing the else to an elseif?

i.e.
Code:

elseif($scorehome == $scoreaway)
{
  $sql = "UPDATE sl_tab_dp SET Pld=Pld+1,D=D+1,F=F+".$scorehome.",A=A+".$scoreaway.",Pts=Pts+1 WHERE Team='$teamhome'";
  $res = mysql_query($sql);
  $sql2 = "UPDATE sl_tab_dp SET Pld=Pld+1,D=D+1,F=F+".$scoreaway.",A=A+".$scorehome."Pts=Pts+1 WHERE Team='$teamaway'";
  $res2 = mysql_query($sql2);
}


But this doesn't work either.
rvec
you could put those last two together in one query. If the $scorehome==$scoreaway you could do this:
Code:
$sql = "UPDATE sl_tab_dp SET Pld=Pld+1,D=D+1,F=F+".$scorehome.",A=A+".$scoreaway.",Pts=Pts+1 WHERE Team='$teamhome' OR Team='$teamaway'";


And you don't have to mysql_real_escape_string those scores. Because they are integers it would be better to do this:
Code:
$scorehome = (int) $_POST['scorehome'];
$scoreaway = (int) $_POST['scoreaway'];

Then you can be sure they are integers and no mysql commands, but also no string could be put in there.

btw your mistake:
you forgot a , after $scorehome in the query.
welshsteve
Thanks for that, much appreciated.
Related topics
Please help me!!!! HTML/Flash help needed!
A very good PHP MySQL Tutorial
HTML form, list of options or text input
Send Mail HTML Form
parking a domain on server 2 using directAdmin
Can't Update Index.html
How to upload a file via html-form and php?
photoshop/ dreamweaver help needed...
Big Help needed .
help needed with css & javascript
Error trying to display SQL statement in html table
mySQL help needed, any other good database thing?
No icons on backend??
upload file to server using html form and CGI C
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.