SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict manipura's Avatar
    Join Date
    Apr 2001
    Location
    Calgary,AB
    Posts
    345
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advanced, Complex Query, Here's a challenge for ya!

    I'm creating a personal ads site. I need to know how to do the right query. I had idea of how to do it in feb when I started this project but my lazy tech manager finaly got around to creating the table for this. Now I'm lost and have to figure out how I was going to do this 6 months ago.

    Right now I am working on the MySQL query line for the search. You do a search and it has to bring up results that are older then one number, younger then the other, or if its a male that wants a female or the other way around (MSF,FSM,FSF,MSM), The area of the city (NW,NE,SW,SE,DT) and what type of relationship, like friends, photo exchange, e-mail/chat, etc......

    Now my problem.......... I sure hope someone out there can help me out here. But For the area of the city and relationsip, it can be looked for as 'Any' where it would show all the results, as long as they follow the age limits and what you are looking for (MSF, FSM, etc.)

    Basically I'm looking for a way to query the DB so that it will show everyone within the age limit and if any was chosen, there would be no limit to what would show after that.

    Ah, this is so complicated, I'm having a trouble explaining it. I sure hope someone understands and can at least help me.

    Thanks
    Mike

  2. #2
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll try

    SELECT * FROM table WHERE (((date + 0) > $low_date) and ((date + 0) < $high_date)) and (seek='$seek') and (area LIKE '%$area%') and ((age > $low_age) and (age < $high_age));

    Not that bad, eh? Just use parenthases.... always works, you know math? use then just like a equation, that's all we're working with here is just an advanced, abstract form of an equation. That should be VERY specific, but hopefully it will help you to step out of the box

    God Bless
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  3. #3
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    exbabylon you have a parenthesis fetish They are very useful even for aiding readability of code when they are not strictly needed. However, over use does slow down execution of the query. You could also write that well thought out SQL that you provide like so:

    SELECT *
    FROM table
    WHERE date > $low_date
    AND date < $high_date
    AND gender ='$gender',
    AND seek='$seek'
    AND area LIKE '%$area%'
    AND age > $low_age
    AND age < $high_age

    I have split the sql over several lines just to aid readablility here in the forum. To add further explaination to exbabylon's query:

    By default make $high_date 200 and $low_date 0 (or the min age allowed) and $area by default to be an empty string.

  4. #4
    SitePoint Addict manipura's Avatar
    Join Date
    Apr 2001
    Location
    Calgary,AB
    Posts
    345
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by exbabylon
    I'll try

    SELECT * FROM table WHERE (((date + 0) > $low_date) and ((date + 0) < $high_date)) and (seek='$seek') and (area LIKE '%$area%') and ((age > $low_age) and (age < $high_age));
    That isn't going to work if someone wants to find just anyone in the city though. If the area was searched for anywhere, then how would I do that? I want to make it so they can chose what part of the city a person should be in, or just give them the choice to look everywhere in the city. With no limits. And I don't have a date thing yet. Even though I don't understand what that date stuff is would you mind explaining?

    When I do a query these variables are there

    $age1 = No Yonger then
    $age2 = No Older then

    $seeking= Male seeking female(named 'MSF' in the DB) , etc.
    $area = NW (north west), NE, Etc...... Along with "ANY"
    $relation = relationship, hang-out, long term, short term, other, etc.....

    Now, Lets see if anyone can figure out how to do this properly.. Because I am sure I can't! Yet at least

  5. #5
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Exbabylon's example query handles the area issue. If you want to search for all areas then leave the value of $area empty. The SQL will then match all values of area in the database.

  6. #6
    SitePoint Addict manipura's Avatar
    Join Date
    Apr 2001
    Location
    Calgary,AB
    Posts
    345
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I got it almost working... It works well enough for me. I'm going to be adding more later anyways. Thanks for all your help guys!

  7. #7
    imagine no limitations exbabylon's Avatar
    Join Date
    Dec 2000
    Location
    Idaho, USA
    Posts
    452
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just read the MySQL reference through twice in like twenty four hours.. it helped me a lot, if you're going to be working with a lot of semi-complex stuff like that then I would suggest reading through it... SQL is your friend!

    But, as for the date....

    I got the impression from the first post that you wanted to be able to search through entries posted from date1 - date2... so I wrote it that way. The addition of the zero to the date puts the date into a INT only srting which can be used to add days, etc. in the case you're taling about just basic integers like freaky gave will work beautifully.

    God Bless
    Blamestorming: Sitting around in a group discussing why a deadline was missed or a project failed and who was responsible.

    Exbabylon- Professional Internet Services

  8. #8
    SitePoint Addict manipura's Avatar
    Join Date
    Apr 2001
    Location
    Calgary,AB
    Posts
    345
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, well this was a lot easier then expected. I thought I had it but there was something wrong with the code, but I just couldn't see what. So I wrote it again and again, and the more I did it, the less sense it made. But then I re-wrote it with a little bit of what you guys told me. With the %LIKE% operator and that helped me out a lot!

    Thanks again guys


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •