• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

update only the changed rows

I am combining php and sql

I am using a php form and I only want to update values where the folderid has been changed by the php form

if I have to update every single row, it may take time, so I only want to update the rows that have been changed by the php form

if I run this
update products set folderid=$folderid where productid=$productid and folderid!=$folderid

then I am still updating every single row, but it may be quicker


what is the quickest way to do this
0
rgb192
Asked:
rgb192
  • 5
  • 4
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how exactly do you identify the rows that are managed by the form?
and what, apart from the folderid field, will be changed eventually?

and what is wrong with the update you showed? it shall indeed only update the row if folderid is not the new folderid value already?!
0
 
rgb192Author Commented:
if(isset($_POST['submit'])) {
       foreach($_POST['titleid'] as $id =>$v){      
         $titleid=$_POST['titleid'][$id];
       $sql="update products set folderid=$titleid where productid=$id and folderid!=$folderid";
           echo '<br>'.$sql;

}//end foreachpost
}//end ifisset


    echo '<form action="" method="POST"';
        <input type="submit" name="submit" value="Submit" />

    foreach($products as $p) {
echo'                
            <td>
              <select name="titleid[' . $p['productid'] . ']">';
                $this->displayProductFoldersC($folderid);
        echo'    </select>
            </td>
      ';
}

</form>
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume you do have a index on productid, so the update should run very fast, affecting possibly 1 or 0 rows ...
so, I repeat: what is the problem, aka what exactly are you trying to improve?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
rgb192Author Commented:
I dont want to run a query for each row

I only want to run a query for the rows I change by the php select menu on the form
0
 
SharathData EngineerCommented:
>> I only want to run a query for the rows I change by the php select menu on the form
How do you know the changed records? Do you have a flag/date stamp that indiactes a record got changed?
0
 
rgb192Author Commented:
>>Do you have a flag/date stamp that indiactes a record got changed?

this is a form with the product on each row
I can change only the folderid of the product using a select menu


if I change just one row
then all the rows get updated


I am not sure if there is a flag/date stamp
0
 
SharathData EngineerCommented:
<< if I change just one row
then all the rows get updated >>

You mean all the other products also getting changed.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need then to "store" the "old" value in a hidden field on the form, so you have, on "post/submit", compare the current value with the old value (from the hidden field)... and run the sql only if the values differ ..
0
 
rgb192Author Commented:
>>
you need then to "store" the "old" value in a hidden field on the form, so you have, on "post/submit", compare the current value with the old value (from the hidden field)... and run the sql only if the values differ ..

how


>>
You mean all the other products also getting changed.

I have to do update statement for every row even if nothing is being changed
update table set row1=1 where row1=1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
your form has currently 2 fields, 1 for the folderid, and 1 for the productid.
on postback, you will get the 2 values from the <input> fields
if you create another <input id=origfolderid type=hidden value=$folderid /> field in the form, on postback of the form, you will find the $_POST["origfolderid"] value, which is the original value of the field folderid, as queried from the database when showing the form ...
0
 
rgb192Author Commented:
best explanation
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now