php mysql date picker to varchar to date... Oh boy, this one might be a challenge

[PNewCode]

I have an old database that has dates that are entered from a date picker. I don't have access to that database to change the structure of it because it's from an old partners website that told me "You can use it but I'm not messing with that stuff anymore" and wont give me the login info.
(only giving a reason why I can't change that)

TASK: To have a php page that selects the listings that are TODAY from the "birthday" column, so I can displays todays birthdays. However the column is VARCHAR instead of DATE

I googled this but my googlie gunk only gave me results on a process to change the table structure and convert.

Is there a way to select today, from the birthdays that are in today (not todays specific date, but like, tomorrow will be a new "today"... etc)

The dates list like the following examples in the column "birthday"

01/15/1980
10/22/1989
11/09/1979

... etc

I'm not asking for anyone to write out a page for me. Just maybe something easier for me to follow than the google results I keep seeing that seems to assume everyone is an expert and can translate everything lol

 

MANY THANKS!!!

[kicken]

You can just get the date prefix and look search for dates using like.

$today = date('m/d/%');
$sql = 'select * from users where birthday like ?';

 

[Barand]

You can convert your varchar date to the correct Y-m-d formats using mysql's STR_TO_DATE() function

eg

mysql> select * from testdate;
+------------+
| birthday   |
+------------+
| 01/15/1980 |
| 10/22/1989 |
| 11/09/1979 |
+------------+


mysql> UPDATE testdate set birthday = str_to_date(birthday, '%m/%d/%Y');


mysql> select * from testdate;
+------------+
| birthday   |
+------------+
| 1980-01-15 |
| 1989-10-22 |
| 1979-11-09 |
+------------+

CAVEAT - make a copy of the table first, just in case.

[PNewCode]

@Barand and @kicken Thank you much. For this one, I don't think it would be wise for me to convert values in the database because it's not mine. I'm only making a Birthday listing from it. If something went wrong, I wouldn't be able to change it back, or have access to import the database again. All I can do with this project is make a display page so I think I better not convert any data in it.

I am looking into the LIKE thing now. Thank you for that. I think this will take me some time to figure out how to make it work specifically for what I need but I appreciate the direction. Thank you very much

[Barand]

Even if don't permanently change the formats, you can still use str_to_date() to make non-standard date formats usable by MySql date/time functions and in date comparison queries.

EG

SELECT birthday 
     , date_format(str_to_date(birthday, '%m/%d/%Y'), '%W %D %M %Y') as formatted 
FROM testdate;

+------------+---------------------------+
| birthday   | formatted                 |
+------------+---------------------------+
| 01/15/1980 | Tuesday 15th January 1980 |
| 10/22/1989 | Sunday 22nd October 1989  |
| 11/09/1979 | Friday 9th November 1979  |
+------------+---------------------------+

 

[PNewCode]

@Barand I see what you mean. Thats great for displaying it in a different format, but I'm not understanding how I can use that to display only the birthdays that are on the day that the page is viewed?

[Barand]

There are dozens of date functions you can use once you have a date in the correct format. That was just one example. Another couple...

SELECT birthday
FROM testdate
WHERE MONTH(str_to_date(birthday, '%m/%d/%Y')) = 10
  AND DAYOFMONTH(str_to_date(birthday, '%m/%d/%Y')) = 22;
  
+------------+
| birthday   |
+------------+
| 10/22/1989 |
+------------+

or, if you want current day's birthdays

SELECT birthday
FROM testdate
WHERE MONTH(str_to_date(birthday, '%m/%d/%Y')) = MONTH(CURDATE())
  AND DAYOFMONTH(str_to_date(birthday, '%m/%d/%Y')) = DAYOFMONTH(CURDATE());

 

[PNewCode]

@Barand thank you. I will test these out to see if I can get them to work when I get back to the computer in the morning. I appreciate it. From what I'm understanding, since my database is called "retirees" then I would have it like the following, and I choose the * so I can also include their name and favorite birthday cake that is listed.

SELECT * FROM retirees WHERE MONTH(str_to_date(birthday, '%m/%d/%Y')) = MONTH(CURDATE()) AND DAYOFMONTH(str_to_date(birthday, '%m/%d/%Y')) = DAYOFMONTH(CURDATE());

and this will look for the birthdays that are on the current date at the time of looking at the webpage, because the string format is being converted and then it's looking for the current date and month, instead of the year too. Correct?

[Barand]

Yes, correct.

Although using SELECT * is a bad habit. Select just the columns you need

[PNewCode]

@Barand I see. And yes since I really only need the birthday listing for this I changed it. I am getting "page isn't working" with it now though. I changed up the ' to " just in case (that is normally why I get that) and got the same results. I used the online trouble shooter and got

Parse error: syntax error, unexpected identifier "birthday" in /tmp/preview on line 25
(this line 25 was with the db connection stuff typed in so it wont match a line 25 below but it's in the sql=select line)

The column name that the birthdays are listed in is named birthday, so I'm not seeing where the problem is. It would be helpful if the error reporting also told why the page "isn't working". I don't have access to the server logs for this one.
Any thoughts?

<?Php

    error_reporting(E_ALL);
    ini_set('display_errors', '1');

//// db connection stuff here, removed for posting ////

$conn = new mysqli($hostname, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}


$sql = SELECT birthday FROM retirees WHERE MONTH(str_to_date(birthday, "%m/%d/%Y")) = MONTH(CURDATE()) AND DAYOFMONTH(str_to_date(birthday, "%m/%d/%Y")) = DAYOFMONTH(CURDATE());


	if (!$result = $conn->query($sql)) {
    	die ('There was an error running query[' . $conn->error . ']');
	}	
?>

 

Edited March 18, 2023 by PNewCode

[Barand]

9 minutes ago, PNewCode said:

It would be helpful if the error reporting also told why the page "isn't working".

It will if you tell it to.

Put this line

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

just before your line calling $conn = new mysqli(...)

[PNewCode]

5 minutes ago, Barand said:

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

Still getting this...

This page isn’t working

... is currently unable to handle this request.

HTTP ERROR 500

Edited March 18, 2023 by PNewCode

[Barand]

35 minutes ago, PNewCode said:

$sql = SELECT birthday FROM retirees WHERE MONTH(str_to_date(birthday, "%m/%d/%Y")) = MONTH(CURDATE()) AND DAYOFMONTH(str_to_date(birthday, "%m/%d/%Y")) = DAYOFMONTH(CURDATE());

$sql needs to be a string value.

Put the query string inside single quotes.

$sql = 'SELECT birthday FROM retirees WHERE MONTH(str_to_date(birthday, "%m/%d/%Y")) = MONTH(CURDATE()) AND DAYOFMONTH(str_to_date(birthday, "%m/%d/%Y")) = DAYOFMONTH(CURDATE())';


	

 

[PNewCode]

@Barand I also just saw something about a date fetch and thought I'd give it a try. I can't believe that this worked. I just made it to work with my page. But I would very much appreciate some education on WHY this is working? I like to learn instead of just copy and paste stuff.

$datefetch = date("m/d/Y");
$sql = "SELECT * FROM `retirees` WHERE `birthday` = '$datefetch'";

[Barand]

1 hour ago, PNewCode said:

I can't believe that this worked.

I can't either. How many retirees do have in your table who were born today (ie 0 years old)

[PNewCode]

@Barand There's over 1500 people in there but none 0 years old lol
This is just to pull the ones that have a birthday of today (month and day) the year isn't really relevant so maybe thats why it works?

 

Edited March 18, 2023 by PNewCode

[ginerjm]

You're missing Barand's point.  You are showing us code that uses today's date to select birthdays from your database.  That code will not select anything at all unless you are running something different than what you posted here.

[PNewCode]

@ginerjm the part that echo's the results aren't included in this because there wasn't any problem with it. The problem I was having was only in the selecting. Filtering the results. The stuff that is printed to the page is extremely long that shows a bunch of birthday cakes pictures and their name and profile pictures and favorite hobbies and stuff like that. I didn't see it being relevant to post that since I was only asking to help filter the results by their birthday. If I posted all that other stuff then there would have been a whole bunch of coding that would have not been the point of the help at all. Hope that explains why I didn't put a bunch of clutter in my post

[ginerjm]

The two lines that you showed us would NOT do the query you have been asking for help with.  If you are changing topics then it's time to begin a new post.  Your query as posted 2 hours ago will only select those records having a (supposed) date that matches '03/18/2023' which is a totally improperly formed date stored in a db table.  I realize that you began this post worrying about just such a thing but to show this code now that the problem has been covered and (supposedly) resolved and say that "it works" is not true.  The code does not match 'only the month and day' but matches ONLY the exact record value that includes the month, day and year as well as 2 slashes.

[PNewCode]

@ginerjm I'm not changing any topics. You said I'm not showing something so I explained why I didn't post the whole page. The code works. The year is irrelevant. I"m sorry if you don't believe it but that is no consequence to me. The code works. I don't know why it works, but for what I need it to do... it works. Thats all I know to explain.
Remember, the idea of this was to show who has a birthday. It doesn't matter what year they are born. As long as it lists those whos month and day are that day of viewing, thats all that matters.

Edited March 19, 2023 by PNewCode

[PNewCode]

@ginerjm and @Barand So I owe an apology but trust I didn't know. I found this infuriating so I reached out to the owner and he said that on Jan 1st every year, the birth year is auto reset in the database to the current year so that none of retirees show their actual age, and this was done after too many hard feelings were made. SO... that could be why this works. Because they are all in fact 0 years old as the system thinks (note this is not an administrative database, it's just a web page for the residents to have fun with).

So I'm sorry. You were correct. And yet, as I could see... it still works for what it's intended to do because of that lol.
So now I'm curious though (not that I can see where I'll use it) but why would it not grab the year as well even if it was a different year? Just asking for my own education.

