Jump to content

Archived

This topic is now archived and is closed to further replies.

anatak

**SOLVED** substring() and where clause

Recommended Posts

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

Share this post


Link to post
Share on other sites
I'm not sure I understand what this has to do with substring()....

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.