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


Problem with code that handles arrays and database updates





welshsteve
I think I've asked this before, but I always seem to come into trouble when re-using this code.

Can somebody please tell me why this code, doesn't work (gives a variable error).

EXAMPLE ONE
firstpage
Code:

<?php
$username = "USER";
$password = "PASS";
$hostname = "HOSTADDRESS";   
$dbcon = mysql_connect($hostname, $username, $password)
   or die("Unable to connect to database");
mysql_select_db("DBNAME");

$div = $_POST['div'];
$sql = "SELECT * FROM StatsOverall ORDER BY Surname, FirstName";
$result = mysql_query($sql);
if (!$result)
echo mysql_error();

echo "<table class='admin'><tr><th>Player</th><th>Pld</th><th>W</th></tr>";
      
while($row = mysql_fetch_array($result))
  {
   echo "<input type='hidden' name='ID[]' value='" . $row['ID'] . "' />";
   echo "<tr>";
   echo "<td><input type='text' style='width:150px;' name='FirstName[]' value='" . $row['FirstName'] . "' /><input type='text' style='width:150px;' name='Surname[]' value='" . $row['Surname'] . "' /></td>";
   echo "<td><input type='text' style='width:30px;' name='Pld[]' value='" . $row['Pld'] . "' /></td>";
   echo "<td><input type='text' style='width:30px;' name='W[]' value='" . $row['W'] . "' /></td>";
  echo "</tr>";
  }
echo "</table>";
echo "<input type='submit' name='submit' value='Update Stats' /></form>";
?>



secondpage
Code:

<?php
$username = "USER";
$password = "PASS";
$hostname = "HOSTADDRESS";   
$dbcon = mysql_connect($hostname, $username, $password)
   or die("Unable to connect to database");
mysql_select_db("DBNAME");

$FirstName = $_POST['FirstName'];
$Surname = $_POST['Surname'];
$Pld = $_POST['Pld'];
$W = $_POST['W'];
$ID = $_POST['ID'];

// run through the array and for each entry in the array, call this variable "$name"
for ($i=0; $i<sizeof($FirstName); $i++){
// start a variable called $i at 0, make it count the size of the $ID array and stop there, and make $i bigger by one each time

if ($FirstName[$i] !== ''){
         $sql = sprintf("UPDATE StatsOverall SET `FirstName`='%s',`Surname`='%s',`Pld`='%s',`W`='%s' WHERE StatsOverall.ID='%s';", $FirstName[$i], $Surname[$i], $Pld[$i], $W[$i] $ID[$i]);
// sprintf will substitute the %s' for the variables listed at the end of the statement

         $qry = mysql_query($sql) or die("Error updating stats<br />: ".mysql_error());
    }

}
echo "<p>Stats updated successfully.</p>";
echo "<p><a href='admin.php'>Back to main admin page</a></p>";
?>




But this one does.

EXAMPLE TWO
firstpage

Code:

<?php
$username = "USER";
$password = "PASS";
$hostname = "HOSTADDRESS";   
$dbcon = mysql_connect($hostname, $username, $password)
   or die("Unable to connect to database");
mysql_select_db("DBNAME");

$div = $_POST['div'];
$sql = "SELECT * FROM StatsOverall ORDER BY Surname, FirstName";
$result = mysql_query($sql);
if (!$result)
echo mysql_error();

echo "<table class='admin'><tr><th>Player</th><th>Pld</th><th>W</th></tr>";
   
while($row = mysql_fetch_array($result))
  {
   echo "<input type='hidden' name='teamname[]' value='" . $row['Team'] . "' /><input type='hidden' name='div' value='" . $div . "' />";
   echo "<tr>";
   echo "<td><input type='text' style='width:150px;' name='Team[]' value='" . $row['Team'] . "' /></td>";
   echo "<td><input type='text' style='width:20px;' name='Pld[]' value='" . $row['Pld'] . "' /></td>";
   echo "<td><input type='text' style='width:20px;' name='W[]' value='" . $row['W'] . "' /></td>";
   echo "<td><input type='text' style='width:20px;' name='D[]' value='" . $row['D'] . "' /></td>";
   echo "<td><input type='text' style='width:20px;' name='F[]' value='" . $row['F'] . "' /></td>";
   echo "<td><input type='text' style='width:20px;' name='A[]' value='" . $row['A'] . "' /></td>";
   echo "<td><input type='text' style='width:20px;' name='Ded[]' value='" . $row['Ded'] . "' /></td>";
  echo "</tr>";
  }
