Jump to content


Photo

Weird problem with agenda


  • Please log in to reply
7 replies to this topic

#1 t.bo

t.bo
  • Members
  • PipPipPip
  • Advanced Member
  • 57 posts

Posted 08 September 2006 - 11:02 AM

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 http://www.djgrammy....?content=agenda and http://www.djgrammy....tent=agendajaar

And of course the 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>";
			}
	}
}
?>

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

Thanks in advance...

#2 t.bo

t.bo
  • Members
  • PipPipPip
  • Advanced Member
  • 57 posts

Posted 08 September 2006 - 11:32 AM

Maybe this helps also...
The MySQL structure and inputted data :
-- 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');


#3 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 08 September 2006 - 12:38 PM

You have random spaces in the field...

Take a look at your first example.. http://www.djgrammy....?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'. <-- Notice the space before the date

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
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#4 joshi_v

joshi_v
  • Members
  • PipPipPip
  • Advanced Member
  • 168 posts

Posted 08 September 2006 - 02:10 PM

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.


#5 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 08 September 2006 - 02:35 PM

Sorry, did I not make it clear enough ;)

Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#6 t.bo

t.bo
  • Members
  • PipPipPip
  • Advanced Member
  • 57 posts

Posted 09 September 2006 - 09:18 AM

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

#7 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 09 September 2006 - 06:03 PM

Don't change the date field to int(10).  Change its type to date.

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#8 jefkin

jefkin
  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 09 September 2006 - 06:13 PM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users