Noob Help I want to update a mysql list

[superblunt]

Hi I want to remove $amount(the amount entered) from my mainitem tables Totalstock field and then add that $amount to all subitem Table totalstock field

 

This is what i am doing

 

<?php

 

error_reporting(0); // I use this cause if the subitem does not exist it will not show the error

 

$mainitem = $_POST["mainitem"];

$amount = $_POST["amount"];

 

$con = mysql_connect("localhost","root","*****");

    if (!$con)

        {

              die('Could not connect: ' . mysql_error());

          }

mysql_select_db("aea", $con);

 

$getmainitem = mysql_query("SELECT * FROM mainitem WHERE id=$mainitem");

$getsubitem = mysql_query("SELECT * FROM subitem1");

 

$getmainitemrow = mysql_fetch_array($getmainitem);

$getmaintotalstock = $getmainitemrow['totalstock'];

$maintotalstock = $getmaintotalstock-$amount;

 

while($getsubitemrow = mysql_fetch_array($getsubitem))

{

$subitemid[] = $getsubitemrow['id'];

$subitemstock[] = $getsubitemrow['totalstock'];

}

 

$row1 =$subitemstock[0]+$amount;

$row2 =$subitemstock[1]+$amount;

$row3 =$subitemstock[2]+$amount;

$row4 =$subitemstock[3]+$amount;

$row5 =$subitemstock[4]+$amount;

$row6 =$subitemstock[5]+$amount;

$row7 =$subitemstock[6]+$amount;

$row8 =$subitemstock[7]+$amount;

$row9 =$subitemstock[8]+$amount;

$row10 =$subitemstock[9]+$amount;

$row11 =$subitemstock[10]+$amount;

$row12 =$subitemstock[11]+$amount;

$row13 =$subitemstock[12]+$amount;

$row14 =$subitemstock[13]+$amount;

$row15 =$subitemstock[14]+$amount;

$row16 =$subitemstock[15]+$amount;

$row17 =$subitemstock[16]+$amount;

$row18 =$subitemstock[17]+$amount;

$row19 =$subitemstock[18]+$amount;

$row20 =$subitemstock[19]+$amount;

 

$id1 =$subitemid[0];

$id2 =$subitemid[1];

$id3 =$subitemid[2];

$id4 =$subitemid[3];

$id5 =$subitemid[4];

$id6 =$subitemid[5];

$id7 =$subitemid[6];

$id8 =$subitemid[7];

$id9 =$subitemid[8];

$id10 =$subitemid[9];

$id11 =$subitemid[10];

$id12 =$subitemid[11];

$id13 =$subitemid[12];

$id14 =$subitemid[13];

$id15 =$subitemid[14];

$id16 =$subitemid[15];

$id17 =$subitemid[16];

$id18 =$subitemid[17];

$id19 =$subitemid[18];

$id20 =$subitemid[19];

 

 

$mainupdate1="UPDATE mainitem SET totalstock = $maintotalstock WHERE id=$mainitem";

 

$subitemupdate1="UPDATE subitem1 SET totalstock = $row1 WHERE id=$id1";

$subitemupdate2="UPDATE subitem1 SET totalstock = $row2 WHERE id=$id2";

$subitemupdate3="UPDATE subitem1 SET totalstock = $row3 WHERE id=$id3";

$subitemupdate4="UPDATE subitem1 SET totalstock = $row4 WHERE id=$id4";

$subitemupdate5="UPDATE subitem1 SET totalstock = $row5 WHERE id=$id5";

$subitemupdate6="UPDATE subitem1 SET totalstock = $row6 WHERE id=$id6";

$subitemupdate7="UPDATE subitem1 SET totalstock = $row7 WHERE id=$id7";

$subitemupdate8="UPDATE subitem1 SET totalstock = $row8 WHERE id=$id8";

$subitemupdate9="UPDATE subitem1 SET totalstock = $row9 WHERE id=$id9";

$subitemupdate10="UPDATE subitem1 SET totalstock = $row10 WHERE id=$id10";

$subitemupdate11="UPDATE subitem1 SET totalstock = $row11 WHERE id=$id11";

$subitemupdate12="UPDATE subitem1 SET totalstock = $row12 WHERE id=$id12";

$subitemupdate13="UPDATE subitem1 SET totalstock = $row13 WHERE id=$id13";

