Results 1 to 4 of 4

Thread: Query was empty

  1. #1
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    13

    Red face Unanswered: Query was empty

    Hello Everyone!

    I have a problem with my mySql/Php page, it's about updating a multi-rows (record) in one go, sometimes it works, sometimes it returns a message "query was empty". Is there anyone who can help me with this?

    Here is the code:

    if ((isset($HTTP_POST_VARS["MM_update"])) && ($HTTP_POST_VARS["MM_update"] == "form1")) {
    for ($i = 0; $i < count($_POST['DtlCdU']); $i++)
    {
    $QtyX=Getfloat($HTTP_POST_VARS['QtyU'][$i]);
    $UnitPX=Getfloat($HTTP_POST_VARS['UnitPriceU'][$i]); $LineTots=$UnitPX*$QtyX; $updateSQL = sprintf("UPDATE pms_orderdtls SET FiltPORcd='".$HTTP_POST_VARS['FiltPORcd'][$i]."', ItemNo='".$HTTP_POST_VARS['ItemNoU'][$i]."', CostCodeOld='".$HTTP_POST_VARS['CostCodeOldU'][$i]."', CostCodeNew='".$HTTP_POST_VARS['CostCodeNewU'][$i]."', ItemNo1='".$HTTP_POST_VARS['ItemNo1U'][$i]."',ItemNo2='".$HTTP_POST_VARS['ItemNo2U'][$i]."', `Description`='".$HTTP_POST_VARS['DescriptionU'][$i]."', `Unit`='".$HTTP_POST_VARS['UnitU'][$i]."', Qty=$QtyX, UnitPrice=$UnitPX, LineTotal=$LineTots, Discount='".$HTTP_POST_VARS['DiscountU'][$i]."', DeletCd='".$HTTP_POST_VARS['DeletCd'][$i]."' WHERE DtlCd='".$HTTP_POST_VARS['DtlCdU'][$i]."'");
    mysql_select_db($database_fdelix_HCADatabase, $fdelix_HCADatabase);
    $Result1 = mysql_query($updateSQL, $fdelix_HCADatabase) or die(mysql_error());
    $updateGoTo = "transac4.php";
    // if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
    // $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
    // $updateGoTo .= $HTTP_SERVER_VARS['QUERY_STRING'];
    // }
    header(sprintf("Location: %s", $updateGoTo));
    }
    }

    Thank you so much!!

    Felix

  2. #2
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    The WHERE clause does not result in a match. The most likely cause is that the form is not producing consecutive index values and the FOR loop is running across missing indexes. You should post your form code as well. The occasions where this fails is probably repeatable, if you posted the HTML of the form that causes this (using the right-click, view source, then copy/paste in the browser) would show the actual variables and values. You should also echo the query string to make sure it contains the expected contents.

    Also, one other problem is the header(...) statement that is inside of the FOR loop. I suspect that this should be after the end of the FOR loop.

    For anyone else that might be able to help, here is a more readable posting of the above code -
    PHP Code:
    if ((isset($HTTP_POST_VARS["MM_update"])) && ($HTTP_POST_VARS["MM_update"] == "form1")) {
        for (
    $i 0$i count($_POST['DtlCdU']); $i++) { 
            
    $QtyX=Getfloat($HTTP_POST_VARS['QtyU'][$i]); 
            
    $UnitPX=Getfloat($HTTP_POST_VARS['UnitPriceU'][$i]);
            
    $LineTots=$UnitPX*$QtyX;
            
    $updateSQL sprintf("UPDATE pms_orderdtls SET FiltPORcd='".$HTTP_POST_VARS['FiltPORcd'][$i]."', ItemNo='".$HTTP_POST_VARS['ItemNoU'][$i]."', CostCodeOld='".$HTTP_POST_VARS['CostCodeOldU'][$i]."',CostCodeNew='".$HTTP_POST_VARS['CostCodeNewU'][$i]."', ItemNo1='".$HTTP_POST_VARS['ItemNo1U'][$i]."',ItemNo2='".$HTTP_POST_VARS['ItemNo2U'][$i]."', `Description`='".$HTTP_POST_VARS['DescriptionU'][$i]."', `Unit`='".$HTTP_POST_VARS['UnitU'][$i]."', Qty=$QtyX, UnitPrice=$UnitPX, LineTotal=$LineTots, Discount='".$HTTP_POST_VARS['DiscountU'][$i]."', DeletCd='".$HTTP_POST_VARS['DeletCd'][$i]."' WHERE DtlCd='".$HTTP_POST_VARS['DtlCdU'][$i]."'");
            
    mysql_select_db($database_fdelix_HCADatabase$fdelix_HCADatabase);
            
    $Result1 mysql_query($updateSQL$fdelix_HCADatabase) or die(mysql_error());
            
    $updateGoTo "transac4.php";
            
    // if (isset($HTTP_SERVER_VARS['QUERY_STRING'])) {
            // $updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
            // $updateGoTo .= $HTTP_SERVER_VARS['QUERY_STRING'];
            // }
            
    header(sprintf("Location: %s"$updateGoTo));
        }

    Also, here is more compact version of the query string -
    PHP Code:
    $updateSQL "UPDATE pms_orderdtls SET FiltPORcd='{$_POST['FiltPORcd'][$i]}', ItemNo='{$_POST['ItemNoU'][$i]}', CostCodeOld='{$_POST['CostCodeOldU'][$i]}', CostCodeNew='{$_POST['CostCodeNewU'][$i]}', ItemNo1='{$_POST['ItemNo1U'][$i]}', ItemNo2='{$_POST['ItemNo2U'][$i]}', `Description`='{$_POST['DescriptionU'][$i]}', `Unit`='{$_POST['UnitU'][$i]}', Qty=$QtyX, UnitPrice=$UnitPX, LineTotal=$LineTots, Discount='{$_POST['DiscountU'][$i]}', DeletCd='{$_POST['DeletCd'][$i]}' WHERE DtlCd='{$_POST['DtlCdU'][$i]}'"

  3. #3
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    13

    Red face

    Dear dbmab,

    Thanks for your time. I have not tried any of your suggestions yet, I am trying to post the form you mentioned, here's the code:

    <form method="post" name="form1" action="<?php echo $editFormAction; ?>">
    <table width="98%" border="0" align="center" class="bodytext">
    <tr valign="middle">
    <td height="16" colspan="7"><strong class="bodytext"><font color="#FF0000"><?php echo $row_filter_transac['OrderRef']; ?></font></strong> <div align="right"></div></td>
    <td height="16" colspan="4"><div align="right"><font color="#009900">Body
    of Order Form</font></div></td>
    </tr>
    <tr valign="middle">
    <td width="4%" height="16" bgcolor="#000066"><div align="center"><strong><font color="#FFFFFF">Sequence
    No </font></strong></div></td>
    <td width="8%" height="16" bgcolor="#000066"> <div align="center"><font color="#FFFFFF"><strong>CC</strong></font></div></td>
    <td width="4%" bgcolor="#000066"><font color="#FFFFFF"><strong>Item</strong></font></td>
    <td width="4%" height="16" bgcolor="#000066"> <div align="center"><font color="#FFFFFF"><strong>Sub-Item</strong></font></div></td>
    <td width="28%" height="16" bgcolor="#000066"> <div align="center"><font color="#FFFFFF"><strong>Description</strong></font></div></td>
    <td width="4%" height="16" bgcolor="#000066"> <div align="center"><font color="#FFFFFF"><strong>Disc</strong></font></div></td>
    <td width="5%" height="16" bgcolor="#000066"> <div align="center"><font color="#FFFFFF"><strong>Unit</strong></font></div></td>
    <td width="10%" height="16" bgcolor="#000066"> <div align="right"><font color="#FFFFFF"><strong>Qty</strong></font></div></td>
    <td width="16%" height="16" bgcolor="#000066"> <div align="right"><font color="#FFFFFF"><strong>Unit
    Price</strong></font></div></td>
    <td width="16%" height="16" bgcolor="#000066"> <div align="right"><font color="#FFFFFF"><strong>Amount</strong></font></div></td>
    <td width="5%" height="16" bgcolor="#000066"> <div align="center"><font color="#FFFF00"><strong>Del?</strong></font></div></td>
    <input type="hidden" name="MM_update" value="form1">
    </tr>
    <?php do { ?>
    <tr valign="top" class="bodytextNoBorder" align="left">
    <td width="4%">
    <div align="center">
    <input name="ItemNoU[]" type="text" class="bodytextNoBorder" id="ItemNoU" value="<?php echo $row_OrderDtls['ItemNo']; ?>" size="4">
    <input name="FiltPORcd[]" type="hidden" id="FiltPORcd" value="<?php echo $row_filter_transac['FiltPORcd']; ?>" size="32">
    </div></td>
    <td width="8%">
    <div align="center">
    <input name="CostCodeOldU[]" type="text" class="bodytextNoBorder" id="CostCodeOldU" value="<?php if($row_OrderDtls['CostCodeOld']!=0) {echo $row_OrderDtls['CostCodeOld']; }else{ echo ' ';} ?>" size="10">
    <input name="CostCodeNewU[]" type="text" class="bodytextNoBorder" id="CostCodeNewU" value="<?php if($row_OrderDtls['CostCodeNew']!=0) {echo $row_OrderDtls['CostCodeNew']; }else{ echo ' ';} ?>" size="10">
    </div></td>
    <td width="4%"><input name="ItemNo1U[]" type="text" class="bodytextNoBorder" id="ItemNo1U" value="<?php echo $row_OrderDtls['ItemNo1']; ?>" size="4"></td>
    <td width="4%">
    <div align="center">
    <input name="ItemNo2U[]" type="text" class="bodytextNoBorder" id="ItemNo2U" value="<?php echo $row_OrderDtls['ItemNo2']; ?>" size="4">
    <br>
    </div></td>
    <td width="28%">
    <div align="left">
    <textarea name="DescriptionU[]" cols="36" rows="5" class="btext11" id="DescriptionU"><?php echo $row_OrderDtls['Description']; ?></textarea>
    </div></td>
    <td width="4%">
    <div align="center">
    <select name="DiscountU[]" size="1" class="bodytextNoBorder" id="select2">
    <option value="0" <?php if (!(strcmp(0, $row_OrderDtls['Discount']))) {echo "SELECTED";} ?>>N</option>
    <option value="1" <?php if (!(strcmp(1, $row_OrderDtls['Discount']))) {echo "SELECTED";} ?>>Y</option>
    </select>
    </div></td>
    <td width="5%">
    <div align="center">
    <input name="UnitU[]" type="text" class="bodytextNoBorder" id="UnitU" value="<?php echo $row_OrderDtls['Unit']; ?>" size="5">
    </div></td>
    <td width="10%">
    <div align="right">
    <input name="QtyU[]" type="text" class="bodytextNumericNoBorder" id="QtyU" value="<?php echo DoFormatNumber($row_OrderDtls['Qty'], 4, '.', ','); ?>" size="8">
    </div></td>
    <td width="16%">
    <div align="right">
    <input name="UnitPriceU[]" type="text" class="bodytextNumericNoBorder" id="UnitPriceU" value="<?php echo DoFormatNumber($row_OrderDtls['UnitPrice'], 4, '.', ','); ?>" size="12">
    </div></td>
    <td width="16%" class="bodytextNumericNoBorder">
    <div align="right">
    <input name="LineTotal" type="text" class="bodytextNumericNoBorder" id="UnitPriceUL" value="<?php echo DoFormatNumber($row_OrderDtls['LineTotal'], 2, '.', ','); ?>" size="12">
    </div></td>
    <td width="5%" align="center">
    <div align="center">
    <select name="DeletCd[]" size="1" class="bodytextNoBorder" id="DeletCd" onChange="sabmit()">
    <option value="1" <?php if (!(strcmp(1, $row_OrderDtls['DeletCd']))) {echo "SELECTED";} ?>>Y</option>
    <option value="0" <?php if (!(strcmp(0, $row_OrderDtls['DeletCd']))) {echo "SELECTED";} ?>>N</option>
    </select>
    </div></td>
    </tr>
    <input name="DtlCdU[]" type="hidden" id="DtlCdU" value="<?php echo $row_OrderDtls['DtlCd']; ?>">
    <?php } while ($row_OrderDtls = mysql_fetch_assoc($OrderDtls)); ?>
    <tr valign="baseline">
    <td width="4%" height="20" valign="middle">&nbsp;</td>
    <td width="8%" height="20" valign="middle">&nbsp;</td>
    <td width="4%" valign="middle">&nbsp;</td>
    <td width="4%" height="20" valign="middle">&nbsp;</td>
    <td width="28%" height="20" valign="middle">&nbsp;</td>
    <td width="4%" valign="middle">&nbsp;</td>
    <td width="5%" height="20" valign="middle">&nbsp;</td>
    <td height="20" colspan="2" valign="middle"> <div align="right"><strong class="bodytext">
    Total <img src="../images/arrows2.gif" width="16" height="8"></strong></div></td>
    <td width="16%" height="20" valign="middle" class="bodytext"> <div align="right"><strong><?php echo DoFormatNumber($row_TtlPO['TtlPO'], 2, '.', ','); ?></strong></div></td>
    <td width="5%" valign="middle"> <div align="center"><font color="#FF0000">Confirm<br>
    Delete </font></div></td>
    </tr>
    <tr valign="baseline">
    <td colspan="8"> <div align="left">
    <?php if ($row_filter_transac['Cancelled']==1) { // Adv Conditional Region ?>
    <img src="images2/cancelled.gif" width="63" height="33">
    <? } // $row_filter_transac['Cancelled']==1 ?>
    </div></td>
    <td colspan="2"> <div align="right">
    <input name="Button" type="button" class="button" value="Save Changes" onClick="sabmit()">
    </div>
    <div align="right"> </div></td>
    <td width="5%"> <div align="center">
    <input name="Button2" type="button" class="button" value="Go" onClick="sabForm3()">
    </div></td>
    </tr>
    </table>
    </form>

    The form is a bit messy with all those property settings, but i think you can follow through it. The on click="sabmit()" is the trigger button for the updating, it is just a "document.form1.submit()" function sent to javascript somewhere in the page.

    Hope this will clear some more vague areas you need to look into. Thanks a lot!

    Felix

  4. #4
    Join Date
    Oct 2003
    Location
    Hong Kong
    Posts
    13
    Dear dbmab,

    I think you hit the target.. I used the compact version of the query string you posted and it works.. I think along the way, during the process of the query with my version, there is parsing happening somewhere.. anyway if you think there are still some problems with my form, kindly let me know. Thanks a lot dbmab.

    Felix

Posting Permissions

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