CODEWITHSUNDEEP
javascriptphpjqueryajax
AlwaysStudent
AlwaysStudent

Reputation: 1374

default NULL is not set

I am having a problem with contenteditable. I am trying to make a ajax post methot using contenteditable. I have two contenteditable div.

If it does not write anything, it must be NULL from the database table but it shows empty post not setting NULL.

AJAX

$("body").on("click", ".post", function() {
   var text1 = $("#text1").html();
   var text2 = $("#text2").html();
   
   var data = 'text1=' + encodeURIComponent(text1) + '&text2=' + encodeURIComponent(text2);
   
      $.ajax({
         type: 'POST',
         url: '/requests/post.php',
         data: data,
         cache: false,
         beforeSend: function() {
            // Do something
         },
         success: function(html) {
            console.log("post sended");
         }
      });
   
});

HTML

<div class="abc" id="text1" contenteditable="true" placeholder="Write something1"></div>
<div class="text2" id="text2" contenteditable="true" placeholder="Write something2"></div>

post.php

<?php 
include "../inc/inc.php";
if(isset($_POST['text1'])){
    $text1 = mysqli_real_escape_string($db, $_POST['text1']);
    $text2 = mysqli_real_escape_string($db, $_POST['text2']);
    $data = $POST->POST($uid,$text1, $text2); 
}
?>

and POST function

public function POST($uid,$text1, $text2) {
    
    
    $time=time(); // Current post time
    $ip=$_SERVER['REMOTE_ADDR']; // user ip
    $query = mysqli_query($this->db,"SELECT post_id,text1,text2 FROM `posts` WHERE uid_fk='$uid' order by post_id desc limit 1") or die(mysqli_error($this->db));
    $result = mysqli_fetch_array($query, MYSQLI_ASSOC); 
    // Add the insert POST
     $query = mysqli_query($this->db,"INSERT INTO `posts` (text1,text2, uid_fk,time) VALUES ('$text1','$text2','$uid','$time')") or die(mysqli_error($this->db));
     
}

All code is working fine.But problem is text2. If doesn't write any text it is showing empty result in database. I want to set id default NULL

enter image description here

Wrong way,

enter image description here

It should looks like this

enter image description here

What i am missing here? Anyone can help me in this regard ?

Upvotes: 0

Views: 62

Answers (2)

Nishanth Matha
Nishanth Matha

Reputation: 6081

The problem is you're passing an empty value instead of null itself

Just check if the string is empty and put null if it's empty

if(isset($_POST['text1'])){
    $text1 = mysqli_real_escape_string($db, $_POST['text1']);
    $text2 = mysqli_real_escape_string($db, $_POST['text2']);
    $text1 = !empty($text1) ? $text1 : null;
    $text2 = !empty($text2) ? $text2 : null;
    $data = $POST->POST($uid,$text1, $text2); 
}

And also you're insert statement is adding ' around the string which will make null as string 'null' :

So to process accordingly use pdo:

$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname","$dbusername","$dbpassword");
$stmt = $dbh->prepare("INSERT INTO `posts` (text1,text2, uid_fk,time) VALUES (:text1,:text2,:uid,:time)");

$statement->execute(array(':text1'=>$text1,
                          ':text2', $text2,
                          ':uid', $uid,
                          ':time', $time
                    ));

Edit:

For some reasons if you can't or don't want to use PDO. You'd end up hacking in the sql. Something like:

$text2 = !empty($text2) ? "'".$text2."'":null;
$text1 = !empty($text1) ? "'".$text1."'":null;
$query = mysqli_query($this->db,"INSERT INTO `posts` (text1,text2, uid_fk,time) VALUES ($text1,$text2,'$uid','$time')");

Upvotes: 1

JustOnUnderMillions
JustOnUnderMillions

Reputation: 3795

Add a precheck to the logic, because you have to give NULL or not useing the field in the insert to get an real NULL in the table.

// Add the insert POST
 $text2 = $text2?"'$text2'":'NULL';
 $query = mysqli_query($this->db,"INSERT INTO `posts` (text1,text2, uid_fk,time) VALUES ('$text1',$text2,'$uid','$time')") or die(mysqli_error($this->db));

INSERT TEXT vs NON-TEXT

Insert a real NULL

INSERT INTO posts (text1) VALUES (null)

Insert a text with content NULL

INSERT INTO posts (text1) VALUES ('NULL')

Insert table default:

INSERT INTO posts (text1) VALUES (default)

Insert a text with content default

INSERT INTO posts (text1) VALUES ('default')

To understand it more here an example sql:

INSERT INTO test (testfield) VALUES (null), ('null'), (default), ('') ;

Make a table test with one field VARCHAR(50) NULL DEFAULT NULL and execute the SQL and look what you get. You will get 2 entries with real null, one with empty string and one with an string null.

Upvotes: 0

Related Questions