MYSQL mutiple WHERE clauses

[devsie]

Hi.

I have a mysql table where i have a number of columns with date01, date02, date03 etc listed.

I'm trying to search the table to see if any of these dates match some user input.

$result3 = mysql_query("SELECT * FROM table WHERE postcode1 = '$row2[0]' AND date01 = '$sqldate'")

Now I assume i could just do
$result3 = mysql_query("SELECT * FROM table WHERE postcode1 = '$row2[0]' AND date01 = '$sqldate' OR date02='$sqldate' OR date03='$sqldate'")

But I have 30 dates. date01 to date30. Is there a simplier/easier way than listing them all individually?

Thankyou. :)

[LifeinAsia]

With the way your table is structured, I don't know of any other way to do it. Does each row have 30 dates or only some of them? It might be better to restructure your table.

Also, make sure you use parenthesis correctly:
SELECT *
FROM table
WHERE postcode1 = '$row2[0]' AND (date01 = '$sqldate' OR date02='$sqldate' OR date03='$sqldate)

By the way, welcome to WW [webmasterworld.com]!

[devsie]

Thankyou.

That comment about the parenthesis is true I probably would've messed that up and spent ages over it.

With my table lay out, i think that is the best i can do really. Basically users (each row) will be able to list days that they are available. I had considered having them in 1 column in a long string, but I think i'll stick with this way.

Thanks again.

[LifeinAsia]

A more efficient design would be to have 2 relational tables:
1) UserInfo:
- UserID
- (other information related to the user)
2) AvailableDates:
- UserID
- AvailableDate

The the select is much easier:
SELECT *
FROM UserInfo, AvalableDates
WHERE AvalableDates.AvailableDate='$sqldate' AND UserInfo.UserID=AvalableDates.UserID

This is a nudge in the (hopefully) more efficient direction. Without knowing more about your current structure or application, it's hard to give more direction.

[devsie]

Thankyou, I've been learning for about 2weeks in my spare time so any guidance is very helpful.
I had looked at this "join" thing from a web tutorial.

Does AvailableDates.AvailableDate='$sqldate' look through all the columns in that table to see if any of them hold the correct date?

For the structure... A user can input say 30 dates. Another user can search to see if anyone is around on the date they chose. Do i need to explain more? I'm happy to.

Thanks :)

[LifeinAsia]

any guidance is very helpful.

Been there, done that. :)

So please consider these comments as suggestions, not criticisms.

I've been learning for about 2weeks in my spare time

When most people start working with databases, they tend to think of things in an Excel spreadsheet model (rows and columns). In a sense, it is. But to unleash the full power of relational databases, you need to visualize things with a different perspective.

Instead of Excel, think set theory. Do you remember back in elementary school when you had different ovals of objects and you have to color the intersections or unions of them? Relational databases use a similar model, with each table being one of the ovals. (With more than 2 tables, you can't visualize it in 2 dimensions any more, so it makes it a little more difficult to visualize things sometimes.) Okay, probably thoroughly confused now... :)

Does AvailableDates.AvailableDate='$sqldate' look through all the columns in that table to see if any of them hold the correct date?

Yes and no. Technically yes, but with an index on that field, your DB knows how to "optimize" the query to just search a subset of the entire table.

In fact, that's another argument against your original structure. To optimize the table, you'd need 30 indexes on it.

A user can input say 30 dates.

Because of that, here are some issues with your original design:
1) If a user only enters 1 date, then that row basically has 29 fields of wasted space. It's not a big issue unless you have a huge DB with millions of rows and limited disk storage.
2) If you want to change things so that users can enter up to 45 dates, you'll have to add 15 more fields (as well as modify your WHERE statement). With relational tables, there's nothing to change- a user could enter thousands of dates with no modification needed to the DB structure.

[devsie]

I'm happy to listen if you're happy to talk :) I know it can take some time to type all these things out.
Venn diagrams or something weren't they. I know the circular things you mean, and i can understand that.

I doubt my database will ever get huge, but I do like to try and do things in the best manner.

With relational tables if i increased it to 45 dates, would i not still need the columns set up ready for the data to be inputted? I know you can create tables using php, but to be honest i have done it with php admin in the main because i've found that easier. The user will be able to change their dates as time goes by and i was going to UPDATE rather than INSERT.

I'm sorry. I'm still a bit confused with relational/join stuff.
AvailableDates.AvailableDate='$sqldate';
This means, "look in the table called available.dates, look in the column availabledate to see if it matches $sqldate"? Or does it mean look through the whole table?

Off to try and find some more online

:o)

[LifeinAsia]

Venn diagrams

Venn diagrams, ovals, whatever. I was trying to remember earlier what they are called- you got me there. :)

With relational tables if i increased it to 45 dates, would i not still need the columns set up ready for the data to be inputted?

On the user side, you'll still need to accommodate however many dates. On the maintenance side, it would be better use PHP to create the HTML tables instead of manually recoding things every time you change something.

This means, "look in the table called available.dates, look in the column availabledate to see if it matches $sqldate"? Or does it mean look through the whole table?

Both, sort of. It means search through the entire table looking at the AvailableDate field and return the information for any rows matching that field. In other words, for all the rows where AvailableDate does not match, it ignores the data in the UserID field.

[devsie]

So the AvailableDate field is what my newbie brain would think of as a column? And would only contain 1 date per user? I think this is where i'm a bit confused with what you're saying.

[devsie]

Oh, and I just found out about "foreach" loops which should make life easier and neater on the inputting of the dates.

[LifeinAsia]

So the AvailableDate field is what my newbie brain would think of as a column? And would only contain 1 date per user?

Correct. So the data might look something like:
- UserID, AvalableDate
- 1, 1/1/2008
- 1, 1/2/2008
- 1, 1/3/2008
So user 1 would be available on 1/1/2008, 1/2/2008, and 1/3/2008.

[devsie]

ahhhhhh :)

So I could put the user into that table lots of times. Hmmmmmm. only my other table i have the UserID (which is a professinol body number) set to primary key and unique so I couldn't get duplicate people.

But i guess i wouldn't need to on the dates one. Hmmmm. That could end up a very long table though.

Hmmmm
Thankyou VERY MUCH. I was a bit slow on the uptake but I got there. I hadn't thought have about having the user on multiple rows. I'll give this some thought. I guess it doesn't matter if the table ends up very long, cause I could always search out the info i needed.
Just thinking about deleting old dates too.

Sorry am waffling, ty again.

[LifeinAsia]

You can still make the UserUD a primary key for the UserInfo table- in fact, it makes sense to do that.

I wouldn't worry too much about "long" tables- that's what DBs are for. I work with several tables that are 15+ million rows, and even that's piddly stuff compared to what some people use.