Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2013
    Posts
    18

    Unanswered: Moving from mysql to PDO

    Hi guys.

    #1064 - 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 ':name,ilpostcode,:email,honenumber,:comments,:maillist)' at line 1.
    Can you help at all with this? A bit baffled.

    Also, re. pdo: how do you specify table name (as opposed to dbname)? I have put my table name following INSERT INTO. Is this correct? This is not as intuitive as mysql...

    PHP Code:
    <?php

    $dbtype 
    ""$dbhost ""$dbname ""$dbuser ""$dbpass "";
     
    $conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

    $name 'name';
    $oilpostcode 'oilpostcode';
    $email 'email';
    $phonenumber 'phonenumber';
    $comments 'comments';
    $maillist 'maillist';
     
    $sql "INSERT INTO offmainsgas (name,oilpostcode,email,phonenumber,comments,maillist) VALUES (:name,:oilpostcode,:email,:phonenumber,:comments,:maillist)";
    $q $conn->prepare($sql);
    $q->execute(array(':name'=>$name,
                      
    ':oilpostcode'=>$oilpostcode,
                  
    ':email'=>$email,
              
    ':phonenumber'=>$phonenumber,
              
    'comments'=>$comments,
              
    'maillist'=>$maillist));
     
    if(
    $result){
    $url 'successful_form_submission.html';
    echo 
    '<META HTTP-EQUIV=Refresh CONTENT="0; URL='.$url.'">';
    }

    else {
    echo 
    "ERROR";
    }
    ?>

    Many thanks in advance.

    Ed

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Print your $sql variable to screen and you'll spot your error



    Clue: check the VALUES part!
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2013
    Posts
    18
    Thanks George! Bloomin colons.

    Can you give me some guidance on where to go to fill in my knowledge gaps. I have got this far with web development by copying and working backwards to adapt things for myself. However, with pdo, this is the first time I am pretty lost. Honestly, the pdo php manuals don't make a huge amount of sense, and unlike mysql there are very few examples about on the web for me to steal and use for my own!

    Unfortunately my code still does not work... when I use

    [echo $sql; die();]

    I get

    [INSERT INTO offmainsgas (name,oilpostcode,email,phonenumber,comments,maill ist) VALUES (:name,ilpostcode,:email,honenumber,:comments,:maillist)]

    when I chuck that into sql on phpmyadmin I get a syntax error telling me that there's something up with my list of values.

    Thanks,

    Ed

  4. #4
    Join Date
    Apr 2013
    Posts
    18
    btw: not sure why those spaces are there (in middle of maillist and before maillist). Here it is again:

    INSERT INTO offmainsgas (name,oilpostcode,email,phonenumber,comments,maill ist) VALUES (:name,ilpostcode,:email,honenumber,:comments,:maillist)

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Wrap your code in [ code] and [/ code] (remove extra spaces) and it should format better
    e.g.
    Code:
    INSERT INTO offmainsgas (name,oilpostcode,email,phonenumber,comments,maill ist)
    VALUES (:name,:pilpostcode,:email,:phonenumber,:comments, :maillist)
    So the problem is that you're not actually supplying values!

    A valid statement could look like this:
    Code:
    INSERT INTO your_table (some_text_column) VALUES ('Text Value');
    INSERT INTO your_table (some_numeric_column) VALUES (937);
    INSERT INTO your_table (some_date_column) VALUES ('2013-06-20');
    So you could change your code to this (assuming all text columns) and it would work, but I doubt that's what you're trying to achieve
    Code:
    INSERT INTO offmainsgas (name,oilpostcode,email,phonenumber,comments,maill ist)
    VALUES (':name',':pilpostcode',':email',':phonenumber',':comments', ':maillist')
    The problem here is that if you were to look at the values that get saved in the "name" column, the value would be ":name"!

    Looks to me like you want a variable value to be entered here, yes?
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2013
    Posts
    18
    Yes, correct. I have given the same column names of my tables to my form field names. So I am trying to send whatever is put in the 'name' field of my form to the 'name' field of my table.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I fear that I may have led you down the wrong path.

    I am not familiar with PDO and after checking the manual I actually think that you're original code should work!

    Here's a couple of things to try:
    Code:
    /* test to get just a single variable to work */
    $sql = "INSERT INTO offmainsgas (name, oilpostcode, email, phonenumber, comments, maillist)
              VALUES (:name, 'TEST', 'TEST', 'TEST', 'TEST', 'TEST')"
    $q = $conn->prepare($sql);
    $q->execute(array(':name' => 'TEST'));
    
    /* don't build the string up in a separate variable */
    $q = $conn->prepare("INSERT INTO offmainsgas (name, oilpostcode, email, phonenumber, comments, maillist)
              VALUES (:name, 'TEST', 'TEST', 'TEST', 'TEST', 'TEST')");
    $q->execute(array(':name' => 'TEST'));
    
    /* bind parameters another way */
    $sql = "INSERT INTO offmainsgas (name, oilpostcode, email, phonenumber, comments, maillist)
              VALUES (:name, 'TEST', 'TEST', 'TEST', 'TEST', 'TEST')"
    $q = $conn->prepare($sql);
    $q->bindParam(':name', 'TEST', PDO::PARAM_STR);
    $q->execute();
    HTH
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2013
    Posts
    18
    It is slightly encouraging to discover this is not entirely simple mindedness on my part. I was encouraged (i think by you!) to move from mysql to either pdo or mysqli, so this is what I am trying to do. I chose pdo over mysqli as googlers seem to put pdo above mysqli. My forms are currently functional using old school sql. However I believe I need to update to a newer library, and I believe I am also leaving myself open to badness as my validation is js...

    How do people send form data to databases?!?!?! Is there a simpler way?! I just want to send various forms' data to their respective databases safely... ;-)

    So: the internet gods declare that there is a parse syntax error with these three bits of the tests above respectively.
    Code:
    $q = $conn->prepare($sql);
    Code:
    $q = $conn->prepare
    Code:
    $q = $conn->prepare($sql);
    Incidentally. Regarding the original code i first posted: I am simply getting the 'ERROR' message, and when I use [code]echo $sql; die();[code] I am getting the message I kept putting up incorrectly before:
    Code:
    INSERT INTO offmainsgas (name,oilpostcode,email,phonenumber,comments,maillist) VALUES (:name,:oilpostcode,:email,:phonenumber,:comments,:maillist)
    hmmmmmm....

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If you are struggling with PDO then I'd suggest you use MySQLi. MySQLi is very simialr to the older MySQL library.

    the MySQL libraries are deprecated, meaning at some stage in the future they will be no longer supported.

    as to the error message
    what error messsage is it
    what is it complaining about

    are you using a try catch block

    have you set error handling to an appropriate level?
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2013
    Posts
    18
    Ok... Happy to move to MySqli. It does indeed feel a bit more intuitive for me...

    BUT following PHP MySQL Insert Into I very simply copied and pasted my values in. This time I get the following error!
    Code:
    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 'on')' at line 3
    Here is said code:
    Code:
    <?php
    $con=mysqli_connect("...","...","...","....");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
    
    $sql="INSERT INTO offmainsgas (name, oilpostcode, email, phonenumber, comment, maillist)
    VALUES
    ('$_POST[name]','$_POST[oilpostcode]','$_POST[email]','$_POST[phonenumber]','$_POST[comment],'$_POST[maillist]')";
    
    if (!mysqli_query($con,$sql))
      {
      die('Error: ' . mysqli_error($con));
      }
    echo "1 record added";
    
    mysqli_close($con);
    ?>
    Am I cursed?
    Last edited by flattenedthird; 06-20-13 at 12:58. Reason: mis-wrote [/code]

  11. #11
    Join Date
    Apr 2013
    Posts
    18
    @*&%ing apostrophes... Ok. I go that working. Sorry to be a pain.

    Can you point me to somewhere i can get me some server side mysqli 'validation' so i'm not reliant on my java script?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    validate in Javascript so you catch user data errors
    validate in PHP so you catch inadvertent and malicious data problems. do that inside your PHP script that handles the results of the javascript
    AND if you are being a smartypants do an additional layer of validations inside the SQL engine used stored procedures
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Apr 2013
    Posts
    18
    Getting somewhere!

    Currently reading PHP: Database Security - Manual and hastily making adjustments!

    THANK YOU generous humans

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    apostrophe's can be handled easily inside PHP using mysqli_real_escape_string

    debugging SQL is a heck of a lot easier you examine the actual SQL being sent to the sql engine rather than the PHP code that raises the error
    consider usign the or die fucntion and send examine the relevant error4 message(s). see Mysqli error handling
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Apr 2013
    Posts
    18
    I will do my best to get my brain round this stuff, but this stuff is what I just don't quite understand how to apply to my own code. I'm sure I will get it, I just need to find some spare minutes in my life!

    Having the words and links you are using are invaluable though for googling with.

    Thanks again.

Posting Permissions

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