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 10 of 10

Thread: PHP and MySQL

  1. #1
    New Coder
    Join Date
    Oct 2006
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    PHP and MySQL

    When I use my php script I get this 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 '12'' at line 1
    The code:
    PHP Code:
    $titletablesql "INSERT INTO TitleTable SET title='$title1', bookid='$bookid'";
    mysql_query($titletablesql) or exit(mysql_error()); 
    The 12 is the value for $bookid.

    Here are the fields:
    titleid - mediumint(10)
    title - varchar(100)
    bookid - mediumint(10)

    For the life of me I can't see what I am doing wrong. I also get the same error on similar things. Could the 12 be a string and not numeric or something?

    NOTE: When I manual type the exact same select statment in the command line mysql prompt (with actual values instead of variables) it works.
    Last edited by JimmyS; 01-03-2007 at 08:50 PM. Reason: Adding a Note

  • #2
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    Hi,

    You've confused the update syntax with the insert syntax, check it out here:
    http://dev.mysql.com/doc/refman/5.0/en/insert.html

    Good luck;

  • #3
    New Coder
    Join Date
    Oct 2006
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm not updating a existing record. bookid is from another table and is used as the foreign key for this new record.

  • #4
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    I never said you were updating, though you've used the update syntax.

    Insert:
    insert into tablename(columns) values(values)

    Update:
    update tablename set column = value, etc...

    PHP Code:
    $titletablesql "INSERT INTO TitleTable(title, bookid) VALUES('$title1', $bookid)";
    mysql_query($titletablesql) or exit(mysql_error()); 

  • #5
    New Coder
    Join Date
    Oct 2006
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh, I get ya, sorry about that.

    I used the same syntax for something else and it work, maybe I should clean that up too.

    Thanks for the help, it is much appriciated.

    Regards,

  • #6
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    I'm fairly sure INSERT INTO ... SET works in mysql. The change you made, and the cause of the reported error, was the 's around $bookid which, as an int and not a string, doesn't need (and can't have) quotes around it.

    If $bookid and $title are coming from user input (a form perhaps?) then they should really be passed through mysql_real_escape_string before being put into the database.
    http://php.net/mysql-real-escape-string

  • #7
    teh Moderatorinator
    Join Date
    Sep 2004
    Location
    USA
    Posts
    2,472
    Thanks
    4
    Thanked 40 Times in 40 Posts
    aye, never have I seen it that way. Clarification taken.

  • #8
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,079
    Thanks
    2
    Thanked 320 Times in 312 Posts
    The second syntax definition on the page at the link posted for the mysql manual documents the INSERT INTO SET... syntax. I just checked and this is present in the 4.1 version of the manual as well.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #9
    Senior Coder koyama's Avatar
    Join Date
    Dec 2006
    Location
    Copenhagen, Denmark
    Posts
    1,246
    Thanks
    1
    Thanked 5 Times in 5 Posts
    Hi guys...
    Hmmm... I never had any problems with quotes around numeric data in MySQL queries. Strings are apparently converted to numbers before inserting. Actually, it seems that it is better to have quotes around numeric data for better protection against injection.

    From the MySQL documentation, it says at some point:
    A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotes around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing non-numeric characters from it.
    JimmyS: When I get these kind of errors, I usually print the full query for better viewing
    Code:
    echo $titletablesql;
    since the MySQL error message always shows only your truncated query.

  • #10
    New Coder
    Join Date
    Oct 2006
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I figured out the problem, I was simply just missing a comma. I didn't notice it because I was working on it too long and over looking little things like that. Guess you shouldn't ruch things.

    Thanks for the help anyways


  •  

    Posting Permissions

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