[Barand]

That being the case, all you need is

$sql = "SELECT .... FROM retirees WHERE CURDATE() = STR_TO_DATE(birthday, '%m/%d/%Y')";

 

16 minutes ago, PNewCode said:

on Jan 1st every year, the birth year is auto reset in the database to the current year

Thank you for keeping that fact hidden (the sample data values you gave us did not reflect the data in the table) and wasting our time.

[PNewCode]

@Barand Thank you for the reply. I didn't know of that fact till just moments ago when I made the reply. He just told me the format example that I put in the post. I promise I wasn't aware of the dates being reset. I don't have permission to look at all of the information as it actually is in the database unfortunately so it made it harder to do.
I could only post with the information I was given. Sorry about that.

Edited March 19, 2023 by PNewCode

[Barand]

2 minutes ago, PNewCode said:

I don't have permission to look at all of the information as it actually is in the database

Then how are managing to query it? How do you check if your query has selected the correct subset?

Perhaps this might have been an idea...

SELECT * FROM retirees ORDER BY birthday;

 

[PNewCode]

@Barand only way I can with this one is to try different things till something works since I'm limited. I'm just told the table is "retirees" the column is "birthday" and it is formatted in varchar as day/month/year (01-01-1960) but he didn't tell me the year was reset to current year to just a little bit ago.

Thank you for the idea, but what I have works now. If I'm not mistaken, you're idea you just posted would show everyone, but in the order of whos birthday is first to last. He wanted the page to only show those that had the birthday on the day you visit the page. There's animation that has a birthday cake and flickering candles and a song that plays "happy birthday" and in the middle it shows the persons face, then under it their hobbies, and crafts and stuff like that. But only shows this for the people that had a birthday on the day of viewing the site.

I fully admit, this was a challenge and a half when I can't see the actual entries and database. I COULD technically run a list of everything but that would be going against the promise I made not to meddle

I would be interested in knowing how to alter what I have to show for any year though instead of just the current year, just in case I ever want to use this work for my own website

Edited March 19, 2023 by PNewCode