[mikejkellyuk]

Hi guys

i have a table called members which stores all the users information. what i am trying to do is allow the user to only update two fields in the database which are team number and pay scale for each user.

at the moment i can display all the user detials, i did this using a loop. what i want to be able to do is update certain fields for certain user, and it would be good if it could all be done in the same page.

any help would be good

here is the code i have so far

PHP Code:

$link = mysql_connect('localhost','username','password');
if(!$link){
die('Notconnected:'.mysql_error());
}
//make foo the current db
$db_selected= mysql_select_db('DB',$link);
if(!$db_selected){
die('Can\'t use addressbook :'.mysql_error());};

$teamnumber=$_SESSION['teamnumber'];


$query="SELECT hrnumber, firstname, lastname, emailaddress, phonenumber, city, country, teamnumber, payscale FROM members WHERE teamnumber='$teamnumber'";
$result=mysql_query($query);
while($row = @mysql_fetch_array($result))
{
echo "<big><b>User Details</b></big>";
echo "<br>";
echo "<br>";
echo "HR Number:";
echo "" . $row['hrnumber'] . "";
echo "<br>";
echo "First Name:";
echo "" . $row['firstname'] . "";
echo "<br>";
echo "Last Name:";
echo "" . $row['lastname'] . "";
echo "<br>";
echo "Email Address:";
echo "" . $row['emailaddress'] . "";
echo "<br>";
echo "Phone Number:";
echo "" . $row['phonenumber'] . "";
echo "<br>";
echo "city:";
echo "" . $row['city'] . "";
echo "<br>";
echo "Country(e.g. UK,USA):";
echo "" . $row['country'] . "";
echo "<br>";
echo "Team Number:";
echo "" . $row['teamnumber'] . "";
echo "<br>";
echo "Pay Scale:";
echo "" . $row['payscale'] . "";
echo "<br>";
echo "<br>";
}
echo "</form></p>"; 

thanks

mike

[CreativeClans]

You'll have to add input fields to the form for the data you want the user to change, and a hidden field for the table key value (HR number?).
Then, the script that receives the form values when the user submits the form, should check the validity of the values the user inserted, and if all is ok update the values in the database for that user.

[kodekin]

you've got a form tag without a form open tag.
<FORM action="http://somesite.com/prog/adduser" method="post">

condense your code to something like:
echo "First Name: <input type='text' value='" . $row['firstname'] . "' /><br>";

then read this page:
SQL UPDATE Statement

[mikejkellyuk]

i have done what you suggest, but my code is not updating the table here is my code

PHP Code:

$link = mysql_connect('localhost','username','password');

if(!$link){

die('Notconnected:'.mysql_error());

}

//make foo the current db

$db_selected= mysql_select_db('DB',$link);

if(!$db_selected){

die('Can\'t use addressbook :'.mysql_error());};

$records = array();

if($_POST["count"])

{

 for($i=0;$i<$_POST["count"];$i++)

 {

  $records[$i]["cb"] = $_POST["cb".$i];

  $records[$i]["hrnumber"] = $_POST["hrnumber".$i];

  }

 for($i=0;$i<count($records);$i++)

 {

  if($records[$i]["cb"] == "on")

  {

   mysql_query("UPDATE members SET teamnumber='" . $row['teamnumber'] . "', payscale='" . $row['payscale'] . "' WHERE hrnumber='".$records[$i]["hrnumber"]."'");

   }

  }

 }

$apprId = 0;



$teamnumber=$_SESSION['teamnumber'];





$query="SELECT hrnumber, firstname, lastname, emailaddress, phonenumber, city, country, teamnumber, payscale FROM members WHERE teamnumber='$teamnumber'";

$result=mysql_query($query);

while($row = @mysql_fetch_array($result))

{

echo "<big><b>User Details</b></big>";

echo "<br>";

echo "<br>";

echo "<input type='checkbox' name='cb".$apprId."' />";

echo "<br>";

echo "HR Number:";

echo "" . $row['hrnumber'] . "";

echo "<br>";

echo "First Name:";

echo "" . $row['firstname'] . "";

echo "<br>";

echo "Last Name:";

echo "" . $row['lastname'] . "";

echo "<br>";

echo "Email Address:";

echo "" . $row['emailaddress'] . "";

echo "<br>";

echo "Phone Number:";

echo "" . $row['phonenumber'] . "";

echo "<br>";

echo "city:";

echo "" . $row['city'] . "";

echo "<br>";

echo "Country(e.g. UK,USA):";

echo "" . $row['country'] . "";

echo "<br>";

echo "Team Number:";

echo "<input type='text' name='teamnumber' value='" . $row['teamnumber'] . "'/>";

echo "<br>";

echo "Pay Scale:";

echo "<input type='text' name='payscale' value='" . $row['payscale'] . "'/>";

echo "<br>";

echo "<input type='submit' value='Submit'><input type='hidden' name='count' value='".$apprId."'>";

echo "<br>";

}

echo "</form></p>";

?> 

thanks

mike

[kodekin]

I'd say condense your two for loops into one, I could be wrong but i've never been able to define variables with a for loop only update variables outside of the loop.

so basically your second loop is trying to run through variables that aren't there anymore, so it fails your if test and does nothing.

[kodekin]

something like this"

if(isset($_POST["count"])){
for($i=0;$i<$_POST["count"];$i++){
$records[$i]["cb"] = $_POST["cb".$i];
$records[$i]["hrnumber"] = $_POST["hrnumber".$i];

if($records[$i]["cb"] == "on"){
mysql_query("UPDATE members SET teamnumber='" . $row['teamnumber'] . "', payscale='" . $row['payscale'] . "' WHERE hrnumber='".$records[$i]["hrnumber"]."'");
}
}

[mikejkellyuk]

i have used the same code in another function and it work in that, i think its something to do with the update statement

[kodekin]

update looks good, the only other thing I can think of is

if(isset($_POST["count"])){

maybe without isset it's not a reading a true/false value.

[CreativeClans]

Change this

PHP Code:

mysql_query("UPDATE members SET teamnumber='" . $row['teamnumber'] . "', payscale='" . $row['payscale'] . "' WHERE hrnumber='".$records[$i]["hrnumber"]."'"); 

in

PHP Code:

$query = "

  UPDATE members 

  SET 

      teamnumber='" . $row['teamnumber'] . "'

    , payscale='" . $row['payscale'] . "' 

  WHERE hrnumber='".$records[$i]['hrnumber']."'

";

echo "query: $query<br />";

mysql_query($query) or die("MySQL error - ".mysql_errno()." - ".mysql_error()); 

The echo will show you the query that is executed, so you can check if it's correct. Delete the echo once everything is working fine.
The 'or die' will show you the error MySQL throws if there is a problem with the query.