PDO - SQL not working without any warning

Question

I wrote a function to insert what i want in any database :

public function insert($db, $array_label, $array_value){
    $DB = new PDO('mysql:host='.$_SESSION['mysql'][0].';dbname='.$_SESSION['mysql'][1].';charset=utf8', $_SESSION['mysql'][2], $_SESSION['mysql'][3]);

    $sql = "INSERT INTO '".$db."' (";

    for($i = 0; $i < count($array_label); $i++){
        if($i > 0)
            $sql .= ", ";

        $sql .= "'".$array_label[$i]."'";
    }

    $sql .= ") VALUES (";

    for($i = 0; $i < count($array_label); $i++){
        if($i > 0)
            $sql .= ", ";

        $sql .= ":".$array_label[$i];
    }
    $sql .= ")";

    $stmt = $DB->prepare($sql);
    echo '\$DB->prepare("'.$sql.'");';//DEBUG

    for($i = 0; $i < count($array_label); $i++){
        $label = ":".$array_label[$i];
        $stmt->bindParam("$label", $array_value[$i]);
        echo '</br>\$stmt->bindValue("'.$label.'", '.$array_value[$i].')';//DEBUG
    }

    $stmt->execute;
    echo "</br></br>Requ&ecirc;te OK.";
}

}

Then, i use it like this :

$array_label = array('ID', 'ID_lang', 'ID_entry_cat', 'date', 'label', 'content');
$array_value = array($ID, $ID_lang, $ID_entry_cat, "2016-03-03 00:00:00", $label, $content);
 $DB->insert('entry', $array_label, $array_value);

After doing a lot of test, it may have a syntax problem, but i wasn't able to figure out where.

Here is what i got from the echo's(//DEBUG) :

$DB->prepare("INSERT INTO 'entry' ('ID', 'ID_lang', 'ID_entry_cat', 'date', 'label', 'content') VALUES (:ID, :ID_lang, :ID_entry_cat, :date, :label, :content)");
\$stmt->bindValue(":ID", 1)
\$stmt->bindValue(":ID_lang", 1)
\$stmt->bindValue(":ID_entry_cat", 1)
\$stmt->bindValue(":date", 2016-03-03 00:00:00)
\$stmt->bindValue(":label", dsqfsdq)
\$stmt->bindValue(":content", sdqfdsqf)

I know there is plenty topics on PDO not returning error but : After reading a lot of them, it didn't help me at all. Let say this : I'm a complete noob with PDO.

The echo i showed to you is what my function is doing.

I enclosed some pictures of my db to help :

PS: i leaved out "date" but it's not supposed to make troubles. I tested the same thing with it and it did the same thing : nothing.

Null isnt allowed in date field. What did you try to put there? What value? — , Mar 03 '16 at 10:53
PhpMyAdmin fill the field with something if you put nothing. — Madz, Mar 03 '16 at 11:00
You are using PDO in this case and I bet it tries to add NULL / nothing to the field which is causing it to fail because field isnt allowed to contain NULL or nothing — , Mar 03 '16 at 11:04
Even with a fake date -> "2016-03-03 00:00:00" , it doesn't work. — Madz, Mar 03 '16 at 11:06
Is ID_lang foreign key or just an index? Might be the foreign key doesnt exist? — , Mar 03 '16 at 11:26

Mihai · Accepted Answer · 2016-03-03T11:37:39.763

1

Replace bindparam with bindvalue since you are passing values of that array: EDIT you also have quotes for table and column names,I replaced them with backticks and also you are missing brackets for execute

 function insert($db, $array_label, $array_value){
        $DB = new PDO('mysql:host='.$_SESSION['mysql'][0].';dbname='.$_SESSION['mysql'][1].';charset=utf8', $_SESSION['mysql'][2], $_SESSION['mysql'][3]);

    $sql = "INSERT INTO `".$db."` (";

    for($i = 0; $i < count($array_label); $i++){
        if($i > 0)
            $sql .= ", ";

        $sql .= "`".$array_label[$i]."`";
    }

    $sql .= ") VALUES (";

    for($i = 0; $i < count($array_label); $i++){
        if($i > 0)
            $sql .= ", ";

        $sql .= ":".$array_label[$i];
    }
    $sql .= ")";

    $stmt = $DB->prepare($sql);
    echo '\$DB->prepare("'.$sql.'");';//DEBUG

    for($i = 0; $i < count($array_label); $i++){
        $label = ":".$array_label[$i];
        $stmt->bindValue("$label", "$array_value[$i]");
        echo '</br>\$stmt->bindValue("'.$label.'", "'.$array_value[$i].'")';//DEBUG
    }

    $stmt->execute();
    echo "</br></br>Requ&ecirc;te OK.";
}

edited Mar 03 '16 at 11:37

answered Mar 03 '16 at 10:44

Mihai

26,325
7
66
81

1

bindValue() type i guess – Muhammed Mar 03 '16 at 10:45
Not working either :/ . I don't understand why you put "'. and ."' so i only changed for bindvalue. – Madz Mar 03 '16 at 11:02
@Madz Edit your question with the echo for this option. – Mihai Mar 03 '16 at 11:03
@Madz I`ll test it on my machine,hold on – Mihai Mar 03 '16 at 11:07
Thanks a lot, your code is working ! Quotes are '' or "" ? Sorry i don't know rhe english for '' and "". – Madz Mar 03 '16 at 12:44
@Madz Single or double quotes are for VALUES (string or dates),backticks are for table and column names.Common beginner mistake – Mihai Mar 03 '16 at 12:47
Not really a 'beginner mistake'. I don't know what are 'backticks'. this -> / ? (i'm not an english native speaker) Anyway, thanks a lot, now it's finally working :) – Madz Mar 03 '16 at 13:39

Muhammed · Answer 2 · 2016-03-03T10:41:29.073

0

Try running PDO in Exception mode, you can then catch PDO exceptions:

$db_pdo = new PDO($db_dsn, $dbuser, $dbpassword);
$db_pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// its function
$stmt->execute();

Also turn on error reporting in PHP to see whats going on

edited Mar 03 '16 at 10:41

answered Mar 03 '16 at 10:38

Muhammed

1,592
1
10
18

Thanks for your answer. Unfortunatly, It did nothing :/ – Madz Mar 03 '16 at 10:41
do try {} catch (PDOException $e) { ..} with pdo statements – Muhammed Mar 03 '16 at 10:42
[do NOT try {} catch (PDOException $e) { ..} with pdo statements](https://phpdelusions.net/pdo#errors) – Your Common Sense Mar 03 '16 at 11:41

PDO - SQL not working without any warning

2 Answers2