che che - 7 months ago 11
SQL Question

Updating values in a mysql database from a PHP array

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;
}

Answer

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']" )

}
Comments