#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0

    Having Trouble with my_sql_query


    I am trying to write some php code that will subtract a value from a value in a mysql database i keep getting the following error
    " 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 ''accounts' SET Balance = Balance+50 WHERE ID = '1'' at line 1"
    here is my code

    PHP Code:
    mysql_query("UPDATE 'accounts'
    SET Balance = Balance - 
    $Amount
    WHERE ID = '
    $SourceAccount'") OR die("Database query failed1: " mysql_error());
                                
    mysql_query("UPDATE 'accounts'
    SET Balance = Balance+
    $Amount
    WHERE ID = '
    $TargetAccount'") OR die("Database query failed2: " mysql_error());
                                
    mysql_query("INSERT INTO 'transactions' ('AccountID',Type,Debit','Credit','Description','Memo')
     VALUES ('
    $TargetAccount', 'Depoist', 0, $Amount, 'Online Deposit', '$Memo')") OR die("Database query failed3: " mysql_error()); 
    I cannot find any syntax errors.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,031
    Rep Power
    377
    echo the query out & run in mysql.


    i am not able to see any error especially since the first update happens successfully
  4. #3
  5. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,303
    Rep Power
    9400
    On the other hand I don't see how the first query possibly worked.

    Did you post your real code? The whole thing without just copying and pasting what you think are the relevant parts?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    Originally Posted by requinix
    On the other hand I don't see how the first query possibly worked.

    Did you post your real code? The whole thing without just copying and pasting what you think are the relevant parts?

    I did not post the entire code here it is all of the post and session variables are correctly set. i have tested them with the print_r() function.


    PHP Code:
    <?php session_start(); ?>
    <?php 
    require_once("include/connection.php"); ?>

    <?php
        
        $Type 
    $_POST['Type'];
        
    $SourceAccount $_POST['SourceAccount'];            //routing number
        
    $TargetAccount $_SESSION['AccountData'][0];
        
    $Amount $_POST['Amount'];
        
    $Memo $_POST['Memo'];
        
        if(
    $Type == "Deposit")
            
                    
    mysql_query("UPDATE 'accounts'
                                SET Balance = Balance - 
    $Amount
                                WHERE ID = '
    $SourceAccount'") OR die("Database query failed1: " mysql_error());
                                
                    
    mysql_query("UPDATE 'accounts'
                                SET Balance = Balance+
    $Amount
                                WHERE ID = '
    $TargetAccount'"); //OR die("Database query failed2: " . mysql_error());
                                
                    
    mysql_query("INSERT INTO 'transactions' ('AccountID','Type','Debit','Credit','Description','Memo')
                                 VALUES ('
    $TargetAccount', 'Depoist', 0, $Amount, 'Online Deposit', '$Memo')") OR die("Database query failed3: " mysql_error());
                                 
                    echo(
    "<a href='StudentPage.php'>Back To Home</a>");
                                 
    mysql_close($connection);
    ?>
    Also! I entered the following code into the mysql terminal
    Code:
     INSERT INTO 'transactions ('AccountID','Type','Debit','Credit','Description','Memo') VALUES ('1','Deposit','0','50','Online Deposit','Test');
    And it returned with:
    Code:
    ERROR 1064 (42000): 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 ''transactions' ('AccountID','Type','Debit','Credit','Description','Memo')
    INSERT' at line 1
  8. #5
  9. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,303
    Rep Power
    9400
    Yeah... No idea how any of those queries are working.

    You can't quote table and field names with 's or "s. Those are exclusively for strings. If you want to quote the names (generally not necessary) then use `s (backticks).
    Code:
    UPDATE `accounts`...
    Code:
    INSERT INTO `transactions` (`AccountID`, `Type`, ...)
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    29
    Rep Power
    0
    did you try as "update accounts" insted of "update 'accounts'"...I think you make a mistake for quote (') on the accounts word
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0
    I apologize for my late reply. I have removed all the quotes. now the first query runs fine. the second query does not return with an error but it does not update the database either. the third query returns with

    Code:
    Database query failed3: 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 'Deposit, 'test')' at line 2
    here is the updated code

    PHP Code:
    <?php session_start(); ?>
    <?php 
    require_once("include/connection.php"); ?>

    <?php
        
        $Type 
    $_POST['Type'];
        
    $SourceAccount $_POST['SourceAccount'];            //routing number
        
    $TargetAccount $_SESSION['AccountData'][0];
        
    $Amount $_POST['Amount'];
        
    $Memo $_POST['Memo'];
        
        if(
    $Type == "Deposit")
            
                    
    mysql_query("UPDATE accounts
                                SET Balance = Balance - 
    $Amount
                                WHERE ID = 
    $SourceAccount") OR die("Database query failed1: " mysql_error());
                                
                    
    mysql_query("UPDATE accounts
                                SET Balance = Balance + 
    $Amount
                                WHERE ID = 
    $TargetAccount") OR die("Database query failed2: " mysql_error());
                                
                    
    mysql_query("INSERT INTO transactions (AccountID,Type,Debit,Credit,Description,Memo)
                                 VALUES (
    $TargetAccount, Depoist, 0, $Amount, Online Deposit, $Memo)") OR die("Database query failed3: " mysql_error());
                                 
                    echo(
    "<a href='StudentPage.php'>Back To Home</a>");
                                 
    mysql_close($connection);
    ?>
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2003
    Posts
    3,622
    Rep Power
    595
    In the case of your 3rd query, you still have some problems with quotes. As a general rule you should enclose strings in single quotes while numbers do not need to be (requinix has already alluded to that).
    PHP Code:
    mysql_query("INSERT INTO transactions (AccountID,Type,Debit,Credit,Description,Memo)
                                 VALUES (
    $TargetAccount, 'Deposit', 0, $Amount, 'Online Deposit', '".$Memo."')") OR die("Database query failed3: " mysql_error()); 
    Note that I am guessing what is a string with respect to your variables. Use the above as an example and adjust for strings on your own.

    As for your 2nd query, it is bad programming practice to pass literal strings to the query. Rather you should build the query into a string variable then you can echo exactly what is being passed to the query. For debugging purposes, you can then copy and paste that string into a MySQL command line to see if it produces what you expect and modify it from there.

    That having been said you are playing with fire here. I don't know why no one has warned you yet but you are wide open to injection. First you should not be using the depreciated MySQL extensions, I recommend you change to PDO and prepared statements. However, if you ignore that advice, at least run your queries through 'mysql_real_escape_string'.

    Comments on this post

    • badger_fruit agrees : Yes, switch to PDO, it's straight forward enough and it helps to protect your database a lot more than the mysql libraries
    Last edited by gw1500se; November 5th, 2012 at 01:18 PM.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  16. #9
  17. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    If I had to guess, I would say the 2nd query doesn't work because $TargetAccount doesn't contain a valid ID.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2012
    Posts
    4
    Rep Power
    0

    Solved


    Thanks this solved it.

IMN logo majestic logo threadwatch logo seochat tools logo