www.webdeveloper.com
Results 1 to 7 of 7

Thread: trouble with update

  1. #1
    Join Date
    Apr 2013
    Location
    gastonia NC
    Posts
    92

    trouble with update

    Hi, please take a look at my code and advise what's wrong. Following is an error message pertaining to the code that follows:
    -----------------------------------------------------
    Update query failed: 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 'WHERE acctno='xxx-xxx-xxxx'' at line 5
    -----------------------------------------------------
    following is the php document code
    -----------------------------------------------------
    Code:
    <html><head> 
    <!--when the paidamt is keyed in, the current date & paid code are autoinserted-->
    <script type="text/javascript" src="payment.js"></script>
    <!--<script type="text/javascript">
    window.google_analytics_uacct = "UA-256751-2";
    </script>
    <script type="text/javascript">
    window.google_analytics_uacct = "UA-256751-2";
    </script>-->
    </head><body bgcolor="#ccffff"><b><center>
    PHP Code:
    <?php
    // error_reporting(0);
    error_reporting(E_ALL E_NOTICE);
    mysql_connect('localhost','root','my_password');
    mysql_select_db('homedb') or die( "Unable to select database");
    if(!empty(
    $_POST["submit"]))
    {
    $acctno $_POST['acctno'];
    $query="SELECT * FROM oocust Where acctno='$acctno'";
    $result=mysql_query($query);
    if(
    mysql_num_rows($result))
    {
    echo 
    date('m/d/y');
    echo 
    "<form action='#' method='post'>Invoice Payment :<br /><br />
    <table cellspacing=0 cellpadding=0 border=1>         
    <th colspan=4></th> 
    <th colspan=2>amounts</th>
    <tr>
    <th>check#</th>          
     <th>acct#</th>
    <th>Name</th>          
    <th>Descr</th>
    <th>Paid</th>
    <th>Due</th>
    <th>Date Paid</th>
    <th>pd</th>
       </tr>"
    ;    
    while(
    $row mysql_fetch_assoc($result))
       {
    echo 
    "<tr>
    <td><input type='text' size=5 name='checkno' value='" 
    $row['checkno'] . "' ></td>
    <td><input type='text' readonly size=15 name='acctno' value='" 
    $row['acctno'] . "' ></td>
    <td><input type='text' readonly size=25 name='bname' value='" 
    $row['bname'] . "'></td>
    <td><input type='text' readonly size=25 name='purpose' value='" 
    $row['purpose'] . "'></td>

    <td><input type='text' size=7 id='paidamt' name='paidamt' value='" 
    $row['paidamt'] ."' 
    onBlur='calculate_paid(this)'></td>

    <td><input type='text' size=7 id='amtdue' name='amtdue' value='" 
    $row['amtdue'] . "'></td>
    <td><input type='text' size=10 id='datepaid' name='datepaid' value='" 
    $row['datepaid'] . "'></td>
    <td><input type='text' size=1 id='pd' name='pd' value='" 
    $row['pd'] . "' ></td>           
          </tr>"
    ;
    }
    echo 
    "</table>
    <input type='submit' name='update' value='make payment' />
    </form>"
    ;
    }  
     else{echo 
    "invalid entry for account# $acctno.<br />Select another?<br />";}
    }
    if(!empty(
    $_POST["update"]))
    {
    $sql "UPDATE oocust SET 
      amtdue = '" 
    mysql_real_escape_string($_POST['amtdue']) . "', 
     datepaid = '" 
    mysql_real_escape_string($_POST['datepaid']) . "',  
     pd = '" 
    mysql_real_escape_string($_POST['pd']) . "',     
          WHERE acctno='"
    .$_POST["acctno"]."'";
    mysql_query($sql) or die("Update query failed: " mysql_error());
    echo 
    "Record for acct# ".$_POST["acctno"]." has been updated";
    }
    ?>
    Code:
    <form method="post" action="#">
    <br />
    <input type="text" name="acctno"/> <p>
    <input type="submit" name="submit" value="select acct#."/><p>
    </form>
    
    </body></html>
    ----------------------------------------------------
    following is the payment.js - the if statement doesn't work ?
    ----------------------------------------------------
    Code:
    function $_(IDS) { return document.getElementById(IDS); }
    function calculate_paid() 
      {
       var pd = document.getElementById("pd");
       var datepaid = document.getElementById("datepaid");
       var paidamt = document.getElementById("paidamt");
       var amtdue = document.getElementById("amtdue");  
       var shipamt = document.getElementById("shipamt");  
       var dateNow = new Date 
       var dayNow = dateNow.getDate();
       var datePaid = (dateNow.getMonth()+1)+"/"+dateNow.getDate()+"/"+dateNow.getFullYear();
    datepaid.value = datePaid;    
    amtdue.value = parsefloat(amtdue.value) + parsefloat(shipamt.value) - parsefloat(paidamt.value);
    // *********************   
    if (amtdue.value=="0")
      { pd.value = "P"; }
    //********************
      }

  2. #2
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    877
    The message says you have a problem with your query statement. All the rest is extraneous.

    What type of field is 'acctno' defined as? If numeric then you don't want those single quotes around $acctno

  3. #3
    Join Date
    Apr 2013
    Location
    gastonia NC
    Posts
    92
    it is varchar - thanks for the response

  4. #4
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    877
    You have a comma in front of your where clause.

    PS - I WOULD BE REMISS IF I DIDN'T TELL YOU :

    1. The MySQL_* interface is already deprecated. That means it will be invalid in an soon-to-be-released version. You need to read up on how to use mysqlI or PDO for your database access and modify this code and all your other code.

    2. You REALLY need to sanitize your input before placing it into a query statement. The use of a POST variable directly into your queries is highly dangerous. You will probably hear this from others on this forum shortly.
    Last edited by ginerjm; 04-09-2014 at 10:39 AM.

  5. #5
    Join Date
    Apr 2013
    Location
    gastonia NC
    Posts
    92
    Yes I know & you're right. I'm researching. I've requested examples from some forums and none work. I'm still trying.

  6. #6
    Join Date
    Jul 2013
    Location
    Voorheesville NY USA
    Posts
    877
    There are fine examples of pdo in the php manual. Stop waiting and try them.

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,616
    It often helps to echo out the actual SQL used in the error message (of course, you don't want to do that in the production version):
    PHP Code:
    mysql_query($sql) or die("Update query failed: " mysql_error()."<br />\n.$sql); 
    "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

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