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


Array Update SQL script not working





welshsteve
Hi everyone. I have a script that works through an array. I have it working for a league table, but using it on another page I can't get it to work. Benn at it for hours and lost the will to live LOL. Could somebody take a look and let me know what's wrong.

Page that displays the data from database
Code:


<?php
$team = $_POST['team'] ;
$theteam = str_replace(" ","_",$team);
$statsteam = "Stats_W_".$theteam;
$username = "USERNAME";
$password = "PASSWORD";
$hostname = "HOST ADDRESS";   
$dbcon = mysql_connect($hostname, $username, $password)
   or die("Unable to connect to database");
mysql_select_db("DB NAME");
$sql = "SELECT * FROM $statsteam ORDER BY Player;";
$result = mysql_query($sql);
if (!$result)
echo mysql_error();

echo "<table class='tabs'>
<tr>
<th class='team'>Player</th>
<th class='item'>Pld</th>
<th class='item'>W</th>
</tr>";
      
while($row = mysql_fetch_array($result))
  {
   echo "<input type='hidden' name='ID[]' value='" . $row['ID'] . "' />";
   echo "<tr>";
   echo "<td class='team'><input type='text' style='width:100px;' name='Player[]' value='" . $row['Player'] . "' /></td>";
   echo "<td class='item'><input type='text' style='width:20px;' name='Pld[]' value='" . $row['Pld'] . "' /></td>";
   echo "<td class='item'><input type='text' style='width:20px;' name='W[]' value='" . $row['W'] . "' /></td>";
  echo "</tr>";
  }
echo "</table>";
echo "<input type='hidden' name='ateam[]' value='" . $theteam . "' />";
echo "<input type='hidden' name='statsteam' value='" . $statsteam . "' />";
echo "<input type='submit' name='submit' value='Update Players' />";

?>


Script that should execute it.
Code:


<?php
$username = "USERNAME";
$password = "PASSWORD";
$hostname = "HOST ADDRESS";   
$dbcon = mysql_connect($hostname, $username, $password)
   or die("Unable to connect to database");
mysql_select_db("DB NAME");
$team = $_POST['ateam'];
$statsteam = $_POST['statsteam'];
$player = $_POST['Player'];
$pld = $_POST['Pld'];
$w = $_POST['W'];
$id = $_POST['ID'];

// run through the array and for each entry in the array, call the variable
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 `$statsteam` SET `Player`='%s',`Pld`='%s',`W`='%s' WHERE $statsteam.ID='%s';", $player[$i], $pld[$i], $w[$i], $id[$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>Players updated successfully.</p>";
echo "<p><a href='admin.php'>Back to main admin page</a></p>";

mysql_close($dbcon);
?>


I've checked the value of the $i variable and it's value is 1 so not sure why this is going wrong. It's probably something simple, but I've been at it that long now that I've lost it. The first page does everything exactly right, it's just the second page that processes it that it all goes wrong.
sonam
Did you get any error message? Did you try to remove sprintf function and update without it and then check in phpmyadmin are your database updated or not?

Your code is:
Code:
`Player`='%s',`Pld`='%s',`W`='%s'


maybe you need to write instead:
Code:
`Player`='$player',`Pld`='$pld',`W`='$w'


but I don't know too much about sprintf and maybe this is wrong idea. Rolling Eyes

Sonam
welshsteve
I was told and shown this is the best way to handle arrays. There are multiple instances of the same variables which is why in the form each variable is entered into a an array. This script works, I'm just havign trouble on this page.

here's an example of a page that does work:

This page loops through each row in the SQL result and displays the data in form text feilds for editing.
Code:


<?php
$username = "USERNAME"; 
$password = "PASSWORD"; 
$hostname = "HOST ADDRESS";     
$dbcon = mysql_connect($hostname, $username, $password)   
    or die("Unable to connect to database"); 
mysql_select_db("DB NAME");

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

/*if($div=='w_div_1') {
    echo "<h3>Division 1</h3>";
}
elseif($div=='w_div_2') {
    echo "<h3>Division 2</h3>";
}
else */echo "<h3>Preliminary Stage</h3>";

echo "<table class='admin'>
<tr>
<th>Team</th><th>Pld</th><th>W</th><th>D</th><th>F</th><th>A</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 "</tr>";
  }
echo "</table>";
echo "<input type='submit' name='submit' value='Update Table' /></form>";
?>


Notice each variable name is in the format name[], meaning it's entered into an array.

Now this page receives each variable array and processes it.
Code:

<?php
$username = "USERNAME"; 
$password = "PASSWORD"; 
$hostname = "HOST ADDRESS";     
$dbcon = mysql_connect($hostname, $username, $password)   
    or die("Unable to connect to database"); 
mysql_select_db("DB NAME");

$team = $_POST['team'];
$pld = $_POST['pld']; 
$w = $_POST['w'];
$d = $_POST['d'];
$f = $_POST['f'];
$a = $_POST['a']; 
$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' WHERE $div.team='%s';", $team[$i], $pld[$i], $w[$i], $d[$i], $f[$i], $a[$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);
?>


This is working perfectly. I just don't know why it won't work with the other page I've asked the question about. If there's a better way of handling this, please let me know as it's doing my head in LOL
sonam
Hmmm, maybe your POST reference are not OK. In first example you have:

Code:
$team = $_POST['ateam'];


in second:
Code:
$team = $_POST['team'];


Sonam
welshsteve
The POST references are fine because it updates one record i.e. the first record in the array. It ignores the others

.e.g.

If the SQL result in the first bit shows 5 records, the second (processing) part will only execute the SQL for the first record.

So the value of the $i variable is being returned as 1 and not 5 as it should be.
sonam
Try to put some error function on the top of the script to see are this evaluate with some error result. I don't see any fatal mistake in your code and it is hard to find out if there is not error message.

Code:
error_reporting(E_ALL);
ini_set("display_errors", "1");
ini_set('error_reporting', E_ALL);


Sonam
welshsteve
OK, I'll give that a try later when I'm at home. new firewall server here at work which is blocking ftp so slow to update any pages at the moment.
welshsteve
I'VE SUSSED IT!!!!!!!

It seems that the data in the team column in the database can be duplicated. I therefore decided to count the number of different players instead. This sorted it. Sorry guys, my bad, should have done that in the first place. Amazing what a day's break and a good night's sleep can do eh?
sonam
welshsteve wrote:
I'VE SUSSED IT!!!!!!!

It seems that the data in the team column in the database can be duplicated. I therefore decided to count the number of different players instead. This sorted it. Sorry guys, my bad, should have done that in the first place. Amazing what a day's break and a good night's sleep can do eh?


Sleeping clearing our minds, for sure. Wink

Sonam
Related topics
I can't upload my Database SQL script ! Helllppppp!!!!
importing SQL databases
Database Privilege
Windows doesn't installs partition table damage
automatic use of .sql file
SQL UPDATING TABLE
SQL script: String function how to?
WordPress and special characters
back up to large to upload to phpmyadmin
How to get a php script working under a cron job?
Copy file script
Script Error When Attempting To Re-Install WordPress
mysql/php
PHP and Javascript Issue handling data from a mysql query
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.