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!!!
small query problem
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: small query problem
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
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
What have you tried so far ...
Anyway ...
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
Re: small query problem
this means I will have to query the db several times, or the union will connect everything?
Re: small query problem
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
Re: small query problem
nothing, I have a very little experience with mysql/sql I'm kinda shooting in the dark here...
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
Re: small query problem
UNION will take a query and join that data set to another query (essentially - this is really a short hand description).