Insert not working in php

Question

I've done it before but this time I dunno what's wrong!

Here is my PHP code:

      if (isset($_POST['add_sub']) && !empty($_POST['add_sub'])) {
          $word = $_POST['word'];
          $phonetic = $_POST['phonetic'];
          $meaning = $_POST['meaning'];
          $engMeaning = $_POST['engMeaning'];
          $example = $_POST['example'];
          $eMeaning = $_POST['eMeaning'];

          $sqlAdd = "INSERT INTO words (word,meaning,eng-meaning,example,example-meaning,phonetic)
 VALUES ('$word','$meaning','$engMeaning','$example','$eMeaning','$phonetic')";

          $db->query($sqlAdd);
          header('location: index');
        }
      }

and here is my form:

  <form action="index" method="post">
    <input type="text" name="word" id="word" placeholder="Word" value="">
    <input type="text" name="phonetic" id="phonetic" placeholder="Phonetic" value="">
    <input type="text" name="meaning" id="meaning" placeholder="Meaning" value="">
    <input type="text" name="engMeaning" id="endMeaning" placeholder="English Meaning" value="">
    <textarea type="text" name="example" id="example" placeholder="Example" value="" rows="5"></textarea>
    <textarea type="text" name="eMeaning" id="eMeaning" placeholder="Example Meaning" value="" rows="5"></textarea>
    <button type="submit" name="add_sub">Add</button>
  </form>

When I click on the submit button it just jumps back to the index page and nothing is added.

Is there anything that I can't see here?!

Update: I can get data from my DB with no problem, I just can't insert.

You are open to SQL injection, use Prepared statements instead — Spoody, Jul 15 '18 at 18:44
@Mehdi the thing is, it works most of the times for me but why not this time?! — GoddamnAllien, Jul 15 '18 at 18:52
@Mehdi and what do you mean by "You are open to SQL injection"? — GoddamnAllien, Jul 15 '18 at 18:53
Why not read up on parameter binding and enabling at least a bit error checking? "Works most of the time" is a clear indicator that the input has an impact. How? We don't know; since concrete input examples are amiss in this question. Chances are it's the usual: quotes + lack of escaping. — mario, Jul 15 '18 at 18:54
You may want to read about SQL injections, here is a useful question [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). And as @mario said there is a chance that the problem you are having is related with lack of escaping — Spoody, Jul 15 '18 at 18:56
This SQL could never work. You have invalid identifier names when unquoted. https://dev.mysql.com/doc/refman/8.0/en/identifiers.html `basic Latin letters, digits 0-9, dollar, underscore` — user3783243, Jul 15 '18 at 18:58
@user3783243 I changed them but still, I have the same problem. — GoddamnAllien, Jul 15 '18 at 19:02
Use error reporting. The driver will tell you exactly what is wrong. Also comment out the redirect for now. — user3783243, Jul 15 '18 at 19:04
@user3783243 There's no error! I did comment out the redirect and then echoed something but nothing was echoed. — GoddamnAllien, Jul 15 '18 at 19:07

Davidos · Answer 1 · 2018-07-15T18:56:25.560

2

