array_intersect() problem

edwardp

I am running two queries, originally i was trying to run one as a subquery but i wasnt getting the expected results.

Query 1:

 SELECT a.PART_ID, b.DESCRIPTION, count(a.PART_ID) as number
 FROM CUST_ORDER_LINE as a, PART as b
 WHERE (a.PART_ID = b.ID) AND a.PART_ID NOT LIKE '%FIN%' AND a.PART_ID NOT LIKE 'AFT%' AND a.PART_ID NOT LIKE 'CI%' AND a.PART_ID NOT LIKE '%D&M%' AND a.PART_ID NOT LIKE 'PRECHA%' AND a.PART_ID NOT LIKE 'works van'
 GROUP BY a.PART_ID, b.DESCRIPTION
 ORDER BY a.PART_ID asc

This query selects all parts ordered and counts them so i can create a 'Top 50' of all ordered parts.

Query 2 -

  SELECT  PART.ID, PART.DESCRIPTION
  FROM dbo.PART, dbo.REQUIREMENT, dbo.OPERATION
  WHERE PART.ID =  REQUIREMENT.PART_ID AND  OPERATION.WORKORDER_BASE_ID =  REQUIREMENT.WORKORDER_BASE_ID AND  OPERATION.WORKORDER_LOT_ID =  REQUIREMENT.WORKORDER_LOT_ID AND  OPERATION.WORKORDER_SPLIT_ID =  REQUIREMENT.WORKORDER_SPLIT_ID AND  OPERATION.WORKORDER_SUB_ID =  REQUIREMENT.WORKORDER_SUB_ID AND  OPERATION.WORKORDER_TYPE =  REQUIREMENT.WORKORDER_TYPE AND ( PART.ID Like '[0-9][0-9][a-z][a-z][a-z][0-9][0-9][0-9][0-9]' and PART.ID NOT Like '[0-9][0-9]SDY[0-9][0-9][0-9][0-9]' and PART.ID NOT Like '[0-9][0-9]SHT[0-9][0-9][0-9][0-9]' AND PART.ID NOT Like '[0-9][0-9]TIM[0-9][0-9][0-9][0-9]' AND (OPERATION.WORKORDER_BASE_ID Like '8029%')) 

 ORDER BY PART.ID asc

This query creates a parts list for a vehicle.

What i would i like to do its reduce the first query to records which occur in the second query.

I am using an MSSQL server and have used mssql_query() to execute both queries. I then got the resulting array and tried to use the following:

   $result3 =  array_intersect($resultQuery1, $resultQuery2) to reduce the lists but i keep getting the following error:


Query1 returns 1330 results, and query2 returns 348 results

edwardp

I would like to reduce the results from query 1 by common elements returned from query 2 by using the array_intersect($resultQuery1, $resultQuery2) but i get the following error

Warning: Argument #1 to array_intersect() is not an array in c:\apache\htdocs\project2\test4.php on line 16

Any ideas??

tsinka

Hi,

how did you create the two arrays ?

Thomas

edwardp

$resultQuery1 = mssql_query($query1);
$resultQuery2 = mssql_query($query2);

then tried:

$result3 = array_intersect($resultQuery1, $resultQuery2);

tsinka

Ok,

that doesn't work. mssql_query returns the result resource identifier. You need to walk through the result set (mssql_fetch_array,mssql_fetch_row or msssql_fetch_assoc) and to create the arrays yourself. Another problem is that the number of columns in the result sets are not equal (3 in first query, 2 in second query), so array_intersect will not work. I think the best way would be to find the correct SQL query that does what you want to do with two queries. How did the SUBQUERY SQL query look like that didn't work ?

Thomas

edwardp

I have merged my queries thus:

SELECT PART.ID, PART.DESCRIPTION
FROM PART, REQUIREMENT, OPERATION
WHERE PART.ID IN (SELECT a.PART_ID FROM CUST_ORDER_LINE as a) and
PART.ID = REQUIREMENT.PART_ID AND
OPERATION.WORKORDER_BASE_ID = REQUIREMENT.WORKORDER_BASE_ID AND
OPERATION.WORKORDER_LOT_ID = REQUIREMENT.WORKORDER_LOT_ID AND
OPERATION.WORKORDER_SPLIT_ID = REQUIREMENT.WORKORDER_SPLIT_ID AND
OPERATION.WORKORDER_SUB_ID = REQUIREMENT.WORKORDER_SUB_ID AND
OPERATION.WORKORDER_TYPE = REQUIREMENT.WORKORDER_TYPE AND
PART.ID Like '[0-9][0-9][a-z][a-z][a-z][0-9][0-9][0-9][0-9]' and
PART.ID NOT Like '[0-9][0-9]SDY[0-9][0-9][0-9][0-9]' and
PART.ID NOT Like '[0-9][0-9]SHT[0-9][0-9][0-9][0-9]' AND
PART.ID NOT Like '[0-9][0-9]TIM[0-9][0-9][0-9][0-9]' AND
PART.ID NOT LIKE '%FIN%' AND
PART.ID NOT LIKE 'AFT%' AND
PART.ID NOT LIKE 'CI%' AND
PART.ID NOT LIKE '%D&M%' AND
PART.ID NOT LIKE 'PRECHA%' AND
PART.ID NOT LIKE 'works van' AND
OPERATION.WORKORDER_BASE_ID Like '8029%'
ORDER BY PART.ID;

But i would like to use count in the sub query like this:

(SELECT a.PART_ID, count(a.PART_ID) as number FROM CUST_ORDER_LINE as a GROUP BY a.PART_ID ORDER BY number)

how can i retrieve the number field, this is the number of orders for a part_id and would like to use this later?

any ideas