Help Needed

[bodycount]

I want to run the same query's for multiple product so I have decided to do it in the following way. I not a code writer by trade which you will problery see from the code below. What I want to know is am I going about this the wrong way? also please note that there will be upto 22 query's per product and I have only listed to products and two query's

/****************************************
/*
/* Get list of products
/*
/****************************************

$query1 = "SELECT distinct `COMTECHMODULE` FROM rmatable order by `COMTECHMODULE`";
$result1 = mysql_query($query1) or die(mysql_error());

while($row = mysql_fetch_array($result1))
{

$COMTECHMODULE = $row['COMTECHMODULE'];

/****************************************
/*
/* Get Info For FG000045
/*
/****************************************
if ($COMTECHMODULE == "FG000045")
{
echo "<fieldset><legend>$COMTECHMODULE</legend>";

/****************************************
/*
/* Count number of FG000045 on system
/*
/****************************************

$query2 = "SELECT `COMTECHMODULE`,COUNT(COMTECHMODULE) FROM rmatable where COMTECHMODULE = '$COMTECHMODULE' GROUP BY COMTECHMODULE";
$result2 = mysql_query($query2) or die(mysql_error());

while($row = mysql_fetch_array($result2))
{
$COMTECHMODULE =$row['COMTECHMODULE'];
$COUNT =$row['COUNT(COMTECHMODULE)'];
}

/****************************************
/*
/* Count number of MANUFAULT for FG000045
/*
/****************************************

$query3 = "SELECT COUNT(MANUFAULT) FROM rmatable where COMTECHMODULE = '$COMTECHMODULE' and MANUFAULT = 'YES' GROUP BY MANUFAULT";
$result3 = mysql_query($query3) or die(mysql_error());

while($row = mysql_fetch_array($result3))
{
$MANUFAULT =$row['MANUFAULT'];
}

echo "$COMTECHMODULE<br>";
echo "$COUNT<br>";
echo "$MANUFAULT<br>";
echo "</fieldset>";
}

/****************************************
/*
/* Get Info For FG000046
/*
/****************************************

if ($COMTECHMODULE == "FG000046")
{
echo "<fieldset><legend>$COMTECHMODULE</legend>";

/****************************************
/*
/* Count number of FG000046 on system
/*
/****************************************
$query2 = "SELECT `COMTECHMODULE`,COUNT(COMTECHMODULE) FROM rmatable where COMTECHMODULE = '$COMTECHMODULE' GROUP BY COMTECHMODULE";
$result2 = mysql_query($query2) or die(mysql_error());

while($row = mysql_fetch_array($result2))
{
$COMTECHMODULE =$row['COMTECHMODULE'];
$COUNT =$row['COUNT(COMTECHMODULE)'];
}

/****************************************
/*
/* Count number of MANUFAULT for FG000046
/*
/****************************************

$query3 = "SELECT COUNT(MANUFAULT) FROM rmatable where COMTECHMODULE = '$COMTECHMODULE' and MANUFAULT = 'YES' GROUP BY MANUFAULT";
$result3 = mysql_query($query3) or die(mysql_error());

while($row = mysql_fetch_array($result3))
{
$MANUFAULT =$row['MANUFAULT'];
}

echo "$COMTECHMODULE<br>";
echo "$COUNT<br>";
echo "$MANUFAULT<br>";
echo "</fieldset>";
}
}

[cameraman]

First off, you've done a pretty fair job considering you're not a code writer. One thing to note is that when you know you're only going to get one row back from a query, for example in this case:

$query2 = "SELECT `COMTECHMODULE`,COUNT(COMTECHMODULE) FROM rmatable where COMTECHMODULE = '$COMTECHMODULE' GROUP BY COMTECHMODULE";
$result2 = mysql_query($query2) or die(mysql_error());

you don't have to use a while loop to get the data - just do this:
$row = mysql_fetch_array($result2)

Next, this isn't too bad a way of doing it, but you don't want to handle each product specifically by name; since your query makes use of the value you got from your first query and you're using a variable to echo it to the browser, you've already set yourself up to handle whatever products get thrown at you. I know I haven't stated that well, so maybe this pseudocode will help illustrate:

$query = "get me each of my product names";
while($product_name) {
$other_query = "specifics for $product_name";
echo $product_name;
echo $count;
echo $manu_fault;
}

See? This is how you've already set it up - very good job. So you don't have to have a section for each and every product, the one bit will handle it.

If you did it that way because you're only interested in reporting on a few products out of many, it would be easier to list them in an array:
$productlist = array('FG000045','FG000046','others');

Then you can enclose your echos in an if block that checks to see if it's a product you want to report:
$query = "get me each of my product names";
while($product_name) {
if(in_array [php.net]($product_name,$productlist) {
$other_query = "specifics for $product_name";
echo $product_name;
echo $count;
echo $manu_fault;
} // EndIf product is in my list
} // EndWhile all the products

That's not necessarily the most efficient way to do it, but it's a lot better than having a separate block to do the same thing for each of a bunch of products.

Finally, there's an even better way to do it. mySQL is a very powerful database engine. As it turns out, it can do just about all your work in one sql statement:
select COMTECHMODULE, count(COMTECHMODULE) as modulecount, (select count(MANUFAULT) from rmatable as rma where rma.COMTECHMODULE=r.COMTECHMODULE and rma.MANUFAULT='YES') as faultcount FROM rmatable as r group by COMTECHMODULE

So to try that out, do the query and set up a while loop just like you did in your post. Don't worry about making it pretty yet, just echo the fields COMTECHMODULE, modulecount, and faultcount (you'll probably want to echo a <br> after each one).

edit - made mistake on field name

[bodycount]

Thanks cameraman

I used the last method and it worked very well, so thanks again.