Jump to content


Photo

**SOLVED** substring() and where clause


  • Please log in to reply
2 replies to this topic

#1 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 407 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 14 June 2006 - 12:13 PM

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)

//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;";


strangely enough it works for past events
this does work
//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";


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
takasi.8008@docomo.ne.jp
tourokum@0508.jp

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 June 2006 - 05:06 PM

I'm not sure I understand what this has to do with substring()....
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 anatak

anatak
  • Members
  • PipPipPip
  • Advanced Member
  • 407 posts
  • LocationJapan, Fukuoka prefecture, Kitakyushu City

Posted 15 June 2006 - 10:07 PM

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
takasi.8008@docomo.ne.jp
tourokum@0508.jp




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users