The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> MySQL query or condition to fetch "1970-01-01" date value in the table cell
shankar from vizag
post Dec 9 2023, 08:33 PM
Post #1


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 9 2023, 11:10 PM
Post #2


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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'.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 10 2023, 03:29 AM
Post #3


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



Or, you could set the column as 'Default NULL'. Then only post the date after you check to be sure it's not blank.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post Dec 12 2023, 01:45 AM
Post #4


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ Dec 10 2023, 01:59 PM) *

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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 12 2023, 02:08 AM
Post #5


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



QUOTE(shankar from vizag @ Dec 12 2023, 12:45 AM) *

QUOTE(CharlesEF @ Dec 10 2023, 01:59 PM) *

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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 12 2023, 09:33 PM
Post #6


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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"]);
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post Dec 22 2023, 08:38 PM
Post #7


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ Dec 13 2023, 08:03 AM) *

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 regardsAttached File  date.php ( 1.74k ) Number of downloads: 724
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 22 2023, 11:39 PM
Post #8


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post Dec 23 2023, 01:18 AM
Post #9


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ Dec 23 2023, 10:09 AM) *

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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 23 2023, 04:29 AM
Post #10


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post Dec 23 2023, 08:28 PM
Post #11


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ Dec 23 2023, 02:59 PM) *

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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 24 2023, 01:13 AM
Post #12


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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]
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 24 2023, 03:06 AM
Post #13


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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')";
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post Dec 24 2023, 10:47 PM
Post #14


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ Dec 24 2023, 11:43 AM) *

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
ShankarAttached Image

This post has been edited by shankar from vizag: Dec 24 2023, 10:49 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post Dec 25 2023, 04:06 AM
Post #15


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



QUOTE(shankar from vizag @ Dec 25 2023, 09:17 AM) *

QUOTE(CharlesEF @ Dec 24 2023, 11:43 AM) *

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
ShankarAttached Image




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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 25 2023, 04:34 PM
Post #16


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 28 2023, 05:33 PM
Post #17


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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";
}
?>
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
shankar from vizag
post Dec 31 2023, 07:58 AM
Post #18


Advanced Member
****

Group: Members
Posts: 202
Joined: 18-June 13
Member No.: 19,316



QUOTE(CharlesEF @ Dec 29 2023, 04:03 AM) *

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
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Dec 31 2023, 02:54 PM
Post #19


Programming Fanatic
********

Group: Members
Posts: 1,984
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



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.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
6 User(s) are reading this topic (6 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 24th July 2024 - 02:00 AM