PDA

View Full Version : Help sorting MySQL results query by date


sydesign
10-30-2013, 03:34 AM
I am trying to figure out a way of sorting out results of my database by events past.

I have an events page and wish to display the next upcoming event, only showing one at a time and once the event is past i want the next event to show up there. I would like to keep the events in the database but only wish to show the next upcoming date in the future here.

Here is the code i have right now sorting the database by the earliest date:


//Retrieves data from MySQL
$data = mysql_query("SELECT * FROM events WHERE active='yes' ORDER BY year ASC, month ASC, day ASC LIMIT 1") or die(mysql_error());

//Puts it into an array
while($info = mysql_fetch_array( $data ))
{

$month = $info['month'];
$month2 = substr($month, 3);

$day = $info['day'];
$year = $info['year'];
$venue = $info['venue'];
$title = $info['title'];
$city = $info['city'];
$state = $info['state'];
$hour = $info['hour'];
$minutes = $info['minutes'];
$band = $info['band'];

$ticketlink = $info['ticketlink'];

if ($ticketlink != ""){
$ticketlink = '<br /><span style="line-height:20px;text-transform:uppercase;" class="tickets">
<a href="' .$ticketlink. '" target="_blank" style="text-decoration:none;">Get your Tickets Now!</a></span>';
} else {
$ticketlink = "<br /><a href='events/'>Tickets available soon!</a>";
}
}


A big thanks in advance for any help! Thank you!

edbr
10-30-2013, 08:34 AM
you will need to compare date of the event date and date on the server.
this will need a function to formulate the date with ethe form you are using in our database

function compareDate ($i_sFirstDate, $i_sSecondDate)
{
$arrFirstDate = explode ("/", $i_sFirstDate);
$arrSecondDate = explode ("/", $i_sSecondDate);

$intFirstMonth = $arrFirstDate[0];
$intFirstDay = $arrFirstDate[1];
$intFirstYear = $arrFirstDate[2];

$intSecondMonth = $arrSecondDate[0];
$intSecondDay = $arrSecondDate[1];
$intSecondYear = $arrSecondDate[2];

$intDate1 = mktime(12, 0, 0, intval($intFirstMonth), intval($intFirstDay), intval($intFirstYear));
$intDate2 = mktime(12, 0, 0, intval($intSecondMonth), intval($intSecondDay), intval($intSecondYear));

return $a = round(abs($intDate2 - $intDate1) / 86400);
}



then you can use an if condition to compare the dates

sydesign
10-30-2013, 06:55 PM
thank you! Though, I'm not too familiar with javascript or the code you have above and am not sure how to implement it. Would you be able to explain and show me how i would use that function?

edbr
10-31-2013, 01:40 AM
its PHP.
the function compares the difference between 2 dates
so an example of calling it might be


$datevent=$row['datevent']; ?/ clearlt edited to suit your database, and assuming the vent daye is a timestamp/datetime

$datevent = date("m/d/y",strtotime($datevent)); //formats the date


// The following compares the database event date with the date on the server ( using date() )

if(compareDate( $datevent,date("m/d/y",time()))< ){


// echo your stuff
}