Solved

MySQL Query to-from dates

Posted on 2013-06-17
11
165 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
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 109

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
 
LVL 55

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 109

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this tutorial viewers will learn how to style elements, such a divs, with a "drop shadow" effect using the CSS box-shadow property Start with a normal styled element, such as a div.: In the element's style, type the box shadow property: "box-shad…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

837 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