small query problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
davejog
Forum Newbie
Posts: 4
Joined: Sun Apr 20, 2008 1:03 am

small query problem

Post 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!!!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: small query problem

Post 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.
davejog
Forum Newbie
Posts: 4
Joined: Sun Apr 20, 2008 1:03 am

Re: small query problem

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: small query problem

Post 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);
}
There are 10 types of people in this world, those who understand binary and those who don't
davejog
Forum Newbie
Posts: 4
Joined: Sun Apr 20, 2008 1:03 am

Re: small query problem

Post by davejog »

this means I will have to query the db several times, or the union will connect everything?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: small query problem

Post by VladSun »

VladSun wrote:What have you tried so far ...
There are 10 types of people in this world, those who understand binary and those who don't
davejog
Forum Newbie
Posts: 4
Joined: Sun Apr 20, 2008 1:03 am

Re: small query problem

Post by davejog »

nothing, I have a very little experience with mysql/sql I'm kinda shooting in the dark here...
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: small query problem

Post by RobertGonzalez »

UNION will take a query and join that data set to another query (essentially - this is really a short hand description).
Post Reply