echo "</table>";
echo "<input type='submit' name='submit' value='Update Table' /></form>";
?>


secondpage

Code:

<?php
$username = "USER";
$password = "PASS";
$hostname = "HOSTADDRESS";   
$dbcon = mysql_connect($hostname, $username, $password)
   or die("Unable to connect to database");
mysql_select_db("DBNAME");

$Team = $_POST['Team'];
$Pld = $_POST['Pld'];
$W = $_POST['W'];
$D = $_POST['D'];
$F = $_POST['F'];
$A = $_POST['A'];
$Ded = $_POST['Ded'];
$teamname = $_POST['teamname'];
$div = $_POST['div'];
// run through the array and for each entry in the array, call this variable "$name"
for ($i=0; $i<sizeof($Team); $i++){
// start a variable called $i at 0, make it count the size of the $team array and stop there, and make $i bigger by one each time

if ($Team[$i] !== ''){
         $sql = sprintf("UPDATE `$div` SET `Team`='%s',`Pld`='%s',`W`='%s',`D`='%s',`F`='%s',`A`='%s',`Ded`='%s' WHERE $div.Team='%s';", $Team[$i], $Pld[$i], $W[$i], $D[$i], $F[$i], $A[$i], $Ded[$i], $teamname[$i]);
// sprintf will substitute the %s' for the variables listed at the end of the statement

//echo $sql;
//echo "<br /><br />";

         $qry = mysql_query($sql) or die("Error updating table<br />: ".mysql_error());
    }

}echo "<p>Table updated successfully.</p>";
echo "<p><a href='admin.php'>Back to main admin page</a></p>";

mysql_close($dbcon);
?>

The only difference I can see is the variable names
LxGoodies
welshsteve wrote:
I think I've asked this before, but I always seem to come into trouble when re-using this code.

Can somebody please tell me why this code, doesn't work

..

But this one does.

..


Q: What goes wrong ?

In the first solution, fields team, teamname, ded, A and F are missing. If you have a database filled in with (2), the UPDATE in (1) could work on more than 1 record, or fail when indexes are missing.. but again, you dont give info about the complaint, so I can only guess !

Rolling Eyes Lx
manfer
The first thing to do with code is to be sure it has no syntax errors and that is not very difficult as PHP shows normally very clear the first line in which it finds a syntax error.

The first code you posted has at least a syntax error on the following line so it can't ever work.
Code:

$sql = sprintf("UPDATE StatsOverall SET `FirstName`='%s',`Surname`='%s',`Pld`='%s',`W`='%s' WHERE StatsOverall.ID='%s';", $FirstName[$i], $Surname[$i], $Pld[$i], $W[$i] $ID[$i]);
Marcuzzo
this part I don't understand:

Code:
$result = mysql_query($sql);
if (!$result)
echo mysql_error();

echo "<table class='admin'><tr><th>Player</th><th>Pld</th><th>W</th></tr>";
   
while($row = mysql_fetch_array($result))
  {


why continue the script when there is an error.
if $result is false then mysql_query failed: http://php.net/manual/en/function.mysql-query.php

also, what is the expected output? what form is posted to this script?
also, as said by lxgoodies, you are missing variables in the first example
welshsteve
I've sorted this now. Missed out a comma after $W[$i]

Code:

$sql = sprintf("UPDATE StatsOverall SET `FirstName`='%s',`Surname`='%s',`Pld`='%s',`W`='%s' WHERE StatsOverall.ID='%s';", $FirstName[$i], $Surname[$i], $Pld[$i], $W[$i] $ID[$i]);


Should be

Code:

$sql = sprintf("UPDATE StatsOverall SET `FirstName`='%s',`Surname`='%s',`Pld`='%s',`W`='%s' WHERE StatsOverall.ID='%s';", $FirstName[$i], $Surname[$i], $Pld[$i], $W[$i], $ID[$i]);


The form posted to the script is the FIRSTPAGE part in each code sample I've posted on here. I've edited the original post to make it more obvious
Related topics
Using PHP code from a Database
Problem with creating a database -- help :(
phpBB plus 1.53 beta8 problem!!
Problem connecting to MySQL database with PHP [RESOLVED]
connection to database
Problem with my blog/database
A problem about numbering in database
One Liners for Code Cowboys
Programming Help & Support Guidelines
IONCUBE DECODER PROBLEM
everybody has server 2 problem? [solved]
setup.php
Best PC Security Programs for me!
websites with Drupal
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.