Solved

MySQL Query to-from dates

Posted on 2013-06-17
11
160 Views
Last Modified: 2013-07-15
The following Query Returns all of the items it should however, it is ignoring the:
AND custom_design_from < curdate()
AND custom_design_to > curdate()

It shows everything instead of filtering.  Any ideas here?

<?php
//  This determins Todays Date to filter sales  //
$today = DATE('D. n/j');

//  echo '<h1>'.$today.'</h1>';
//  This determins all of the different sales categories  //


$q ="SELECT entity_id,
	custom_design_to As end_date,
	custom_design_from As start_date,
	thumbnail,
	url_path,
	name
FROM magentocatalog_category_flat_store_1
WHERE parent_id='175'
AND is_active=1
AND custom_design_from < curdate()
AND custom_design_to > curdate()
ORDER BY end_date ASC";

$intCounter = 1;

$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));

	$intNumRows = $r->num_rows;
	$intNumObjs = $intNumRows + 2;

	if ($r) {
	for ($i=1;$i<=$intNumObjs;$i++) {
	$row = mysqli_fetch_array($r, MYSQLI_ASSOC);
	$end_date = $row['end_date'];
	$entity_id = $row['entity_id'];
	$sale_name = $row['name'];
	$product_link = $row['url_path'];
	$img = $row['thumbnail'];

	$q =
  "SELECT value AS short_desc
FROM magentocatalog_category_entity_varchar
WHERE attribute_id='173' AND entity_id='$entity_id'";

		$r5 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n
<br />
MySQL Error: " . mysqli_error($dbc));
		if ($r5) {
			$row5 = mysqli_fetch_array($r5, MYSQLI_ASSOC);
			$short_desc = $row5['short_desc'];
		};
	?>

Open in new window

0
Comment
Question by:rgranlund
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39254486
Are "custom_design_from" and "custom_design_to" MySQL DATE columns?  You can't do a date comparison if they are not.
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39254547
Yes, they are date columns
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39254628
Try...
AND start_date < curdate()
AND end_date > curdate()

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39254832
How do you know it is ignoring some of the fields in the query?  Do you have some test data you can post so we reproduce the issue and we can see the SSCCE in action?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255183
CURDATE() gives today's date.
Hence your existing date range logic will only return records where the (custom_design_from to custom_design_to) duration "spans today".

a simple mock-up of this is available here: http://sqlfiddle.com/#!9/3e2d5/2
3 records, only one of these "spans" a June 2013 date

Please double-check your data, you may find the query is doing exactly what it was asked to do. This might mean your logic isn't correct (e.g. you may only want to evaluate the custom_design_from instead of both the date fields).

'start_date' or 'end_date' aliases will not work the where clause, and would make no difference to filtering logic even of they did.
The 'end_date' alias is available to the order by clause (as the resultset has by that point adopted the alias)
: personally I would recommend you use the true field names(s) in the order by clause in any case.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 52

Expert Comment

by:Julian Hansen
ID: 39255338
Start with your base query and 1 by 1 add the where conditions.

At some point you will receive no results - that will give you a clue as to which part of the query is filtering out all the results.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39255435
": personally I would recommend you use the true field names(s) in the order by clause in any case."

That actually wouldn't work in this case because they are aliased and don't exist in the results.  "ORDER BY end_date ASC" is correct in this case because 'end_date' is included in the results and the original column 'custom_design_to' is not.  ORDER BY must be a column that is part of the results set.  
Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1:
http://dev.mysql.com/doc/refman/5.5/en/select.html
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39255492
>>That actually wouldn't work ... because they are aliased and don't exist in the results.
Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions
Regret to say that you may still refer to true column names throughout
BUT, you may only refer to column aliases AFTER the where clause

due to this - I find - it is simpler (and less of a maintenance dilemma) to use column names consistently through a query - UNLESS one is using nested subqueries THEN you must use the column aliases in the higher level query.

seeing is believing: http://sqlfiddle.com/#!9/3e2d5/5
SELECT entity_id,
	custom_design_to As end_date,
	custom_design_from As start_date,
	thumbnail,
	url_path,
	name
FROM magentocatalog_category_flat_store_1
WHERE parent_id='175'
AND is_active=1
AND custom_design_from < curdate()
AND custom_design_to > curdate()
ORDER BY custom_design_to ASC -- column name in order by (not alias)

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255529
@DaveBaldwin - btw, I can barely spell PHP hence I rarely venture into those waters and I wish I had a small percent of your skills in that area. Wasn't trying to be offensive and sorry if it came across that way.

Although I may just make matters worse - I also don't encourage use of column numbers in the order by... I've seen that go belly-up more than a 100 times (due to some innocent change somewhere in a query) thus my personal recommendation is to stick with true column names in the order by where possible.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39256228
Consider using EXPLAIN SELECT.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39257114
PortletPaul, I'm not offended but I always express myself.  You may be correct but I wouldn't do it your way because I have this vague memory of having to use the alias in the ORDER BY because the column name was no longer available in the result set.  Ray's suggestion is probably right though.  Of course, I have never used an alias in my own work anyway and I keep the column and tables names short to keep the amount of typing down because I don't use any automated methods to generate my code.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Finding original email is quite difficult due to their duplicates. From this article, you will come to know why multiple duplicates of same emails appear and how to delete duplicate emails from Outlook securely and instantly while vital emails remai…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now