CODEWITHSUNDEEP
phpmysql
Mohan Wijesena
Mohan Wijesena

Reputation: 21

Save loop output to database

  1. 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;
     }
    }
}
?>

Upvotes: 0

Views: 241

Answers (2)

Dave
Dave

Reputation: 1001

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

Upvotes: 0

Logan_V
Logan_V

Reputation: 21

try changing this line

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

to

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

Upvotes: 1

Related Questions