php - mysql

Hi.

i have records with these fields, start date and end date in string format D-M-Y H:M:S
i need to get 7 records the middle result should from now, start and end should be within the current time.

i have no idea how to start with the query... thanks

[ moved ]

Why don't you just use MySQL's BETWEEN method?

i haveno idea how?

I just posted you a link of the documentation! Read it and you will know how.

ok i do this

Code:

---

$result = $oDatabase->Query('SELECT Start_date, End_date, Booking_id, Description FROM dms.bookings WHERE (Date_Format(Now(), \'%Y-%m-%d %H:%i:%s\') BETWEEN Start_date AND End_date) AND Product_id='.$zaal.' AND Description IS NOT NULL LIMIT 7');

---

problem is...

i want to get 7 results
i now get the result that is the current event.
i want now to add events to the query that are closest to the current event..

say now i have the event rapper ice cube.
but in a few hours i have the event nas.
i want to have the list from current running event and the closest to now.

thanks for your help

You really should read some documentation.

Code:

---

SELECT * FROM tablename ORDER BY timestamp DESC LIMIT 0, 7

---

You dont understood my problem

Code:

---

$result = $oDatabase->Query('SELECT `Start_date`, `End_date`, `Booking_id`, `Description`, (DATE_FORMAT(NOW(), \'&#37;Y-%m-%d %H:%i:%s\') BETWEEN `Start_date` AND `End_date`) as `is_now` FROM `bookings` WHERE DATE_FORMAT(NOW(), \'%Y-%m-%d %H:%i:%s\') < `End_date` '.(($zaal > 0) ? 'AND `Product_id`='.$zaal.' ' : '').'AND`Description` IS NOT NULL ORDER BY `Start_date` ASC LIMIT 7');

---

this code works.
however i must ROTATE the events.
the current event must be in the middle.
and below that must be a few earlier events in order
and above that musta be a few later events in order.

it must rotate

I guess I'm not understanding your problem because I am having difficulty understanding your English.

I understand that you want to pick a date and show a specific number of dates before, and a specific number after. However, what are you talking about by "rotating?"

i am sorry for not writing proper english grammer....

i want to have this result example.

2009-01-10 - 2009-01-11 - after tomorow
2009-01-09 - 2009-01-10 - tomorow
2009-01-08 - 2009-01-09 - TODAy
2009-01-07 - 2009-01-08 - yesterday
2009-01-06 - 2009-01-07 - before yesterday

after X interval this info is refreshed. ( i havent include time to make easier)
so you can see TODAY will be "move" up in the rotation...

thanks

Okay, what you need is to make use of PHP's built in functions date() and mktime(). Using these together, you can work with a temporary date formatted variable which can change giving you the "rotation" effect.

PHP Code:

---

// altering these variables will give you the rotation
// setting them as session variables would get them across pages
$y_temp = 2009;
$m_temp = 1;
$d_temp = 27;

// the following will give you the parts for the SQL query's BETWEEN method
$begin = date('Y-m-d', mktime(0, 0, 0, $m_temp, $d_temp - 2, $y_temp));
$end = date('Y-m-d', mktime(0, 0, 0, $m_temp, $d_temp + 2, $y_temp)); 


---

this doe not help me at all cause i do not know the time diffrent between events.



and there is no way those functions can help me.

The example of dates you just gave prior to my post shows nothing about events. It shows 5 dates back to back. The code I posted does exactly what you said you wanted in post #10.

no it doesnt help me.

and the dates ARE the events!

i do not know the time difrence between events!
the event before today
it might as well been 2 years ago!
or 2 mintus ago.

i will never know!