MySQL Query to-from dates

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

LVL 7
rgranlundAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
>>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
 
Dave BaldwinFixer of ProblemsCommented:
Are "custom_design_from" and "custom_design_to" MySQL DATE columns?  You can't do a date comparison if they are not.
0
 
rgranlundAuthor Commented:
Yes, they are date columns
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dave BaldwinFixer of ProblemsCommented:
Try...
AND start_date < curdate()
AND end_date > curdate()

Open in new window

0
 
Ray PaseurCommented:
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
 
PortletPaulfreelancerCommented:
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
 
Julian HansenCommented:
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
 
Dave BaldwinFixer of ProblemsCommented:
": 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
 
PortletPaulfreelancerCommented:
@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
 
Ray PaseurCommented:
Consider using EXPLAIN SELECT.
0
 
Dave BaldwinFixer of ProblemsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.