Jump to content


Photo

Delayed Writing Of Records


  • Please log in to reply
1 reply to this topic

#1 AV1611

AV1611
  • Members
  • PipPipPip
  • Advanced Member
  • 997 posts

Posted 27 June 2006 - 04:59 PM

ok,

got a table with several field I'll show three for this illustration:

SerNo step passed
11111 1 1
11111 2 1
11111 3 0
11111 4 0
11112 1 1
11112 2 1
11113 1 1
11113 2 1
11113 3 1
etc...

I am generating a csv file, but my problem is that I need to exclude any serial number that didn't pass on ANY step.

so, above, all 4 records for serialno 1111 would be excluded, but the other two would not.

here is my full script thus far, but it includes all serial numbers. the field above are serialno step passed

<?php
$i=0;
$t='\t';
echo "line".$t."it_date".$t."insptype".$t."process".$t."product".$t."serialno".$t."step".$t."step_desc".$t."hi".$t."lo".$t."units".$t."actual".$t."gonogo".$t."passed\n";
$connect = odbc_connect("QES9000", "", "");
$query = "SELECT
    itdatshta.`datshtid`, itdatshta.`sample`, itdatshta.`it_date`, itdatshta.`insptype`, itdatshta.`process`, itdatshta.`product`, itdatshta.`descr`, itdatshta.`serialno`, itdatshta.`failed`,
    itdatshtda.`datshtid`, itdatshtda.`sample`, itdatshtda.`descr`, itdatshtda.`hi`, itdatshtda.`lo`, itdatshtda.`units`, itdatshtda.`actual`, itdatshtda.`gonogo`, itdatshtda.`passfail`, itdatshtda.`step`, itdatshtda.`failed`, itdatshtda.`passed`
FROM
    `itdatshta` itdatshta INNER JOIN `itdatshtda` itdatshtda ON
        itdatshta.`datshtid` = itdatshtda.`datshtid` AND
    itdatshta.`sample` = itdatshtda.`sample`
WHERE
    (itdatshta.`process` = 'VQ Functional Outputs' OR
    itdatshta.`process` = 'VQ Functional Inputs' OR
    itdatshta.`process` = 'QA Evaluation' OR
    itdatshta.`process` = 'Functional Test') AND
    (itdatshta.`product` = 'TEKOS3UNIT1' OR
    itdatshta.`product` = 'TEKOS3UNIT' OR
    itdatshta.`product` = 'TEK.OS3.UNIT1' OR
    itdatshta.`product` = 'TEK.OS3.UNIT')
ORDER BY
    itdatshta.`it_date` ASC,
    itdatshta.`serialno` ASC,
    itdatshta.`process` ASC";

$result = odbc_do($connect, $query);
WHILE($row=odbc_fetch_array($result))
    {
    if($row['step']==1){
        $sn=$row['serialno'];
        }
    if($row['serialno']=='$sn' && $row['passed']==1)
    $i++;
    echo $i."\t".$row['it_date']."\t".$row['insptype']."\t".$row['process']."\t".$row['product']."\t".$row['serialno']."\t".$row['step']."\t".$row['descr']."\t".$row['hi']."\t".$row['lo']."\t".$row['units']."\t".$row['actual']."\t".$row['gonogo']."\t".$row['passed'];
    echo "\n";
    }
odbc_close($connect);

?>


#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 27 June 2006 - 07:32 PM

SELECT a.* FROM mytable a WHERE NOT EXISTS
(SELECT * FROM mytable b WHERE b.serialno = a.serialno
AND b.passed = 0)
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users