Solved

MySQL Query to-from dates

Posted on 2013-06-17
11
157 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 82

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 82

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 51

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 82

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 82

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…
The viewer will learn how to count occurrences of each item in an array.

705 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

11 Experts available now in Live!

Get 1:1 Help Now