i think i want to use MySQL UNIX_TIMSTAMP() to format date

ATS16805

I know this is a common inquiry, but i just can't seem to get past where i'm stuck w/ this...

Obviously, I am dealing w/ displaying a date field from a MySQL table in my PHP output. because my mind is a bit mushy from being fixated on this problem for so long, i decided to go to the command line rather than continue trial and error by way of modifying my PHP query (which is what i should have done in the first place, i know).

i did some research here, and i see that i can use UNIX_TIMSTAMP() to turn the MySQL date field into something that PHP can use. during my investigation, at the command line, i entered the following:

select unix_timestamp(myTable-date-field) from myTable

which produced the following numerical data for my two rows containing real date data:
1124942400
1118116800
(the other rows' output 0, as there are no valid dates there)

so, i assume my syntax for unix_timestamp() is correct. i looked at http://us3.php.net/date but i'm confused w/ how to write the syntax for the PHP date() function for use w/ this unix_timestap() data. i'm not even sure i should use date()... maybe i should use strtotime(), or mktime? remember i'm a beginner here, so i really am at a loss for this challenge. i've named my date field "showtime" because i'm trying to develop a calendar app for a local musician. here's what i'm trying.
note: this is a separate test script from my larger script. just trying to play w/ this date formatting function.

<?php
$conn = mysql_connect("localhost", "myUser", "myPass");
mysql_select_db("db1", $conn);
$timestp = "SELECT unix_timestamp(showtime) FROM myTable";
$tabledata = mysql_query($timestp, $conn);
while ($tableArray=mysql_fetch_array($tabledata)) {
	$format = date(M-d-Y, $tableArray);

echo "$format<br />";
}
?>

i thought this would work, but all i get is a list of zeros representing each row.

what am i doing wrong, and/ or how do i fix this? the rest of my insert query is working properly. i'd like to move on from this so i can start to try the real challenge which is to figure out how i'm going to organize the output into some kind of aesthetically pleasing format.

(if you have any suggestions on how i should approach the format of my larger output, i'd very much appreciate a link to a resource, or even your own advice... i realize i need to concentrate on one challenge at a time of course)

thank you!

Drakla

$timestp = "SELECT unix_timestamp(showtime) as utime FROM myTable";
$tabledata = mysql_query($timestp, $conn);

while ($tableArray = mysql_fetch_assoc($tabledata))
{
	$format = date('M-d-Y', $tableArray['utime']);

    echo "$format<br />";
}

ATS16805

thank you! where should i look to find out more about this utime? i assume it's a MySQL (or SQL?) function since it's part of the SELECT statement-- should i look at php.net as well? any article in particular?

what do you recommend i research to further my understanding of what i'm trying to do w/ this date format?

thanks so much!

edit: and this is the first i've encountered mysql_fetch_assoc
i assume that means "associative array"? any recommendations on that?

edit2:
since this is going to be an array loop, how do i plug it in so that i can still use my other mysql_fetch_array loop? what is the proper syntax/ or approach when using two loops like this?

(sorry for all the questions at once! i'm just trying to learn..)

Drakla

mysql_fetch_assoc does return an associative array. fetch_array returns an array with both associative and numeric keys - most of the time a complete waste. utime isn't a function, what I've actually done there is rename the field to utime to make it easier to reference in the associative array. Look at the SELECT syntax in the mysql manual for more on that.

I don't see two loops in your code, but you can select as many fields as you like in the select. If you're running a second query for each row of data brought back then you've got to use a different result handle to reference it.

That's all for today.

ATS16805

regarding the two loops, yes, i did leave out some code here to spare everyone the b/s.
but, the good news is, i figured out how to do what i want w/ this bit! i'm going to have to go back and study how my house of cards isn't falling over currently, but i'm glad i'm on the right track now. thank you for the help.

edit. i don't want to mark this resolved until i can report w/ confidence how i got it to work so that others may learn from this.

edit2:
okay, here's the entire bit of code. if anyone cares to spend some time w/ it, maybe it will help someone to figure out some simple date query formatting, as i have, along w/ other outputting other queries as well. i would like to hear report as to whether i've worked this out properly, or if this is working as a one-off. i hope it's proper so no one else would mistakenly borrow erroneous code. i hope that additional commentary here might prove to illustrate something useful.

oh, and by the way, my little

n++

is just an experiment-- i was trying to figure out different ways of incrementing the output "row number"... one by way of the MySQL ID field, and one by way of the incrementing variable.

<?php
global $conn;
$conn = mysql_connect("localhost", "myUser", "myPass");
mysql_select_db("db1", $conn);
$show = "SELECT * FROM myTable";
echo "<table class=\"html\">";
$tabledata = mysql_query($show, $conn);
$timestp = "SELECT unix_timestamp(showtime) as utime FROM myTable";
$tymdata = mysql_query($timestp, $conn);
global $num;
$num = (1);
while ($tableArray=mysql_fetch_array($tabledata))  {
	$id = $tableArray[id];
	$venue = $tableArray[venue];
	$details = $tableArray[details];
	$link = $tableArray[link];
	$email = $tableArray[email];
	$showtime = $tymdata[showtime];
	$tymArray = mysql_fetch_assoc($tymdata);
	$format = date('M-d-Y', $tymArray['utime']);
echo "<th>Entry $num or $id</th><tr><td>";
echo "Venue: <span class=\"dynamic\">$venue</span></td>";
echo "<td>Details entered: <span class=\"dynamic\">$details</span></td>";
echo "<td>Relevant Link: <span class=\"dynamic\"><a href=\"$link\" target=\"_blank\">go to site</a></td>";
echo "<td>Relevant email: <span class=\"dynamic\"><a href=\"mailto:$email\">send message</a></td>";
echo "<td>Day of Show: <span class=\"dynamic\">$format</span></td>";
echo "</tr>";
$num++;
}
echo "</table>";
?>
<html>
<p class="html">Click <a href="adddates.htm">here</a> to add more entries</p>
<p class="html">Click <a href="index.htm">here</a> to go back to main page</p>
</html>

cheers!