Page 1 of 1

Update in MySQL retreivng different values

Posted: Fri Jul 25, 2003 6:54 pm
by rododos
I have a little problem with my Update sequence...really I don't know if this can be done or not. What I need is that you can Edit some values from a displaying table in my form, the thing is that I was only getting the last of the values in my table, I've managed to obtain the first value or the value I want...But how can I make the update to get me all the values from the table...The code is as follows...

Code: Select all

<?php
include ("connect.php"); //This I use to connect to my sql, it works fine..
 
 $short="style='width: 60px;'"; //Just some defined styles for my text 
 $small="style='width: 90px;'";
 $big="style='width: 200px;'";
 
 $strSQL="SELECT * FROM Action_Items WHERE project  LIKE '".$project."%' ORDER BY project_status DESC, deadline";
 $recordset=$db1->Execute($strSQL); //This is where I get all the fields
 $a=0; //Variables for managing the names of the fields
 $b=0; 
 $c=0; 
 $d=0; 
 $e=0; 
 $f=0; 
 $g=0; 
 $h=0; 
 while(!$recordset->EOF)
 {
  $id=$recordset->fields[0]; //Obtain the Id from the table for later use in the Update...

?>
Here is where I got my problem.

Code: Select all

<?php
if(isset($updtAI)){
    $strUpdt="UPDATE Action_Items SET start_date='$newStrtdate0', description='$newDesc0', comments='$newCmms0', action_taken='$newAct0', responsible='$newResp0', project_status='$newProjSt0', deadline='$newDln0', delivery_date='$newDlvr0' WHERE project='$project' and id='$id'";
$recordset=$db1->Execute($strUpdt); 
 echo "Sentencia: ".$strUpdt."<br>";
 }

?>
If I put the 0, I get the values of the first field, If I put a 1, I get the values of the second field and so on... What I want to know is...How can I manage to put the number that follows, i.e., description='$newDesc1' and so on... but in some sort of automatic way...
I know I can use many update sequences, I've done that before, but because I don't know the exact number of fields in my table (Because it changes constantly) there's my problem...

Code: Select all

<?php
//Here is where I display my table, as you can see I've assigned names to each field so I can do my Update and know wich value I am getting... ...[/b]

	
  $class_row="td_sow";
  
	
  echo "<td style="text-align: left;"><img src='$tmpColor'></td>";  
  echo "<td style="text-align: left; font-size: 7pt;"><nobr>".$recordset->fields[1]."</nobr></td>";  
  echo "<td $short><input class='td_col' style="background-color:#ffffff;" $short value='".$strtdate."' name='newStrtdate$a' $dis></td>"; 
  echo "<td $short><input class='td_col' style="background-color:#ffffff;" $short value='".$recordset->fields[7]."' name='newProjSt$b'  $dis></td>"; 
  echo "<td><textarea class='td_col' style="width: 180px; background-color:#ffffff; text-align: left;" name='newDesc$c' $dis>".$recordset->fields[3]."</textarea></td>";  
  echo "<td><textarea class='td_col' style="width: 180px; background-color:#ffffff; text-align: left;" name='newCmms$d' $dis>".$recordset->fields[4]."</textarea></td>";  
  echo "<td><input class='td_col' style="background-color:#ffffff;" value='".$recordset->fields[6]."' name='newResp$e' $dis></td>"; 
  echo "<td $short><input class='td_col' style="background-color:#ffffff;" $short value='".$deadln."' name='newDln$f' $dis></td>"; 
  echo "<td $short><input class='td_col' style="background-color:#ffffff;" $short value='".$dlvr."' name='newDlvr$g'$dis></td>"; 
  echo "<td><textarea class='td_col' style="width: 180px; background-color:#ffffff; text-align: left;" name='newAct$h' $dis>".$recordset->fields[5]."</textarea></td>"; 
  echo "</tr>";
  $a=$a+1; //These variables are to manage the names of the fields I am displaying, as you can se above.... So the variables have different names and can be managed...
  $b=$b+1; 
  $c=$c+1; 
  $d=$d+1; 
  $e=$e+1; 
  $f=$f+1; 
  $g=$g+1; 
  $h=$h+1; 
 $recordset->MoveNext();
  }

?>
Please..if someone knows how to this I would be very grateful with you...I'm a little desperate...I try to explain myself as much as possible, but as you can see it's a little confusing what I am trying to do, but I know you can give me a hand on this..

Posted: Tue Jul 29, 2003 7:43 pm
by jmarcv
Boy, I don't have a clue what you are wanting, but let me pass on a few tips. First, NEVER use select *, it causes one of the problems you are complaining about. Instead: try:
select f1,f2,f3 from table
and then do
list($f1,$f2,$f3)=$db1->Execute($strUpdt);
Then field order doesn't matter.
---------------------------------
Second, consider this for your form:

while( list($id,$f1,$f2,$f3)=$db1->Execute($strUpdt)){
echo "<input type=text name="ID[]" value="$id"><br>;
echo "<input type=text name="f1[]" value="$f1"><br>;
echo "<input type=text name="f2[]" value="$f2"><br>;
echo "<input type=text name="f3[]" value="$f3"><br>;
}
the [] makes an array.

To update, something like this:

while (list($key,$id) = each($ID)) {
$sql="update table SET f1='$f1',f2='$f2',f3='$f3' WHERE id='$id'";
execute;
}

Be careful, too many records at once will die on some systems.

Thanks for your help

Posted: Wed Jul 30, 2003 3:06 pm
by rododos
Thanks for your help... I've come to the solution of my problem...what I was trying to do was something like a dynamic UPDATE, and the thing is that I didn't know how put on the UPDATE the names of the values dynamically...but If you want to know how I did it...here is the answer, maybe with these you will get the idea of what I was trying to do...I know its a little confusing but I am sure you'll understand it seeing the answer..

Code: Select all

<?php
$strCount="SELECT count(*) no FROM Action_Items WHERE project='".$project."'";
$recordset=$db1->Execute($strCount);
   $num=$recordset->fields["no"];
 for($a=0; $a<$num; $a++)
   {
    $idAux="id".$a; 
    $strtAux="newStrtdate".$a;
    $descAux="newDesc".$a;
    $cmmsAux="newCmms".$a;
    $actTknAux="newAct".$a;
    $respAux="newResp".$a;
    $projStAux="newProjSt".$a;
    $dlnAux="newDln".$a;
    $dlvrAux="newDlvr".$a;
//Here is how I manage to make the Update in a "dynamic" way the thing was to use $$ so it could take the value I needed until the cicle was done...
$strUpdt="UPDATE Action_Items SET description='".$$descAux."', comments='".$$cmmsAux."', action_taken='".$$actTknAux."', responsible='".$$respAux."', project_status='".$$projStAux."', deadline='".$$dlnAux."', delivery_date='".$$dlvrAux."' WHERE project='$project' and id=".$$idAux;
    $db1->Execute($strUpdt);

?>
Thanks for the tips... I am going to consider them and make the changes in my project. :wink:

I hope the answer helps other people...Maybe the explanation of my problem wasn't to clear, but as I said before maybe you could understand it better seeing the answer...as I see it, its just a "dynamic" update...

Posted: Wed Jul 30, 2003 5:42 pm
by jmarcv
Very good. Your solution definately works. Many programmers 'frown' at variable variables, as php calls them. My example uses arrays. Of course, yours requires less work on your existing code. But as I always say, whatever works...