Weird problem with agenda

[t.bo]

Hi all,

I've made an agenda that orders by date of the event. Almost every event is ordered correctly but now some events that are added later are not displayed correctly by date...

you can see the problem at [url=http://www.djgrammy.be/index2.php?content=agenda]http://www.djgrammy.be/index2.php?content=agenda[/url] and [url=http://www.djgrammy.be/index2.php?content=agendajaar]http://www.djgrammy.be/index2.php?content=agendajaar[/url]

And of course the code [code]<?php
include('dbconnect.php');
$currentmonth = date("m");
$currentyear = date("Y");

$sql = mysql_query("select * from agenda order by date") or die(mysql_error());
$month = date("F");
$year = date("Y");
echo "<tr><td><h1>$month $year</h1></td></tr>";
while($row = mysql_fetch_array($sql))
{
$event = stripslashes($row["eventfield"]);
$id = $row["idfield"];
$url = $row["urlfield"];
$date = $row["date"];

$val = strtotime($date);
$month2 = date("F", $val);
$datecorrection = strtotime($date);
$year = date("Y", $datecorrection);
$day = date("l", $datecorrection);
$numberday = date("dS", $datecorrection);
$month3 = date("m", strtotime($date));

if($currentyear == $year) {

// has category changed ?
// if so, print it
if($currentmonth == $month3) {


echo  "<tr><td><b><a href=\"http://$url\">$event</a></b></td>";
echo  "<td>$day the $numberday<td></tr>";
}
}
}
?>[/code]

Really hope that someone can help. I wonder if changing the field in the DB for the date will help?

Thanks in advance...

[t.bo]

Maybe this helps also...
The MySQL structure and inputted data :
[code]-- phpMyAdmin SQL Dump

--

CREATE TABLE `agenda` (
  `idfield` int(11) NOT NULL auto_increment,
  `eventfield` varchar(250) NOT NULL default '',
  `urlfield` varchar(250) NOT NULL default '',
  `monthfield` varchar(250) NOT NULL default '',
  `date` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`idfield`)
) ENGINE=MyISAM AUTO_INCREMENT=81 DEFAULT CHARSET=latin1 AUTO_INCREMENT=81 ;

--
--
--

