(not solved) between 2 dates

[drisate]

Hey guys, i have a very big hard time retreving the events between 2 dates from a working calander. The database looks like this

 

-- phpMyAdmin SQL Dump
-- version 2.11.6
-- http://www.phpmyadmin.net
--
-- Serveur: localhost
-- Généré le : Mar 09 Septembre 2008 à 20:38
-- Version du serveur: 5.0.51
-- Version de PHP: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Base de données: `direct_connex`
--

-- --------------------------------------------------------

--
-- Structure de la table `jos_extcal_events`
--

CREATE TABLE IF NOT EXISTS `jos_extcal_events` (
  `extid` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `description` text NOT NULL,
  `contact` text NOT NULL,
  `url` varchar(100) NOT NULL default '',
  `email` varchar(120) NOT NULL default '',
  `picture` varchar(100) NOT NULL default '',
  `cat` tinyint(2) NOT NULL default '0',
  `day` tinyint(2) NOT NULL default '0',
  `month` smallint(2) NOT NULL default '0',
  `year` smallint(4) NOT NULL default '0',
  `approved` tinyint(1) NOT NULL default '0',
  `start_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `end_date` datetime default '0000-00-00 00:00:00',
  `recur_type` varchar(16) default NULL,
  `recur_val` tinyint(4) default '0',
  `recur_end_type` tinyint(1) unsigned NOT NULL default '0',
  `recur_count` tinyint(3) unsigned NOT NULL default '0',
  `recur_until` date default '0000-00-00',
  `week` tinyint(2) default '0',
  PRIMARY KEY  (`extid`),
  UNIQUE KEY `extid` (`extid`),
  KEY `start_date` (`start_date`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COMMENT='Table holding events and their attributes' AUTO_INCREMENT=715 ;

--
-- Contenu de la table `jos_extcal_events`
--

INSERT INTO `jos_extcal_events` (`extid`, `title`, `description`, `contact`, `url`, `email`, `picture`, `cat`, `day`, `month`, `year`, `approved`, `start_date`, `end_date`, `recur_type`, `recur_val`, `recur_end_type`, `recur_count`, `recur_until`, `week`) VALUES
(468, 'Festival Gospel Agape', 'Pour la troisième édition qui aura lieu du 1er au 3 août 2008 la scène de Produc-Sion Agapé se pare des couleurs du Festival Gospel Agapé.\r\nEspace unique entre l''art et la foi, lieu d''échanges et de partages, ce festival s''affirme comme un formidable tremplin pour ces artistes venus communiquer avec le public.\r\nPlus qu''un festival de musique, le Festival Gospel Agapé se veut être un temps de partage, de rencontres et de découverte de nouveaux horizons.\r\nPour plus d''info. Visitez notre site a l''adresse suivante\r\nhttp://www.festivalgospelagape.com/\r\n', 'Je me nomme Rich-Art arsenault,directeur d''un centre d''hébergement et de Produc-Sion Agape qui produit le Festival Gospel Agape', 'www.festivalgospelagape.com/', 'gospelagape@hotmail.com', 'ef89_Gaspe0671_320.jpg', 1, 1, 8, 2008, 1, '2008-08-01 13:00:00', '2008-08-03 23:59:59', '', 0, 0, 1, '2008-08-01', 31),
(683, 'ROCK CONCERT - JAY & THE LOVEBIRDS', 'ROCK - ROCK - ROCK - ROCK !!!!!![b]\r\nJAY AND THE LOVEBIRDS\r\n\r\nHUGE rock concert for youth\r\nFeaturing band "Jay and the LoveBirds"[b]\r\n\r\nFriday August 8th \r\nDoors open at 7:00pm\r\n\r\n**Geared towards to youth of Montreal**\r\nFREE ADMISSION[b]\r\n\r\n*Giveaways and prizes to be won*\r\n\r\nCome and rock out with us!\r\n\r\nVenue:\r\nCompassionate Youth\r\n2310 Ste-Catherine East\r\n[corner of Fullum]\r\nMetro: Papineau\r\nInfo: 514-989-1083\r\n', 'Word of Life \r\n2310 Ste-Catherine East\r\nMontreal Quebec\r\nH2K 2J4\r\n', '', 'wordoflife@bellnet.ca', '', 1, 8, 8, 2008, 1, '2008-08-08 07:00:00', '2008-08-08 23:59:59', '', 0, 0, 1, '2008-08-08', 32),
(684, 'The Leadership Summit', '[img=http://www.growingleadership.com/2008/webimages/Summit518X150.jpg]\r\n\r\nDon’t miss this event and join 100,000 leaders for this annual injection of vision, skill development and inspiration!!!  Whether it’s at church, home, work or in the community, God is calling his people to do what leaders do.\r\n\r\n[b]Cast Vision.  Set Strategy.  Navigate Change.  Motivate Teams.  Build Into Others.[/b]\r\n\r\nBut whose job is it to build into you?  The truth is, leaders are responsible for their own leadership development.  That’s why tens of thousands of Christ-following leaders in more than 140 different cities across North America gather each August for the Leadership Summit.  It’s a trusted, high-caliber event that’s designed to stretch, challenge and inspire you every 12 months.  And for the 4th year running, [b]Westview Bible Church[/b] in the West Island of Montreal has been selected as one of the satellite locations to host the event.  \r\n\r\n[b]Thursday August 7 and Friday August 8 2008[/b]\r\n10:00am -6:30 pm\r\nWestview Bible Church\r\n16789 Pierrefonds Blvd.\r\nPierrefonds, Quebec\r\n\r\nExperience this world-class event and learn from an elite line-up of proven leaders such as:\r\n[b]Brad Anderson[/b]\r\nVice-Chairman and CEO of Best Buy Inc.!!!\r\n-He started out as an entry-level sales person in a local stereo store and went on to grow the chain into a $19.2 billion retailer.  He was recently named one of “The Best Ceo’s in America” by Institutional Investor Magazine.\r\n\r\n[b]Efrem Smith[/b]\r\nSenior Pastor of The Sanctuary Covenant Church\r\nHe is an internationally recognized leader, speaker and sonsultant on diversity and multi-ethnic issues.  Smith has held leadership positions in both church and para-church organizations such as the Boys & Girls Club.\r\n\r\n[b]Bill George[/b]\r\nProfessor Harvard Business School & Former CEO of Medtronic\r\nAs a professor of management and Ethics, Bill George teaches from his personal experience on the topic of Leadership and Leadership development.\r\n\r\nCall Now and Register for seats for You and Your Team!!!  The Leadership Development Skills you and your team will learn will be well worth the investment.  \r\n\r\nFor more information on the event, registration, pricing and the complete line-up of speakers, visit growingleadership.com/summit\r\n\r\nFor specific information at the Montreal Host site, please contact\r\nJennifer Torres \r\nLeadership Summit – Promotion Team\r\n514.926.0027\r\nemail summit.westview@gmail.com', 'For specific information at the Montreal Host site, please contact\r\nJennifer Torres \r\nLeadership Summit – Promotion Team\r\n514.926.0027', 'www.growingleadership.com/summit', 'summit.westview@gmail.com', '', 2, 7, 8, 2008, 1, '2008-08-07 10:00:00', '2008-08-08 23:59:59', '', 0, 2, 1, '2008-08-07', 32),
(689, 'Jennifer Meade Farewell Concert', 'Before she leaves our fair city for the bright lights of New York, Jennifer will be giving a farewell concert for the benefit of Evangel''s Youth and Children.  Come and enjoy an evening of Jennifer''s great and energetic music.  A Free-will offering will be received.', 'Evangel Pentecostal Church  514-935-9656', '', 'evangel@evangel.qc.ca', 'ccec_JenniferMeade.jpg', 1, 3, 8, 2008, 1, '2008-08-03 18:00:00', '2008-08-03 20:00:00', '', 0, 0, 1, '2008-08-03', 32),
(695, 'Eagle Vision Monthly Workshops', '
[center][b]EAGLE VISION MINISTRIES Inc.\r\n\r\nHost: Merlene Natalianis Pitts (Pastor Merl)\r\n                         \r\n                          Invites You\r\n\r\n               To The Eagle''s Monthly Workshops\r\n\r\nSpeakers:  Anointed with varied Giftings & Callings\r\n\r\nDates:  The Last Saturday of each month\r\n\r\nJuly 26, Aug. 30, Sept.27, Oct. 25, Nov. 29, Dec. 20 \r\n\r\n\r\nN.B: July 26, the Workshop Event is entitled: "This One Thing I Do...:\r\n\r\nTime:  10a.m.-4:00pm; Room 602\r\n\r\nLocation:  Centre de ressources communautaires Cote-des-Neiges\r\n6767 Cotes des Neiges, Montreal, Qc, H3S 2T6[b][center]\r\n\r\n* Time, Room & Location remains the same each month*\r\n\r\n(This announcement is forerunner for the workshops scheduled for the complete year of 2008.)  ', 'Pastor Merlene Natalianis Pitts @514-631-6398', '', 'eaglevisionministries@yahoo.com', '', 10, 26, 7, 2008, 1, '2008-07-26 10:00:00', '2008-07-27 16:00:00', 'week', 2, 2, 1, '2008-12-20', 0),
(701, 'FTE COURS  AUTOMNE 2008', 'Faculté de Théologie Évangélique\r\nUniversité Acadia\r\n\r\nHORAIRE ET DESCRIPTION DES COURS  AUTOMNE 2008\r\nDÉBUT DU TRIMESTRE : Lundi 8 sept. / FIN DU TRIMESTRE : vend. 19 déc. \r\nSemaine de lecture 27-31 octobre 2008\r\n\r\nEB 5003 Introduction aux études bibliques – mardi 18h30 – 21h30\r\nCours conçu pour  les étudiants qui n''ont pas le niveau CEGEP.\r\n\r\nEB 6023  Apostolic Era of the New Testament (Acts to Revelation)  (BTH, CEB, MA, MDiv) – Thursday 1-4\r\nGeneral Introduction to the second half of the New Testament followed by a study of these books.\r\n\r\nEB 6013 Les quatre évangiles (Introduction au NT I) (BTH, CEB, MA, MDiv) – lundi 18h30 - 21h30\r\nIntroduction générale aux quatre évangiles suivie d’un survol de la vie et de l’enseignement de Jésus.\r\n\r\nEB 7153  Acts of the Apostles  (BTH, CEB, MA, MDiv) \r\nThis course will examine the beginnings of the Church of the New Testament and apply this to the church today.\r\n\r\nFR 7003  Français (BTH, CEB, CR) – mardi 13h – 16h\r\nPrincipes fondamentaux de la grammaire française et de la dissertation.\r\n\r\nEC 6023  Philosophie de l’éducation chrétienne (BTH, CEB, MDiv) Une semaine intensive 27 octobre – 1er nov\r\nCe survol a pour but d’éclairer l’étudiant sur le domaine de l’éducation chrétienne à tous les niveaux d’âge dans la perspective de la croissance personnelle des chrétiens et de leur implication dans leurs milieux de vie, particulièrement dans l’église \r\n\r\nFC 7053  Gestion de conflits (CR, BTH, MDiv) – jeudi 18h30 – 21h30\r\nIntroduction aux différentes théories de résolution de conflits. Attention particulière aux conflits conjugaux et familiaux \r\n\r\nGR 6006  Grammaire grecque du NT (BTH, CEB, MA, MDiv) – mardi 9h – 12h00\r\nUne étude des éléments essentiels de la grammaire et du vocabulaire du grec néo-testamentaire (6 crédits, automne et hiver).\r\n\r\nGR 7013  Littérature grecque du NT  (BTH, MA, MDiv) – mardi 18h30 – 21h30\r\nLecture de livres ou de choix de textes, privilégiant l’étude de la grammaire, dans le Nouveau Testament grec. Préalable: GR 6006\r\n\r\nHB 7006  Hébreu biblique élémentaire  (BTH, MA) – jeudi 18h30 – 21h30\r\nIntroduction à la grammaire et à la syntaxe de l’hébreu biblique (6 crédits – automne et hiver).\r\n\r\nHB 7013  Lecture dans l’A.T. hébreu (BTH, MA) – jeudi 9h00 – 12h00\r\nUn cours de lecture de l’A. T. en hébreu, comportant une étude de la langue, ainsi que l’histoire du texte et du canon des Écritures hébraïques. Préalable : HB 7006.\r\n\r\nID 8803 Herméneutique et théologie (MTh) – jeudi 13h – 16h\r\nCe cours a pour objet d’apprécier la dimension herméneutique de toute démarche théologique ainsi que les implications théologiques de toute herméneutique. Le cours établira la nécessité et la responsabilité (théologiques, pastorales et ecclésiastiques) de l’herméneutique biblique. Il comporte un survol des méthodes herméneutiques et des modèles théologiques mis en œuvre dans les deux derniers siècles avec une attention spéciale aux dernières décennies. Nous nous intéresserons de façon critique aux pratiques herméneutiques de nos milieux évangéliques.\r\n\r\nTH 8403 Théologie de la Réforme (MTh)\r\nUne étude à partir des documents de l’époque de la théologie et de l’ecclésiologie de la Réforme magistérielle, ainsi que l’examen de plusieurs textes émanant de la Réforme dite radicale, qui confirment ou qui contestent certains aspects de la Réforme protestante.\r\n\r\nTP 5123  Ministère pastoral (BTH, MDiv) – lundi 18h30 - 21h30\r\nOn examine le ministère pastoral exercé au sein de la congrégation locale, à la lumière du contexte actuel. Le cours met en lumière les fonctions multiples du pasteur. \r\nTP 6623  Perte, affliction, deuil et mortalité (BTH, CR, MDiv)\r\n Ce cours examine la signification et les ré¬ponses bibliques reliées aux pertes, afflictions et deuils : séparation, divorce, maladie, problèmes associés à la vie monoparentale, vieillissement, mortalité.\r\n', 'Faculté de Théologie Évangélique\r\nUniversité Acadia\r\n2285, av. Papineau Montréal (Qc)  H2K 4J5\r\nTéléphone : (514) 526-2003 Télécopieur : (514) 526-6887\r\nCourriels : reg@fteacadia.ca sec@fteacadia.ca\r\nwww.fteacadia.ca', 'www.fteacadia.ca', 'reg@fteacadia.ca', '', 10, 25, 7, 2008, 1, '2008-07-25 08:00:00', '2008-07-25 23:59:59', 'day', 3, 2, 1, '2008-09-08', 30),
(702, 'Torrents De Vie', '[center][/center]
[b]Ministère Torrents de Vie[/b]\r\n\r\nÊtes-vous en lutte avec les difficultés relationnelles ou sexuelles?\r\n\r\nTorrents de Vie, une programme de formation de disciples et de restauration offre un espace sécuritaire où vous pouvez approfondir votre relation avec Jésus Christ .\r\nVous découvrirez aussi une liberté de la dépendance émotionnelle, l''addiction sexuelle, les effets de l''abus ou d''autres problèmes relationels.\r\n\r\nLe prochain programme de 25 semaines débutera mardi le 9 septembre, 2008.\r\n\r\nNous vous suggérons de vous inscrire le plus tôt possible car les places sont limitées. \r\n\r\nNotez bien que toute information est gardée confidentielle.', 'Pour plus d''information ou de recevoir un formulaire d''inscription contacter- 514-727-1371 ou (cprovost001@sympatico.ca)\r\nVisitez notre site national à www.livingwaterscanada.org', '', 'cprovost001@sympatico.ca', '', 4, 28, 7, 2008, 1, '2008-07-28 19:00:00', '2008-07-28 23:59:59', 'week', 1, 2, 7, '2008-09-09', 37);

 

Those events all appears in week 32 of this calander:

http://www.directionconnexion.ca/component/option,com_extcalendar/Itemid,63/extmode,cal/date,2008-08-01/lang,fr/

 

I need to retreive them in order to list a given week in a montly email script. But for some reason, the only one that appears is extid: 689, 684, 683

 

I am currently using this script:

if (!function_exists('getJobs')) {
function getJobs($limit=NULL, $type) {

$dbh2=mysql_connect("localhost", "", "") or die ('1 Erreur: '.mysql_error());
mysql_select_db("");

if ($type=="JOB"){$table="jos_jl_jobposting"; $col="title"; $id="id";}
if ($type=="SHOP"){$table="jos_marketplace_ads"; $col="ad_headline"; $id="id";}
if ($type=="CAL"){$table="jos_extcal_events"; $col="title"; $id="extid";

$val=explode("/", $limit);

$yearStart = "$val[0]-01-01";

$weekNumber = $val[1];

$oneDay = 24*60*60;

$tempTimestamp = strtotime($yearStart) + ($weekNumber-1)*7*$oneDay;

$weekDay = date("w", $tempTimestamp); // 0 means sunday, 1 means monday


if($weekDay == 0) // start from monday
$tempTimestamp += $oneDay;
else // start from monday
$tempTimestamp -= $weekDay * $oneDay;
$weekDate = date("Y-m-d", $tempTimestamp);
$lweekDate = date("Y-m-d", ($tempTimestamp + 604800));

  $res = mysql_query("
  
  SELECT * FROM $table where 
  (start_date >= '$weekDate 00:00:00' and recur_until <= '$lweekDate')
  
  ") or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
     
          $jobs[] = "- $row[start_date] <a href='http://www.directionconnexion.ca/component/option,com_extcalendar/Itemid,63/extmode,view/extid,$row[extid]/lang,en/'/>".$row[$col]."</a>";
  }

return @implode('<br />', $jobs);

}else{

   $query = "SELECT * FROM $table";
   if ($limit != null) {
         $query .= " LIMIT $limit";
   }
  $res = mysql_query($query) or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
   
if ($type=="JOB"){$url="http://www.directionconnexion.ca/component/option,com_jobline/Itemid,70/task,view/id,".$row[$id]."/lang,en";}
if ($type=="SHOP"){$url="http://www.directionconnexion.ca/component/option,com_marketplace/page,show_ad/catid,0/adid,329/Itemid,".$row[$id]."/lang,en/";$price=" $row[ad_price]";}
   
          $jobs[] = "- <a href='".$url."'/>".$row[$col]."</a>$price";
  }
  return implode('<br />', $jobs);
}
}
}


$content = preg_replace('/\[JOB(=([0-9]+))?\]/ie', "getJobs($2, 'JOB')", $content);
$content = preg_replace('/\[sHOP(=([0-9]+))?\]/ie', "getJobs($2, 'SHOP')", $content);
$content = preg_replace('/(\[CAL\=([0-9]*\/[0-9]*)\])/ie', "getJobs('$2', 'CAL')", $content);

 

In other words, when the client types [CAL=2008/32] it will be replaced by a liste of events from the given week number of the given year.

 

Anybody can help me figure this up?

[discomatt]

Why are day,month,year ints, and start_date and end_date datetimes? They should all be datetime, then you could use the BETWEEN clause

[drisate]

i did not make the calander and i really don't wana start changing stuff in it. I just wana retreive the events.

[drisate]

i dont get it ...

 

  SELECT * FROM $table where

  (start_date >= '$weekDate 00:00:00' and recur_until <= '$lweekDate')

 

returns only 3 results and all 7 are between the 2 dates ...

 

I tryed puting them in a normal number format using str_replace to take out the space, the : and the - but my query is obviously not working ... lol FUNCTION direct_connex.str_replace does not exist

 

$weekDate = date("Ymd000000", $tempTimestamp);
$lweekDate = date("Ymd000000", ($tempTimestamp + 604800));

  $res = mysql_query("
  
  SELECT * FROM $table where 
  (str_replace(':', '', str_replace(' ', '', str_replace('-', '', start_date))) >= '$weekDate' and recur_until <= '$lweekDate')
  
  ") or die(mysql_error());

[drisate]

bump

anybody?

[drisate]

i tryed  a new approche

 

$val=explode("/", $limit);
$yearStart = "$val[0]-01-01";
$weekNumber = $val[1];
$oneDay = 24*60*60;
$tempTimestamp = strtotime($yearStart) + ($weekNumber-1)*7*$oneDay;
$weekDay = date("w", $tempTimestamp); // 0 means sunday, 1 means monday
$tempTimestamp -= $weekDay * $oneDay;
$weekDate = date("Ymd", $tempTimestamp);
$weekDate=$weekDate."000000";
$lweekDate = date("Ymd", ($tempTimestamp + 604800));
$lweekDate=$lweekDate."235959";

  $res = mysql_query("SELECT * FROM $table") or die(mysql_error());
  while ($row = mysql_fetch_assoc($res)) {
  	
  	$f=str_replace('-', '', $row[start_date]);
  	$f=str_replace(' ', '', $f);
  	$f=str_replace(':', '', $f);
  	
  	$l=str_replace('-', '', $row[recur_until]);
  	$l=$l."235959";  	
  	
  	if (($f >= $weekDate and $l <= $lweekDate and $row[approved] = "1")){
    $jobs[] = "<br />$f >= $weekDate<br />$l <= $lweekDate<br />- $row[start_date] <a href='http://www.directionconnexion.ca/component/option,com_extcalendar/Itemid,63/extmode,view/extid,$row[extid]/lang,en/'/>".$row[$col]."</a>";
  }
  }

 

And that returned:

 

20080808070000 >= 20080803000000

20080808235959 <= 20080810235959

- 2008-08-08 07:00:00 ROCK CONCERT - JAY & THE LOVEBIRDS

 

20080807100000 >= 20080803000000

20080807235959 <= 20080810235959

- 2008-08-07 10:00:00 The Leadership Summit

 

20080803180000 >= 20080803000000

20080803235959 <= 20080810235959

- 2008-08-03 18:00:00 Jennifer Meade Farewell Concert

 

So i am missing 468, 689, 701, 702 ... why? i don't get it ... those result appears in the calander so they should normaly appear in this code as well ...

[xoligy]

Stupid question but why dont you visit the site that created it? Surly they understand how it works rather than asking people to try and figure it out? I believe this is there forum:

 

http://maroctour.com/forums/

 

i got it from the bottom of the script

[drisate]

Yeah but the resgistration process and board is buged and there contact email sends back a delevery failier.

 

[drisate]

bump