Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Nov 2010
    Location
    California
    Posts
    42
    Thanks
    6
    Thanked 2 Times in 2 Posts

    In desperate need for help on this insert query

    I get an error message saying to check my syntax when I try to use an insert query., but I just can't find the error myself. Maybe I need a fresh set of eyes to find it for me. Anyone got any clues?

    PHP Code:
    if($action == 'complete')
    {
        
    $date date('D n/j/Y @ g:i A');
        
    $total sanitize($_GET['total']);
        
    $change sanitize($_GET['change']);
        
    $method sanitize($_GET['payment']);
        
    $tendered sanitize($_GET['tendered']);
        
    $purchasedQuery $db->query("SELECT * FROM `current_charge`");
        
    $listIds = array();
        
    $count 0;
        while(
    $purchased $purchasedQuery->fetch_assoc())
        {
            
    $listIds[] = $purchased['pid'];
            
    $count++;
        }
        echo 
    "<center><table cellpadding='10' width='45%' style=\"background-color:#FFFFFF; border:2px; border-style:solid;\">";
        
    $psold implode(","$listIds);
        
    //$count = $purchasedQuery->num_rows;
        
    $shift $db->query("SELECT * FROM `current_shift`");
        
    $tax1Query $db->query("SELECT SUM(tax1) AS tax1 FROM current_charge");
        
    $tax1Array $tax1Query->fetch_assoc();
        
    $tax1 $tax1Array['tax1'];
        
    $tax2Query $db->query("SELECT SUM(tax2) AS tax2 FROM current_charge");
        
    $tax2Array $tax2Query->fetch_assoc();
        
    $tax2 $tax2Array['tax2'];
        
    $sale $db->query("INSERT INTO general_sales (total, tax1, tax2, ptype, ctendered, change, psold, icount, shift) VALUES ('$total', '$tax1', '$tax2', '$method', '$tendered', '$change', '$psold', '$count', '$shift')");
        if(
    $db->errno)
        {
            die(
    $db->error);
            
        }
        echo 
    "<center><table cellpadding='10' width='45%' style=\"background-color:#FFFFFF; border:2px; border-style:solid;\">";
        echo 
    "<tr><tr>charged";
        echo 
    "</table></center>";
        

    Puppet Master + Programming = Eternal Bliss

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Number one, echo the query text. Like..
    PHP Code:
    echo "INSERT INTO general_sales (total, tax1, tax2, ptype, ctendered, change, psold, icount, shift) VALUES ('$total', '$tax1', '$tax2', '$method', '$tendered', '$change', '$psold', '$count', '$shift')"
    Number two, show us the error message. It points right to the problem in your query.

  • Users who have thanked Fumigator for this post:

    Puppet Master (06-06-2011)

  • #3
    New Coder
    Join Date
    Nov 2010
    Location
    California
    Posts
    42
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Fumigator View Post
    Number one, echo the query text. Like..
    PHP Code:
    echo "INSERT INTO general_sales (total, tax1, tax2, ptype, ctendered, change, psold, icount, shift) VALUES ('$total', '$tax1', '$tax2', '$method', '$tendered', '$change', '$psold', '$count', '$shift')"
    Number two, show us the error message. It points right to the problem in your query.
    Here's the exact error message that I get back from MySQL not PHP:

    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 'change, psold, icount, shift) VALUES ('3.77', '0', '0', 'cash', '5.00', '1.23', ' at line 1
    Puppet Master + Programming = Eternal Bliss

  • #4
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Why not do what Fumigator suggested as the first thing to do?

    Have a look at the actual insert query being run. It's the fist thing I would do as well.

  • #5
    New Coder
    Join Date
    Nov 2010
    Location
    California
    Posts
    42
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Sorry, I was going to do that but I got distracted.

    I echoed the sql statement, and it turns out, there is a comma being printed after the last value $shift. Why is that? I checked the variables and sql and couldn't find anything?

    INSERT INTO general_sales (total, tax1, tax2, ptype, ctendered, change, psold, icount, shift) VALUES ('3.77', '0', '0', 'cash', '5.00', '1.23', '1,4,58', '3', '')
    Puppet Master + Programming = Eternal Bliss

  • #6
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    Count the number of column names and number of values you have.

    It appears you have an empty value for your last column.

    Code:
    , '')
    also, are you sure

    Code:
    '1,4,58',
    is a correct value for psold?

  • Users who have thanked bullant for this post:

    Puppet Master (06-06-2011)

  • #7
    New Coder
    Join Date
    Nov 2010
    Location
    California
    Posts
    42
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by bullant View Post
    Count the number of column names and number of values you have.

    It appears you have an empty value for your last column.

    Code:
    , '')

    Yea I took a closer look at an earlier part in the script, and I realized that I was getting the $shift with a query to current_shift rather than the actual name of the table currentshift. And since mysql_errno will only bring the newest error message, it didn't show up in the error message.

    Now I'm getting a new error message :P
    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 'change, psold, icount, shift) VALUES ('3.77', '0', '0', 'cash', '5.00', '1.23', ' at line 1
    Puppet Master + Programming = Eternal Bliss

  • #8
    Banned
    Join Date
    Feb 2011
    Posts
    2,699
    Thanks
    13
    Thanked 395 Times in 395 Posts
    ok, then an easy way to fix php code that generates sql is to first write and debug the sql command in an sql window like phpMyAdmiin or SQLyog. Then transfer the sql command to your php script. It's often easier to create/debug sql queries in an sql GUI.

  • #9
    New Coder
    Join Date
    Nov 2010
    Location
    California
    Posts
    42
    Thanks
    6
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by bullant View Post

    also, are you sure

    Code:
    '1,4,58',
    is a correct value for psold?
    Yea, I add some IDs to an array and use php implode() to convert them into one string.

    I double checked the SQL statement and found out some of the variables were misaligned. Now the only error is on PHPs end, Mysql is expecting a string to input for one of the field. But PHP is supplying something else.

    I just gotta go through the script line by line to see where its going wrong.
    Puppet Master + Programming = Eternal Bliss


  •  

    Posting Permissions

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