INSERT INTO `agenda` VALUES (24, 'Resident  Club Outline', 'www.cluboutline.com', '', '2006-07-01');
INSERT INTO `agenda` VALUES (25, 'Resident  Club Outline', 'www.cluboutline.com', '', '2006-07-08');
INSERT INTO `agenda` VALUES (26, 'Resident  Club Outline', 'www.cluboutline.com', '', '2006-07-15');
INSERT INTO `agenda` VALUES (27, 'Resident  Club Outline', 'www.cluboutline.com', '', '2006-07-22');
INSERT INTO `agenda` VALUES (28, 'Resident  Club Outline', 'www.cluboutline.com', '', '2006-07-29');
INSERT INTO `agenda` VALUES (29, 'Cirque Cental  22u-2u', 'www.cirquecentral.be', '', '2006-07-07');
INSERT INTO `agenda` VALUES (30, 'Bar a Bar    3u-4u', 'www.barabar.be', '', '2006-07-07');
INSERT INTO `agenda` VALUES (31, 'La Rocca  Papaya  1.30u-3.30u', 'www.larocca.be', '', '2006-07-08');
INSERT INTO `agenda` VALUES (32, 'Groovy Tunes', 'www.groovy-tunes.be', '', '2006-07-14');
INSERT INTO `agenda` VALUES (33, 'Resident Club Industria \\"Closing Party\\"', 'www.clubindustria.be', '', '2006-07-20');
INSERT INTO `agenda` VALUES (34, 'La Gazz    \\"Dusart on fire\\"', 'www.lagazz.com', '', '2006-07-21');
INSERT INTO `agenda` VALUES (35, 'La Gazz  @  Beerschot', 'www.lagazz.com', '', '2006-07-22');
INSERT INTO `agenda` VALUES (36, 'La Gazz  @  The Century', 'www.lagazz.com', '', '2006-07-23');
INSERT INTO `agenda` VALUES (37, 'Summer Party  (Mielen Boven Aalst)', 'www.summerparty.be', '', '2006-07-29');
INSERT INTO `agenda` VALUES (38, 'Groovy Tunes', 'www.groovy-tunes.be', '', '2006-08-04');
INSERT INTO `agenda` VALUES (39, 'Groovalicious  (Hoeselt, Ter Komme)', 'www.groovalicious.be', '', '2006-08-05');
INSERT INTO `agenda` VALUES (40, 'Block Party  (Hosted by Cirque Central)', 'www.cirquecentral.be', '', '2006-08-11');
INSERT INTO `agenda` VALUES (41, 'Private Pool Party', '', '', '2006-08-11');
INSERT INTO `agenda` VALUES (42, 'Dance Parade ( Rotterdam NL)', '', '', '2006-08-12');
INSERT INTO `agenda` VALUES (43, 'Mac Billy\\''s  24u-1.30u', '', '', '2006-08-14');
INSERT INTO `agenda` VALUES (44, 'White Party  ( Herk de Stad)', 'www.indegloria.be', '', '2006-08-14');
INSERT INTO `agenda` VALUES (45, 'Bar a Bar meets Club Industria', 'www.barabar.be', '', '2006-08-19');
INSERT INTO `agenda` VALUES (46, 'La Gazz  Parkingconcerten Tielt', 'www.lagazz.com', '', '2006-08-24');
INSERT INTO `agenda` VALUES (47, 'Fabulous  (Genk)  21u-2u', '', '', '2006-08-26');
INSERT INTO `agenda` VALUES (48, 'Kim\\''s B-Day @ Bar a Bar  5u-7u', 'www.barabar.be', '', '2006-08-26');
INSERT INTO `agenda` VALUES (49, 'Resident Club Outline  8u-10u', 'www.cluboutline.com', '', '2006-08-26');
INSERT INTO `agenda` VALUES (50, 'Laundry Day', 'www.laundryday.be', '', '2006-09-02');
INSERT INTO `agenda` VALUES (51, 'Resident  Club Outline  5u-8u', 'www.cluboutline.com', '', '2006-08-05');
INSERT INTO `agenda` VALUES (52, 'Resident  Club Outline  5u-8u', 'www.cluboutline.com', '', '2006-08-12');
INSERT INTO `agenda` VALUES (53, 'Resident  Club Outline  5u-8u', 'www.cluboutline.com', '', '2006-08-19');
INSERT INTO `agenda` VALUES (54, 'Alden Biezen (Bilzen)  La Gazz', '', '', '2006-09-02');
INSERT INTO `agenda` VALUES (55, 'Laundry Night @ BONTHYS', 'www.bonthys.be', '', '2006-09-02');
INSERT INTO `agenda` VALUES (56, 'British American Tobacco (Nijvel)  La Gazz', 'www.lagazz.com', '', '2006-09-04');
INSERT INTO `agenda` VALUES (74, 'Kafka  Groovy Night  (Sint-truiden)    24u-2u', 'url', '', ' 2006-09-29');
INSERT INTO `agenda` VALUES (59, 'Levenslijn    Kattendijkdok Antwerpen  La Gazz  21u-22u', 'www.lagazz.com', '', '2006-09-08');
INSERT INTO `agenda` VALUES (60, 'Cirque Central  Dj Grammy ft Shitfiltr  23u-...', 'www.cirquecentral.be', '', '2006-09-08');
INSERT INTO `agenda` VALUES (61, 'Always Events  (Dendermonde)  La Gazz 19u-22.30u', 'www.lagazz.com', '', '2006-09-09');
INSERT INTO `agenda` VALUES (62, 'ROGER SANCHEZ @ Bonthys (Eilandje Antwerpen)', 'www.bonthys.be', '', '2006-09-09');
INSERT INTO `agenda` VALUES (63, 'GRAND OPENING  \\"BONTHYS\\"    ?u', 'www.bonthys.be', '', '2006-09-16');
INSERT INTO `agenda` VALUES (64, 'Club Outline  (Diest)  6u-8u', 'www.cluboutline.com', '', '2006-09-16');
INSERT INTO `agenda` VALUES (65, 'Groovy Tunes  Dj Grammy ft Sax, Percussie (La Gazz)  Hasselt  1u-3u', 'www.groovy-tunes.be', '', '2006-09-22');
INSERT INTO `agenda` VALUES (66, 'Bonthys  (Eilandje Antwerpen)  ?u', 'www.botnhys.be', '', '2006-09-23');
INSERT INTO `agenda` VALUES (67, 'Club Outline  (Diest)  6u-8u', 'www.cluboutline.com', '', '2006-09-23');
INSERT INTO `agenda` VALUES (72, 'Feestbureau    (Neerlanden)  La Gazz', 'www.lagazz.com', '', '2006-09-29');
INSERT INTO `agenda` VALUES (73, 'Club Outline    (Diest)  6u-8u', 'www.cluboutline.com', '', '2006-09-30');
INSERT INTO `agenda` VALUES (70, 'Merry Makers  (Zoo van Antwerpen)  La Gazz', 'www.lagazz.com', '', '2006-09-30');
INSERT INTO `agenda` VALUES (71, 'Bonthys  (Eilandje Antwerpen)  ?u', 'www.bonthys.be', '', '2006-09-30');
INSERT INTO `agenda` VALUES (75, 'Kafka  Groovy Night  (Sint-truiden)    24u-2u', 'url', '', ' 2006-10-13');
INSERT INTO `agenda` VALUES (76, 'Kafka  Groovy Night  (Sint-truiden)    24u-2u', 'url', '', ' 2006-11-10');
INSERT INTO `agenda` VALUES (77, 'Kafka  Groovy Night  (Sint-truiden)    24u-2u', 'url', '', ' 2006-12-08');
INSERT INTO `agenda` VALUES (78, 'MONTINI  Grand Opening      5.30u-8u', 'www.montini.cc', '', ' 2006-11-04');
INSERT INTO `agenda` VALUES (79, 'Montini    (Sint-truiden)    1.30u-4.30u', 'www.montini.cc', '', ' 2006-11-11');
INSERT INTO `agenda` VALUES (80, 'Montini    (Sint-truiden)    1.30u-4.30u', 'www.montini.cc', '', ' 2006-12-09');
[/code]

