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

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0

    PHP MySQL Insert Not Working


    I am working on a website for a client. I have created a login/register system for the website. I've been using MySQL as a database for the login and registration. My most recent work on this website has been creating an order form that stores information in the database about orders made through the company. I know for a fact that my code is getting to the section where it should be inserting information into the database, and it is displaying the success message that comes after the insert statement. I understand that I need to have better security for MySQL code insertion on the order form. I will fix that later. For now, here is my insert statement.

    $query=mysqli_query($con,"insert into orders (user_index,order_date,item_number,price,tax,pay_method) values (".$user_index.",'".$order_date."',".$item_number.",".$price.",".$tax.",'".$pay_method."')");

    $con contains the connection statement. Orders is the table. The only variable not represented here is a primary key autoincrement. I have a similar table set up for storing information about the users and it all works just fine. user_index and item_number are Ints. order_date is a date. price and tax are doubles. pay_method is a string. Please help, I am at a total loss. I've looked all over the internet for an answer and even posted this question elsewhere. My client is beginning to grow impatient and I fear losing his business.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,632
    Rep Power
    1945
    just a question to be sure: When you say that
    it is displaying the success message that comes after the insert statement
    you have checked that $query is true?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    37
    Rep Power
    2
    Try doing it simply :
    PHP Code:
    $query=mysqli_query($con,"insert into orders (user_index,order_date,item_number,price,tax,pay_method) values ($user_index,'$order_date',$item_number,$price,$tax,'$pay_method')"); 
    Could you show us the result when you run this query from the phpmyadmin ??
    Code:
    DESCRIBE TABLE orders
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by The Alchemist
    Try doing it simply :
    PHP Code:
    $query=mysqli_query($con,"insert into orders (user_index,order_date,item_number,price,tax,pay_method) values ($user_index,'$order_date',$item_number,$price,$tax,'$pay_method')"); 
    Could you show us the result when you run this query from the phpmyadmin ??
    Code:
    DESCRIBE TABLE orders
    Error 1064. I even tried "describe table 'orders'". I have MySQL 5.0.95-community.
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,907
    Rep Power
    6351
    1064 is a syntax error. Try:
    Code:
    SHOW CREATE TABLE `orders`;
    What was the error from the FIRST statement you ran, the one that prompted you to make this thread? Did you check the error? Do you echo mysqli_error() to the screen and read it?
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by ManiacDan
    1064 is a syntax error. Try:
    Code:
    SHOW CREATE TABLE `orders`;
    What was the error from the FIRST statement you ran, the one that prompted you to make this thread? Did you check the error? Do you echo mysqli_error() to the screen and read it?
    show create table 'orders'; shows the same error.
    That's the thing. From my experience when a MySQL line of PHP breaks, so does the full function of the page regardless of whether or not I tell it to show any errors. I'll try it to see if I can get it to show an error.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Here is the 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 ''20130101',1234567890,5,5,'check')' at line 1

    Since I can't get it to show what you're looking for, I'll tell you the columns and data types to at least get you some of the information about the table:
    order_index - int, autoincrement, primary key
    user_index - int
    order_date - date
    item_number - int
    price - double
    tax - double
    pay_method - enum

    Hope this info helps
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Sydney Australia
    Posts
    183
    Rep Power
    83
    Originally Posted by cpdemond9313
    Here is the 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 ''20130101',1234567890,5,5,'check')' at line 1
    If you are trying to store your date in a DATE type (and you should be), then the format is wrong, and will fail, which is what your error shows.

    The date has to be YYYY-mm-dd format (or YYYY/mm/dd) for MySQL to take it.
  16. #9
  17. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,907
    Rep Power
    6351
    show create table 'orders'; shows the same error.
    COPY AND PASTE. Do not retype what I said because you think you can get it right. Copy and paste, this is a computer, not a human being. You have single quotes (apostrophes) where I had backticks (the thing on the tilde key). Apostrophes will, in fact, throw this exact error.
    That's the thing. From my experience when a MySQL line of PHP breaks, so does the full function of the page regardless of whether or not I tell it to show any errors.
    That's not true, but further down you find the error.

    Barry is correct, your syntax for DATE type columns is incorrect. Y-m-d is the format string to date() that you want to give it.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by ManiacDan
    COPY AND PASTE. Do not retype what I said because you think you can get it right. Copy and paste, this is a computer, not a human being. You have single quotes (apostrophes) where I had backticks (the thing on the tilde key). Apostrophes will, in fact, throw this exact error.
    That's not true, but further down you find the error.

    Barry is correct, your syntax for DATE type columns is incorrect. Y-m-d is the format string to date() that you want to give it.
    Backticks made zero difference. Is there something I need to do to convert the string to a date format that MySQL will recognize? I added the dashes, but it still didn't work.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,987
    Rep Power
    375
    1. you know you are supposed to put this directly in MYSQL not your PHP?
    2. "describe table 'orders'" is not what the poster gave you. he gave you:

    describe table orders but having tested it out it should be:

    describe orders

    I think what they are trying to see is your columns in your table,. so in phpmyadmin do a structure of your table, take a screenshot and post it here.

    Also what you need to do is, print out your QUERY on screen and then copy and paste it directly into php my admin, and also post it here if you still get errors
  22. #12
  23. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,907
    Rep Power
    6351
    Right, most of what we're suggesting should have been done in your query browser.

    Make this SQL its own variable and print it out:
    PHP Code:
    $sql "insert into orders (user_index,order_date,item_number,price,tax,pay_method) values ($user_index,'$order_date',$item_number,$price,$tax,'$pay_method')";
    echo 
    "Executing: <i>{$sql}</i>";
     
    $query=mysqli_query($con,$sql); 
    Copy and paste the results here (obviously with the corrected order_date)
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.

IMN logo majestic logo threadwatch logo seochat tools logo