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

Thread: database update duplication

  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,337
    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 2009
    Posts
    3,346
    WHERE id='$ud_id'
    I was suggesting removing the quotes from this part. Those are single quotes unless my eyes are deceiving me.

  11. #11
    Join Date
    Jul 2010
    Posts
    106
    Quote Originally Posted by criterion9 View Post
    I was suggesting removing the quotes from this part. Those are single quotes unless my eyes are deceiving me.
    I got the below error when I removed the single quotes.

    Parse error: syntax error, unexpected T_STRING in C:\xampp\htdocs\hofiles\refreshprerent.php on line 23

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

  13. #13
    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";

  14. #14
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,337
    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

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

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