[RESOLVED] select from table where date[yyyy-mm-dd]

[ATS16805]

i have date data stored in a MySQL table in this formate yyyy-mm-dd, put there via $_POST['string'] where string="2005-08-23" for example.

when i use mysql_query() to retreive that data, i ultimately want to use PHP to make it read as Tuesday, August 23rd, 2005, for example.

how should i feed the retrieved date info into which function to properly achieve these results?

i don't know if it matters, but ultimately, i'll be adding a list of dates, each of which i'd like to either output as above, or at least as "american" date format, 08-23-05, for example.

i have been trying to manipulate this date('l dS \of F Y h:i A') to get what i want, but i get the feeling that is the wrong PHP function for this application. i did research at php.net, but i'm a bit confused by the many different options regarding date and date time functions. i appreciate your understanding!

thanks!

[dalecosp]

I just love [man]strtotime[/man] for stuff like this. I think it could handle the conversion pretty well.

[dalecosp]

I meant to throw this into the mix....

<?php
$dt="2005-08-23";

echo date("l, F dS, Y",strtotime($dt));

?>

[toplay]

If your column in your table is of a date or data/time data type, then you can retrieve the date directly from MySQL in the format you want. Always, store dates and times in some form of MySQL Date and time Types.

To retrieve a date in the format you want, do something like this:

SELECT DATE_FORMAT(date_column, '%W, %M %D, %Y') AS formatted_date
FROM table_name

See:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#id3199229

🙂

[ATS16805]

the table i'm querying the data from is the concerts table you can see in the screen-shot there-- stored as MySQL DATE. this particular function is for a

$seldates1 = "SELECT * from concerts where artist_id1 =$_GET[var]";

w/ two more SELECT queries for artist_id2 and artist_id3. the idea here being that i will be able to complile all of a single artist's dates for a sort of comprehensive display on one page

