Results 1 to 9 of 9

Thread: Php sql help

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2007
    Posts
    912

    Php sql help

    i have a table called tbl_questions

    in this table i have a field called Category

    and this category field contain value like

    A
    B
    C
    D
    E
    2
    3
    4
    5
    55
    ZA
    GF
    KO
    PL
    4F

    My question is if i want to

    select 5 records from category B
    select 10 records from category 55
    select 8 records from category J

    So what should be my SQL query?

    Do i need to use UNION? Or there is any option smaller option in query?

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Php sql help

    so you have two tables? Categories and tbl_Questions?

    what's in the Questions table? What does it look like?


    Also, this is more of a SQL question so I'm going to ask the mods to move it there.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Php sql help

    Moderator Action: Moved thread to Database Development.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  4. #4

    Thread Starter
    Fanatic Member
    Join Date
    Aug 2007
    Posts
    912

    Re: Php sql help

    No there is only single table tblquestions

    It has few columns like
    Question
    Title
    Etc...
    But column category contain 1 category in one record...
    Like
    A
    B
    M
    K
    P
    O
    U
    4
    5
    6
    7
    77
    33
    4r

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Php sql help

    so,,, what's the problem... select your questions based on WHERE Category = what ever category type you want.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: Php sql help

    if you want to have all 23 questions to be returned in one sql then yes, you have to use top x union with three different where clauses. it sounds like you prob. want RANDOM questions returned, then there is also a way where you order by a random number. i do not need this often nor have i ever used it in mysql so you would need to google that.

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Php sql help

    RANDOM questions
    The easiest, and fairly counter intuitive way, is to order by a NewID(). Because a NewID gives you a random guid it gives you a random order.

    So you want something like (nb typed straight in so beware of syntax errors):-
    Code:
    Select Question, Title
    From (Select Top 5 Question, Title
        From tbl_Questions
        Where Category = 'B'
        Order By NewID())
    Union All
    Select Question, Title
    From (Select Top 10 Question, Title
        From tbl_Questions
        Where Category = '55'
        Order By NewID())
    Union AllSelect Question, Title
    From (Select Top 8 Question, Title
        From tbl_Questions
        Where Category = 'J'
        Order By NewID())
    Order By NewID()
    You need the inner selects because you can't apply an order by to the the indivdual parts of a union, only the overall set. And I've assumed you'd want the order of the overall set randomized as well, if not drop the last OrderBy.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Php sql help

    Actually... before that... we need to know what the database system is. Is it SQLServer or MySQL? Since this was originally in the PHP section, I'm guessing it's more likely to be MySQL, which means instead of TOP, a LIMIT would need to be used. Also I'm not sure newId is available in MySQL, I'm sure there is SOME fuinction available to do the same thing, just not specifically newid().


    That said, the solution would be fine if all you ever need is three sections. What if you need 2, or 5?

    I'd use a single query that returned rows for a single category. Then pass in the row limit count and the category as a parameter... then if I need to run it for two categories, cal it twice, once for one category, and another time for hte other category.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Php sql help

    we need to know what the database system is
    Good Point! I didn't think to stop and check.

    I don't think it would be possible to write a single query to resolve the whole problem if there was a variable number of categories (though a bit of me is wondering if you couldn't do something with a cte - just can't seem to formulate it myself). If that was the requirement I think I'd agree with TG, write it as a table valued function and call it from an sp.

    If you do want to randomize the result and if you are using sql server you might also want to look at the TABLESAMPLE keyword. It's not truly random but it's likely random enough and it's more performant over large data sets.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

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



Click Here to Expand Forum to Full Width