www.webdeveloper.com
Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26

Thread: database update duplication

  1. #16
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,623
    Well, simply echoing out "Records updated" does nothing to confirm that anything actually worked. Without checking the return value of mysql_query() and doing some error logging/reporting if it's false, there's no way to tell if the query was even accepted as valid by MySQL. And even if it is valid syntax, that does not mean any rows actually got updated (e.g. if the where clause did not match any record) in which case you can use mysql_affected_rows() to determine how many rows were updated (and again do some error logging/reporting if it returns zero).
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  2. #17
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    One of the things I do with a query is to echo it to the screen with the actual mysql_query line commented out. I examine the query to make sure it is the correct values before letting it actually update or insert data.

  3. #18
    Join Date
    Jul 2010
    Posts
    106
    I added the sugggested code and got the below error: I don't know about this lineabout line 33)
    PHP Code:
    $sql "SELECT FROM  testtable"

    Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in C:\xampp\htdocs\hofiles\refreshprerent.php on line 28
    PHP Code:
    <?php 
    error_reporting
    (E_ALL);

    // this code should update the database and gives a confirmation 
    $ud_id=$_POST['ud_id']; 
    $ud_amtpaid=$_POST['ud_amtpaid']; 
    $ud_rentdue=$_POST['ud_rentdue']; 
    $ud_prevbal=$_POST['ud_prevbal']; 
    $ud_misc=$_POST['ud_misc']; 
    $ud_tentpay=$_POST['ud_tentpay']; 
    $ud_hudpay=$_POST['ud_hudpay']; 
    $ud_datepaid=$_POST['ud_datepaid']; 
    if((
    $ud_misc $ud_prevbal $ud_rentdue) == $ud_amtpaid

    $misc 0
    $prevbal 0
    $amtpaid 0
    $tentpay 0
    $datepaid " "


    if (!
    $conn = @mysql_connect(localhost,root,"")) { 
    echo 
    'could not connect to MYSQL:' .mysql_error();
    exit;
    }

    if(!@
    mysql_select_db('test')) {
       echo 
    'Unable to select database:'mysql_error();
       exit;
    }

    // mysql_query returns false on failure

                       
    $sql "SELECT FROM  testtable";

    $result = @mysql_query($sql);
    if(!
    $result) {
        echo 
    "could not run query (sql);" .mysql_error();
       exit;
    }
    // if we expect rows, let's make sure some exist
    if(mysql_num_rows($result) < 1) {
       echo 
    "no rowsmatch your query, Please try again";
       exit;
    }

    // We know $result is valid and rows exist to fetch
    while ($row mysql_fetch_assoc($result)) {
       
    print_r($row);
    }

    $query sprintf
       
    "UPDATE testtable 
       SET 
          amtpaid='%s', 
          prevbal='%s', 
          misc='%s', 
          tentpay='%s', 
          hudpay='%s', 
          datepaid='%s' 
       WHERE id=%d"

       
    mysql_real_escape_string($ud_amtpaid), 
       
    mysql_real_escape_string($ud_prevbal), 
       
    mysql_real_escape_string($ud_misc), 
       
    mysql_real_escape_string($ud_tentpay), 
       
    mysql_real_escape_string($ud_hudpay), 
       
    mysql_real_escape_string($ud_datepaid), 
       (int) 
    $ud_id 
    ); 
    // $result = mysql_query($query) or die(mysql_error()."<br />\n$query"); 
    // $query="UPDATE testtable SET id=".$ud_id.", amtpaid=".$ud_amtpaid.", prevbal=".$ud_prevbal.", // misc=".$ud_misc.", tentpay=".$ud_tentpay.", hudpay=".$ud_hudpay.", datepaid='".$ud_datepaid."'  // WHERE id=".$ud_id.";
    // echo $query."\n<br>";
    // on numeric values you can delete the single quotes. 


    mysql_query($query); 
    echo 
    "Records Updated"
    mysql_close(); 
    ?>

  4. #19
    Join Date
    Jul 2010
    Posts
    106
    This below is my latest attempt and below that the errors encountered.

    PHP Code:
    <?php 
    error_reporting
    (E_ALL);
    // this code should update the database and gives a confirmation 
    $ud_id=$_POST['ud_id']; 
    $ud_amtpaid=$_POST['ud_amtpaid']; 
    $ud_rentdue=$_POST['ud_rentdue']; 
    $ud_prevbal=$_POST['ud_prevbal']; 
    $ud_misc=$_POST['ud_misc']; 
    $ud_tentpay=$_POST['ud_tentpay']; 
    $ud_hudpay=$_POST['ud_hudpay']; 
    $ud_datepaid=$_POST['ud_datepaid']; 
    if((
    $ud_misc $ud_prevbal $ud_rentdue) == $ud_amtpaid

    $misc 0
    $prevbal 0
    $amtpaid 0
    $tentpay 0
    $datepaid " "

    if (!
    $conn = @mysql_connect(localhost,root,"")) { 
    echo 
    'could not connect to MYSQL: ' .mysql_error();
    exit;
    }
    if(!@
    mysql_select_db('test')) {
       echo 
    'Unable to select database: ' .mysql_error();
       exit;
    }
    // mysql_query returns false on failure
                       
    $sql "SELECT FROM  testtable";
    $result = @mysql_query($sql);
    if(!
    $result) {
        echo 
    "could not run query (sql); " .mysql_error();
       exit;
    }
    // if we expect rows, let's make sure some exist
    if(mysql_num_rows($result) < 1) {
       echo 
    "no rowsmatch your query, Please try again";
       exit;
    }

    // We know $result is valid and rows exist to fetch
    while ($row mysql_fetch_assoc($result)) {
       
    print_r($row);
    }

    $query sprintf
       
    "UPDATE testtable 
       SET 
          amtpaid='%s', 
          prevbal='%s', 
          misc='%s', 
          tentpay='%s', 
          hudpay='%s', 
          datepaid='%s' 
       WHERE id=%d"

       
    mysql_real_escape_string($ud_amtpaid), 
       
    mysql_real_escape_string($ud_prevbal), 
       
    mysql_real_escape_string($ud_misc), 
       
    mysql_real_escape_string($ud_tentpay), 
       
    mysql_real_escape_string($ud_hudpay), 
       
    mysql_real_escape_string($ud_datepaid), 
       (int) 
    $ud_id 
    ); 
    // $result = mysql_query($query) or die(mysql_error()."<br />\n$query"); 
    // $query="UPDATE testtable SET id=".$ud_id.", amtpaid=".$ud_amtpaid.", prevbal=".$ud_prevbal.", // misc=".$ud_misc.", tentpay=".$ud_tentpay.", hudpay=".$ud_hudpay.", datepaid='".$ud_datepaid."'  // WHERE id=".$ud_id.";
    // echo $query."\n<br>";
    // on numeric values you can delete the single quotes. 


    mysql_query($query); 
    echo 
    "Records Updated"
    mysql_close(); 
    ?>



    Code:
    Notice: Undefined index: ud_id in C:\xampp\htdocs\hofiles\refreshprerent.php on line 4
    
    Notice: Undefined index: ud_amtpaid in C:\xampp\htdocs\hofiles\refreshprerent.php on line 5
    
    Notice: Undefined index: ud_rentdue in C:\xampp\htdocs\hofiles\refreshprerent.php on line 6
    
    Notice: Undefined index: ud_prevbal in C:\xampp\htdocs\hofiles\refreshprerent.php on line 7
    
    Notice: Undefined index: ud_misc in C:\xampp\htdocs\hofiles\refreshprerent.php on line 8
    
    Notice: Undefined index: ud_tentpay in C:\xampp\htdocs\hofiles\refreshprerent.php on line 9
    
    Notice: Undefined index: ud_hudpay in C:\xampp\htdocs\hofiles\refreshprerent.php on line 10
    
    Notice: Undefined index: ud_datepaid in C:\xampp\htdocs\hofiles\refreshprerent.php on line 11
    could not run query (sql); You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM testtable' at line 1

  5. #20
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,623
    The warnings would indicate that no matching $_POST data was received. Either the form was not submitting via the POST method, or the form field names don't match, or you were just testing by calling the file directly without any form data being submitted.

    The error with the query is that it does not specify any columns to be selected.
    PHP Code:
    // mysql_query returns false on failure
                       
    $sql "SELECT FROM  testtable"
    At the very least it would need to be:
    PHP Code:
    // mysql_query returns false on failure
                       
    $sql "SELECT * FROM  testtable"
    Generally better is to explicitly list the columns to be used, unless you know you will always need every column.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  6. #21
    Join Date
    Jan 2009
    Posts
    3,346
    // mysql_query returns false on failure
    $sql = "SELECT FROM testtable";
    $result = @mysql_query($sql);
    if(!$result) {
    echo "could not run query (sql); " .mysql_error();
    exit;
    }
    Why would you run a select query way up in the code before you are executing the planned update query? Maybe you should look at some tutorials for php & mysql development. I get the impression you are missing some of the basics which will make developing this much much harder than it needs to be.

  7. #22
    Join Date
    Jul 2010
    Posts
    106
    You're right, there is no form, I'm calling it directly. Below is the current code with suggested changes.

    PHP Code:
    <?php 
    // this code should update the database and gives a confirmation 
    $ud_id=$_POST['ud_id']; 
    $ud_amtpaid=$_POST['ud_amtpaid']; 
    $ud_rentdue=$_POST['ud_rentdue']; 
    $ud_prevbal=$_POST['ud_prevbal']; 
    $ud_misc=$_POST['ud_misc']; 
    $ud_tentpay=$_POST['ud_tentpay']; 
    $ud_hudpay=$_POST['ud_hudpay']; 
    $ud_datepaid=$_POST['ud_datepaid']; 
    if((
    $ud_misc $ud_prevbal $ud_rentdue) == $ud_amtpaid

    $misc 0
    $prevbal 0
    $amtpaid 0
    $tentpay 0
    $datepaid " "

    if (!
    $conn = @mysql_connect(localhost,root,"")) { 
    echo 
    'could not connect to MYSQL: ' .mysql_error();
    exit;
    }
    if(!@
    mysql_select_db('test')) {
       echo 
    'Unable to select database: ' .mysql_error();
       exit;
    }
    // mysql_query returns false on failure
                       
    $sql "SELECT * FROM  testtable";
    $result = @mysql_query($sql);
    if(!
    $result) {
        echo 
    "could not run query (sql); " .mysql_error();
       exit;
    }
    // if we expect rows, let's make sure some exist
    if(mysql_num_rows($result) < 1) {
       echo 
    "no rowsmatch your query, Please try again";
       exit;
    }

    // We know $result is valid and rows exist to fetch
    while ($row mysql_fetch_assoc($result)) {
       
    print_r($row);
    }

    $query sprintf
       
    "UPDATE testtable 
       SET 
          amtpaid='%s', 
          prevbal='%s', 
          misc='%s', 
          tentpay='%s', 
          hudpay='%s', 
          datepaid='%s' 
       WHERE id=%d"

       
    mysql_real_escape_string($ud_amtpaid), 
       
    mysql_real_escape_string($ud_prevbal), 
       
    mysql_real_escape_string($ud_misc), 
       
    mysql_real_escape_string($ud_tentpay), 
       
    mysql_real_escape_string($ud_hudpay), 
       
    mysql_real_escape_string($ud_datepaid), 
       (int) 
    $ud_id 
    ); 

    mysql_query($query) or die("<b>A fatal MySQL error occured</b>.\n<br />Query: " $query "<br />\nError: " mysql_error()); 

    $query="UPDATE testtable SET amtpaid=$ud_amtpaid, prevbal=$ud_prevbal, misc=$ud_misc, tentpay=$ud_tentpay, hudpay=$ud_hudpay, datepaid='$ud_datepaid' WHERE id=$ud_id;

    // echo 
    $query."\n<br>";
    // on numeric values you can delete the single quotes. 


    mysql_query(
    $query); 
    echo "
    Records Updated"; 
    mysql_close(); 
    ?>

  8. #23
    Join Date
    Jul 2010
    Posts
    106
    Thanks for your help. I am running this locally on one machine. Please take a look at this last attempt:
    The error is below the code:

    PHP Code:
    <?php 
    // this code should update the database and gives a confirmation 
    $ud_id=$_POST['ud_id']; 
    $ud_amtpaid=$_POST['ud_amtpaid']; 
    $ud_rentdue=$_POST['ud_rentdue']; 
    $ud_prevbal=$_POST['ud_prevbal']; 
    $ud_misc=$_POST['ud_misc']; 
    $ud_tentpay=$_POST['ud_tentpay']; 
    $ud_hudpay=$_POST['ud_hudpay']; 
    $ud_datepaid=$_POST['ud_datepaid']; 
    if((
    $ud_misc $ud_prevbal $ud_rentdue) == $ud_amtpaid

    $misc 0
    $prevbal 0
    $amtpaid 0
    $tentpay 0
    $datepaid " "

    if (!
    $conn = @mysql_connect(localhost,root,"")) { 
    echo 
    'could not connect to MYSQL: ' .mysql_error();
    exit;
    }
    if(!@
    mysql_select_db('test')) {
       echo 
    'Unable to select database: ' .mysql_error();
       exit;
    }
    // mysql_query returns false on failure
                       
    $sql "SELECT * FROM  testtable";
    $result = @mysql_query($sql);
    if(!
    $result) {
        echo 
    "could not run query (sql); " .mysql_error();
       exit;
    }
    // if we expect rows, let's make sure some exist
    if(mysql_num_rows($result) < 1) {
       echo 
    "no rowsmatch your query, Please try again";
       exit;
    }
    // We know $result is valid and rows exist to fetch
    while ($row mysql_fetch_assoc($result)) {
       
    print_r($row);
    }
    $query="UPDATE testtable SET amtpaid=$ud_amtpaid, prevbal=$ud_prevbal, misc=$ud_misc, tentpay=$ud_tentpay, hudpay=$ud_hudpay, datepaid='$ud_datepaid' WHERE id=$ud_id";
    echo 
    "Records Updated"
    mysql_close(); 
    ?>
    Array ( [id] => 1 [dep] => [name] => name1 [apt] => [amtpaid] => 530 [rentdue] => 530 [prevbal] => 0 [misc] => 0 [tentpay] => 530 [hudpay] => 0 [datepaid] => 8/5/2010 ) Array ( [id] => 2 [dep] => [name] => name2 [apt] => [amtpaid] => 150 [rentdue] => 500 [prevbal] => 350 [misc] => 0 [tentpay] => 150 [hudpay] => 0 [datepaid] => 8/4/2010 ) Array ( [id] => 3 [dep] => [name] => name3 [apt] => [amtpaid] => 437 [rentdue] => 555 [prevbal] => 0 [misc] => -118 [tentpay] => 437 [hudpay] => 0 [datepaid] => 8/2/2010 ) Array ( [id] => 4 [dep] => [name] => name4 [apt] => [amtpaid] => 505 [rentdue] => 530 [prevbal] => -25 [misc] => 0 [tentpay] => 505 [hudpay] => 0 [datepaid] => 8/1/2010 ) Array ( [id] => 5 [dep] => [name] => name5 [apt] => [amtpaid] => 490 [rentdue] => 490 [prevbal] => 10 [misc] => 0 [tentpay] => 490 [hudpay] => 0 [datepaid] => 8/3/2010 ) Records Updated
    __________________

  9. #24
    Join Date
    Jul 2010
    Posts
    106
    Each time I test, I check the table thru phpmyadmin, for updates. There are none even tho it says "records updated". I got the below idea from a book. Can it possibly work if coded right? As it is the result is the same.

    PHP Code:
    <?
    if (!$conn = @mysql_connect(localhost,root,"")) { 
    echo 
    'could not connect to MYSQL: ' .mysql_error();
    exit;
    }
    if(!@
    mysql_select_db('test')) {
       echo 
    'Unable to select database: ' .mysql_error();
       exit;
    }
    // mysql_query returns false on failure
      
    $sql "SELECT amtpaid, prevbal, misc, tentpay, hudpay, datepaid FROM  testtable WHERE misc + prevbal + rentdue = amtpaid";
    $result = @mysql_query($sql);
    if(!
    $result) {
        echo 
    "could not run query (sql); " .mysql_error();
       exit;
    }
    // if we expect rows, let's make sure some exist
    if(mysql_num_rows($result) < 1) {
       echo 
    "no rowsmatch your query, Please try again";
       exit;
    }
    $query="UPDATE testtable SET amtpaid=0, prevbal=0, misc=0,tentpay=0, hudpay=0, datepaid=0 ";
     
    mysql_query($sql) or die("Update query failed.");
    echo 
    "Records Updated"
    mysql_close(); 
    ?>

  10. #25
    Join Date
    Jul 2010
    Posts
    106
    This works, thanks

    PHP Code:
    <?php
    $con 
    mysql_connect("localhost","root","");
    if (!
    $con)
      {
      die(
    'Could not connect: ' mysql_error());
      }
    mysql_select_db("test"$con);
    // for every tenant record, if the rentdue, prevbal & misc equals the 
    // amtpaid, clear the misc, prevbal, amtpaid, tentpay and datepaid
    mysql_query("UPDATE testtable SET amtpaid = '0',  prevbal = '0', misc = '0', tentpay = '0', datepaid = ' '
    WHERE misc + prevbal + rentdue = amtpaid"
    );
    mysql_close($con);
    ?>

  11. #26
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    PHP Code:
    mysql_query("UPDATE testtable SET amtpaid = '0',  prevbal = '0', misc = '0', tentpay = '0', datepaid = ' '
    WHERE misc + prevbal + rentdue = amtpaid"
    ); 
    If you have more than one renter whose rent is "misc + prevbal + rentdue = amtpaid" it will update all similar records. In your test model it is 0 + 0 + 0 = 0 not a very good test. Or if never previously updated null + null + null = null.

    How do you save a history of payments if you only UPDATE an entry instead of creating a complete history of each renter. I suspect that if someone didn't pay a complete rent payment and then rentdue would change, but what is in the database is the last rentdue, not the current rentdue, so the WHERE clause will fail.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles