martin69
asked on
PHP Mysql matching data loop until ended
I have a database which has Work Orders each work order has a parent so say WO 123 as a parent of 234. but then you will get children of children etc. what i have got with my code so far is it works for two levels to main work order number searched for then the child but for children of children. is there a way to make it loop until there is no parents so its a lone work order. plus you will see from the code i also need this WO number help for it to check Timewrite PO and SMS tables. hope i make sense. its creating a csv by the way so can be saved as text and put into excel
Wonum,Desc,Status,RepBy,Parent,Eqnum,EqDesc,Location,LocDesc,JobOwner,WorkType,Tstart,TComp,acctperson,Estimate,ApproveHoldStop,Initial
<?php
include ("include.php");
$query0111 = "SELECT * FROM ineos_evwo WHERE Wonum = '$woid'";
$result0111 = mysql_query($query0111) or die("Error: " . mysql_error());
$count0111 = mysql_num_rows($result0111);
while($row0111 = mysql_fetch_array($result0111)){
?>
<? echo $row0111['Wonum'];?>,<? echo'"';echo $row0111['description'];echo'"';?>,<? echo $row0111['Status'];?>,<? echo $row0111['RepBy'];?>,<? echo $row0111['Parent'];?>,<? echo $row0111['Eqnum'];?>,<? echo'"';echo $row0111['EqDesc'];echo'"';?>,<? echo $row0111['Location'];?>,<? echo'"';echo $row0111['LocDesc'];echo'"';?>,<? echo $row0111['JobOwner'];?>,<? echo $row0111['WorkType'];?>,<? echo $row0111['Tstart'];?>,<? echo $row0111['TComp'];?>,<? echo $row0111['acctperson'];?>,<? echo $row0111['Estimate'];?>,<? echo $row0111['ApproveHoldStop'];?>,<? echo $row0111['Initial'];$cr = "\n";print"$cr";?><?php } ?>
<?php
include ("include.php");
$query = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
?>
<? echo $row['Wonum'];?>,<? echo'"';echo $row['description'];echo'"';?>,<? echo $row['Status'];?>,<? echo $row['RepBy'];?>,<? echo $row['Parent'];?>,<? echo $row['Eqnum'];?>,<? echo'"';echo $row['EqDesc'];echo'"';?>,<? echo $row['Location'];?>,<? echo'"';echo $row['LocDesc'];echo'"';?>,<? echo $row['JobOwner'];?>,<? echo $row['WorkType'];?>,<? echo $row['Tstart'];?>,<? echo $row['TComp'];?>,<? echo $row['acctperson'];?>,<? echo $row['Estimate'];?>,<? echo $row['ApproveHoldStop'];?>,<? echo $row['Initial'];$cr = "\n";print"$cr";?><?php } ?>
<?php
include ("include.php");
$query22 = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result22 = mysql_query($query22) or die("Error: " . mysql_error());
$count22 = mysql_num_rows($result22);
while($row22 = mysql_fetch_array($result22)){
?><? $woid00 = $row22['Wonum']; ?>
<?php
include ("include.php");
$query = "SELECT * FROM ineos_evwo WHERE Parent = '$woid00'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
?>
<? echo $row['Wonum'];?>,<? echo'"';echo $row['description'];echo'"';?>,<? echo $row['Status'];?>,<? echo $row['RepBy'];?>,<? echo $row['Parent'];?>,<? echo $row['Eqnum'];?>,<? echo'"';echo $row['EqDesc'];echo'"';?>,<? echo $row['Location'];?>,<? echo'"';echo $row['LocDesc'];echo'"';?>,<? echo $row['JobOwner'];?>,<? echo $row['WorkType'];?>,<? echo $row['Tstart'];?>,<? echo $row['TComp'];?>,<? echo $row['acctperson'];?>,<? echo $row['Estimate'];?>,<? echo $row['ApproveHoldStop'];?>,<? echo $row['Initial'];$cr = "\n";print"$cr";?><?php } } ?>
PO No,P Line,Desc,Issue Type,Post Date,Qty,Unit Cost,Line Cost,WONUM,WO Status,WO Rep By,Invoice No,GL Debit,BP/Ineos
<?php
include ("include.php");
$query01 = "SELECT * FROM ineos_pos WHERE WONUM = '$woid'";
$result01 = mysql_query($query01) or die("Error: " . mysql_error());
$count01 = mysql_num_rows($result01);
while($row01 = mysql_fetch_array($result01)){
?>
<? echo $row01['PONUM'];?>,<? echo $row01['PLINE'];?>,<? echo'"';echo $row01['DESCRIPTION'];echo'"';?>,<? echo $row01['ISSUETYPE'];?>,<? echo $row01['POSTINGDATE'];?>,<? echo $row01['QUANTITY'];?>,<? echo $row01['UNITCOST'];?>,<? echo $row01['LINECOST'];?>,<? echo $row01['WONUM'];?>,<? echo $row01['WOSTATUS'];?>,<? echo $row01['WO_REPORTEDBY'];?>,<? echo $row01['INVOICENUM'];?>,<? echo $row01['GLDEBITACCT'];?>,<? echo $row01['BPineos'];$cr = "\n";print"$cr";?><?php } ?>
<?php
include ("include.php");
$query = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
?><? $woid0 = $row['Wonum']; ?>
<?php
include ("include.php");
$query0 = "SELECT * FROM ineos_pos WHERE WONUM = '$woid0'";
$result0 = mysql_query($query0) or die("Error: " . mysql_error());
$count0 = mysql_num_rows($result0);
while($row0 = mysql_fetch_array($result0)){
?>
<? echo $row0['PONUM'];?>,<? echo $row0['PLINE'];?>,<? echo'"';echo $row0['DESCRIPTION'];echo'"';?>,<? echo $row0['ISSUETYPE'];?>,<? echo $row0['POSTINGDATE'];?>,<? echo $row0['QUANTITY'];?>,<? echo $row0['UNITCOST'];?>,<? echo $row0['LINECOST'];?>,<? echo $row0['WONUM'];?>,<? echo $row0['WOSTATUS'];?>,<? echo $row0['WO_REPORTEDBY'];?>,<? echo $row0['INVOICENUM'];?>,<? echo $row0['GLDEBITACCT'];?>,<? echo $row0['BPineos'];$cr = "\n";print"$cr";?><?php } } ?>
<?php
include ("include.php");
$query222 = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result222 = mysql_query($query222) or die("Error: " . mysql_error());
$count222 = mysql_num_rows($result222);
while($row222 = mysql_fetch_array($result222)){
?><? $woid00 = $row222['Wonum']; ?>
<?php
include ("include.php");
$query333 = "SELECT * FROM ineos_evwo WHERE Parent = '$woid00'";
$result333 = mysql_query($query333) or die("Error: " . mysql_error());
$count333 = mysql_num_rows($result333);
while($row333 = mysql_fetch_array($result333)){
?><? $woid0011 = $row333['Wonum']; ?>
<?php
include ("include.php");
$query0 = "SELECT * FROM ineos_pos WHERE WONUM = '$woid0011'";
$result0 = mysql_query($query0) or die("Error: " . mysql_error());
$count0 = mysql_num_rows($result0);
while($row0 = mysql_fetch_array($result0)){
?>
<? echo $row0['PONUM'];?>,<? echo $row0['PLINE'];?>,<? echo'"';echo $row0['DESCRIPTION'];echo'"';?>,<? echo $row0['ISSUETYPE'];?>,<? echo $row0['POSTINGDATE'];?>,<? echo $row0['QUANTITY'];?>,<? echo $row0['UNITCOST'];?>,<? echo $row0['LINECOST'];?>,<? echo $row0['WONUM'];?>,<? echo $row0['WOSTATUS'];?>,<? echo $row0['WO_REPORTEDBY'];?>,<? echo $row0['INVOICENUM'];?>,<? echo $row0['GLDEBITACCT'];?>,<? echo $row0['BPineos'];$cr = "\n";print"$cr";?><?php } } } ?>
WONUM,DESCRIPTION,LOCLEVEL2,LEADCRAFT,GLACCOUNT,MATLS,BIS,SHIRE,UNSCOPED,PREMIUM,LSUM_LABR,LSUM_MATL,US_FLAG,UNSCOPED_HRS,LSUM_HRS,BPIneos,Ineos,BP
<?php
include ("include.php");
$query11 = "SELECT * FROM ineos_sms WHERE WONUM = '$woid'";
$result11 = mysql_query($query11) or die("Error: " . mysql_error());
$count11 = mysql_num_rows($result11);
while($row11 = mysql_fetch_array($result11)){
?>
<? echo $row11['WONUM'];?>,<? echo'"';echo $row11['DESCRIPTION'];echo'"';?>,<? echo $row11['LOCLEVEL2'];?>,<? echo $row11['LEADCRAFT'];?>,<? echo $row11['GLACCOUNT'];?>,<? echo $row11['MATLS'];?>,<? echo $row11['BIS'];?>,<? echo $row11['SHIRE'];?>,<? echo $row11['UNSCOPED'];?>,<? echo $row11['PREMIUM'];?>,<? echo $row11['LSUM_LABR'];?>,<? echo $row11['LSUM_MATL'];?>,<? echo $row11['US_FLAG'];?>,<? echo $row11['UNSCOPED_HRS'];?>,<? echo $row11['LSUM_HRS'];?>,<? echo $row11['BPIneos'];?>,<? echo $row11['Ineos'];?>,<? echo $row11['BP'];$cr = "\n";print"$cr";?><?php }?>
<?php
include ("include.php");
$query = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
?> <? $woid1 = $row['Wonum']; ?>
<?php
include ("include.php");
$query1 = "SELECT * FROM ineos_sms WHERE WONUM = '$woid1'";
$result1 = mysql_query($query1) or die("Error: " . mysql_error());
$count1 = mysql_num_rows($result1);
while($row1 = mysql_fetch_array($result1)){
?>
<? echo $row1['WONUM'];?>,<? echo'"';echo $row1['DESCRIPTION'];echo'"';?>,<? echo $row1['LOCLEVEL2'];?>,<? echo $row1['LEADCRAFT'];?>,<? echo $row1['GLACCOUNT'];?>,<? echo $row1['MATLS'];?>,<? echo $row1['BIS'];?>,<? echo $row1['SHIRE'];?>,<? echo $row1['UNSCOPED'];?>,<? echo $row1['PREMIUM'];?>,<? echo $row1['LSUM_LABR'];?>,<? echo $row1['LSUM_MATL'];?>,<? echo $row1['US_FLAG'];?>,<? echo $row1['UNSCOPED_HRS'];?>,<? echo $row1['LSUM_HRS'];?>,<? echo $row1['BPIneos'];?>,<? echo $row1['Ineos'];?>,<? echo $row1['BP'];$cr = "\n";print"$cr";?><?php } }?>
<?php
include ("include.php");
$query222 = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result222 = mysql_query($query222) or die("Error: " . mysql_error());
$count222 = mysql_num_rows($result222);
while($row222 = mysql_fetch_array($result222)){
?><? $woid00 = $row222['Wonum']; ?>
<?php
include ("include.php");
$query333 = "SELECT * FROM ineos_evwo WHERE Parent = '$woid00'";
$result333 = mysql_query($query333) or die("Error: " . mysql_error());
$count333 = mysql_num_rows($result333);
while($row333 = mysql_fetch_array($result333)){
?><? $woid0011 = $row333['Wonum']; ?>
<?php
include ("include.php");
$query1 = "SELECT * FROM ineos_sms WHERE WONUM = '$woid0011'";
$result1 = mysql_query($query1) or die("Error: " . mysql_error());
$count1 = mysql_num_rows($result1);
while($row1 = mysql_fetch_array($result1)){
?>
<? echo $row1['WONUM'];?>,<? echo'"';echo $row1['DESCRIPTION'];echo'"';?>,<? echo $row1['LOCLEVEL2'];?>,<? echo $row1['LEADCRAFT'];?>,<? echo $row1['GLACCOUNT'];?>,<? echo $row1['MATLS'];?>,<? echo $row1['BIS'];?>,<? echo $row1['SHIRE'];?>,<? echo $row1['UNSCOPED'];?>,<? echo $row1['PREMIUM'];?>,<? echo $row1['LSUM_LABR'];?>,<? echo $row1['LSUM_MATL'];?>,<? echo $row1['US_FLAG'];?>,<? echo $row1['UNSCOPED_HRS'];?>,<? echo $row1['LSUM_HRS'];?>,<? echo $row1['BPIneos'];?>,<? echo $row1['Ineos'];?>,<? echo $row1['BP'];$cr = "\n";print"$cr";?><?php } } }?>
Warehouse,ISSUETYPE,ITEMNUM,ITEMDESCRIPTION,WONUM,REPORTEDBY,WONSTATUS,WONDESCRIPTION,POSTINGDATE,GLCREDIT,GLDEBITACCT,QUANTITY,UNITCOST,LINECOST,LOCATION,EQNUM,IneosBP
<?php
include ("include.php");
$query22 = "SELECT * FROM ineos_stores WHERE WONUM = '$woid'";
$result22 = mysql_query($query22) or die("Error: " . mysql_error());
$count22 = mysql_num_rows($result22);
while($row22 = mysql_fetch_array($result22)){
?>
<? echo $row22['Warehouse'];?>,<? echo $row22['ISSUETYPE'];?>,<? echo $row22['ITEMNUM'];?>,<? echo'"';echo $row22['ITEMDESCRIPTION'];echo'"';?>,<? echo $row22['WONUM'];?>,<? echo $row22['REPORTEDBY'];?>,<? echo $row22['WONSTATUS'];?>,<? echo'"';echo $row22['WONDESCRIPTION'];echo'"';?>,<? echo $row22['POSTINGDATE'];?>,<? echo $row22['GLCREDIT'];?>,<? echo $row22['GLDEBITACCT'];?>,<? echo $row22['QUANTITY'];?>,<? echo $row22['UNITCOST'];?>,<? echo $row22['LINECOST'];?>,<? echo $row22['LOCATION'];?>,<? echo $row22['EQNUM'];?>,<? echo $row22['BPIneos'];$cr = "\n";print"$cr";?><?php } ?>
<?php
include ("include.php");
$query = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
?><? $woid2 = $row['Wonum']; ?>
<?php
include ("include.php");
$query2 = "SELECT * FROM ineos_stores WHERE WONUM = '$woid2'";
$result2 = mysql_query($query2) or die("Error: " . mysql_error());
$count2 = mysql_num_rows($result2);
while($row2 = mysql_fetch_array($result2)){
?>
<? echo $row2['Warehouse'];?>,<? echo $row2['ISSUETYPE'];?>,<? echo $row2['ITEMNUM'];?>,<? echo'"';echo $row2['ITEMDESCRIPTION'];echo'"';?>,<? echo $row2['WONUM'];?>,<? echo $row2['REPORTEDBY'];?>,<? echo $row2['WONSTATUS'];?>,<? echo'"';echo $row2['WONDESCRIPTION'];echo'"';?>,<? echo $row2['POSTINGDATE'];?>,<? echo $row2['GLCREDIT'];?>,<? echo $row2['GLDEBITACCT'];?>,<? echo $row2['QUANTITY'];?>,<? echo $row2['UNITCOST'];?>,<? echo $row2['LINECOST'];?>,<? echo $row2['LOCATION'];?>,<? echo $row2['EQNUM'];?>,<? echo $row2['BPIneos'];$cr = "\n";print"$cr";?><?php } } ?>
<?php
include ("include.php");
$query222 = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result222 = mysql_query($query222) or die("Error: " . mysql_error());
$count222 = mysql_num_rows($result222);
while($row222 = mysql_fetch_array($result222)){
?><? $woid00 = $row222['Wonum']; ?>
<?php
include ("include.php");
$query333 = "SELECT * FROM ineos_evwo WHERE Parent = '$woid00'";
$result333 = mysql_query($query333) or die("Error: " . mysql_error());
$count333 = mysql_num_rows($result333);
while($row333 = mysql_fetch_array($result333)){
?><? $woid0011 = $row333['Wonum']; ?>
<?php
include ("include.php");
$query2 = "SELECT * FROM ineos_stores WHERE WONUM = '$woid0011'";
$result2 = mysql_query($query2) or die("Error: " . mysql_error());
$count2 = mysql_num_rows($result2);
while($row2 = mysql_fetch_array($result2)){
?>
<? echo $row2['Warehouse'];?>,<? echo $row2['ISSUETYPE'];?>,<? echo $row2['ITEMNUM'];?>,<? echo'"';echo $row2['ITEMDESCRIPTION'];echo'"';?>,<? echo $row2['WONUM'];?>,<? echo $row2['REPORTEDBY'];?>,<? echo $row2['WONSTATUS'];?>,<? echo'"';echo $row2['WONDESCRIPTION'];echo'"';?>,<? echo $row2['POSTINGDATE'];?>,<? echo $row2['GLCREDIT'];?>,<? echo $row2['GLDEBITACCT'];?>,<? echo $row2['QUANTITY'];?>,<? echo $row2['UNITCOST'];?>,<? echo $row2['LINECOST'];?>,<? echo $row2['LOCATION'];?>,<? echo $row2['EQNUM'];?>,<? echo $row2['BPIneos'];$cr = "\n";print"$cr";?><?php } } } ?>
LabourGroup,WONUM,DESCRIPTION,LABORCODE,MEMO,REGULARHRS,PAYRATE,LINECOST,POSTINGDATE,ENTERDATE,GLDEBITACCT,LOCATION,EQNUM,BPIneos
<?php
include ("include.php");
$query33 = "SELECT * FROM ineos_timewrite WHERE WONUM = '$woid'";
$result33 = mysql_query($query33) or die("Error: " . mysql_error());
$count33 = mysql_num_rows($result33);
while($row33 = mysql_fetch_array($result33)){
?>
<? echo $row33['LabourGroup'];?>,<? echo $row33['WONUM'];?>,<? echo'"';echo $row33['DESCRIPTION'];echo'"';?>,<? echo $row33['LABORCODE'];?>,<? echo $row33['MEMO'];?>,<? echo $row33['REGULARHRS'];?>,<? echo $row33['PAYRATE'];?>,<? echo $row33['LINECOST'];?>,<? echo $row33['POSTINGDATE'];?>,<? echo $row33['ENTERDATE'];?>,<? echo $row33['GLDEBITACCT'];?>,<? echo $row33['LOCATION'];?>,<? echo $row33['EQNUM'];?>,<? echo $row33['BPIneos'];$cr = "\n";print"$cr";?><?php } ?>
<?php
include ("include.php");
$query = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)){
?><? $woid3 = $row['Wonum']; ?>
<?php
include ("include.php");
$query3 = "SELECT * FROM ineos_timewrite WHERE WONUM = '$woid3'";
$result3 = mysql_query($query3) or die("Error: " . mysql_error());
$count3 = mysql_num_rows($result3);
while($row3 = mysql_fetch_array($result3)){
?>
<? echo $row3['LabourGroup'];?>,<? echo $row3['WONUM'];?>,<? echo'"';echo $row3['DESCRIPTION'];echo'"';?>,<? echo $row3['LABORCODE'];?>,<? echo $row3['MEMO'];?>,<? echo $row3['REGULARHRS'];?>,<? echo $row3['PAYRATE'];?>,<? echo $row3['LINECOST'];?>,<? echo $row3['POSTINGDATE'];?>,<? echo $row3['ENTERDATE'];?>,<? echo $row3['GLDEBITACCT'];?>,<? echo $row3['LOCATION'];?>,<? echo $row3['EQNUM'];?>,<? echo $row3['BPIneos'];$cr = "\n";print"$cr";?><?php } } ?>
<?php
include ("include.php");
$query222 = "SELECT * FROM ineos_evwo WHERE Parent = '$woid'";
$result222 = mysql_query($query222) or die("Error: " . mysql_error());
$count222 = mysql_num_rows($result222);
while($row222 = mysql_fetch_array($result222)){
?><? $woid00 = $row222['Wonum']; ?>
<?php
include ("include.php");
$query333 = "SELECT * FROM ineos_evwo WHERE Parent = '$woid00'";
$result333 = mysql_query($query333) or die("Error: " . mysql_error());
$count333 = mysql_num_rows($result333);
while($row333 = mysql_fetch_array($result333)){
?><? $woid0011 = $row333['Wonum']; ?>
<?php
include ("include.php");
$query3 = "SELECT * FROM ineos_timewrite WHERE WONUM = '$woid0011'";
$result3 = mysql_query($query3) or die("Error: " . mysql_error());
$count3 = mysql_num_rows($result3);
while($row3 = mysql_fetch_array($result3)){
?>
<? echo $row3['LabourGroup'];?>,<? echo $row3['WONUM'];?>,<? echo'"';echo $row3['DESCRIPTION'];echo'"';?>,<? echo $row3['LABORCODE'];?>,<? echo $row3['MEMO'];?>,<? echo $row3['REGULARHRS'];?>,<? echo $row3['PAYRATE'];?>,<? echo $row3['LINECOST'];?>,<? echo $row3['POSTINGDATE'];?>,<? echo $row3['ENTERDATE'];?>,<? echo $row3['GLDEBITACCT'];?>,<? echo $row3['LOCATION'];?>,<? echo $row3['EQNUM'];?>,<? echo $row3['BPIneos'];$cr = "\n";print"$cr";?><?php } } } ?>
ASKER
Hi yes, what i have found so far is it can be as far as 15 levels child 14 but if it could be more to have it loop until it cant find children or children etc.
hi,
function data($parent =0)
if($parent != null)
{
$query = select * from table where table.parent = $parent;
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result) ){
$x = $row['childrowvalue']
$parentid = $row['parentrowvalue']
$this->data($parentid);
}
Please note this is only algo and not actual code
function data($parent =0)
if($parent != null)
{
$query = select * from table where table.parent = $parent;
$result = mysql_query($query) or die("Error: " . mysql_error());
$count = mysql_num_rows($result);
while($row = mysql_fetch_array($result)
$x = $row['childrowvalue']
$parentid = $row['parentrowvalue']
$this->data($parentid);
}
Please note this is only algo and not actual code
In this case you may need a recursive function rather than loops.
ASKER
could i have a ish working example for me to play with?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You mean to say that
parent 1
->Child 1
->Child2