I retrieve three datasets with query1:to find new products,query2:products in sale,query3:popular products and i need to find if a products is new and in sale and also is popular or if a products only new or in sale etc. but since i limit my queries with 5 there is the possibility of not getting the same ID's at the same time.I also added the PHP code but it is logically wrong as i mentioned.What is the best of way doing that,i need your help.Thanks in advance Query1:
SELECT p.`ProductID`,`TypeID`,c.CompanyID as CID,c.`Name` as CN,p.`Name`,`Picture`,`Price`,s.`SalePrice`,Count(l.ProductID) as Likes,'1' as popular
FROM `Product` p inner join Company c on c.CompanyID=p.CompanyID inner join Likes l on l.ProductID=p.ProductID left outer join Sale s on s.ProductID=p.ProductID
where l.ts>date_sub(now(), INTERVAL 7 DAY) and c.CompanyID in(1,2,3) group by p.`ProductID`,`TypeID`,c.`Name`,p.`Name`,`Picture`,`Price`
order by Likes desc LIMIT 0,5
Query2:
SELECT p.`ProductID`,`TypeID`,c.CompanyID as CID,c.`Name` as CN,p.`Name`,`Picture`,`Price`,s.`SalePrice`,s.Endtime,s.Begintime,'1' as sale
FROM `Product` p inner join Company c on c.CompanyID=p.CompanyID inner join Sale s on s.ProductID=p.ProductID left outer join SaleSizeLimit sl on sl.SaleID=s.SaleID where now()>=s.Begintime and s.Endtime>=now() and c.CompanyID in(1,2,3)
order by s.Endtime asc LIMIT 0,5
Query3:
SELECT p.`ProductID`,`TypeID`,c.CompanyID as CID,c.`Name` as CN,p.`Name`,`Picture`,s.`SalePrice`,`Price`,'1' as new
FROM `Product` p inner join Company c on c.CompanyID=p.CompanyID left outer join Sale s on s.ProductID=p.ProductID where p.ts>date_sub(now(), INTERVAL 7 DAY) and c.CompanyID in(1,2,3) order by p.`ProductID` desc LIMIT 0,5
PHP CODE:
function Discriminate($set1,$set2,$set3) { // popular,sale,new
$drt=array();
$drv=0;
if(count($set1)>0){
$drv=1;
$drt=$set1;
if (count($set2)>0){
foreach ($set2 as $p2) {
$interim=0;
$i=0;
foreach ($set1 as $p1) {
if($p1->ProductID==$p2->ProductID){
$drt[$i]->Sale=1;
$interim=1;
}
$i++;
}
if($interim==0){
$drt[]=$p2;
}
}
}
$seti=$drt;
if (count($set3)>0){
foreach ($set3 as $p3) {
$interim=0;
$i=0;
foreach ($seti as $pi) {
if($pi->ProductID==$p3->ProductID){
$drt[$i]->New=1;
$interim=1;
}
$i++;
}
if($interim==0){
$drt[]=$p3;
}
}
}
}
else{ // there is no new product in this company
if(count($set2)>0){
$drv=1;
$drt=$set2;
if (count($set3)>0){
foreach ($set3 as $p3) {
$interim=0;
$i=0;
foreach ($set2 as $p2) {
if($p2->ProductID==$p3->ProductID){
$drt[$i]->New=1;
$interim=1;
}
$i++;
}
if($interim==0){
$drt[]=$p2;
}
}
}
}
else{ // there is no sale in this company
if(count($set3)>0){
$drv=1;
$drt=$set3;
}
else{ // there is no popular product in this company
$drv=0;
}
}
}
if($drv==1){
return $drt;
}
else{
return 0;
}
} // end discrimination
