SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    May 2003
    Location
    New York
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mySQL SELECT in two tables

    I have two tables members and ads, members table has auto_increment 'ID' , 'CREDIT' and some members information.
    ads table has 'AID' auto_increment and 'ID' to track who's link is that

    Basically 1 member can have up to 5 link ads. I want to get from query LIMIT 5 ads who has enough credits and not from same members ID

    I'm using php have 4000 members row and growing

    how can i design it

  2. #2
    SitePoint Member
    Join Date
    Mar 2003
    Location
    Greensboro, NC
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK if I'm understanding what you want to do, something like below should work for ya.

    SELECT m.id,a.aid,a.id FROM members as m, ads as a WHERE m.id = a.id LIMIT 5

    Hope that helps.

    -David
    Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.
    --Rich Cook

  3. #3
    SitePoint Member
    Join Date
    May 2003
    Location
    New York
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    members table
    ---------------------------------
    ID , CREDIT , fname , lname
    1 , 24 , jon , smith
    2 , 50 , mark , tween
    3 , 23 , sam , jonson
    4 , 61 , dave , derian
    5 , 28 , gio , fery
    6 , 95 , anna , lopez

    ads table
    --------------------------------
    AID , ID , ACTIVE
    1 , 1 , Y
    2 , 1 , Y
    3 , 1 , Y
    4 , 2 , Y
    5 , 2 , N
    6 , 2 , Y
    7 , 3 , Y
    8 , 3 , Y
    9 , 3 , Y
    10 , 4 , N
    11 , 4 , Y
    12 , 4 , Y



    members can have few ads few rows in ads table. I want to get LIMIT 5 ads from different member randomly every time different ad. Lets say if 1 member has 3 ads get 1 ad randomly from each member LIMIT 5 times

    i'm using php4 mysql3.23


    this one works but problem is it gives me a first ad in group always, it doesn't randomize

    SELECT * FROM members m inner join ads a ON m.ID=a.ID WHERE CRED>1 GROUP BY m.ID ORDER BY RAND() LIMIT 5


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
  •