Page 1 of 1

small query problem

Posted: Tue Apr 29, 2008 9:36 am
by davejog
Hi,

I know it's a generic data, but it will help simplify the question:

table with fields: ID (int, auto increment), name (varchar), price (int), company (int)

lets assume there are around 300 rows in this table.

company can be 1,2,3,4

is there a way to query the following:

shuffle all the data and return 4 results where 'company'=1 and 5 results where 'company'=2 and 3 results where 'company'=3 and 1 result where 'company'=4

I need a total of 4+5+3+1=13 rows, and to order them randomly (so it wont look like: company1,company1,company1,company1,company2,company2,company2...

any help is appreciated!

thanks!!!

Re: small query problem

Posted: Tue Apr 29, 2008 2:22 pm
by RobertGonzalez
The way that looks like is that you will have to run a union query four times over then shuffle the result code side.

Re: small query problem

Posted: Wed Apr 30, 2008 3:49 am
by davejog
what if I don't need it shuffled, only get x results with company=1, y results with company=2 and z results with company=3?

Re: small query problem

Posted: Wed Apr 30, 2008 4:00 am
by VladSun
What have you tried so far ...
Anyway ...

Code: Select all

function my_query($params)
{
    $sql = array();
    foreach ($params as $company => $times)
        $sql[] =    "   
                        SELECT 
                            *
                        FROM
                            mytable
                        WHERE
                            company = ".$company."
                        LIMIT
                            ".$times." 
                    ";
    return implode("UNION", $sql);
}

Re: small query problem

Posted: Wed Apr 30, 2008 4:17 am
by davejog
this means I will have to query the db several times, or the union will connect everything?

Re: small query problem

Posted: Wed Apr 30, 2008 4:34 am
by VladSun
VladSun wrote:What have you tried so far ...

Re: small query problem

Posted: Wed Apr 30, 2008 6:04 am
by davejog
nothing, I have a very little experience with mysql/sql I'm kinda shooting in the dark here...

Re: small query problem

Posted: Wed Apr 30, 2008 11:16 am
by RobertGonzalez
UNION will take a query and join that data set to another query (essentially - this is really a short hand description).