Databases
StackOverflow2013
Postsdbo
POWhy isn't my mysql query seeing this one combination of two variables but sees the rest?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy isn't my mysql query seeing this one combination of two variables but sees the rest?
    primarykey
    data
    text
    <p>I wrote a script that takes date entries and for some reason whenever I specify just a starting date with a blank end date the query never picks it up. Here's what I wrote.</p> <pre><code>&lt;cfquery name="stec_mysql_loan_tracking_qry" result="meta_tracking" datasource="STLinux1MySQL"&gt; Select tslo.created, tslo.created_by, tslo.last_modified, tslo.last_modified_by, tslo.active, tslo.is_manager, tslo.pick_userid, tslo.customer_code, tslo.name, tst.user_ip as ip, tsl.loan_identifier, tst.command, tsl.tax_search_loan_id as id From tax_search_loan_officers tslo Left Join tax_search_loans tsl On tsl.tax_search_loan_officer_id = tslo.tax_search_loan_officer_id Left Join tax_search_track tst On tst.pick_userid = tslo.pick_userid Where tslo.customer_code In (&lt;cfqueryparam value="#tw_custcodes#" cfsqltype="cf_sql_varchar" list="yes"&gt;) &lt;cfif IsDefined('url.active')&gt; &lt;cfif url.active neq ""&gt; AND tslo.active = &lt;cfqueryparam value="#Trim(url.active)#" cfsqltype="cf_sql_varchar" list="yes"&gt; &lt;/cfif&gt; &lt;/cfif&gt; &lt;cfif IsDefined('url.is_managed')&gt; &lt;cfif url.is_managed neq ""&gt; AND tslo.is_manager = &lt;cfqueryparam value="#Trim(url.is_managed)#" cfsqltype="cf_sql_varchar" list="yes"&gt; &lt;/cfif&gt; &lt;/cfif&gt; &lt;cfif IsDefined('url.start_end')&gt; &lt;cfif url.start_date neq "" and url.end_date eq ""&gt; AND &lt;cfqueryparam value="#Trim(url.start_date)#" cfsqltype="cf_sql_date"&gt; &lt;= DATE_FORMAT(tslo.last_modified, '%Y-%m-%d') AND DATE_FORMAT(tslo.last_modified, '%Y-%m-%d') &lt;= DATE_FORMAT(NOW(), '%Y-%m-%d') &lt;/cfif&gt; &lt;/cfif&gt; &lt;cfif IsDefined('url.start_date')&gt; &lt;cfif url.end_date neq "" and url.start_date eq ""&gt; AND '2012-01-01' &lt;= DATE_FORMAT(tslo.last_modified, '%Y-%m-%d') AND DATE_FORMAT(tslo.last_modified, '%Y-%m-%d') &lt;= &lt;cfqueryparam value="#Trim(url.end_date)#" cfsqltype="cf_sql_date"&gt; &lt;/cfif&gt; &lt;/cfif&gt; &lt;cfif isDefined('url.start_date')&gt; &lt;cfif (url.start_date neq "") and (url.end_date neq "")&gt; AND &lt;cfqueryparam value="#Trim(url.start_date)#" cfsqltype="cf_sql_date"&gt; &lt;= DATE_FORMAT(tslo.last_modified, '%Y-%m-%d') AND DATE_FORMAT(tslo.last_modified, '%Y-%m-%d') &lt;= &lt;cfqueryparam value="#Trim(url.end_date)#" cfsqltype="cf_sql_date"&gt; &lt;/cfif&gt; &lt;/cfif&gt; &lt;/cfquery&gt; </code></pre> <p>And here is what it sees if <code>url.end_date = ""</code> but <code>url.start_date = a value</code>:</p> <pre><code>Select tslo.created, tslo.created_by, tslo.last_modified, tslo.last_modified_by, tslo.active, tslo.is_manager, tslo.pick_userid, tslo.customer_code, tslo.name, tst.user_ip as ip, tsl.loan_identifier, tst.command, tsl.tax_search_loan_id as id From tax_search_loan_officers tslo Left Join tax_search_loans tsl On tsl.tax_search_loan_officer_id = tslo.tax_search_loan_officer_id Left Join tax_search_track tst On tst.pick_userid = tslo.pick_userid Where tslo.customer_code In (?) </code></pre> <p>However, every other combination is fine. I've tried rewriting the <code>cfif</code> blocks but this structure is the only one that gets 2/3 while the rest fail.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMiguel-F
    1. USVinceOmega
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy isn't my mysql query seeing this one combination of two variables but sees the rest?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COcan you explain the issue a little more? I'm not following
      singulars
      1. POWhy isn't my mysql query seeing this one combination of two variables but sees the rest?
      1. USMatt Busche
    2. COsorry for the obvious, but your sure you are sending URL variables with either a form action=get or a href tag, or are you using a FORM POST..... which is a "FORM.variablename"
      singulars
      1. POWhy isn't my mysql query seeing this one combination of two variables but sees the rest?
      1. USsteve
    3. COYeah, the logic is very hard to follow ... can you elaborate? It looks like you have three date variables: `url.start_date`, `url.end_date` and `url.start_end`. What is `url.start_end` - is it a typo? As an aside, you might want to set defaults values for the variables so you could eliminate some of the cfif conditions. Then work on simplifying the rest of the logic, because it seems more complex than is necessary ...
      singulars
      1. POWhy isn't my mysql query seeing this one combination of two variables but sees the rest?
      1. USLeigh
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload