Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    13

    Unanswered: mySql/PHP re-initialization of recordset

    Hello,

    I am trying to display records from two recordsets having to point through a common field. The first loop refers to the first recordset, and within a loop, another loop from second recordset and trying to compare the common field, if the current record in the outer loop matches the data in the inner loop, one of inner loop fields will be displayed. In my code only the first records of outer loop gets the data, succeeding records in the repeat region don't capture data from inner loop. Here's the full code. Please help.


    <?php require_once('Connections/fdelix_HCADatabase.php'); ?>
    <?php

    $colname_ProjDet = "1";
    if (isset($HTTP_GET_VARS['ProjNo'])) {
    $colname_ProjDet = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['ProjNo'] : addslashes($HTTP_GET_VARS['ProjNo']);
    }
    mysql_select_db($database_fdelix_HCADatabase, $fdelix_HCADatabase);
    $query_ProjDet = sprintf("SELECT pms_transac.ProjectRef, pms_ccdetails.CostCodeOld, pms_transac.Supplier, Sum(pms_ccdetails.VOAmt) AS SumOfVOAmt, Sum(pms_ccdetails.POAmt) AS SumOfPOAmt, pms_transac.Cancelled, Avg(pms_projectdtls.PDAmount) AS AvgOfPDAmount,Sum(pms_ccdetails.VOAmt)+sum(pms_ccd etails.POAmt) AS TotalAmt FROM (pms_ccdetails INNER JOIN pms_projectdtls ON pms_ccdetails.CostCodeOld = pms_projectdtls.CostCodeOld) INNER JOIN pms_transac ON (pms_ccdetails.FiltPORcd = pms_transac.FiltPORcd) AND (pms_projectdtls.ProjectRef = pms_transac.ProjectRef) GROUP BY pms_transac.ProjectRef, pms_ccdetails.CostCodeOld, pms_transac.Supplier, pms_transac.Cancelled HAVING (((pms_transac.ProjectRef)='%s') AND ((pms_transac.Cancelled) Like 0)) ORDER BY pms_ccdetails.CostCodeOld, pms_transac.Supplier", $colname_ProjDet);
    $ProjDet = mysql_query($query_ProjDet, $fdelix_HCADatabase) or die(mysql_error());
    $row_ProjDet = mysql_fetch_assoc($ProjDet);
    $totalRows_ProjDet = mysql_num_rows($ProjDet);

    $colname_Recordset1 = "1";
    if (isset($HTTP_GET_VARS['ProjNo'])) {
    $colname_Recordset1 = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['ProjNo'] : addslashes($HTTP_GET_VARS['ProjNo']);
    }
    mysql_select_db($database_fdelix_HCADatabase, $fdelix_HCADatabase);
    $query_Recordset1 = sprintf("SELECT pms_transac.ProjectRef, pms_ccdetails.CostCodeOld, pms_transac.Supplier, pms_transac.OrderRef, pms_ccdetails.VOAmt, pms_ccdetails.POAmt, pms_transac.OrderDate, pms_transac.Cancelled, pms_transac.PODesc, POAmt+VOAmt as TtlPO, PDAmount FROM (pms_ccdetails INNER JOIN pms_projectdtls ON pms_ccdetails.CostCodeOld = pms_projectdtls.CostCodeOld) INNER JOIN pms_transac ON (pms_transac.ProjectRef = pms_projectdtls.ProjectRef) AND (pms_ccdetails.FiltPORcd = pms_transac.FiltPORcd) WHERE (((pms_transac.ProjectRef)='%s') AND ((pms_transac.Cancelled) <>1)) ORDER BY pms_ccdetails.CostCodeOld, pms_transac.Supplier, pms_transac.OrderRef", $colname_Recordset1);
    $Recordset1 = mysql_query($query_Recordset1, $fdelix_HCADatabase) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    $totalRows_Recordset1 = mysql_num_rows($Recordset1);

    mysql_select_db($database_fdelix_HCADatabase, $fdelix_HCADatabase);
    $query_ProjectList = "SELECT *, date_add(now(), interval 15 hour) as ngayon, userid FROM pms_emproj , pms_project WHERE ProjectRef=ProjectNo and Approved =1 GROUP BY ProjectNo ORDER BY ProjectNo DESC";
    $ProjectList = mysql_query($query_ProjectList, $fdelix_HCADatabase) or die(mysql_error());
    $row_ProjectList = mysql_fetch_assoc($ProjectList);
    $totalRows_ProjectList = mysql_num_rows($ProjectList);

    $colname_CCode = "1";
    if (isset($HTTP_GET_VARS['ProjNo'])) {
    $colname_CCode = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['ProjNo'] : addslashes($HTTP_GET_VARS['ProjNo']);
    }
    mysql_select_db($database_fdelix_HCADatabase, $fdelix_HCADatabase);
    $query_CCode = sprintf("SELECT CostCodeOld FROM pms_ccdetails WHERE ProjectRef = '%s' GROUP BY CostCodeOld ORDER BY CostCodeOld ASC", $colname_CCode);
    $CCode = mysql_query($query_CCode, $fdelix_HCADatabase) or die(mysql_error());
    $row_CCode = mysql_fetch_assoc($CCode);
    $totalRows_CCode = mysql_num_rows($CCode);

    $colname_data_hdr = "1";
    if (isset($HTTP_GET_VARS['ProjNo'])) {
    $colname_data_hdr = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['ProjNo'] : addslashes($HTTP_GET_VARS['ProjNo']);
    }
    mysql_select_db($database_fdelix_HCADatabase, $fdelix_HCADatabase);
    $query_data_hdr = sprintf("SELECT * FROM pms_transac_hdr WHERE ProjectRef = '%s'", $colname_data_hdr);
    $data_hdr = mysql_query($query_data_hdr, $fdelix_HCADatabase) or die(mysql_error());
    $row_data_hdr = mysql_fetch_assoc($data_hdr);
    $totalRows_data_hdr = mysql_num_rows($data_hdr);

    ?>

    <html>
    <head>
    <title>Cost Code Summary</title>
    <meta http-equiv="Content-Type" content="text/html; charset=big5">
    <link href="hcapms.css" rel="stylesheet" type="text/css">
    </head>

    <body bgcolor="#FFFFFF" text="#000000" link="#000000" vlink="#000000" alink="#000000" leftmargin=0 topmargin=0 class="highlight">
    <div id ="headText">
    <table width="650" border="0" class="textmenu">
    <tr valign="top">
    <td width="17%" height="15"> <div align="right"><strong>Project No <img src="../images/arrows2.gif" width="16" height="8">
    </strong></div></td>
    <td width="30%" align="left"><form name="form1" method="get" action="">
    <div align="left"><strong>
    <select name="ProjNo" class="textmenu" id="ProjNo" onChange="document.form1.submit()">
    <option value="" <?php if (!(strcmp("", $row_data_hdr['ProjectRef']))) {echo "SELECTED";} ?>>--
    Select Project --</option>
    <?php
    do {
    ?>
    <option value="<?php echo $row_ProjectList['ProjectNo']?>"<?php if (!(strcmp($row_ProjectList['ProjectNo'], $row_data_hdr['ProjectRef']))) {echo "SELECTED";} ?>><?php echo $row_ProjectList['ProjectNo']?></option>
    <?php
    } while ($row_ProjectList = mysql_fetch_assoc($ProjectList));
    $rows = mysql_num_rows($ProjectList);
    if($rows > 0) {
    mysql_data_seek($ProjectList, 0);
    $row_ProjectList = mysql_fetch_assoc($ProjectList);
    }
    ?>
    </select>
    </strong> </div>
    </form></td>
    <td colspan="2" align="left">&nbsp;</td>
    </tr>
    <tr align="right" valign="top">
    <td height="26" colspan="4"> <div align="right"> </div>
    <div align="left"> </div></td>
    </tr>
    </table>
    <div id="dataBody">
    <table width="650" border="0" align="left" class="bodytext">
    <tr>
    <td width="917" height="17" colspan="2" valign="bottom">
    <table width="100%" border="0" class="bodytext">
    <tr>
    <td width="6%"> <div align="center"><strong>Cost Code</strong></div></td>
    <td width="10%"> <div align="right"><strong>Budget</strong></div></td>
    <td width="15%"><strong>Order Ref </strong> <div align="center"></div></td>
    <td width="16%"><strong>Company</strong></td>
    <td width="9%"><strong>Order Date</strong></td>
    <td width="17%"><strong>Order Description</strong></td>
    <td width="11%"> <div align="right"><strong>Order Amt</strong></div></td>
    <td width="11%"><div align="right"><strong>VO Amt</strong></div></td>
    <td width="11%"><div align="right"><strong>PO Value</strong></div></td>
    </tr>
    <tr>
    <td colspan="9"> <div align="center">
    <hr size="0" noshade>
    </div>
    <div align="right"></div>
    <div align="right"></div>
    <div align="right"></div></td>
    </tr>
    <?php do { ?>
    <tr valign="top">
    <td width="6%">
    <div align="center"><?php echo $row_Recordset1['CostCodeOld']; ?></div></td>
    <td width="10%" class="bodytextNumeric">
    <div align="right"><?php echo DoFormatNumber($row_Recordset1['PDAmount'], 2, '.', ','); ?></div></td>
    <td width="15%"><?php echo $row_Recordset1['OrderRef']; ?> <div align="center"></div></td>
    <td width="16%"><?php echo $row_Recordset1['Supplier']; ?></td>
    <td width="9%"><div align="center"><?php echo makeDateTime($row_Recordset1['OrderDate'], 'd-m-y'); ?></div></td>
    <td width="17%">
    <div align="left"><?php echo $row_Recordset1['PODesc']; ?></div></td>
    <td width="11%" class="bodytextNumeric"><?php echo DoFormatNumber($row_Recordset1['POAmt'], 2, '.', ','); ?></td>
    <td width="11%" class="bodytextNumeric"><?php echo DoFormatNumber($row_Recordset1['VOAmt'], 2, '.', ','); ?></td>
    <td width="11%" class="bodytextNumeric"><?php echo DoFormatNumber($row_Recordset1['TtlPO'], 2, '.', ','); ?></td>
    </tr>
    <tr valign="top">
    <td colspan="9">
    <?php do { ?>
    <?php if($row_ProjDet['Supplier']==$row_Recordset1['Supplier']){
    echo DoFormatNumber($row_ProjDet['TotalAmt'], 2, '.', ',');} ?>
    <?php } while ($row_ProjDet = mysql_fetch_assoc($ProjDet)); ?>
    </td>
    </tr>
    <?php } while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
    </table></td>
    </tr>
    <tr>
    <td height="17" colspan="2"><hr size="0" noshade></td>
    </tr>
    </table>
    </div>
    </div>
    </body>
    </html>
    <?php
    mysql_free_result($ProjDet);
    mysql_free_result($Recordset1);
    mysql_free_result($ProjectList);
    mysql_free_result($CCode);
    mysql_free_result($data_hdr);
    ?>

  2. #2
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    13
    Please help!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    .....speaking entirely for myself, I dont have the time or the inclination to plough through that amount of code. I did have a brief look and just gave up as it looked very odd and alien to me.


    You mention that you are doing a match, is their any way you can use a JOIN to retrieve the rows you want as part of your SQL statement, rahter than do it via code.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    Just based on the title (if the code was posted using the php code wrapper and with indentation someone might be able to read through it, but not me in the current form), I am guessing that you want to reuse a recordset without executing the query again? If so, take a look at the mysql_data_seek(...) function. This will let you reset the data pointer to any row.

  5. #5
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    13
    thanks for your comments.. I don't know if you do php coding.

  6. #6
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    13
    thank you dbmap.. at one time you rescued me also in one of my problems. Yes, I had in mind looking at data_seek function and not tried doing it, I need to start doing that homework. You, also at one time re-organized my codes by putting it into a window with scrolling mechanism, that.. I just don't know how. Sorry for a somewhat scambled codes as it was copied and pasted. Regards..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •