how to leave a column in a MYSQL phpmyadmin database empty if no data entered

Question

I have 5 columns in my database: id, post, datetime, name, img_dir

I want the 'name' and 'img_dir' column to be optional to post in. I can't post the post or datetime data without posting the name and img_dar data aswell. if i try and post just the post and datetime without the name or img_dir it sends no data to the database. how do i make the name and img_dir columns blank if no data is posted?

$sql = "INSERT INTO $table(post, datetime, name, img_dir)VALUES('$post', '$datetimeOG', '$name', '$img_dir')";
$mysqli->query($sql) or die ($mysqli->error);

Answer 1

OP Stated in a comment:

both columns default have been set to NULL although the problem still persists

This is because you're passing an empty value into the SQL, because the way your SQL is written, it expects values, even if you don't have and values to insert.

So; here's a rewrite to only add values to the INSERT if they're not empty (or you can increase specificity* and set if they're not null)

NOTICE:

This code is for illustration only, and should probably not be used as-is as it might have multiple security issues inherited from the original code. In a perfect world I would recommend you rewrite your entire codebase to use try..catch blocks and to use MySQL Transactions and Prepared Statements (more on those below ;-)).

$insert = []; //make array
$insert['post'] = $post;
$insert['datetime'] = $datetimeOG;
$insert['name'] = $name;
$insert['img_dir'] = $img_dir;

// Now, we have an array of VALUEs and the KEYs are the column names. 
// So let's remove the 'empty' values.
$insert = array_filter($insert);

*NOTE:

This includes whitespace and zero and "falsey" values so you will need to tweak to your own exact requirements. See the linked question for more info for how to do this.

Reference - Removing empty array elements

Security Sidenote:

There are lots of things wrong with the security of your SQL / PHP on the question, far out of the scope of this late night answer, but the most important thing is the $table variable.

If this $table variable is always going to be the same value, it shouldn't be a variable and should be hardcoded. Then you can use Prepared Statements.

If the table variable is from a shortlist, it should be whitelisted, typically using a switch statement or similar.

If the tavble variable is from a longlist, then you can use REGEX to remove anything nasty, this is what I will do for example, as I have no idea what you're data looks like:

// remove all non a-z or _ - characters. 
$table = preg_replace('/[^a-z_-]/i','',$table); 

Reference - How to Insert into MySQL using Perpared Statements

Magic implosions!

Arrays are magical entities. They can be imploded (like monkeys, but with less hair and blood).

So; we have the $insert array and the $sql string;

This can be magnificently recombined and without even realising it you will be the belle of the embassy ball; I mean, er, yes, it will work.

    // The column string is made from the array keys
    $columns = implode(",",array_flip($insert));
    // Be careful to note the quotes...
    $values = "'".implode("','", $insert )."'"; 

Creating the final outcome.....

So let's wrap this up and bring it all together!

$insert = []; //make array
$insert['post'] = $post;
$insert['datetime'] = $datetimeOG;
$insert['name'] = $name;
$insert['img_dir'] = $img_dir;

$insert = array_filter($insert);

/***
 * Check just in case....
 ***/
if(count($insert) > 0 ){
    $cleanedTableName = preg_replace('/[^a-z_-]/i','',$table); 
    $columns = implode(",",array_flip($insert));
    // Be careful to note the quotes...
    $values = "'".implode("','", $insert )."'"; 
    $sql = INSERT INTO ". $cleanedTableName." (".$columns.") VALUES (".$values.")";  
    $mysqli->query($sql) or 
        error_log("You have an SQL Error! (NEVER output your error to screen): ".$mysqli->error);
}

Gorgeous. I'm off to make a tea.

See it in action!!!