Updating values in a mysql database from a PHP array

Question

I am querying the database, inserting the resultant values in an array then manipulating them and thereafter I want to update each of values in the affected rows using mysql_update from the array and that's where I am having trouble.

This is my code - kindly assist:

name   sellerid quantity
-------------------------
john       12     10
joel       23     20
brian      40     10

Let's take that as the query result and someone orders 25 items now the program is to take the items and assign them to one who ordered then deduct from the sellers.

$cursor="SELECT itemquantity,sellerid FROM mytable WHERE  price='$price'";                     
//it is a table containing data about people selling their commodities

$foundItems = array();

// likely to be a parameter of a function...
$totalUnitsOrdered = 25;

// maps user to amount assigned from him
$assignedQuantityPerUser = array();


while ( $row = mysql_fetch_assoc( $cursor ) ) {

  // Still order Quantity left?
  if ( 0 < $totalUnitsOrdered ) {

    if ( $row[ "itemquantity" ] <= $totalUnitsOrdered ) {
      if (!isset($assignedQuantityPerUser[$row["sellerid"]])) {
        $assignedQuantityPerUser[$row["sellerid"]] = 0;
      }

      // assign all of $row[ "itemquantity" ]
      $totalUnitsOrdered  -= 0 + $row[ "itemquantity" ];
      $assignedQuantityPerUser[ $row[ "sellerid" ] ] += 0 + $row[ "itemquantity" ];

    }  else {
      // assign all the rest: $totalUnitsOrdered
      $totalUnitsOrdered   = 0;
      $assignedQuantityPerUser[ $row[ "sellerid" ] ] += $totalUnitsOrdered;
    }
  }

  $newItem[] = $row[ "sellerid" ];
  $newItem[] = $row[ "itemquantity" ];

  // Append $newItem to the end of $foundItems 
  $foundItems[] = $newItem;
}

This question was already recently posted: stackoverflow.com/questions/9531487/…. I don't think it is clear enough, hence lack of response — user1237595
– user1237595, Commented Mar 5, 2012 at 11:50
@lng but clearly it is a different problem not similar to the one previously asked..tried simplifying it as best as i can. — che
– che, Commented Mar 5, 2012 at 12:03

Naveen Kumar · Accepted Answer · 2012-03-06 11:49:08Z

1

There are three cases (Algorithm ) this is pseudo code not exact code i hope you get the idea

while($row= mysql_fetch_assoc($rec))
{
  if(quantity for user is  > total quantity ordered)
  {
    quantity of user -=total quantity ordered 
    update tableNamse set qty =quantity for user where userId=$row['id'];
    exit while
  }
  else if(quantity for user = total quantity ordered)
  {
     quantity of user=0;
    update tableNamse set qty =0 where userId=$row['id'];
    exit while
  }
  else
  {
     total quantity ordered - = quantity of user
    update tableNamse set qty =0 where userId=$row['id'];
    continue while loop
  }
 }

-- Update

   if($row['itemquantity'] > $totalUnitsOrdered)
   {
     $qtyUser=$row['itemquantity']-$totalUnitsOrdered;
     mysql_query("update tableName set itemQuantity=$qtyUser
                  where userId=$row['userId']" )
      break; // exit while
   }

--Update for third case

  else
{
$totalUnitsOrdered-=$row['itemquantity'];
mysql_query("update tableName set itemQuantity=0
                  where userId=$row['userId']" )

}

edited Mar 6, 2012 at 11:49

answered Mar 5, 2012 at 12:03

Naveen Kumar

4,6011 gold badge20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

che Over a year ago

.thank you i get the idea.could you just kindly assist me with the code for even at least one of the cases.thanks.

che Over a year ago

i am having trouble coding the third case,kindly assist .thanks a lot.sorry for disturbing you,it's just that i am new to php and mysql.thanks

che Over a year ago

thanks but still on the third case(when $totalUnitsOrdered> $row['itemquantity'] at index 0,it should go to $row['itemquantity'] at index 1 and so on till $totalUnitsOrdered =0),how do i include a while loop to loop through the db till $totalUnitsOrdered =0 and update each of the rows,because currently it doesn't achieve that.thanks a lot.this is my main hurdle in the project.kindly assist me again.thanks Naveen

Naveen Kumar Over a year ago

Is the code not working (3rd case), $totalUnitsOrdered is reduced by the current user amount and loop continue, inside firstly check if($totalUnitsOrdered >0) break;,

che Over a year ago

,should it break if($totalUnitsOrdered >0) or if($totalUnitsOrdered =0),i think it's the second one

Collectives™ on Stack Overflow

Updating values in a mysql database from a PHP array

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related