PHP Notice: Undefined variable: when trying to insert data into mysql database

Question

I have a MySQL database and I am trying to insert data into it using a PHP form running on IIS on my Windows 10 machine. I have the code below in a .php file:

<?php

if (isset($_POST['submit'])) {

    require "config.php";

    try {
        $connection = new PDO($dsn, $username, $password, $options);
        // insert new user code will go here



        $new_user = array(
            "firstname" => $_POST['firstname'],
            "lastname"  => $_POST['lastname'],
            "city"      => $_POST['city'],
            "country"   => $_POST['country'],
            "age"       => $_POST['age']
        );

        $sql = sprintf(
            "INSERT INTO %s (%s) values (%s)",
            "users",
            implode(", ", array_keys($new_user)),
            ":" . implode(", :", array_keys($new_user))
        );

        $statement = $connection->prepare($sql);
        $statement->execute($new_user);




    } catch(PDOException $error) {
        echo $sql . "<br>" . $error->getMessage();
    }
}
?>


<?php include "templates/header.php"; ?><h2>Add a user</h2>

<form method="post">
    <label for="firstname">First Name</label>
    <input type="text" name="firstname" id="firstname">
    <label for="lastname">Last Name</label>
    <input type="text" name="lastname" id="lastname">
    <label for="city">City</label>
    <input type="text" name="city" id="city">
    <label for="country">Country</label>
    <input type="text" name="country" id="country">
    <label for="age">Age</label>
    <input type="text" name="age" id="age">
    <input type="submit" name="submit" value="Submit">
</form>

<a href="index.php">Back to home</a>
<?php include "templates/footer.php"; ?>

When I fill out my form and hit "submit" to write to mysql database, I get the following error:

PHP Notice: Undefined variable: sql in C:\inetpub\wwwroot\create.php on line 35

I am new to both mysql and php and trying to learn the basics using a little project.

score 1 · Answer 1 · answered Nov 22 '19 at 06:12

1

when your script has an exception in try block, it goes to the catch block and there is no var like $sql so the solution is:

$sql define an empty variable before the try block

answered Nov 22 '19 at 06:12

RIYAJ MULLA

50
6

score 1 · Answer 2 · answered Nov 22 '19 at 06:22

$sql variable has defined one the before try block, Please check the below code -

<?php

 if (isset($_POST['submit'])) {

 require "config.php";
 $sql = "";
 try {
    $connection = new PDO($dsn, $username, $password, $options);
    // insert new user code will go here

   $new_user = array(
      "firstname" => $_POST['firstname'],
      "lastname"  => $_POST['lastname'],
      "city"      => $_POST['city'],
      "country"   => $_POST['country'],
      "age"       => $_POST['age']
  );

  $sql = sprintf(
      "INSERT INTO %s (%s) values (%s)",
      "users",
      implode(", ", array_keys($new_user)),
      ":" . implode(", :", array_keys($new_user))
  );

  $statement = $connection->prepare($sql);
  $statement->execute($new_user);

  } catch(PDOException $error) {
    echo $sql . "<br>" . $error->getMessage();
  }
} 
?>

score -1 · Answer 3 · answered Nov 22 '19 at 06:33

here you don't have any need to use $sql in catch block. Mainly you'll have to edit $sql variable in try block. Please find the below details:

<?php
if(isset($_POST['submit'])) {
  require "config.php";

  try {
    $connection = new PDO($dsn, $username, $password, $options);
    // insert new user code will go here
    $new_user = array(
      "firstname" => $_POST['firstname'],
      "lastname"  => $_POST['lastname'],
      "city"      => $_POST['city'],
      "country"   => $_POST['country'],
      "age"       => $_POST['age']
    );

    $sql = sprintf(
      "INSERT INTO %s (%s) values (%s)",
      "users",
      implode(", ", array_keys($new_user)),
      "'" . implode("', '", $new_user) . "'"
    );

    $statement = $connection->prepare($sql);
    $statement->execute($new_user);
  } catch(PDOException $error) {
    echo $error->getMessage();
  }
}
?>

Here, you can see that the variable substitution has been modified. This was incorrect in your code and hence the catch block gets executed and the undefined variable error i.e. $sql related issue occurs. Hope this may help.

PHP Notice: Undefined variable: when trying to insert data into mysql database

3 Answers3