SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: sort in a join

  1. #1
    SitePoint Member atem451's Avatar
    Join Date
    May 2001
    Location
    Berlin
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sort in a join

    Hi.


    I got 2 tables. Let's say seminare and lehrgaenge.
    both contain a field called beginn (Type DATE)
    , ort (text), title (text) and some other stuff.

    I want to show all events for one month from both tables in one list, sorted by different criteria (title, place or date).

    I just don't know how to build a join, which gives me
    all events for the one month, sorted by the give criteria.

    my Idea was something like this:


    $query="

    select seminare.id as sid, seminare.beginn as beginn, seminare.titel as titel, seminare.inhalt_kurz as sinhalt_kurz, seminare.ort as ort, lehrgaenge.id as lid, lehrgaenge.beginn as beginn, lehrgaenge.titel as titel, lehrgaenge.inhalt_kurz as linhalt_kurz, lehrgaenge.ort as sort from lehrgaenge, seminare where YEAR(beginn) = '$year' and MONTH(beginn)=$month order by $sort

    ";


    $sort contains beginn, titel or ort.


    I just don't think that it works.
    I think I have to use a crossjoin.

    Anybody got some ideas how to build a smart join that can perform this?
    Footbag: Spread the Spirit

  2. #2
    SitePoint Wizard TWTCommish's Avatar
    Join Date
    Aug 1999
    Location
    Pittsburgh, PA, USA
    Posts
    3,910
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Trying adding "ASC" or "DESC" after the $sort variable. IE: ORDER BY $sort ASC.

  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)
    It sounds like what you want is a UNION query which MySQL does not support. There are work-arounds, and while I don't have first hand experience, I do remember that this has been discussed on the boards a few times. You could do a search on "MySQL AND UNION" in this forum and read past threads on this. I do recall that the manual suggests creating a temporary table and inserting the data from the two different tables into the temporary table, querying it, then dropping the temporary table.

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://mysql.he.net/documentation/my...nce.html#UNION

    Interesting. When will MySQL 4 be out?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  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)
    wow Freddy

    You know what this could mean though? This suggests to me that maybe mysql 4 will impliment sub-queries. Yippee! Thanks for the link.

  6. #6
    SitePoint Evangelist CyberFuture's Avatar
    Join Date
    May 2001
    Location
    San Diego, CA
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    VERY COOL

    Thanks Freedy!

    Does anybody know when version 4 will be out?


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
  •