1
sarahmx
extcal database query
  • 2017/3/6 8:49

  • sarahmx

  • Quite a regular

  • Posts: 381

  • Since: 2007/10/28


Hello XOOPSian

can you help me with this query

i would like to show todays event manually using the query below outside of xoops..currently testing this in localhost inside XOOPS

event_start is stored as integer eg 1306112400

$time time(); 
//echo $time;
$today=date("d/m/Y"strval($time));
//echo $today;

global  $xoopsDB;
$sql "SELECT * FROM " $xoopsDB->prefix('extcal_event') . "  WHERE DATE_FORMAT(event_start, '%d/%m/%Y')='$today' ORDER BY event_start";


The code is not working at the moment, can you guys help me..Please

2
Bleekk
Re: extcal database query
  • 2017/3/6 11:43

  • Bleekk

  • Theme Designer

  • Posts: 940

  • Since: 2002/12/14


Maybe you can try something like this
$time time();  
$today date("d/m/Y"strval($time)); 

global  
$xoopsDB
$result $xoopsDB->query("SELECT * FROM " $xoopsDB->prefix('extcal_event') );
if (
$result===false) {
    die(
$xoopsDB->error()); 

while (
$row $xoopsDB->fetchArray($result)) { 
    if ((
date('d/m/Y'$row['event_start'])) == $today){
      echo 
$row['event_title'];
    }
}

3
sarahmx
Re: extcal database query
  • 2017/3/7 1:22

  • sarahmx

  • Quite a regular

  • Posts: 381

  • Since: 2007/10/28


Thank you its working bleek..i added event_end also

if ((date('d/m/Y'$row['event_start'])) == $today OR (date('d/m/Y'$row['event_end'])) == $today){


but if i have 3 days event

let say 1/3 to 3/3 and today is 2/3 how should i change my code to display the event for 2/3

4
sarahmx
Re: extcal database query
  • 2017/3/7 4:30

  • sarahmx

  • Quite a regular

  • Posts: 381

  • Since: 2007/10/28


i tried it something like this but its not working

$time time(); 
$today=date("d/m/Y"strval($time));


global  
$xoopsDB;
$sql "SELECT * FROM " $xoopsDB->prefix('extcal_event') . " "
if (
$result===false) { 
    die(
$xoopsDB->error());  

$result $xoopsDB->query($sql); 

while(
$row=$xoopsDB->fetchArray($result)) {
$event_title $row['event_title']; 
$event_start=date("d/m/Y"strval($row["event_start"]));
$event_end=date("d/m/Y"strval($row["event_end"]));

//if ((date('d/m/Y', $row['event_start'])) == $today OR (date('d/m/Y', $row['event_end'])) == $today){ 

if ($event_start <= $today  && $today <= $event_end){ 
 echo 
$row['event_title']; 
    } 
}

5
zyspec
Re: extcal database query
  • 2017/3/7 5:41

  • zyspec

  • Module Developer

  • Posts: 1095

  • Since: 2004/9/21


I think what you want is to replace the last 3 lines with the following:

if ($row['event_start'] <= $time && $time <= $row['event_end']) { 
  echo 
$row['event_title']; 
}


This compares the numeric value of today, the starting time and the ending time. Your previous attempt compares the formatted date string, which isn't what you want.

6
sarahmx
Re: extcal database query
  • 2017/3/7 6:12

  • sarahmx

  • Quite a regular

  • Posts: 381

  • Since: 2007/10/28


Thank you zyspec...all now is working



$time time();  
$today=date("d/m/Y"strval($time)); 


global  
$xoopsDB
$sql "SELECT * FROM " $xoopsDB->prefix('extcal_event') . " ";  
if (
$result===false) {  
    die(
$xoopsDB->error());   
}  
$result $xoopsDB->query($sql);  

while(
$row=$xoopsDB->fetchArray($result)) { 
$event_title $row['event_title'];  
$event_start=date("d/m/Y"strval($row["event_start"])); 
$event_end=date("d/m/Y"strval($row["event_end"])); 

//if ((date('d/m/Y', $row['event_start'])) == $today OR (date('d/m/Y', $row['event_end'])) == $today){  

if ($event_start == $today OR $event_end==$today OR $row['event_start'] <= $time && $time <= $row['event_end']){ 
 echo 
$row['event_title'];  
    }  
}

7
Bleekk
Re: extcal database query
  • 2017/3/7 7:12

  • Bleekk

  • Theme Designer

  • Posts: 940

  • Since: 2002/12/14


you need to change the order of the first part of your code to this
global  $xoopsDB;  
$sql "SELECT * FROM " $xoopsDB->prefix('extcal_event') . " ";   
$result $xoopsDB->query($sql);
if (
$result===false) {   
    die(
$xoopsDB->error());    
}

8
sarahmx
Re: extcal database query
  • 2017/3/8 2:58

  • sarahmx

  • Quite a regular

  • Posts: 381

  • Since: 2007/10/28


noted and tq Bleek

Login

Who's Online

189 user(s) are online (112 user(s) are browsing Support Forums)


Members: 0


Guests: 189


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits