Ignore record

[lard]

Hi

 

How can I ignore a record? What I'm trying to do is list a table of events chronologically. So if the date/time has already passed ignore the record and move on.

 

Thanks

[corbin]

Psuedo code:

 

 

WHERE timecolumn > NOW();

[lard]

Cheers Corbin

 

Technically that does work, thanks. However for my purposes I need something different. The reason is that not all records will have the date included. Some events will be weekly, monthly, first saturday of the month etc etc. So I need to manipulate the data with php after the mysql query.

 

I know I could set all these records as a date far in the future but I'd still need to eliminate records after the sql query.

 

So basically what would be perfect for me is to do something like:

 

if argument=false, skip to next record.

 

If that makes sense...

 

[corbin]

How are you storing the scheduling of things?

[lard]

To be honest I'm not exactly sure just yet. I'm kinda fumbling my way along. My thinking so far is to have:

 

Field which lists the days included - so if current day matches it, record is included.

 

Field listing type - i.e. daily/weekly/monthly/unique/first or last saturday etc

 

Field for month (some will be every monday in March for example)

 

Field listing start time

 

Then weeding out non applicable fields and ordering it by the date and time.

 

I'm just trying to gradually sort it bit by bit at the mo. I'm sure there should be a way of doing what I suggested, I just can't find one! (i.e. if argument=false, skip to next record.)

[lard]

Finally found the right syntax - what I was talking about was:

 

while($row = mysql_fetch_array($result))
  {
          if( ! $argument)
                continue;
  } 

[.josh]

You should try to keep your data filtering inside your query.  That is, after all, what databases are for.  They are specifically built to store and optimally retrieve/filter data.

 

If you want to select all dates not past yet, but also have open-ended 'events' that have no dates, you need to tell us how those 'open-ended' dates are being stored. What value is in the date field?  Is it 'null'? 0? 0000-00-00?  You have to tell us what it looks like in your database.  The query is easy enough:

 

... WHERE datecolumn > NOW() OR datecolumn = null <--- insert the value of the open-ended dates here

 

 

 

[lard]

Thanks for that crayon. I totally get that you're right it should be done as much within the sql query as possible. I think I'll use your idea for the null dates (incidentally it's 0000:00:00 format). I had attacked it in a different way, by entering 3000:01:01 for any records without dates but your way is much better.

 

I don't think I have any choice but to do some of the filtering outside of the query because of the complexities of the differences in types of data, but I might be wrong.

 

I'll post some of my code later when I've got a bit further with it.

[.josh]

I don't think I have any choice but to do some of the filtering outside of the query because of the complexities of the differences in types of data, but I might be wrong.

 

If you find yourself saying that then you probably need to sit down and re-think your database/table scheme/structure. 

[lard]

I know what you're saying and I'm sure you're right. Unfortunately I'm new to this stuff - databases are not my forte! I have given it a fair bit of thought but my sql knowledge is very limited too so I'm not totally sure how far I can use that.

 

It's a steep learning curve from here on in...

[.josh]

LoL I feel for you.  Got to start somewhere.  No worries.

 

p.s.-

 

I had attacked it in a different way, by entering 3000:01:01 for any records without dates but your way is much better.

 

My suggestion about the null or zeroed date field isn't necessarily better than this.  Technically it's better, because then you won't have to worry about having to change your script when Jan 1st, 3000 becomes a date things are being set for, but 1000 years in the future is a pretty long time, lol. 

 

 

[lard]

 

... WHERE datecolumn > NOW() OR datecolumn = null <--- insert the value of the open-ended dates here

 

 

I'm not sure exactly what syntax I need here. I need some kind of 'AND OR' if that makes sense. Using OR on it's own brings in extra records because because I have other WHERE variables which need to work in conjuntion with it.

 

My code so far is:

SELECT * FROM `events` INNER JOIN `sites` WHERE events.siteName = sites.siteName AND date > NOW() OR date = '0000:00:00' ORDER BY date, startTime, sites.displaySiteName 

 

So the OR means that loads of extra info is now listed (because of the INNER JOIN). Lots more requirements will be added as I go too. I hope that makes sense...

 

So is there such a thing as an 'AND OR'?

[.josh]

wrap it in parenthesis.

 

(date > NOW() OR date = '0000:00:00')