PHP Wizards - Teh Forum

<body> <div id="header"> <span class="left"> <span class="back"><a href="lthread_list.php?webtag=DEFAULT"><span class="image mobile_back" title="Back"></span></a></span> <span class="image mobile_logo" title="Beehive Forum Logo"></span> </span> <ul> <li> <a class="reply_all" href="lpost.php?webtag=DEFAULT&reply_to=38393.0&return_msg=38393.82"> <span class="image mobile_reply_all"></span><span class="text">Reply to All</span> </a> </li> <li> <a class="navigation" href="#"> <span class="image mobile_navigation"></span><span class="text">Show messages</span> </a> </li> <li> <a class="main" href="#"><span class="image mobile_menu" title="Menu"></span> </a> </li> </ul> </div> <div class="menu main"> <ul> <li><a href="lthread_list.php?webtag=DEFAULT">Messages</a></li> <li><a href="lpm.php?webtag=DEFAULT">Inbox</a></li> <li><a href="lsearch.php?webtag=DEFAULT">Search</a></li> <li><a href="llogon.php?webtag=DEFAULT">Login</a></li> </ul> </div> <div class="menu navigation"><a href="lmessages.php?webtag=DEFAULT&msg=38393.1">1–10</a>…<a href="lmessages.php?webtag=DEFAULT&msg=38393.61">61–70</a><a href="lmessages.php?webtag=DEFAULT&msg=38393.71">71–80</a><a href="lmessages.php?webtag=DEFAULT&msg=38393.81">81–90</a><a href="lmessages.php?webtag=DEFAULT&msg=38393.91">91–100</a><a href="lmessages.php?webtag=DEFAULT&msg=38393.101">101</a></div> <div id="page_content"> <h3 class="thread_title"><a href="index.php?webtag=DEFAULT&amp;msg=38393.82">PHP Wizards</a> </h3> <div id="messages" data-navigation="38393_82_101_10"> <a name="a38393_82"></a><div class="message" id="message_38393_82"> <div class="message_header"> <div class="message_from"> From: Ken (SHIELDSIT)<span class="message_time">20 Apr 2011 22:26</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 20 Apr 2011 23:05"></span></span> <span class="message_count">82 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.82">38393.82</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.80">38393.80</a></div> <div class="message_body"> This is what I did. It let me eliminate a bunch of if's and other math.<br /> <div class="quotetext" id="code-"><b> code:</b></div><pre class="code"><CFQUERY NAME="sluth" DATASOURCE="isiswood"> SELECT ID=rudID, Category=rudCategory, Seq=rudSeq, Type=rudType, Product_ID=rudProductID, Description=rudDescrip, PcsPerPkg=rudPcsPerPkg, Tally=rudTally, UOM=rudBaseUnitID, Pieces=rudPieces, Volume=rudVolume, NetVolume=rudNetVolume, Cost=rudCost, AddedCost= rudAddedCost, MktValue=rudMarketValue, Total=rudTotal, NetThick=rudNetThick, NetWidth=rudNetWidth, NetLength=rudNetLength, NetType=rudVolType, LocationID=rudLocationID,rudFIFOVolume,rudForceCost,rudBoomID,prdLengthID=(SELECT prdLengthID FROM ProductMaster WHERE prdID=rudProductID) FROM ProductionDetail WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%' ORDER BY Seq; </cfquery></pre></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.82&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.82"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a38393_83"></a><div class="message" id="message_38393_83"> <div class="message_header"> <div class="message_from"> From: Ken (SHIELDSIT)<span class="message_time">20 Apr 2011 22:39</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 20 Apr 2011 23:05"></span></span> <span class="message_count">83 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.83">38393.83</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.80">38393.80</a></div> <div class="message_body"> Here is my code after your help.</div> <div class="message_attachments"> <span>Attachments:</span> <ul> <li><span class="image attach"></span><a href="get_attachment.php?webtag=DEFAULT&hash=13fc172782c76af063c8d9342fa87eac&filename=batch.cfm" target="_blank">batch.cfm</a></li> </ul> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.83&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.83"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a38393_84"></a><div class="message" id="message_38393_84"> <div class="message_header"> <div class="message_from"> From: Peter (BOUGHTONP)<span class="message_time">20 Apr 2011 23:23</span> <div class="clearer"></div> </div><div class="message_to">To: Ken (SHIELDSIT) <span><span class="image post_read" title="Read: 20 Apr 2011 23:50"></span></span> <span class="message_count">84 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.84">38393.84</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.82#a38393_83" target="_self">38393.83</a></div> <div class="message_body"> Much better... though still a whole bunch of stuff I want to change there. <span class="emoticon e_embarrassed" title=":$"><span class="e__">:$</span></span><br /> <br /> But anyway, yeah, if you make it to "<tt>ORDER BY WoodCount DESC</tt>" in your count query, then all you need is <tt><cfset WoodType = Count.WoodType[1] /></tt> instead of that cfoutput loop.<br /> <br /> (Or, you can skip that entirely, and later on just use <tt>#Count.WoodType[1]#</tt> directly.)<br /> <br /> You can probably save a small bit of memory by putting a "TOP 1" after the SELECT too, since by then you'll only care about the first row, but it'll still work without that, just not as efficiently.<br /> <br /> <br /> All this is just an internal tool, yes - not something that will be publicly available?<br /> <br /> In general, you should never directly do #form.whatever# or #url.whatever# or #cookie.whatever# inside a cfquery, because it potentially allows people to inject SQL, which is bad.<br /> <br /> Solution is to use query parameters, like this:<br /> <div class="quotetext" id="code-"><b> code:</b></div><pre class="code">AND rudID = <cfqueryparam value="#form.batch#" /></pre><br /> <br /> Which prevents SQL injection (and sometimes makes it easier for a database to optimise queries, so can make it faster too).<br /> <br /> Same thing applies on the HTML side - to prevent people injecting HTML/JavaScript you need to use HtmlEditFormat for any unsanitized user input:<br /> <div class="quotetext" id="code-"><b> code:</b></div><pre class="code">Run ID = <cfoutput>#HtmlEditFormat(form.batch)#</cfoutput></pre><br /> <br /> Of course, that matters more for stuff on the actual internet - if this is a local tool then it's a question of if you can trust your work colleagues, but both of them are good habits to get into. <span class="emoticon e_smile" title=":)"><span class="e__">:)</span></span><div class="edit_text">EDITED: 20 Apr 2011 23:24 by BOUGHTONP</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.84&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.84"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a38393_85"></a><div class="message" id="message_38393_85"> <div class="message_header"> <div class="message_from"> From: Ken (SHIELDSIT)<span class="message_time">20 Apr 2011 23:53</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 21 Apr 2011 12:23"></span></span> <span class="message_count">85 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.85">38393.85</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.82#a38393_84" target="_self">38393.84</a></div> <div class="message_body"> <p>Yes just an internal tool so I'm not really concerned about injections, but you're right. Get in the habit!</p> <p> </p> <p>I have no doubt there are things that need changed. I haven't used CF in about 2 years and even then I wasn't what I would consider much good at it.</p></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.85&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.85"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a38393_86"></a><div class="message" id="message_38393_86"> <div class="message_header"> <div class="message_from"> From: Ken (SHIELDSIT)<span class="message_time">21 Apr 2011 12:17</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 21 Apr 2011 12:23"></span></span> <span class="message_count">86 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.86">38393.86</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.82#a38393_85" target="_self">38393.85</a></div> <div class="message_body"> Any idea why this would work:<br /> <br /> <div class="quotetext" id="code-"><b> code:</b></div><pre class="code">WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%'</pre><br /> <br /> And this wouldn't?<br /> <br /> <div class="quotetext" id="code-"><b> code:</b></div><pre class="code">WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip LIKE '%boards%' AND rudDescrip LIKE '%blocking%'</pre><br /> <br /> It tells me this:<br /> <br /> <div class="quotetext" id="quote"><b>quote: </b></div><div class="quote">Can't cast Complex Object Type Query to String</div><div class="edit_text">EDITED: 21 Apr 2011 12:19 by SHIELDSIT</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.86&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.86"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a38393_87"></a><div class="message" id="message_38393_87"> <div class="message_header"> <div class="message_from"> From: Peter (BOUGHTONP)<span class="message_time">21 Apr 2011 12:42</span> <div class="clearer"></div> </div><div class="message_to">To: Ken (SHIELDSIT) <span><span class="image post_read" title="Read: 21 Apr 2011 12:44"></span></span> <span class="message_count">87 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.87">38393.87</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.82#a38393_86" target="_self">38393.86</a></div> <div class="message_body"> It's not the NOTs that are making it not work (and if it was, they would give an SQL error instead).<br /> <br /> The error message says you're trying to use a Query variable as if it were a String, and a query can't be implicitly converted (like numbers,booleans,etc can), so it falls over and complains.<br /> <br /> Did you do the change in msg:38393.84 and/or anything else?<br /> <br /> You've only got two variables shown, form.batch and WoodType - and neither of them should be queries based on the previous code, so either the code has changed, or the data has changed and is causing different behaviour in the code.<br /> <br /> Is that definitely the line that the error is pointing to? <br /> If so, are there any other variables inside the cfquery block?</div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.87&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.87"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a38393_88"></a><div class="message" id="message_38393_88"> <div class="message_header"> <div class="message_from"> From: Ken (SHIELDSIT)<span class="message_time">21 Apr 2011 12:47</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 21 Apr 2011 12:48"></span></span> <span class="message_count">88 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.88">38393.88</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.82#a38393_87" target="_self">38393.87</a></div> <div class="message_body"> No the only thing I changed was removing the NOT.<br /> <br /> This works:<br /> <div class="quotetext" id="code-"><b> code:</b></div><pre class="code"><CFQUERY NAME="woodtype" DATASOURCE="isiswood"> SELECT ID=rudID, Category=rudCategory, Seq=rudSeq, Type=rudType, Product_ID=rudProductID, Description=rudDescrip, PcsPerPkg=rudPcsPerPkg, Tally=rudTally, UOM=rudBaseUnitID, Pieces=rudPieces, Volume=rudVolume, NetVolume=rudNetVolume, Cost=rudCost, AddedCost= rudAddedCost, MktValue=rudMarketValue, Total=rudTotal, NetThick=rudNetThick, NetWidth=rudNetWidth, NetLength=rudNetLength, NetType=rudVolType, LocationID=rudLocationID,rudFIFOVolume,rudForceCost,rudBoomID,prdLengthID=(SELECT prdLengthID FROM ProductMaster WHERE prdID=rudProductID) FROM ProductionDetail WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip NOT LIKE '%boards%' AND rudDescrip NOT LIKE '%blocking%' ORDER BY Seq; </cfquery></pre><br /> <br /> This doesn't:<br /> <div class="quotetext" id="code-"><b> code:</b></div><pre class="code"><CFQUERY NAME="bandb" DATASOURCE="isiswood"> SELECT ID=rudID, Category=rudCategory, Seq=rudSeq, Type=rudType, Product_ID=rudProductID, Description=rudDescrip, PcsPerPkg=rudPcsPerPkg, Tally=rudTally, UOM=rudBaseUnitID, Pieces=rudPieces, Volume=rudVolume, NetVolume=rudNetVolume, Cost=rudCost, AddedCost= rudAddedCost, MktValue=rudMarketValue, Total=rudTotal, NetThick=rudNetThick, NetWidth=rudNetWidth, NetLength=rudNetLength, NetType=rudVolType, LocationID=rudLocationID,rudFIFOVolume,rudForceCost,rudBoomID,prdLengthID=(SELECT prdLengthID FROM ProductMaster WHERE prdID=rudProductID) FROM ProductionDetail WHERE rudID='#form.batch#' AND rudCategory='Produced' AND rudProductID LIKE '%#WoodType#%' AND rudDescrip LIKE '%boards%' OR rudDescrip LIKE '%blocking%' ORDER BY Seq; </cfquery></pre></div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.88&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.88"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a38393_89"></a><div class="message" id="message_38393_89"> <div class="message_header"> <div class="message_from"> From: Peter (BOUGHTONP)<span class="message_time">21 Apr 2011 12:53</span> <div class="clearer"></div> </div><div class="message_to">To: Ken (SHIELDSIT) <span><span class="image post_read" title="Read: 21 Apr 2011 12:53"></span></span> <span class="message_count">89 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.89">38393.89</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.82#a38393_88" target="_self">38393.88</a></div> <div class="message_body"> <p>And the name of the query. <span class="emoticon e_tongue" title=":P"><span class="e__">:P</span></span></p> <p> </p> <p>Do you happen to have both queries in the code, but the second query is after the first one?</p> <p> </p> <p>Cus if so, once the first query has executed, the WoodType variable will be replaced with that query, and so the second one will fall over when it tries to use the original WoodType string.</p> <p> </p> <p>(Which is exactly what the error is suggesting.)</p> <p> </p> <p>A simple way to avoid it is to prefix or suffix all query variables, (so you could call it qryWoodType or WoodTypeQry and it wouldn't conflict with the original WoodType string), or you can give it a completely different name.</p><div class="edit_text">EDITED: 21 Apr 2011 12:54 by BOUGHTONP</div> </div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.89&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.89"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a38393_90"></a><div class="message" id="message_38393_90"> <div class="message_header"> <div class="message_from"> From: Ken (SHIELDSIT)<span class="message_time">21 Apr 2011 12:57</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 21 Apr 2011 13:08"></span></span> <span class="message_count">90 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.90">38393.90</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.82#a38393_89" target="_self">38393.89</a></div> <div class="message_body"> I'm a dumbass. Thanks, that was it!</div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.90&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.90"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div><a name="a38393_91"></a><div class="message" id="message_38393_91"> <div class="message_header"> <div class="message_from"> From: Ken (SHIELDSIT)<span class="message_time">23 Apr 2011 19:36</span> <div class="clearer"></div> </div><div class="message_to">To: Peter (BOUGHTONP) <span><span class="image post_read" title="Read: 23 Apr 2011 19:49"></span></span> <span class="message_count">91 of 101</span><div class="clearer"></div> </div> </div> <div class="message_links"> <a href="lmessages.php?webtag=DEFAULT&msg=38393.91">38393.91</a> In reply to <a href="lmessages.php?webtag=DEFAULT&msg=38393.82#a38393_90" target="_self">38393.90</a></div> <div class="message_body"> Pete you any good with the dates in CF? I need to search by month and year. I'm pretty sure I can use #LEFT and #MID. Is that how you would approach it?</div> <div class="message_footer"> <div class="message_footer_links"><a href="lpost.php?webtag=DEFAULT&reply_to=38393.91&return_msg=38393.82" class="reply"><span class="image post"></span>Reply</a></div> <div class="message_vote_form" data-msg="38393.91"> <span class="rating">0/0</span> <span class="image vote vote_down vote_down_off" title="Vote Down"></span> <span class="image vote vote_up vote_up_off" title="Vote Up"></span> </div> </div> </div></div> <div class="message_page_footer"> <ul> <li class="right_col"><form accept-charset="utf-8" method="get" action="lmessages.php" target="_self"><input type="hidden" name="webtag" id="webtag" class="bhinputtext" value="DEFAULT" dir="ltr" /><input type="hidden" name="msg" id="msg" class="bhinputtext" value="38393.92" dir="ltr" /><input type="submit" name="submit" id="keep_reading" value="Keep reading…" class="button" /></form></li> </ul> </div> </div> </body>