Save loop output to database

Question

On the below code up to echo $AA it works! The echo shows the $rec['totsb'] minus 1 correctly, subtracting 1 from the existing value in the database. Then I need to save the new number generated after reducing one back to the database which is where my code is for sure is wrong. I tried many alternatives to correct it but couldn't get through. Can some one tell me how to save the new number back to the database?

info: my database looks like below. And as you see in between numbers are populated in a drop down to be selected by the user.(database has only starting 302 and end as 309, drop down has all 302,303,304...309) So if a user picks 306 for instance it should automatically identify in between which start and end number 306 fits in and save the new number as appropriate in totsb.

+--------+---------+------+
|sbstart |sbend    | totsb|
+--------+---------+------+
|302     |309      | 8    |
|200     |208      | 9    |
|405     |409      | 5    |
+--------+---------+------+

Code:

<?php
$con=mysql_connect('localhost','root') or die ("Server connection failure!");
$db=mysql_select_db('regional_data',$con) or die ("Couldn't connect the database");
$SQL="SELECT * FROM newchk";
$run=mysql_query($SQL,$con) or die ("SQL Error");
$nor=mysql_num_rows($run);

while ($rec = mysql_fetch_array($run))
{
for($i=$rec['sbstart']; $i<=$rec['sbend']; $i++)
    {
    $opt=$_POST['options'];
    if($i = $opt)
     {
        if($rec['totsb'] <= "0")
        {
        echo "You have already entred this cheque number."; 
        return false;
        } else {
        echo "You can proceed with this entry";
        $AA = $rec['totsb']-1;
        $BB=$rec['sbstart'];
        echo $AA;
        $con=mysql_connect('localhost','root') or die ("Server connection      failure!");
        $db=mysql_select_db('regional_data',$con) or die ("Couldn't connect the     database");
        $SQL="UPDATE newchk SET totsb='$AA'";   
        return false;
        }
     }
     else 
     { echo "Error: Cant find choosen in the databse"; 
      return false;
     }
    }
}
?>

You mean to say that you select all table to update 2,3 fields ? Sincerely my brain got frozen, I simply can't understand what you are trying to do. — Mihai Iorga
– Mihai Iorga, Commented Sep 10, 2012 at 14:43
database has only three fields sbstart, sbend and totsb which is the difference of sbstart and end. So how should I change my approach? Thanks — Mohan Wijesena
– Mohan Wijesena, Commented Sep 10, 2012 at 14:46

Logan_V · Accepted Answer · 2012-09-10 14:55:31Z

1

try changing this line

$SQL="UPDATE newchk SET totsb='$AA'";

to

$SQL="UPDATE newchk SET totsb=".$AA;

answered Sep 10, 2012 at 14:55

Logan_V

211 bronze badge

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

Comments

Dave · Accepted Answer · 2012-09-10 15:48:19Z

0

unless you are missing code...

1) you are not doing anything with the SQL statement (need to actually run it) as you did the first one.
2) if the amount is numeric as you indicate in your table chema, you don't need quotes around it
3) also recommend using your sbstart as a qualifier for the record to update or you will update everything.

$SQL2="UPDATE newchk SET totsb=$AA where sbstart=$BB"; 
$run2=mysql_query($SQL2,$con) or die ("SQL Error");

if that doesn't help, send more info on your database schema

added code

$matches=0;
$opt=$_POST['options'];
while ($rec = mysql_fetch_array($run)){
    if($opt>=$rec['sbstart'] && $opt<=$rec['sbend']){
        # we have a match, run your code

        if($rec['totsb'] <= "0") 
        { 
        echo "You have already entred this cheque number.";  
        return false; 
        } else { 
        echo "You can proceed with this entry"; 
        $AA = $rec['totsb']-1; 
        $BB=$rec['sbstart']; 
        echo $AA; 
        $con=mysql_connect('localhost','root') or die ("Server connection      failure!"); 
        $db=mysql_select_db('regional_data',$con) or die ("Couldn't connect the     database"); 
# fixed lines
        $SQL2="UPDATE newchk SET totsb=$AA where sbstart=$BB"; 
        $run2=mysql_query($SQL2,$con) or die ("SQL Error"); 
# end fixed lines
        return false; 
        } 
        # end your code
        $matches++
    }else{
        # no match
    }
} # while loop through records

if($matches==0){
    echo "Error: Cant find choosen in the databse";  
    return false; 
}else{
    return true;
}

you could clean up your code a bit more, but that should get you on the right track

edited Sep 10, 2012 at 15:48

answered Sep 10, 2012 at 14:48

Dave

1,0011 gold badge8 silver badges16 bronze badges

6 Comments

Dave Over a year ago

also you can reuse the $db link rather than recalling those connection strings... unless you have closed them elsewhere and they are not shown

Mohan Wijesena Over a year ago

Dave, Thanks for the response. This saves the new number generated back to the database. However in a situation where there are more than one start and end numbers in the database as in my above demonstration the update happens only at the very first data row. (I'm sure this is due to the qualified issue u mentioned.) To further clarify what Im trying to say, for instance if the user picks a value falling within 200 to 208, one should be subtracted from 9 as per the above database sample. But actually what happens is one is reduced from 8 which is the 'totsb' for the value range 302 to 309.

Mohan Wijesena Over a year ago

Simply before the update back to the database takes place I need to code to find out to within which sbstart and sbend the value falls in and then update as appropriate. How do you think I should get about this. Thanks for the help so far.

Dave Over a year ago

then you are matching the wrong "row" from the database. Try this;

Dave Over a year ago

also check the "return true" i added near the validation at the bottom, you may not want that.

|

Collectives™ on Stack Overflow

Save loop output to database

2 Answers 2

Comments

added code

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

added code

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related