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

    Join Date
    May 2013
    Posts
    15
    Rep Power
    0

    Cant insert rows in my DB


    Hi,

    I am trying to insert a few rows into my DB, but it doesnt work.

    Situation:
    • I can connect to the DB via php, but I cant insert data

    • All elements of $InhaltTdElemente[1] are not NULL


    Problem:
    PHP Code:
    $res=mysqli_query($con,"INSERT INTO Deutschland(Land,Model,Farbe,Typnummer,Anrede,Name,Vorname,Straße,Hausnummer,PLZ,Stadt,Email1,Email2,Email3) VALUES ($InhaltTdElemente[1][$i],$InhaltTdElemente[1][$i+1],$InhaltTdElemente[1][$i+2],$InhaltTdElemente[1][$i+3],$InhaltTdElemente[1][$i+4],$InhaltTdElemente[1][$i+5],$InhaltTdElemente[1][$i+6],$InhaltTdElemente[1][$i+7],$InhaltTdElemente[1][$i+8],$InhaltTdElemente[1][$i+9],$InhaltTdElemente[1][$i+10],$InhaltTdElemente[1][$i+11],$InhaltTdElemente[1][$i+12],$InhaltTdElemente[1][$i+13])");

    if (!
    $res) {  
       die(
    'Invalid query: ' mysql_error());
     } else  { 
        
    // Do here what you need 



    Result:
    "Invalid query: "

    Thats my DB:
    http://postimg.org/image/471pk39s9/

    And Iam having problems with the collations, it should by by default "utf_8_bin german

    Thanks for your support(hopefully) (:
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,868
    Rep Power
    368
    echo out your query and try it straight into mysql and see what is happening.

    I am guessing: Straße as a field name is not acceptable? or should be in ''. You also have: Email1,Email 2,Email3 which is wrong!
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,920
    Rep Power
    1045
    Hi,

    I'm surprised this doesn't throw a big PHP parsing error ...

    Anyway, this doesn't even remotely do what you might expect. There are no quotes, so MySQL won't even recognize the strings. Things like $i + 1 don't get evaluated in a PHP string. And you don't do any escaping.

    I think it's time do use MySQLi correctly. Dynamic queries should be done with prepared statements. Do not just inject some variables into the query strings. This makes the code wide open to SQL injections. Make a prepared statement and then pass the values to the bound variables. The link above shows a concrete example of how to do it.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    15
    Rep Power
    0
    hi,
    thanks for your help- I tried it with prepared statements -but now i got an error
    "Database prepare error"
    I can't find any mistakes in my query...

    PHP Code:
    $sql1 "INSERT INTO `Deutschland`(`Land`,`Model`,`Farbe`,`Typnummer`,`Anrede`,`Name`,`Vorname`,`Strae`,`Hausnummer`,`PLZ`,`Stadt`,`Email1`,`Email2`,`Email3`)                                          VALUES                                         (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";                                        

    if (!
    $stmt $con->prepare($sql1)) { 
            echo 
    'Database prepare error'
            exit;      
    }      

    $stmt->bind_param('ssssssssssssss'$InhaltTdElemente[1][$i],$InhaltTdElemente[1][$i+1],$InhaltTdElemente[1][$i+2],$InhaltTdElemente[1][$i+3],$InhaltTdElemente[1][$i+4],$InhaltTdElemente[1][$i+5],$InhaltTdElemente[1][$i+6],$InhaltTdElemente[1][$i+7],$InhaltTdElemente[1][$i+8],$InhaltTdElemente[1][$i+9],$InhaltTdElemente[1][$i+10],$InhaltTdElemente[1][$i+11],$InhaltTdElemente[1][$i+12],$InhaltTdElemente[1][$i+13]);   

    if (!
    $stmt->execute()) {  
           echo 
    'Database execute error';
           exit; 
    }     

     
    $stmt->close(); 
    Thanks for your help.
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,920
    Rep Power
    1045
    Check the error: echo $con->error;. Or even better, turn on exceptions:

    PHP Code:
    $mysqli_driver = new mysqli_driver();
    $mysqli_driver->report_mode MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT
    I'm pretty sure that `PL Z` is not the correct column name. You should get rid of the stupid backticks (``), because they tend to cover up obvious errors like this. You should also rethink your naming style and use lowercase English names ("first_name", "last_name" etc. instead of "Vorname", "Nachname"). MiXeD cAsE, as pretty as it may be, can lead to problems as soon as you try to use the database on a different operating system. SQL isn't Excel. The names in SQL are technical identifiers, they're not supposed to be pretty column headings that look nice on the screen.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    15
    Rep Power
    0
    hi,

    latest code:

    PHP Code:
       $sql1 "INSERT INTO 
                                            `Deutschland`(`Land`,`Model`,`Farbe`,`Typnummer`,`Anrede`,`Name`,`Vorname`,`Strae`,`Hausnummer`,`PLZ`,`Stadt`,`Email1`,`Email2`,`Email3`) 
                                            VALUES
                                            (?,?,?,?,?,?,?,?,?,?,?,?,?,?) 
                                         "
    ;
                                         
     if (!
    $stmt $con->prepare($sql1)) {
            echo 
    'Database prepare error';
            exit;
        }

        
    $stmt->bind_param('ssssssssssssss'$InhaltTdElemente[1][$i],$InhaltTdElemente[1][$i+1],$InhaltTdElemente[1][$i+2],$InhaltTdElemente[1][$i+3],$InhaltTdElemente[1][$i+4],$InhaltTdElemente[1][$i+5],$InhaltTdElemente[1][$i+6],$InhaltTdElemente[1][$i+7],$InhaltTdElemente[1][$i+8],$InhaltTdElemente[1][$i+9],$InhaltTdElemente[1][$i+10],$InhaltTdElemente[1][$i+11],$InhaltTdElemente[1][$i+12],$InhaltTdElemente[1][$i+13]);

        if (!
    $stmt->execute()) {
            echo 
    'Database execute error';
            exit;
        }

        
    $stmt->close(); 

    I turned on that reporting thing:

    PHP Code:
    Fatal errorUncaught exception 'mysqli_sql_exception' with message 'Unknown column 'Straße' in 'field list'' in /opt/lampp/htdocs/xampp/new/ConvertToCvs.php:94

     Stack trace
    #0 /opt/lampp/htdocs/xampp/new/ConvertToCvs.php(94): mysqli->prepare('INSERT INTO ? ...') #1 {main} thrown in /opt/lampp/htdocs/xampp/new/ConvertToCvs.php on line 94 

    Line 94: (?,?,?,?,?,?,?,?,?,?,?,?,?,?)


    "Straße" thats strange I don't have a field with that value, but I have problems with the collation... maybe thats a problem...it automatic change the collation of my DB-table(Deutschland).

    I tried, but it doesnt work:
    PHP Code:
    $sql="ALTER TABLE Deutschland DEFAULT CHARACTER SET utf8 COLLATE utf8_bin"
    Thats my DB:
    http://postimg.org/image/471pk39s9/


    Thats strange when I edit that post 'PLZ' looks like 'PLZ`, but when I post it looks like 'PL Z' ...


    Thanks
  12. #7
  13. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,920
    Rep Power
    1045
    Get rid of the German names and use proper English names. Seriously. I mean, it's just pointless to keep fighting with exotic identifiers when the whole idea is bad.

    The column names have nothing to do with the character set of the database or the table. They're metadata, not database content. Your issue probably has to do with the connection encoding or maybe the encoding of the source code. But again: Use standard English names.
    The 6 worst sins of security How to (properly) access a MySQL database with PHP

    Why cant I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    15
    Rep Power
    0
    Thanks it worked (:

IMN logo majestic logo threadwatch logo seochat tools logo