Inserting variable form data using PHP and MySQLi

Question

sorry if this is very basic, but I've been going around in circles for a while. I'm new to PHP, but none of the documentation seems to help, and none of the previous questions on here quite explain it.

I'm trying to create a simple form that takes user input and creates a record in a database, but the code I have does not seem to recognise the variables provided. When I use strings instead, the insert operation completes, so I know it's not a problem with the connection.

I don't get any error messages - the page refreshes as though nothing had happened.

<form action="" method="post">
    <input type="text" name="new_word" id="word" required="required"/>
    <input type="text" name="book_ref" id="book" required="required"/>
    <input type="text" name="page_range" id="page" required="required"/>
    <input type="submit" value="Submit" name="submit"/>
</form>
<?php
if(isset($_POST["submit"])){
    $servername = "localhost";
    $username = "user";
    $password = "pass";
    $dbname = "database";
    $conn = new mysqli($servername, $username, $password, $dbname);
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    $stmt = $conn->prepare("INSERT INTO `words` (`Word`, `Book ID`, `Page`) 
VALUES (?,?,?)");
    $stmt->bind_param(1, $word);
    $stmt->bind_param(2, $book);
    $stmt->bind_param(3, $page);
    $word = $_POST["new_word"];
    $book = $_POST["book_ref"];
    $page = $_POST["page_range"];
    $stmt->execute();
    $conn->close();
}
?>

getting values after insert query? put your `$word` etc variables before `$stmt` — Arsalan Mithani, Feb 17 '17 at 11:31
you have to define these variables $word,$book ,$page after this condition if(isset($_POST["submit"])){ — Rushil K. Pachchigar, Feb 17 '17 at 11:31

The Codesee · Answer 1 · 2017-02-17T11:55:04.047

2

You need to define the variables before you use them.

Your code should look like this:

$word = $_POST["new_word"];
$book = $_POST["book_ref"];
$page = $_POST["page_range"];

$stmt = $conn->prepare("INSERT INTO `words` (`Word`, `Book ID`, `Page`) 
VALUES (?,?,?)");
$stmt->bind_param("sss", $word, $book, $name);
$stmt->execute();
$conn->close();

edited Feb 17 '17 at 11:55

answered Feb 17 '17 at 11:30

The Codesee

3,714
5
38
78

You could change `$stmt->bind_param("sss", $word, $book, $name);` to just `$stmt->execute([$word, $book, $name]);` to make life a little simpler ^_^ – Option Feb 17 '17 at 11:32
1

@Option Good idea, I've added this to my answer. – The Codesee Feb 17 '17 at 11:33
@Option Just out of interest, which out of the two methods are more safe from SQL Injection, or are they both the same? – The Codesee Feb 17 '17 at 11:34
Thanks for the speedy response, but unfortunately that doesn't seem to fix it. I've tried the code exactly as you wrote it, and also tried putting the variable definition right after the IF statement opens, but no luck! – Luke Feb 17 '17 at 11:36
@Option you could refrain from commenting until you learn to read the question and the answer. – Your Common Sense Feb 17 '17 at 11:37
@YourCommonSense, I passed on additional info that TheCodesee could add into his answer. My addition isn't incorrect it would just further input for his reponse. – Option Feb 17 '17 at 11:38
@Luke Please add `print_r($stmt->errorInfo());` after `$stmt->execute();` and submit the form and let me know if it returns any errors. – The Codesee Feb 17 '17 at 11:38
@Option you could pass additional ideas only if you have an idea what are you talking about. – Your Common Sense Feb 17 '17 at 11:43
@YourCommonSense, so my additional info was incorrect? Strangely... I picked up using $execute([$var]); from your PDO blog... – Option Feb 17 '17 at 11:45
@Option and where can we see any trace of PDO here? – Your Common Sense Feb 17 '17 at 11:45

score 0 · Answer 2 · answered Feb 17 '17 at 11:32

You have to assign values first and then use them.

$word = $_POST["new_word"];
$book = $_POST["book_ref"];
$page = $_POST["page_range"];    

$stmt = $conn->prepare("INSERT INTO `words` (`Word`, `Book ID`, `Page`) 
VALUES (?,?,?)");
$stmt->bind_param(1, $word);
$stmt->bind_param(2, $book);
$stmt->bind_param(3, $page);



$stmt->execute();

$conn->close();

score 0 · Answer 3 · answered Feb 17 '17 at 11:34

Try this

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<input type="text" name="new_word" id="word" required="required"/>
<input type="text" name="book_ref" id="book" required="required"/>
<input type="text" name="page_range" id="page" required="required"/>
<input type="submit" value="Submit" name="submit"/>
</form>

<?php
if(isset($_POST["submit"])){
  $servername = "localhost";
  $username = "user";
  $password = "pass";
  $dbname = "database";
  $conn = new mysqli($servername, $username, $password, $dbname);
  if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
  }
$word = $_POST["new_word"];
$book = $_POST["book_ref"];
$page = $_POST["page_range"];
 $stmt = $conn->prepare("INSERT INTO `words` (`Word`, `Book ID`, `Page`) VALUES (?,?,?)");
$stmt->bind_param(1, $word);
$stmt->bind_param(2, $book);
$stmt->bind_param(3, $page);
$stmt->execute();
$conn->close();
}
?>

score 0 · Accepted Answer · answered Feb 17 '17 at 11:37

0

Why don't you just write it like this:

$stmt->bind_param('sss', $_POST['new_word'], $_POST['book_ref'], $_POST['page_range']);

It will insert each element without creating superfluous variables, and since it seems all the POST-content is from text fields, they're all strings. If there are digits only, you can change the corresponding s to a d

answered Feb 17 '17 at 11:37

junkfoodjunkie

3,168
1
19
33

Ah! Yes! That did it. I don't know why that did it, but it did it. Thank you! – Luke Feb 17 '17 at 11:42

Inserting variable form data using PHP and MySQLi

4 Answers4