height converter to MySQL Database

  • Profile Image
    jmuyky
    Asked on November 27, 2016 at 12:44 AM

    I'm using the height converter widget ( https://widgets.jotform.com/widget/height_converter ).  I'm trying to send my submissions to the MySql database.  It is working fine for my other fields except for my height field.  I have structured my height field with a VARCHAR datatype. Please see error message below

    "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '6\"","CM":"167.64"}]',"

    Not a developer. Appreciate any advise on possible solutions.  Thanks

  • Profile Image
    liyam
    Answered on November 27, 2016 at 12:59 AM

    Hello jmuyky,

    Can you copy and paste here the SQL code for us to be able to see the issue? If the data is too long, it would be fine if you can paste here a few lines of around 4-5 before and after to where that part of the error is happening? it looks like the double quote that is prematurely being included that may be causing the problem but we'll need to see the data in order for us to see what's wrong.

    Or if you can share with us on now are getting the SQL data so we can try and check, it would be great.

    We'll look forward to your response.

     

  • Profile Image
    jmuyky
    Answered on November 27, 2016 at 01:17 AM

    I agree. I think the double quote is the culprit.

    Followed the instructions here ( https://www.jotform.com/help/126-How-to-send-Submissions-to-Your-MySQL-Database-Using-PHP)

    Here is 

    DATA SUBMITTED:[height68] => [{"Feet & Inches":"5'6\"","CM":"167.64"}]

    PARTIAL CODE:

    $dbhost = '****';

    $dbusername = '****';

    $dbpassword = '*****';

    $dbname = '****';

    mysql_connect( $dbhost, $dbusername, $dbpassword) or die(mysql_error());

    mysql_select_db($dbname);

    $height68 = $_POST['height68'];

    mysql_query("INSERT INTO `table_name` (height68) VALUES ('$height68') ") 

        or die(mysql_error());  

     

    ERROR MESSAGE:

    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '6\"","CM":"167.64"}]',

    Hope this helps...

  • Profile Image
    liyam
    Answered on November 27, 2016 at 02:00 AM

    Hello,

    It looks like you're inserting a JSON encoded data. If what you want is to insert the actual json data, I would suggest to convert it to array first:

        $height68 = serialize(json_decode($_POST['height68'], true));

    Then use the $height68 variable to be inserted to your database. 

    If the problem persists, please let us know.

  • Profile Image
    jmuyky
    Answered on November 27, 2016 at 02:09 AM

    tried it..unfortunately problem persist, but somewhat different error message. See below.

    Quote.

    You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '6"";s:2:"CM";s:6:"167.64";}}'

    Unquote.

     

  • Profile Image
    jmuyky
    Answered on November 27, 2016 at 02:21 AM

    will using this help?

    mysql_real_escape_string() OR replace()

  • Profile Image
    jmuyky
    Answered on November 27, 2016 at 02:53 AM

    I used mysql_real_escape_string() and it worked fine and it inputted data as submitted.  Just need to fix the format I guess.  Thanks for the help.

  • Profile Image
    Boris
    Answered on November 27, 2016 at 05:16 AM

    We're glad to hear that you've got it working. :)

    If there is anything else we can do to help, please don't hesitate opening another support thread in our forum any time:

    https://www.jotform.com/contact

    Cheers!