Newbie with stupid question about DATETIME...I know.

[Bminor]

Right, first post here as I have now realised I'm not bright enough to teach myself php & MySql on my own and need a forum to push me in the right direction when i'm bangin 'my head against a brick wall...you lucky people.

Apologies if my questions seem soooo simple it hurts, but i'm only posting because I've exhausted all my options. Please be gentle.

 

My issue is this :

Im following a tutorial: http://www.freewebmasterhelp.com/tutorials/phpmysql/1 - i know its well out dated, but I seem to understand it(which is more than can be said for other tutorials on the web), and I can see a way to create what I need using this tutorial and adding more bits as I learn.

 

I have got my DB set up, got my html forms and php scripts written and I am able to add to the database and view all from the database in rows that I've formatted with html. Its going pretty well so far, except for one thing.

I needed to store the registration date, so in MySql the field is of the DATETIME format and i use CURDATE() to add the date-stamp to the database. All is fine until I want to display the date along-side the rest of the data.

I can get the rest of the data to display properly but I just can't get the registration date to display at all.

I checked in phpMyAdmin and the date is being stored.

 

I believe that i need to do something with the variable containing the registration date($registration_date) - Ive tried many ways to make this work and I just dont understand enough to muddle through yet.

 

I enclose the code I'm using below so you can see how un-secure and dated this tutorial actually is.

Any help or pointers in the right direction would really help me out.

 

NB: I know this is well out of date, un-secure and not the correct or safest way of doing things - Once I know how it all works Ill update the code as I learn, better techniques and best practices.

Dont Laugh :

 

<?php include 'header.php'; ?>
<br />
<?
$username="username";
$password="password";
$database="database";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM listings";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>

<table border="0" cellspacing="0" cellpadding="0">
<tr>
<th width="150" height="20" align="left"><font face="Arial, Helvetica, sans-serif">DATE</font></th>
<th width="150" height="20" align="left"><font face="Arial, Helvetica, sans-serif">NAME</font></th>
<th width="200" height="20" align="left"><font face="Arial, Helvetica, sans-serif">URL</font></th>
<th width="200" height="20" align="left"><font face="Arial, Helvetica, sans-serif">LOCATION</font></th>
<th width="200" height="20" align="left"><font face="Arial, Helvetica, sans-serif">TELEPHONE</font></th>
</tr>

<?
$i=0;
while ($i < $num) {

$registration_date=mysql_result($result,$i,"reg_date");
$name=mysql_result($result,$i,"name");
$url=mysql_result($result,$i,"url");
$country=mysql_result($result,$i,"country");
$county=mysql_result($result,$i,"county");
$city=mysql_result($result,$i,"city");
$phone=mysql_result($result,$i,"phone");
$email=mysql_result($result,$i,"email");
?>



<tr>
<td width="150" align="left"><? echo $registration_date; ?></td>
<td width="150" align="left"><? echo $name; ?></td>
<td width="200" align="left"><a href="<? echo $url; ?>"><? echo $url; ?></a></td>
<td width="200" align="left"><? echo $city.", ".$country; ?></td>
<td width="200" align="left"><? echo $phone; ?></td>
</tr>
<tr>
<td width="150" height="1" bgcolor="#d1d0d0"></td>
<td width="150" height="1" bgcolor="#d1d0d0"></td>
<td width="200" height="1" bgcolor="#d1d0d0"></td>
<td width="200" height="1" bgcolor="#d1d0d0"></td>
<td width="200" height="1" bgcolor="#d1d0d0"></td>
</tr>

<?
$i++;
}
echo "</table>";
?>
<br />
<br />
<?php include 'footer.php'; ?>

 

Thanks to anyone who reads this far, doesn't laugh and puts thier 2 pence worth in.

Nice to be aquainted with you all.

 

B.

[Adam]

Don't worry, everyone's been there.

 

After this line:

 

$registration_date=mysql_result($result,$i,"reg_date");

 

Add:

 

var_dump($registration_date);

 

That will dump the contents of the variable and tell you the datatype, which should help debug the issue.

[EdwinPaul]

And something more to make your life easier:

you don't need to:

a. create your own loop with $i until you processed all the rows in your table.

b. you don't need to declare your own variables to show them.

 

If you use a loop like:

 

<table>
<?php
while ($row = mysql_fetch_array($result)){    // this will put the fields of 1 row in an array named $row
   echo '<tr>';

   echo '<td>'.$row['name'].'</td>'               // repeat this line with the fields you want, accessing them by name

   echo '</tr>';
}
?>
</table>

[Bminor]

Mr Adam, many thanks for the swift reply.

I have inserted the code and uploaded to the server...and I'm almost ashamed to say I don't know what I should do next! Running the page still results in the same issue.

 

With the 'var_dump' command - does this delete all data within the specified variable, or does it push the contents of the variable to somewhere where I can see what data is contained within it, if any or should/can/does it print the contents of the variable to the page somehow.

 

I think this is the bit where you tell me to learn about debugging php before going any further.

 

EdwinPaul - thanks for the pointer, will get my head around it as soon as i overcome this, the first of many issues you'll see my name attached to 

 

Like i said...really new to this...sorry. Gotta be painful for you guys. I keep little plasters for those of you who bleed when you bang your head repeatedly against a wall whilst reading or answering my posts.

They have small monkeys on them.

 

Many thanks,

B.

[EdwinPaul]

var_dump() shows the type and contents of a variable. It does NOT effect the contents.

See http://php.net/manual/en/function.var-dump.php

[Bminor]

