Error when passing an empty string to a non-null column using prepared statement

PHP
1

I am receiving an error message when I pass an empty string (‘’) to the ? placeholder in a prepared statment when the column is defined in MySQL as NOT NULL. I’m not sure if this is a PHP or MySQL question. The error message says the column cannot be null even though I am passing the empty string. Do I need to define the column as accepting nulls to make this work.

I would really prefer to use the prepared statement because I use it for several purposes and sometimes the value is assigned and sometimes it is the empty string.

Thanks

2

Can you post some code?

You should be able to pass an empty string in like so (using PDO):


$stmt = $db->prepare('INSERT INTO test_table SET test_value = ?');
$stmt->execute(array('')); // note the empty string
3

Is the MySQL database field for test_value set to NOT NULL?

I would love for someone to review this code and tell me where to improve it.


class Users {
	public $userid=0;
	public $email='';
	public $password='';
	public $firstname='';
	public $lastname='';
	public $username='';
	
	function __construct() {
		if (isset($_SESSION['user']['userid'])) {
			$userid = $_SESSION['user']['userid'];	
			$username = $_SESSION['user']['username'];	
			if (isset($_SESSION['user']['email'])){$email = $_SESSION['user']['email'];}	
		}
	}
	
	public function addUser() {
		include $_SESSION['dbserver'];
		$qt_sql = "insert into users (email, password, firstname, lastname, username, dateadded, datelastchange) values (?, ?, ?, ?, ?, now(), now())";
		$passwordm = md5($password . 'xxx');
		try {
			if (!$prep1 = $link->prepare($qt_sql)) {throw new Exception($link->error);}
			if (!$prep1->bind_param("sssss", $email, $passwordm, $firstname, $lastname, $username)) {throw new Exception($link->error);}
			if (!$prep1->execute()) {throw new Exception($link->error);}
			$retval = 99;
		} catch (Exception $e) {
			$retval = $e->getMessage();
		}
		return $retval;
	}
}

When I am using this the first time, there is no email value so it is set to ‘’ and the email field in MySQL is set to NOT NULL.

4

In your addUser function, you’re not passing the values of $email, $password, $firstname etc.

Therefore, they are inserted bound to your query as NULL.

You need to either pass them to the addUser function like so: function addUser($email, $password, $firstname) or use them like this in your query: $this->email

5

Aha, thanks so much. I am new to OOP obviously but trying to learn and this helps a bunch. I understand exactly what happened why it’s doing it. Again, thanks

6

No problem, glad to help :slight_smile: