update query not working in array

Question

Here is my code:

I want to update the total_values field using an array. I just fetch the data's from table then i want to update the field for same table

// select query to get the value
    for($j=0;$j<count($capacity);$j++)
   {  
     $capaci=$capacity[$j];
    // select query to get the value
     $sql2=mysql_query("SELECT recieved-allocate*plate_quantity as ans from total_values where capacity='$capaci'"); 
     while($fetch=mysql_fetch_array($sql2)) 
     {
      $recieves=$fetch['ans'];
      $sql3="update total_values set recieved='$recieves' where capacity='$capaci' and month='$mon'";
      mysql_query($sql3);
     }
    }

There's no query being executed (and no question being asked by the way). — laurent, Apr 13 '12 at 04:42
and please don't create sql like that: http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — idstam, Apr 13 '12 at 04:44

score 1 · Answer 1 · answered Apr 13 '12 at 04:47

1

If i understood that this is what u are trying to do:

for($j=0;$j<count($capacity);$j++)
       {  
         $capaci=$capacity[$j];
        // select query to get the value
         $sql2=mysql_query("SELECT recieved-allocate*plate_quantity as ans from total_values where capacity='$capaci'"); 
         while($fetch=mysql_fetch_array($sql2)) 
         {
          $recieves=$fetch['ans'];
          $sql3="update total_values set recieved='$recieves' where capacity='$capaci' and month='$mon'";
          mysql_query(sql3);
         }
        }

And as idstam said in comments, learn to use PDO to prevent SQL injection

answered Apr 13 '12 at 04:47

Milan Halada

1,943
18
28

thanks for ur reply.. but i get wrong updation ... – Suresh PHP Begginer Apr 13 '12 at 05:01
ans - field name 1 6 – Suresh PHP Begginer Apr 13 '12 at 05:04
what exactly is wrong with the updates? – Milan Halada Apr 13 '12 at 05:14
i need the value like 1 and 6 but am getting 6 and 6 – Suresh PHP Begginer Apr 13 '12 at 05:17
i think for loop repeat the value ... please see my updated code on top – Suresh PHP Begginer Apr 13 '12 at 05:18
Depends on your variables and select result, can not tell without knowing `$capacity` value and seeing contents of `total_values` table – Milan Halada Apr 13 '12 at 05:23
ya correct i had declare the $capacity value before itself, am getting the value from text box. so no issue in that.. – Suresh PHP Begginer Apr 13 '12 at 05:26
just check your variables and table, follow it - use debugger or print_r and echo them so you can see how are they changing and what are the results of select query. Error is in another part of your code or in SQL tables – Milan Halada Apr 13 '12 at 05:37

score 0 · Answer 2 · answered Apr 13 '12 at 04:58

0

Your SQL is pretty messy, but functionally, the problem may simply be that you need to have a call to mysql_query($sql3) on that line after you define $sql3. Also, you probably want to remove the echo statement just before you set $sql3.

answered Apr 13 '12 at 04:58

Camden S.

2,185
1
22
27

ya this is my query but not getting perfect answer: for($j=0;$j – Suresh PHP Begginer Apr 13 '12 at 05:14

Camden S. · Answer 3 · 2012-04-13T05:46:24.377

0

If you're not getting the mathematical result that you're expecting, maybe you have a simple error in your arithmetical operator sequence... ?

In the line of code that starts with $sql2=mysql_query...

You're currently saying:

SELECT recieved-allocate*plate_quantity as ans...

... but perhaps you mean to say the following?

SELECT (recieved-allocate)*plate_quantity as ans...

or [EDIT: This is a third option:]

SELECT recieved-(allocate*plate_quantity) as ans...

These statements would, of course, produce different mathematical results.

Is that the problem/solution?

edited Apr 13 '12 at 05:46

answered Apr 13 '12 at 05:25

Camden S.

2,185
1
22
27

no if i run this query in phpmyadmin means i can get the perfect value... i get the error in loop only.. for your reference shall i put my whole code? – Suresh PHP Begginer Apr 13 '12 at 05:31
It's probably best to use parenthesis to explicit specify your desired order of operations anyway. Can you try the second and (newly edited) third example here in my examples above just for kicks and let me know if it works? – Camden S. Apr 13 '12 at 05:45
ya tried it gives balance (-2 and -14) but i need (1 and 6).. – Suresh PHP Begginer Apr 13 '12 at 05:54

score 0 · Answer 4 · answered Apr 13 '12 at 05:59

0

Make sure the recieved, allocate, and plate_quantity fields are all numeric types in your MySQL table.

answered Apr 13 '12 at 05:59

Camden S.

2,185
1
22
27

its varchar ...if numeric means what i have to do and – Suresh PHP Begginer Apr 13 '12 at 06:07
Ah!! Then that may be your problem. Are you using phpMyAdmin? If so, just use it to edit your table to make all those fields types `int(11)` instead of `varchar`. You can only perform reliable math on numeric fields. Try it and let me know... – Camden S. Apr 13 '12 at 06:43
y i changed but also same condition. see i need two values (1 and 6) but i get (6 and 6). if i put print_r means i got this Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 [4] => 0 [5] => 0 [6] => 0 [7] => 1 [8] => 6 [9] => 0 [10] => 0 [11] => 0 [12] => 0 [13] => 0 [14] => 0 ) so 7th and 8th value only i need but its updating 8th value for both. – Suresh PHP Begginer Apr 13 '12 at 09:47
am waiting for your reply only boss – Suresh PHP Begginer Apr 13 '12 at 10:27

update query not working in array

4 Answers4