New row is created with empty values in php using mysql database

Question

I am using ampps server for my database. Using php I have written script to connect with server database. Now I want to insert data in the database table.

I tried writing the script and new row is getting inserted with null values.

I tried multiple insert queries but did not succeed.

  <?php

require("testDB.php");


class insertUserHelper
{

    private $name;
    private $email;
    private $status;


    function insertUserHelper($name,$email,$status)
    {
          $this -> name = $name;
          $this -> email = $email;
          $this -> status = $status;

    }

    function insert()
    {
       $con = testDatabase::getDB();

       echo $name;
       echo $email;
       echo $status;

       $sql =  "INSERT INTO user ". "(name,email,status) ".
       "VALUES ". "('$name','$email','$status')";

       if (mysqli_query($con, $sql)) {
                 echo "New record created successfully";
       } else {
                echo "Error: " . $sql . "<br>" . mysqli_error($con);
        }

    }
}

?>

Can anyone help please,very new to php. Thank you..

score 1 · Answer 1 · edited Aug 03 '16 at 10:54

1

try this

$sql =  "INSERT INTO user (name,email,status) VALUES ('$name','$email','$status')";

edited Aug 03 '16 at 10:54

Nana Partykar

10,556
10
48
77

answered Aug 03 '16 at 10:42

ayush

134
9

score 1 · Accepted Answer · answered Aug 03 '16 at 10:46

1

You are creating local variables $name, $email and $status in your insert() function, try using $this->name, $this->email and $this->status instead. And use a prepared statement with bind variables instead of building a vulnerable query like that.

answered Aug 03 '16 at 10:46

rypskar

2,012
13
13

yes got it. Thank you so much. could you please guide me for the prepared statement for this code. @rypskar – Aug 03 '16 at 10:59
1

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php, http://stackoverflow.com/questions/9629328/how-to-use-mysqli-prepared-statements-in-php and the answer here from @NanaPartykar are good places to start using prepared statement – rypskar Aug 03 '16 at 11:04

score 0 · Answer 3 · edited Aug 03 '16 at 10:55

0

please replace

$sql =  "INSERT INTO user ". "(name,email,status) ".
       "VALUES ". "('$name','$email','$status')";

with

$sql =  "INSERT INTO user ". "(name,email,status) ".
       "VALUES ". "('$this->name','$this->email','$this->status')";

edited Aug 03 '16 at 10:55

Nana Partykar

10,556
10
48
77

answered Aug 03 '16 at 10:42

Nagesh

437
3
13

Nana Partykar · Answer 4 · 2016-08-03T11:28:01.247

use bind_param to execute your query.
Use $this->name, $this->email, $this->status instead of $name, $email, $status.

UPDATED CODE

function insert()
{
  $con = testDatabase::getDB();

  $stmt = mysqli_prepare($con, "INSERT INTO user (name,email,status) VALUES (?, ?, ?)");
  mysqli_stmt_bind_param($stmt, 'sssd', $this->name, $this->email, $this->status);

  if (mysqli_stmt_execute($stmt)) {
    echo "New record created successfully";
  } else {
    echo "Error: " . $sql . "<br>" . mysqli_error($con);
  }
}

For More Info, Click mysqli_stmt::bind_param

EXPLANATION

$stmt->bind_param("sss", $firstname, $lastname, $email);

The sss argument lists the types of data that the parameters are. The s character tells mysql that the parameter is a string.

The argument may be one of four types:

i - integer
d - double
s - string
b - BLOB

We must have one of these for each parameter. By telling mysql what type of data to expect, we minimize the risk of SQL injections.

For more info, click Prepared Statements

What is the second parameter 'sssd' in mysqli_stmt_bind_param? @Nana Partykar — , Aug 03 '16 at 11:11

New row is created with empty values in php using mysql database

4 Answers4