i'm sorry if this is irrelevant, but just to show you how i'm doing it now, green as i am (btw, i'm not sure if i do, or why i need those global declarations-- but it seems to get rid of the Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource. i'd like to research that a bit more as well-- to know better how to handle the issue in the future! 😉

function getdates() {
	global $datesres;
	global $conn;
	$seldates = "SELECT * from concerts where artist_id1 =$_GET[var]";
	$datesres = mysql_query($seldates, $conn) or die(mysql_error());
	return $datesres;
	}

function getdates2() {
	global $datesres2;
	global $conn;
	$seldates2 = "SELECT * from concerts where artist_id2 =$_GET[var]";
	$datesres2 = mysql_query($seldates2, $conn) or die(mysql_error());
	return $datesres2;
	}

function getdates3() {
	global $datesres3;
	global $conn;
	$seldates3 = "SELECT * from concerts where artist_id3 =$_GET[var]";
	$datesres3 = mysql_query($seldates3, $conn) or die(mysql_error());
	return $datesres3;
	}

$datelist1 = getdates();
$datelist2 = getdates2();
$datelist3 = getdates3();	

while ($datesArray1 = mysql_fetch_array($datelist1)) {
$datesoutput = "$datesoutput ".$datesArray1['show_date']."<br>";

}

while ($datesArray2 = mysql_fetch_array($datelist2)) {
$datesoutput2 = "$datesoutput2 ".$datesArray2['show_date']."<br>";

}

while ($datesArray3 = mysql_fetch_array($datelist3)) {
$datesoutput3 = "$datesoutput3 ".$datesArray3['show_date']."<br>";

}

echo "$datesoutput<br>";
echo "$datesoutput2<br>";
echo "$datesoutput3<br>";

i'm sure i've wasted some space w/ it, but at least, at this point, it is giving me what i want, albeit, not without a few

Notice: Undefined variable: datesoutput

in there in its current state!
so, my plan is to take those results, and instead of echoing them raw as they are now, to process, for example, the output

2008-12-20

2006-02-20
2001-06-25

1999-12-31
2007-05-15

into a more human-friendly format.
THANK YOU very much for your input here. i haven't had the chance yet to try anything as i've just now got back to the project, but i'll come back and let you know what i came up with. if seeing this more detailed info turns on any light-bulbs on your end, i'd be most grateful if you'd share your wisdom! 😉

EDIT:
i'm trying to get this to work

<?php
$dt="2005-08-23";
echo date("l, F dS, Y",strtotime($dt));
?> 

i think it's just what i need!!
🙂 yay!
except i'm having difficulty figuring out how to extract the dates now that i've setup these arrays

EDIT2:
okay, i learned something so far...
i can eliminate the "Notice: undefined variable..." by declaring those variables as globals at the begining of the script-- resulting in NO errors-- except that i still haven't figured out how to get this strtotime() to work w/ my MySQL DATE format-- i keep getting this error:
Windows does not support dates prior to midnight (00:00:00), January 1, 1970
what can i do to tell PHP how to handle the MySQL date so i don't get that error (assuming that's what's going on)?

thanks again!!!

[ATS16805]

well, i figured out one thing i need to do is change the MySQL query to pass a UNIX_TIMESTAMP as in:
"SELECT UNIX_TIMESTAMP(show_date) from concerts where artist_id3 =$_GET[var]"
but, i can't figure out what to do w/ it once i get it into my PHP script. i'm trying this:

$phpdate = date("1, F ds, Y",$datesArray1);

but i keep getting a result of

1, December 3101, 19691, December 3101, 1969

!?
obviously, i'm sending the wrong arguments to the date(); function-- either that, or i'm supposed to be using a different PHP function to get this result.

i'll keep trying .

[Weedpacket]

I reckon toplay's suggestion is better; make the date formatting part of the query. Smart data and dumb code beats dumb data and smart code any day.

Also:

ATS16805 wrote:

by declaring those variables as globals at the begining of the script

is the wrong solution. Declaring variables global is so that variables declared outside functions can be used inside functions, not for defining variables that don't exist yet. You're getting the error because first time through the loop

 while ($datesArray1 = mysql_fetch_array($datelist1)) {
    $datesoutput = "$datesoutput ".$datesArray1['show_date']."<br>";
     

$datesoutput doesn't exist yet - not until you've already tried to use its value as part of a string. Create the variable explicitly:

$datesoutput = '';
while ($datesArray1 = mysql_fetch_array($datelist1)) {
    $datesoutput = $datesoutput." ".$datesArray1['show_date']."<br>";
     

[ATS16805]

Weedpacket wrote:

Create the variable explicitly:

$datesoutput = '';
     

ahhhh. now THAT is a great little bit to know!

VERY AWESOME about the dates!!!! THANK YOU, toplay!!! (and thank you, WeedPacket, for "knocking me up-side the head"!!)

I can't believe i didn't go back and try toplay's suggestion!! lesson learned.
see,-- i thought i was using, as you call it, "smart data" when i started pulling the dates out as UNIX_TIMESTAMP-- once i went in that direction, i kind of forgot to revisit toplay's suggestion... as i thought for sure i would discover a solution through trial and error and a lot of php.net/manual/ reference-- oh well, at least i learned all about

$phpdate = unixtojd($datesArray1);
		$dategc = cal_from_jd($phpdate, CAL_GREGORIAN);

not to mention many other things i studied over the past few hours. even though i didn't achieve success yet, i sure did read a lot of stuff i hadn't hit yet, and also the peripheral learning (eg. "..create the variable explicity...") is "priceless".

when i set out to build this app, someone suggested (not that i was a bad suggestion, of course) that i use a CMS instead, but i really wanted to face this challenge. my confidence, and genuine wherewithal of PHP and MySQL has improved exponentially over the past several days. i owe MUCH of that to this forum, by the way.

thanks so much, members of PHPBuilder.com who have helped me with this event scheduler! it's far from finished, but i believe i have probably already conquered its most difficult challenges..

POST-RESOLVE EDIT:
hey, one last quick question... how do i get rid of this:

Notice: Use of undefined constant formatted_date - assumed 'formatted_date'

?? i tried $formatted_date = ''; which i thought is what you were saying, WeedPacket about, instead of declaring a global outside of a function (which i knew was the definition, btw, but it seemed to "work", so i went w/ it). is this different because i'm calling this array $datesArray1[formatted_date], so the conditions are different, and i must do what diferently now?

[Weedpacket]

Ah, it's just a matter of remembering that MySQL (and Apache) have manuals, too.....🙂

Notice: Use of undefined constant formatted_date - assumed 'formatted_date'

Bad habit, not quoting your strings. You're telling it to use a [man]defined[/man] constant named formatted_date - but there is no such constant - so it whinges and guesses you meant to say 'formatted_date'. See http://www.php.net/manual/en/language.types.array.php#language.types.array.foo-bar

[ATS16805]

just "for the record", i do typically use defined constants, i think.

in this particular situation, having not often used the SQL: "SELECT ... AS... " syntax, and having copy/ pasted toplay's suggestion verbatim, at first i wasn't sure at all what was going to happen to the data, and what was being assinged to what, etc. then, i realized that 'formatted_date' essentially became the new name for something else, then i began to realize what i needed to do to achieve a functionality w/out error or notices. and again, thanks to the help here where such oversights are so often recognized and illustrated, all for the better learning.