Use ` at columns, then it should work:

$sqlAdd = "INSERT INTO words (`word`,`meaning`,`eng-meaning`,`example`,`example-meaning`,`phonetic`) VALUES ('$word','$meaning','$engMeaning','$example','$eMeaning','$phonetic')"

You should use backticks (`) for table and column names and single quotes (') for strings

If didn't fix then:

Debug query:

$result = $sql->query($sqlAdd) or exit("Error code ({$sql->errno}): {$sql->error}");

Are you sure you are passing $_POST['add_sub']?

Check your connection with database with

if (mysqli_connect_errno()) {
     echo 'There was an error with your connection: '.mysqli_connect_error();
}

edited Jul 15 '18 at 18:56

answered Jul 15 '18 at 18:45

Davidos

419
5
17

1

The backticks are recommended but not required. – Spoody Jul 15 '18 at 18:49
`example-meaning` and `eng-meaning` are the only ones that need backticks. – user3783243 Jul 15 '18 at 18:51
@GoddamnAllien so check your connection with database, show us more code. Are you sure you are passing $_POST['add_sub']? – Davidos Jul 15 '18 at 18:51
@Davidos I am getting data from my DB with no problem I just can't insert. – GoddamnAllien Jul 15 '18 at 18:54
@Davidos please take a look at the way he concaternated the sql and the variables.It is not supported an many phps. You must close the " and then use a dir like ` '".$var.".`. Got me ? – Jul 16 '18 at 18:37
@IWASBANNEDBYSOMESNOWFLAKE you can use $variables in double quotes. Example: `$test = 'mystring'; echo "'$test'";` will return `'mystring'` – Davidos Jul 16 '18 at 19:31

score 0 · Answer 2 · answered Jul 16 '18 at 04:51

First I created a table in the database MyDB

CREATE TABLE `words` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `word` VARCHAR(50) NOT NULL DEFAULT '0',
    `phonetic` VARCHAR(50) NOT NULL DEFAULT '0',
    `meaning` VARCHAR(50) NOT NULL DEFAULT '0',
    `engmeaning` VARCHAR(50) NOT NULL DEFAULT '0',
    `example` VARCHAR(50) NOT NULL DEFAULT '0',
    `eMeaning` VARCHAR(50) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

then I created a file and named it index.php and adapted your code a bit until it worked

<?php
    $_names         = array("word", "phonetic", "meaning",  "engmeaning",       "'example", "eMeaning");
    $_ids           = array("word", "phonetic", "meaning",  "engMeaning",       "example",  "eMeaning");
    $_placeholders = array("Word",  "Phonetic", "Meaning",  "English Meaning",  "Example",  "Example Meaning");

    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "myDB";


    function x($x){
          if(isset($_POST[$x])){
              return addslashes(strip_tags($_POST[$x]));
    }}
    function i($i){
        global $_names;
        global $_ids;
        global $_placeholders;
        echo "<input type=\"text\" name=\"".$_names[$i]."\" id=\"".$_ids[$i]."\" placeholder=\"".$_placeholders[$i]."\" value=\"".x($_names[$i])."\" /><BR>";}
    function t($i){
        global $_names;
        global $_ids;
        global $_placeholders;
        echo "<textarea type=\"text\" name=\"".$_names[$i]."\" id=\"".$_ids[$i]."\" placeholder=\"".$_placeholders[$i]."\" >".x($_names[$i])."</textarea><BR>";}


    if (isset($_POST['add_sub']) && !empty($_POST['add_sub'])) {
        // Create connection
        $conn = new mysqli($servername, $username, $password, $dbname);
        // Check connection
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }   
        $sql = "INSERT INTO `words` 
                    (   `word`,
                        `meaning`,
                        `engmeaning`,
                        `example`,
                        `eMeaning`,
                        `phonetic`
                    )VALUES(
                        '".x("word")."',
                        '".x("meaning")."',
                        '".x("engMeaning")."',
                        '".x("example")."',
                        '".x("eMeaning")."',
                        '".x("phonetic")."'
                    )"; 

        if ($conn->query($sql) === TRUE) {
            echo "New record created successfully";
        } else {
            echo "Error: " . $sql . "<br>" . $conn->error;
        }
        $conn->close();
    }

?>
<form action="index.php" method="post">
<?php i(0);i(1);i(2);t(3);t(4); ?>
<input type="submit" name="add_sub" value="Add">
</form>

I hope its usefull.

Your method of escaping is flat out wrong and dangerous. See also: https://stackoverflow.com/a/7810880/362536 — Brad, Jul 16 '18 at 05:01
I did... did you even read the answer I linked you to? I wouldn't call helping you fix your code "bragging". — Brad, Jul 16 '18 at 12:52
there are no killerbits in it. Supply the poof that my code is dangerous. or delete your post. — , Jul 16 '18 at 18:45

Insert not working in php

2 Answers2