height converter to MySQL Database

  • 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

  • liyam
    Replied 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.

     

  • jmuyky
    Replied on November 27, 2016 at 1: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...

  • liyam
    Replied on November 27, 2016 at 2: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.

  • jmuyky
    Replied on November 27, 2016 at 2: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.

     

  • jmuyky
    Replied on November 27, 2016 at 2:21 AM

    will using this help?

    mysql_real_escape_string() OR replace()

  • jmuyky
    Replied on November 27, 2016 at 2: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.

  • Boris
    Replied on November 27, 2016 at 5: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!