MySQL query or condition to fetch "1970-01-01" date value in the table cell Options

[shankar from vizag]

Greetings

I have a mysql table with date column cell. At the time input from the php page, if a date type input left blank which is saving 1970-01-01 as the cell value.

When I use select query to display records, those particular fields displaying 1970-01-01.

Could anyone help me either in saving the empty date field as 1970-01-01 as blank

or

a condition to display a blank field in the php page by the select query when the cell value contains 1970-01-01.

Regards
shankar

[CharlesEF]

If you have a MySQL DATE or DATETIME column and it's not marked as 'NOT NULL' then you should be able to save a blank date using 'NULL'.

[CharlesEF]

Or, you could set the column as 'Default NULL'. Then only post the date after you check to be sure it's not blank.

[shankar from vizag]

Or, you could set the column as 'Default NULL'. Then only post the date after you check to be sure it's not blank.

thank you for replies. I tried both ways. But when I left a Date type input field empty, it is saving 1970-01-01.

What I am doing now is, while fetching records with select query, masking if the date is 1970-01-01 with rgba(0,0,0,0).

If the table dont save like that, problem will be solved.

Kindly help me out.

Regards

[CharlesEF]

Or, you could set the column as 'Default NULL'. Then only post the date after you check to be sure it's not blank.

What I am doing now is, while fetching records with select query, masking if the date is 1970-01-01 with rgba(0,0,0,0).

If the date value is empty/blank then you must bind NULL to the date column. DO NOT leave the date value empty/blank. No masking either.

This post has been edited by CharlesEF: Dec 12 2023, 02:09 AM

[CharlesEF]

Since you posted no code all I can do is show you how I do it. I use PDO. DATE, DATETIME, INT all work using null. I could store 0 for an INT but I just store null instead.

CODE

$pdo->bind(":type", empty($_POST["type"]) ? null : $_POST["type"]);

[shankar from vizag]

Since you posted no code all I can do is show you how I do it. I use PDO. DATE, DATETIME, INT all work using null. I could store 0 for an INT but I just store null instead.

CODE

$pdo->bind(":type", empty($_POST["type"]) ? null : $_POST["type"]);

Greetings

Sorry for the delayed reply.. Till now I am trying various ways to fix the issue but still not succeeded. I tried to attach mysql database which contains date file but failed since the site restricts .sql format and hence attached the php file for reference.

database name i put as `test` with a table name `date` contains two columns. One is name with varchar datatype and other one is DoB (date of birth) with date datatype with null option through phpmyadmin.

Pls suggest me a way out.

thanks and regards  date.php ( 1.74k ) Number of downloads: 142

[CharlesEF]

Your current code is wide open to SQL injection attacks. NEVER use any user supplied data directly in your code. You use 'mysqli' and you should be using parameterized queries. See here for an example. If you refuse to take my advice you can try:

CODE

$dob=empty($_POST['dob']) ? null : $_POST['dob'];

You're still trying to insert a blank date right?

This post has been edited by CharlesEF: Dec 22 2023, 11:42 PM

[shankar from vizag]

Your current code is wide open to SQL injection attacks. NEVER use any user supplied data directly in your code. You use 'mysqli' and you should be using parameterized queries. See here for an example. If you refuse to take my advice you can try:

CODE

$dob=empty($_POST['dob']) ? null : $_POST['dob'];

You're still trying to insert a blank date right?

Thank you charles for your guidance.

I will try with the above line of code and come back to you.

Regards
Shankar

[CharlesEF]

It's been many years since I've used mysqli so you might have to try this. Use this line for the $dob:

CODE

$dob=empty($_POST['dob']) ? null : "'{$_POST['dob']}'";

Then change this line:

CODE

$query="INSERT INTO date(name,date) VALUES('$name', '$dob')";

To this:

CODE

$query="INSERT INTO date(name,date) VALUES('$name', $dob)";

Let me know which one works.

[shankar from vizag]

It's been many years since I've used mysqli so you might have to try this. Use this line for the $dob:

CODE

$dob=empty($_POST['dob']) ? null : "'{$_POST['dob']}'";

Then change this line:

CODE

$query="INSERT INTO date(name,date) VALUES('$name', '$dob')";

To this:

CODE

$query="INSERT INTO date(name,date) VALUES('$name', $dob)";

Let me know which one works.

Hi Charles

I tried with the suggestion. What happened is, when both name and date inputs are given its working fine. But when I put date input empty query not working at all.

Regards

[CharlesEF]

Try this and let me know about error messages.

CODE

<?php
session_start();
$con=mysqli_connect("localhost", "root", "", "test");
if(isset($_POST['save']))
{
    $name=$_POST['name'];
    $dob=empty($_POST['dob']) ? null : "'" . $_POST['dob'] . "'";

    $query="INSERT INTO date(name,date) VALUES('$name', $dob)";
    $query_run = mysqli_query($con, $query);
    $_SESSION['status']="Date values inserted";
}
else
{
    $_SESSION['status']="Date values Inserting failed";
}
?>

[code]

[CharlesEF]

You should run a few tests. Don't use the $_POST array at all. Hard code some test name and run the INSERT statement. Check if either works:

CODE

$query="INSERT INTO date(name,date) VALUES('Test Name', null)";

Or

CODE

$query="INSERT INTO date(name,date) VALUES('Test Name', 'null')";

[shankar from vizag]

Try this and let me know about error messages.

CODE

