www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: database update duplication

Hybrid View

  1. #1
    Join Date
    Jul 2010
    Posts
    106

    database update duplication

    What am I doing wrong? The below code is intended to update a single selected record in my database but changes all records?

    <?php
    $host="localhost";
    $username="root";
    $password="";
    $database="prerentdb";

    mysql_connect($host,$username,$password);
    mysql_select_db($database) or die( "Unable to select database");

    if(!empty($_POST["submit"]))
    {
    $apt = $_POST['apt'];
    $query="SELECT * FROM payments Where apt='$apt'";
    $result=mysql_query($query);
    if(mysql_num_rows($result))
    {
    echo "<form action='#' method='post'><b><center>Present Report</center></b><br /><br />
    <table border='1'>
    <tr>
    <th>dep</th>
    <th>apt</th>
    <th>tenant</th>
    <th>month</th>
    <th>prev</th>
    <th>misc</th>
    <th>rent</th>
    <th>hud</th>
    <th>tenant</th>
    <th>amt</th>
    <th>date</th>
    </tr>
    <tr>
    <th colspan=2>num</th>
    <th>name</th>
    <th>chg</th>
    <th>bal</th>
    <th>amt</th>
    <th>due</th>
    <th colspan=2>pay</th>
    <th colspan=2>paid</th>
    </tr>";

    while($row = mysql_fetch_assoc($result))
    {
    echo "<tr>
    <td><input type='text' name='dep' value='" . $row['dep'] . "'></td>
    <td><input type='text' name='apt' value='" . $row['apt'] . "'></td>
    <td><input type='text' name='name' value='" . $row['name'] . "'></td>
    <td><input type='text' name='rent' value='" . $row['rent'] . "'></td>
    <td><input type='text' name='prevbal' value='" . $row['prevbal'] . "'></td>
    <td><input type='text' name='misc' value='" . $row['misc'] . "'></td>
    <td><input type='text' name='rentdue' value='" . $row['rentdue'] . "'></td>
    <td><input type='text' name='hudpay' value='" . $row['hudpay'] . "'></td>
    <td><input type='text' name='tentpay' value='" . $row['tentpay'] . "'></td>
    <td><input type='text' name='amtpaid' value='" . $row['amtpaid'] . "'></td>
    <td><input type='text' name='datepaid' value='" . $row['datepaid'] . "'></td>
    </tr>
    <tr>
    <td colspan='11' align='center' style='padding-top:20px;'><input type='submit' name='update' value='Update Record'></td>
    </tr>";
    }
    echo "</table>
    </form>";
    }
    else{echo "No listing for appartment $apt.<br />Please select another.<br />";}
    }
    if(!empty($_POST["update"]))
    {
    $sql = "UPDATE payments SET dep='".$_POST["dep"]."',apt='".$_POST["apt"]."',name='".$_POST["name"]."',rent='".$_POST["rent"]."',prevbal='".$_POST["prevbal"]."',misc='".$_POST["misc"]."',rentdue='".$_POST["rentdue"]."',hudpay='".$_POST["hudpay"]."',tentpay='".$_POST["tentpay"]."',amtpaid='".$_POST["amtpaid"]."',datepaid='".$_POST["datepaid"]."'";
    mysql_query($sql) or die("Update query failed.");
    echo "Record for appartment ".$_POST["apt"]." has been updated ...";
    }
    ?>
    <form method="post" action="#">
    <br />
    <input type="text" name="apt"/>
    <p>
    <input type="submit" name="submit" value="Select Apartment for Payment"/>
    </form>

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,175
    You have no WHERE clause in your UPDATE query, so there is nothing to filter which records get updated, so by default they all do.

    PS: Please wrap you code samples here in [php]...[/php] bbcode tags -- it makes it much easier to read.
    "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

  3. #3
    Join Date
    Jul 2010
    Posts
    106
    I believe this should work for what I need.

    PHP Code:
    <?php
    // this code should update the database and gives a confirmation

    $ud_id=$_POST['ud_id'];
    $ud_dep=$_POST['ud_dep'];
    $ud_name=$_POST['ud_name'];
    $ud_apt=$_POST['ud_apt'];
    $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(
    misc prevbal rentdue == amtpaid)
    {
    misc 0;
    prevbal 0;
    amtpaid 0;
    tentpay 0;
    datepaid " ";
    }

    mysql_connect(localhost,root,"");
    $query="UPDATE testtable SET dep='$ud_dep', name='$ud_name', apt='$ud_apt', amtpaid='$ud_amtpaid', 

    rentdue='
    $ud_rentdue', prevbal='$ud_prevbal', misc='$ud_misc', tentpay='$ud_tentpay', 

    hudpay='
    $ud_hudpay', datepaid='$ud_datepaid',  WHERE id='$ud_id'";
    mysql_query($query);
    echo 
    "Record Updated";

    mysql_close();
    ?>

  4. #4
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    You will need to remove the , before the WHERE

    Also you only have to update the fields that change. So if the ud_name and ud_apt for a given id don't change they don't have to be updated when something like amt paid is being updated.

  5. #5
    Join Date
    Jul 2010
    Posts
    106
    You mean like this?
    Also, it doesn't like line 11 ( "misc=0;" )

    <?php
    // this code should update the database and gives a confirmation
    $ud_amtpaid=$_POST['ud_amtpaid'];
    $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(misc + prevbal + rentdue == amtpaid)
    {
    misc = 0;
    prevbal = 0;
    amtpaid = 0;
    tentpay = 0;
    datepaid = " ";
    }

    mysql_connect(localhost,root,"");
    mysql_select_db(test) or die( "Unable to select database");

    $query="UPDATE testtable SET dep='$ud_dep', name='$ud_name', apt='$ud_apt', amtpaid='$ud_amtpaid',

    rentdue='$ud_rentdue', prevbal='$ud_prevbal', misc='$ud_misc', tentpay='$ud_tentpay',

    hudpay='$ud_hudpay', datepaid='$ud_datepaid' WHERE id='$ud_id'";
    mysql_query($query);
    echo "Record Updated";

    mysql_close();
    ?>
    Last edited by ckdoublenecks; 08-08-2010 at 09:59 AM. Reason: for clarity

  6. #6
    Join Date
    Jan 2009
    Posts
    3,346

    if(misc + prevbal + rentdue == amtpaid)
    {
    misc = 0;
    prevbal = 0;
    amtpaid = 0;
    tentpay = 0;
    datepaid = " ";
    }
    That is not valid PHP. All variables begin with "$".

  7. #7
    Join Date
    Jul 2010
    Posts
    106
    Thanks so much for your help. The below updates but it updates the entire database regardless of comparison?


    <?php
    // this code should update the database and gives a confirmation
    $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 = " ";
    }

    mysql_connect(localhost,root,"");
    mysql_select_db(test) or die( "Unable to select database");
    $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'";
    mysql_query($query);
    echo "Record Updated";
    mysql_close();
    ?>
    Last edited by ckdoublenecks; 08-08-2010 at 04:11 PM. Reason: clarity

  8. #8
    Join Date
    Jan 2009
    Posts
    3,346
    id='$ud_id'"
    Where is $ud_id being set? I see it in older posts but it didn't seem to make the cut. If the field is a numeric type don't use the additional single quotes (auto_increment by default is stored as a numeric type).

  9. #9
    Join Date
    Jul 2010
    Posts
    106
    Quote Originally Posted by criterion9 View Post
    Where is $ud_id being set? I see it in older posts but it didn't seem to make the cut. If the field is a numeric type don't use the additional single quotes (auto_increment by default is stored as a numeric type).
    Below is the current code. If I remove the " (not a single quote) I get an error. As it is when I execute the program all the database records are updated. It's as if the comparison is made at the first and all are cleared.

    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 " ";
    }

    mysql_connect(localhost,root,"");
    mysql_select_db(test) or die( "Unable to select database");
    $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'";
    mysql_query($query);
    echo 
    "Record Updated";
    mysql_close();
    ?>

  10. #10
    Join Date
    Jan 2006
    Location
    MN
    Posts
    440
    I usually write queries with concatenates;

    PHP Code:
    $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. 
    I do have a question about the concept you are showing. Why do you UPDATE an entire entry. You lose all your history on rent payments for the user. Why not maintain history, and show each payment as a new entry. That way you can build a report showing all the history of the renter, and whether they have been late, paid in full etc.

    Also make sure that you test the mysql_query() to insure that it completed properly.
    Last edited by Dasher; 08-08-2010 at 08:55 PM.

  11. #11
    Join Date
    Jan 2009
    Posts
    3,346
    Again, if the "id" field is numeric don't use single quotes or you might get unexpected results:
    WHERE id='".$ud_id."'"
    Code:
    WHERE id=".$ud_id." LIMIT 1";

  12. #12
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,175
    I like to use sprintf(), which both keeps things clearer and allows for easy use of escaping or casting mechanisms. Also, there's no law that says the query string must be on one line. (In fact, it's usually easier to debug if you split it up, as the mysql_error() messages will tell you which line failed.)
    PHP Code:
    $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"); 
    "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

  13. #13
    Join Date
    Jul 2010
    Posts
    106

    nothing new with php code

    You're absolutely in your logic; but the end result must look like Corporate's paper system or they'll have a cat. I'm building a system parallel to their paper system to eleviate so much paper shuffling and drawer pulling. Below is the current program: I get "Records updated" - with no updates?

    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 " "


    mysql_connect(localhost,root,""); 
    mysql_select_db(test) or die( "Unable to select database"); 
    $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(); 
    ?>

  14. #14
    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(); 
    ?>

  15. #15
    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

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