EdwinPaul - thanks mate, just what i needed to know.

 

Okey Dokey you cleaver people,

I have managed to use the dump_vars() to get the info stored in it.

The output is below :

 

string(19) "2011-06-28 00:00:00" string(19) "2011-06-28 00:00:00" string(19) "2011-06-28 00:00:00" string(19) "2011-06-28 00:00:00" string(19) "2011-06-29 00:00:00"

 

So it looks to me like its holding 5 strings of 19 characters each, and they correspond to the correct dates that the 5 test records were created(4 yesterday and 1 today). I presume that this is what it is supposed to be holding, so now i'm looking for a way to get them on to the page along with the rest of the row of data, which is displaying fine.

 

I did read about the strtotime() function to get the next bit done and had a crack at it before, but nothing i tried seemed to work. Is this the best way to proceed as I imagine that i will have to transform the string in to a format that looks like a date? Statement Question.

 

Thanks again, I really appreciate your patience.

B.

[PFMaBiSmAd]

If you can var_dump() the value, you can also echo the value.

 

Are you sure you have all the variable names correct in the actual code?

 

What does a 'view source' of the page in your browser show?

[TeNDoLLA]

Just read through this, and only thing I noticed was $num=mysql_numrows($result); should be $num=mysql_num_rows($result); But i guess it is just a typo, since OP gets the results from the database.

 

I have a feeling he just wants to format the date string from database differently.

[PFMaBiSmAd]

mysql_numrows is an old depreciated alias for mysql_num_rows

[TeNDoLLA]

mysql_numrows is an old depreciated alias for mysql_num_rows

 

Ah, allright.

[Bminor]

Ok...well, thats just weird.

 

I removed all html output from the page so i could just see the var_dump() as per my last post, then reading the post from PFMaBiSmAd where they say:

If you can var_dump() the value, you can also echo the value.

Are you sure you have all the variable names correct in the actual code?

What does a 'view source' of the page in your browser show?

 

...i had copied and pasted the html formatting i removed for swift re-entry later on, so pasted it back in to the page and uploaded to find out what the source code looked like as per PFM's suggestion and low and behold, its now showing the date from the database as opposed to nothing(which is what it was doing). Sweet.

 

I dont know what I did, or rather what you guys did but the date is now appearing where it should!

 

PFMaBiSmAd cheers for the pointer.

 

TeNDoLLA - you are now absoultely right -  I now need to format this like' 29 June 2011' rather than the

standard '2011-06-28 00:00:00'  as I dont need the time, just the day, month and year. Any pointers by any and all will be highly appreciated.

 

mysql_numrows is an old depreciated alias for mysql_num_rows

 

With regards to the above quote - can I just change all instances of the depreciated command to the latest one without messing anything up, or are there any other changes I'd need to make to be able to use the newer alias?

 

Seriously...mucho thanks goes out to all of you who helped me today. I really felt like i wasn't getting anywhere and you've reaffirmed my faith in people helping other people and also in the idea that a designer, can create a CMS system with php & MySql - although this is still to be confimed.

 

Awesome, awesome people.

B

 

[TeNDoLLA]

$date = '2011-06-28 00:00:00';
$newDate = date('d M Y', strtotime($date));
echo $newDate;

 

or you can use DateTime class, which is pretty handy in more complext datetime functions

$date = '2011-06-28 00:00:00';
$newDate = new DateTime($date);
echo $newDate->format('d M Y');

 

And yes you can change all the mysql_numrows to mysql_num_rows.

[Bminor]

TeNDoLLA you're a star!

 

Just for my newbie reference, if my variable holding the dates is '$registration_date' would i just substitute the '$date' variable in your example for my variable name?

 

If not, i'm just a little confused as to how my variable gets in to the equation at all.

I know i'm pushing it a little, but this is where i m really stuck - i saw this formatting solution around before when i was hunting and don't really understand it. Can you give me a quick rundown of what the code is doing in english?

 

From what i think i understand i would do this :

 

$registration_date = '2011-06-28 00:00:00';
$newDate = date('d M Y', strtotime($registration_date));
echo $newDate;

 

Right track, or miles away?

Thanks mate.

B

[TeNDoLLA]

Exactly, and things like this you can test by just trying it.

[WebStyles]

lol. Right track!

[Bminor]

Beautiful! Can't believe how much help you've been on here.

Ill make more of an effort to just try stuff before buggin' you guys about it in the future

 

Ill be messing with this for a while now so will post back when i cant spell 'php' or something equally as trivial.

Many thanks again, you've no idea how much you helped me today. Monkey plasters for everyone!

 

Have a great day/evening/morning! - delete as applicable depending on your world location.

Word.

B.

[Bminor]

Just thought id finish off by letting you know i used the code below to get my DATETIME string to format as I wanted.

 

<? $newDate = date('d M Y', strtotime($registration_date)); echo $newDate; ?>

 

I removed the first line of example code posted by TeNDoLLA a few posts back, as this just shows the hard coded date in all records, then inserted this code in the table cell where I wanted the dates to show.

 

All is well...i think.

B

[PFMaBiSmAd]

Mysql has a DATE_FORMAT() function that you can use directly in a query to format a DATE/DATETIME data type when you retrieve it and it's at least 8 times faster than using php code to format the value.

[EdwinPaul]

It can be easier with the built-in function of MySQL:

 

$query='SELECT date_format("%e %M %Y", registration_date) AS formatted_registration_date FROM listings';

 

 

[edit]

this took me too long... PFM was first

[/edit]

[xyph]

Read more here

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format