<?php
session_start();
$con=mysqli_connect("localhost", "root", "", "test");
if(isset($_POST['save']))
{
    $name=$_POST['name'];
    $dob=empty($_POST['dob']) ? null : "'" . $_POST['dob'] . "'";

    $query="INSERT INTO date(name,date) VALUES('$name', $dob)";
    $query_run = mysqli_query($con, $query);
    $_SESSION['status']="Date values inserted";
}
else
{
    $_SESSION['status']="Date values Inserting failed";
}
?>

[code]

Good Morning Chales

Let me first greet you Christmas wishes.

When I use the above code, its working fine when name and date input values are given.

But it is not working when date column left empty.

Regards
Shankar

This post has been edited by shankar from vizag: Dec 24 2023, 10:49 PM

[shankar from vizag]

Try this and let me know about error messages.

CODE

<?php
session_start();
$con=mysqli_connect("localhost", "root", "", "test");
if(isset($_POST['save']))
{
    $name=$_POST['name'];
    $dob=empty($_POST['dob']) ? null : "'" . $_POST['dob'] . "'";

    $query="INSERT INTO date(name,date) VALUES('$name', $dob)";
    $query_run = mysqli_query($con, $query);
    $_SESSION['status']="Date values inserted";
}
else
{
    $_SESSION['status']="Date values Inserting failed";
}
?>


if I try the following line

$dob=empty($_POST['dob']) ? null : $_POST['dob'];

empty cell is saving with 00-00-0000.

[code]

Good Morning Chales

Let me first greet you Christmas wishes.

When I use the above code, its working fine when name and date input values are given.

But it is not working when date column left empty.

Regards
Shankar

I also tried the following

$con=mysqli_connect("localhost", "root", "", "test");

if(isset($_POST['save']))
{
$name=$_POST['name'];

if(empty($_POST['dob'])){
$dob=NULL;
}
else{
$dob=$_POST['dob'];
}


which also saving 00-00-0000

regards



Finally,



<?php
session_start();
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');


if(isset($_POST['save']))
{
$name=$_POST['name'];
$dob=$_POST['dob'];

$sql = "INSERT INTO date (name, date) VALUES (?, ?)";
$stmt = $pdo->prepare($sql);
$stmt->execute([$name, $dob === '' ? NULL : $dob]);

$_SESSION['status']="Date values inserted";

}

else
{
$_SESSION['status']="Date values Inserting failed";

}
?>

with this change its now working. Thank you Charles for your guidance. But I want to know how can we achieve the same result with conventional mysqli coding instead of PDO application ?

Regards

This post has been edited by shankar from vizag: Dec 25 2023, 04:47 AM

[CharlesEF]

I'm glad the PDO version works. But, I'd still like you to run the 2 tests I posted. To be clear, I don't want you to use your PHP code at all. I want you to run the queries directly in MySQL Workbench or phpMyAdmin. As I said, it has been many years since I've use mysqli but I seem to remember it worked when using parameterized queries.

[CharlesEF]

I have not heard from you about the test results. But try this:

CODE

<?php
session_start();
$con=mysqli_connect("localhost", "root", "", "test");
if(isset($_POST['save']))
{
    $name=$_POST['name'];
    $dob=empty($_POST['dob']) ? "NULL" : "'{$_POST['dob']}'";

    $query="INSERT INTO date(name,date) VALUES('$name', $dob)";
    $query_run = mysqli_query($con, $query);
    $_SESSION['status']="Date values inserted";
}
else
{
    $_SESSION['status']="Date values Inserting failed";
}
?>

[shankar from vizag]

I have not heard from you about the test results. But try this:

CODE

<?php
session_start();
$con=mysqli_connect("localhost", "root", "", "test");
if(isset($_POST['save']))
{
    $name=$_POST['name'];
    $dob=empty($_POST['dob']) ? "NULL" : "'{$_POST['dob']}'";

    $query="INSERT INTO date(name,date) VALUES('$name', $dob)";
    $query_run = mysqli_query($con, $query);
    $_SESSION['status']="Date values inserted";
}
else
{
    $_SESSION['status']="Date values Inserting failed";
}
?>

Hi Charles

Let me wish one and all of this forum a very happy new year in advance

Its my hobby to design in PHP MYSQL. I learnt from the online resources like our forum, youtube videos hence you may find lots of errors in my approach.

my happiness of putting down the above error evaporates with a fresh error with select query to display records.

Whereever NULL values in the table cell, select query fetching and displaying as 1970-01-01. The following is the code,

$conn = new mysqli("localhost", "root", "", "test");
$sql="select * from date";
$res=$conn->query($sql);
while($row=$res->fetch_assoc()){
?>

<tr>
<td>
if(is_null($row['dob'])){
echo '--';
}else{
echo 'date("d-m-Y",strtotime($row['dob']))';
}?>
</td>
</tr>


With the above If condition, the display of 1970-01-01 is stopped where there is a NULL in table cell but when there is a date, that also showing as blank.

Please guide me to overcome this issue.

Regards

This post has been edited by shankar from vizag: Dec 31 2023, 07:59 AM

[CharlesEF]

You've posted new code about reading a database but you haven't answered my questions. Lets fix the INSERT problem first.

What is the result of the 2 tests I asked you to run in MySQL Workbench or phpMyAdmin?
What is the result of the PHP code I posted a few days later? That code is based on the fact that PHP and MySQL NULL values are different. So instead of passing a PHP NULL value I want to try passing a MySQL NULL value. On the PHP side it is just a string. On the MySQL side it should be a MySQL NULL value. But I need you to run the test.