**SOLVED** substring() and where clause

[anatak]

hello,

I have a query that select events according to the date

it works for the past events but not for the future events
the problem is that the substring_index() function does not select anything
if I take out the
WHERE EventDate > '2006-06-15'
it works

this does not work (future events)

[code]
//query as sent to server
SELECT event.EventId, event.EventAuthor, event.EventType, event.EventTitle, ken.KenName, city.CityName, SUBSTRING_INDEX(event.EventDescription, ' ', 50), event.EventDate, date_format(event.EventUpdateDate, '%M %D, %Y') as updatedate, date_format(event.EventPostDate, '%M %D, %Y') as postdate, user.UserId, user.UserHandle FROM event LEFT JOIN user ON event.EventAuthor = user.UserId LEFT JOIN city ON event.EventPlace = city.CityNr LEFT JOIN ken ON city.KenNr = ken.KenNr WHERE EventDate > '2006-06-15' ORDER BY EventDate ASC;


//query as it is in my php script
$QuerySelectCommingEvent = "SELECT $TableName1.EventId, $TableName1.EventAuthor, $TableName1.EventType, $TableName1.EventTitle,
                        $TableName3.KenName, $TableName4.CityName,
                        SUBSTRING_INDEX($TableName1.EventDescription, ' ', $w),
                        $TableName1.EventDate,
                        date_format($TableName1.EventUpdateDate, '%M %D, %Y') as updatedate,
                        date_format($TableName1.EventPostDate, '%M %D, %Y') as postdate,
                        $TableName2.UserId, $TableName2.UserHandle
                        FROM $TableName1 LEFT JOIN $TableName2 ON $TableName1.EventAuthor = $TableName2.UserId
                        LEFT JOIN $TableName4 ON $TableName1.EventPlace = $TableName4.CityNr
                        LEFT JOIN $TableName3 ON $TableName4.KenNr = $TableName3.KenNr
                        WHERE EventDate > '$todaydate' ORDER BY EventDate ASC;";

[/code]

strangely enough it works for past events
this does work
[code]
//query as sent to server
SELECT event.EventId, event.EventAuthor, event.EventType, event.EventTitle, ken.KenName, city.CityName, SUBSTRING_INDEX(event.EventDescription, ' ', 50), event.EventDate, date_format(event.EventUpdateDate, '%M %D, %Y') as updatedate, date_format(event.EventPostDate, '%M %D, %Y') as postdate, user.UserId, user.UserHandle FROM event LEFT JOIN user ON event.EventAuthor = user.UserId LEFT JOIN city ON event.EventPlace = city.CityNr LEFT JOIN ken ON city.KenNr = ken.KenNr WHERE EventDate < '2006-06-15' ORDER BY EventDate DESC
Select a Page


//query as it is in my php script
$QuerySelectPastEvent = "SELECT $TableName1.EventId, $TableName1.EventAuthor, $TableName1.EventType, $TableName1.EventTitle,
                        $TableName3.KenName, $TableName4.CityName,
                        SUBSTRING_INDEX($TableName1.EventDescription, ' ', $w),
                        $TableName1.EventDate,
                        date_format($TableName1.EventUpdateDate, '%M %D, %Y') as updatedate,
                        date_format($TableName1.EventPostDate, '%M %D, %Y') as postdate,
                        $TableName2.UserId, $TableName2.UserHandle
                        FROM $TableName1 LEFT JOIN $TableName2 ON $TableName1.EventAuthor = $TableName2.UserId
                        LEFT JOIN $TableName4 ON $TableName1.EventPlace = $TableName4.CityNr
                        LEFT JOIN $TableName3 ON $TableName4.KenNr = $TableName3.KenNr
                        WHERE EventDate < '$todaydate' ORDER BY EventDate DESC";

[/code]

the only difference is
WHERE EventDate > '$todaydate'
and
WHERE EventDate < '$todaydate'


I am at a total loss here
anyone who knows what to do and why this happens ?

thanks
anatak

[fenway]

I'm not sure I understand what this has to do with substring()....

[anatak]

Hello,

Sorry for the trouble I found the reason
one way or the other the data was deleted (DUH) so of course it did not select anything.

I have no idea how the column got deleted only the data out of the future events

thanks for looking and sorry again for your trouble

anatak