Displaying Portion Of Field From Mysql

[mfoland]

I am in need of assistance. I'm learning and advancing in php, although I've come across a problem.

 

I'm working on a project and I want to to show the time of something and then another part to show just the date. In my database I have a field called date_played and it has a format like this:

2012-11-23 23:27:06

 

What I want to do is have php look for the 2012-11-23 for the date and then 23:27:06 for the time and then go even further with the date feature have it echo the time like 11:27 PM ( I think I know that part).

 

Can you please help? Your help would be greatly appreciated!

[trq]

Have you looked at the Date & Time Functions? That's the best place to start.

[mfoland]

I have but I'm not having much luck

[mfoland]

This is in a php page as well.

[Barand]

date function

 

eg

 

<?php
$db_date = "2012-11-23 23:27:06";   // date from DB

echo date("jS F Y", strtotime($db_date)) . '<br />';	   // 23rd November 2012
echo date("d M Y", strtotime($db_date)) . '<br />';	    // 23 Nov 2012

echo date("g:i A", strtotime($db_date)) . '<br />';	    // 11:27 PM

?>

[mfoland]

I'm wanting it to do todays date and eventually maybe dependant on what is typed in. say date = 2012-11-23 or whatever

[mfoland]

And i've tried the date

 

$sql = "SELECT * FROM historylist WHERE `songtype` = 'S' AND `date_played` = 'date(Y-m-d)";

and no go. I had something to where I got only the time and that worked, but I can't get the SQL to get songs by the current date.

[Barand]

date('Y-m-d') is a PHP function. Use

 

$sql = "SELECT * FROM historylist WHERE `songtype` = 'S' AND `date_played` = CURDATE()";

 

Correction - as the dates in DB contain time elements you need

 

$sql = "SELECT * FROM historylist WHERE `songtype` = 'S' AND DATE(date_played) = CURDATE()";

 

The MySQL DATE() function gets just the date part from the date field

Edited November 24, 2012 by Barand

[mfoland]

Thank you so much! Now if I wanted to say history.php?date=2012-11-23 would I replace CURDATE with something? I know I can do curdate -1 if I wanted to eventually..

 

Thanks again!

[Barand]

Given your example

 

$searchDate = date('Y-m-d', $_GET['date']);	 // accepts date=23 nov 2012 and other formats also and ensures correct format

$sql = "SELECT * FROM historylist WHERE `songtype` = 'S' AND DATE(date_played) = '$searchDate' ";

[mfoland]

I tried the search date and I got a blank page

 

Correction -- It won't display the data for the search date.

Edited November 24, 2012 by mfoland

[Barand]

Oops.

should be

$searchDate = date('Y-m-d', strtotime($_GET['date']));

[mfoland]

Woah! Hey thanks for your help!

 

I got another question on pagination.. http://www.lite945.com/iplaylist/playlist.html take a look at the bottom.. See how that is. I'm trying to figure out how to get that. I know how to get the results from the database, for what I'm doing, but pagination is a whole nother thing haha.. Any help is greatly appreciated. Thanks again!

[Barand]

Use a query with a "LIMIT r, n" clause at the end

 

"r" is row number to start from

"n" is the number of rows to retieve

 

If "p" is the current page (starting at 1) then r = (p-1) * n

 

At the bottom (and/or top) of the page you have a list of page number each (except current) have a link to that page (?page=n)

 

I usually show first and last with a couple each side of the current page eg

 

1 ..... 10 11 12 13 14 ...... 20

[mfoland]

Would I be able to echo say 1-20? I do my rows by 20.

[Barand]

That's what I said.

 

// first 20 rows - page 1
$sql = "SELECT * FROM historylist WHERE `songtype` = 'S' AND DATE(date_played) = '$searchDate' LIMIT 0,20";
// page 2
$sql = "SELECT * FROM historylist WHERE `songtype` = 'S' AND DATE(date_played) = '$searchDate' LIMIT 20,20";

//etc

[mfoland]

I was messing around with the date thing and got it going to where I could do -3 days, etc.. However, the problem is I can keep going to 3 more days, and I'm not stoo sure how to do that part. Can I do something like $mindate or something?

[Barand]

I don't understand what you are asking

[mfoland]

http://www.mradio105fm.com/iplaylist/mediabase/history.php

 

On there, I have a drop down that says select date, I can go back to three days, and when I click on previous days, I can continue going back in days, but I want it so after the 3 days they can't go anymore. Like if you keep playing with the drop down, you'll notice what it's doing.

[Barand]

Can't you just check that the date is never less than today - 3 days?

[mfoland]

I don't think I've ever messed with the date function like that.

 

In that drop down I do the date - 1 2 or 3 days so it works to that aspect, but they're able to keep going back 3 days or whatever.

[Barand]

Are you building the -3 day menu from the date that they selected and not always from today's date?

[mfoland]

And back to the pagination on that link on page one that was playlist.html

 

You see numbers like this at the bottom:

 

1-20 21-40 , etc and since it's no net or if net=1 it bolds that.. That's the pagination i'm trying to do. Do I count my artists in my songlist table or how am I doing that? It's new to me

Excerpt of how that pagination on that page is.. If you click the links, you can see how it works!

1-20

21-40 41-60 61-80 81-100 101-120 121-140 141-160 161-180 181-200 201-220 221-240 241-260 261-280 281-293

oh and I believe it's doing the date selected. How should I echo to have it do the today's date? That might be the problem with that!

Edited November 26, 2012 by mfoland

[mfoland]

Pagination is figured out.. Just the date problem needs fixed!

[Christian F.]

strtotime ("-3 days") should help you out there, or the equivalent for MySQL, and then just check if the date given is less than the result. That's all there is to it.