Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MySQL Query to-from dates

Posted on 2013-06-17
11
Medium Priority
?
170 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 84

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 84

Expert Comment

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

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 111

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 49

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 59

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 84

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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 111

Expert Comment

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

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Originally, this post was published on Monitis Blog, you can check it here . Websites are getting bigger and more complicated by the day. Video, images and custom fonts are all great for showcasing your product or service. But the price to pay in…
This article discusses how to implement server side field validation and display customized error messages to the client.
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 learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

636 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