$subitemupdate14="UPDATE subitem1 SET totalstock = $row14 WHERE id=$id14";

$subitemupdate15="UPDATE subitem1 SET totalstock = $row15 WHERE id=$id15";

$subitemupdate16="UPDATE subitem1 SET totalstock = $row16 WHERE id=$id16";

$subitemupdate17="UPDATE subitem1 SET totalstock = $row17 WHERE id=$id17";

$subitemupdate18="UPDATE subitem1 SET totalstock = $row18 WHERE id=$id18";

$subitemupdate19="UPDATE subitem1 SET totalstock = $row19 WHERE id=$id19";

$subitemupdate20="UPDATE subitem1 SET totalstock = $row20 WHERE id=$id20";

 

 

 

?>

<?php

 

if (!mysql_query($mainupdate1))

  {

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

  }

echo "1 record Updated Mainitem Stock";

 

 

if (!mysql_query($subitemupdate1))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate2))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate3))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate4))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate5))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate6))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate7))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate8))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate9))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate10))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate11))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate12))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate13))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate14))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate15))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate16))

  {

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

  }

echo "1 record Updated Sub Item Stock";

if (!mysql_query($subitemupdate17))

  {

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

  }

echo "1 record Updated Sub Item Stock";

if (!mysql_query($subitemupdate18))

  {

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

  }

echo "1 record Updated Sub Item Stock";

if (!mysql_query($subitemupdate19))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

if (!mysql_query($subitemupdate20))

  {

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

  }

echo "1 record Updated Sub Item Stock";

 

 

?>

 

Now this works but if more then 20 subitems is added it will not update the <20 fields

 

And is there a way to turn of SQL errors cause if the sql field does not exist it show the error

 

Thx

[MasterACE14]

all these variables...

$subitemupdate14="UPDATE subitem1 SET totalstock = $row14 WHERE id=$id14";

 

you need to place comma's around the variables within the queries. for every single one of them.

$subitemupdate14="UPDATE subitem1 SET totalstock = '$row14' WHERE id='$id14'";

 

Regards ACE

[superblunt]

thx but all of them are numbers and work like that

 

is there an easy why to make a var in a loop

 

so i just add $item and it adds a number to the back

[ratcateme]

try this

<?php

error_reporting(0); // I use this cause if the subitem does not exist it will not show the error

$mainitem = $_POST["mainitem"];
$amount = $_POST["amount"];

$con = mysql_connect("localhost", "root", "*****");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("aea", $con);

$getmainitem = mysql_query("SELECT * FROM mainitem WHERE id=$mainitem");
$getsubitem = mysql_query("SELECT * FROM subitem1");

$getmainitemrow = mysql_fetch_array($getmainitem);
$getmaintotalstock = $getmainitemrow['totalstock'];
$maintotalstock = $getmaintotalstock - $amount;

while ($getsubitemrow = mysql_fetch_array($getsubitem)) {
$subitemid[] = $getsubitemrow['id'];
$subitemstock[] = $getsubitemrow['totalstock'] + $amount;
}

$mainupdate1 = "UPDATE mainitem SET totalstock = $subitemid WHERE id=$mainitem";

if (!mysql_query($mainupdate1)) {
die('Error: ' . mysql_error());
}
echo "1 record Updated Mainitem Stock";


for ($i = 0; $i <= count($subitemid)-1; $i++) {
if (!mysql_query("UPDATE subitem1 SET totalstock = {$subitemstock[$i]} WHERE id={$subitemid[$i]}")) {
	die('Error: ' . mysql_error());
}
echo "1 record Updated Mainitem Stock";
}
?>

 

Scott.

[samshel]

UPDATE subitem1 SET totalstock = totalstock + $amount;

 

Will update all rows and add amount to all totalstocks. no need of loops.

[superblunt]

Thx allot ratcateme and everyone els

 

just one question to ratcateme

 

for ($i = 0; $i <= count($subitemid)-1; $i++)

 

do you think i can just use

 

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

 

or is there a reason that it has to be equal and then - s

[ratcateme]

in the array it is numbered from 0-19 so there a 20 items in the array right.

without the "-1" it would look for item 20 in the array which wouldn't exist.

if you had an array numbered from 1-20 then you can leave out the "-1"

 

Scott.