[HuggieBear]

You have random spaces in the field...

Take a look at your first example.. http://www.djgrammy.be/index2.php?content=agenda

Kafka Groovy Night appears at the top instead of three from bottom... If you look at the SQL dump, you'll see the date value for that field is not '2006-09-29' it's actually ' 2006-06-29'. [color=red]<-- Notice the space before the date[/color]

That's what my money's on....  Your database allows this as the field type for 'date' is set to varchar as opposed to date.

Regards
Rich

[joshi_v]

Hi,

Remove the white spaces for the date fields,else my suggestion is convert the date type form varchar to  date field with(10) chars length.Because when you are comparing date wise fields,because of white spaces in date, it is not retrieving properly.

Rest, everything is looking fine.

[HuggieBear]

Sorry, did I not make it clear enough ;)

Rich

[t.bo]

Thanks a lot guys.
I deleted the spaces in the agenda and the problem is solved now.

But how can I solve the problem if the user puts a space in again?

If I convert datefield to int(10) only 2006 was left as the data for date.

thanks in advance

[HuggieBear]

Don't change the [b]date[/b] field to [color=green]int(10)[/color].  Change its type to [color=green]date[/color].

Regards
Rich

[jefkin]

t.bo

There are some hacking ways to suck the spaces out, but I'd suggest fixing the database.  This is to expand on Rich's comment

To convert to using a DATETIME column involves more than just the database, but the database is probably the hardest part.

first add the date column (using a different name)

mysql> ALTER TABLE `agenda` ADD date_dt DATETIME;

next copy the existing date data

mysql> UPDATE `agenda` SET date_dt = date;

you may have to deal with problems here, but that shouldn't be too hard.

now you have the data in a datetime column.  your next step is to remove the old, invalid column.

mysql> ALTER TABLE `agenda` DROP date;

in order to minimize the impact on your scripts, let's change the name of the valid date column to 'date'.

mysql> ATLER TABLE `agenda` CHANGE date_dt date DATETIME;

Now, your table has a real date column.  This might affect your php scripts, but if so, it should be for the better. 

Good luck,

Jeff