MySQL query or condition to fetch "1970-01-01" date value in the table cell |
MySQL query or condition to fetch "1970-01-01" date value in the table cell |
shankar from vizag |
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 |
CharlesEF |
Dec 9 2023, 11:10 PM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,996 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'.
|
CharlesEF |
Dec 10 2023, 03:29 AM
Post
#3
|
Programming Fanatic Group: Members Posts: 1,996 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.
|
shankar from vizag |
Dec 12 2023, 01:45 AM
Post
#4
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
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 |
Dec 12 2023, 02:08 AM
Post
#5
|
Programming Fanatic Group: Members Posts: 1,996 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. 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). This post has been edited by CharlesEF: Dec 12 2023, 02:09 AM |
CharlesEF |
Dec 12 2023, 09:33 PM
Post
#6
|
Programming Fanatic Group: Members Posts: 1,996 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"]); |
shankar from vizag |
Dec 22 2023, 08:38 PM
Post
#7
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
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: 978 |
CharlesEF |
Dec 22 2023, 11:39 PM
Post
#8
|
Programming Fanatic Group: Members Posts: 1,996 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 |
shankar from vizag |
Dec 23 2023, 01:18 AM
Post
#9
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
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 |
Dec 23 2023, 04:29 AM
Post
#10
|
Programming Fanatic Group: Members Posts: 1,996 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. |
shankar from vizag |
Dec 23 2023, 08:28 PM
Post
#11
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
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 |
Dec 24 2023, 01:13 AM
Post
#12
|
Programming Fanatic Group: Members Posts: 1,996 Joined: 27-April 13 From: Edinburg, Texas Member No.: 19,088 |
Try this and let me know about error messages.
CODE <?php [code]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"; } ?> |
CharlesEF |
Dec 24 2023, 03:06 AM
Post
#13
|
Programming Fanatic Group: Members Posts: 1,996 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')"; |
shankar from vizag |
Dec 24 2023, 10:47 PM
Post
#14
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
Try this and let me know about error messages. CODE <?php [code]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"; } ?> 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 |
Dec 25 2023, 04:06 AM
Post
#15
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
Try this and let me know about error messages. CODE <?php [code]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. 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 |
Dec 25 2023, 04:34 PM
Post
#16
|
Programming Fanatic Group: Members Posts: 1,996 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.
|
CharlesEF |
Dec 28 2023, 05:33 PM
Post
#17
|
Programming Fanatic Group: Members Posts: 1,996 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"; } ?> |
shankar from vizag |
Dec 31 2023, 07:58 AM
Post
#18
|
Advanced Member Group: Members Posts: 202 Joined: 18-June 13 Member No.: 19,316 |
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 |
Dec 31 2023, 02:54 PM
Post
#19
|
Programming Fanatic Group: Members Posts: 1,996 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. |
Lo-Fi Version | Time is now: 10th December 2024 - 06:15 AM |