Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

Query ASC and DESC not working

The ORDER BY ASC and DESC is not working.  It always orders it the same way:

$q = "SELECT magentocatalog_category_entity_text.value AS short_desc, magentocatalog_category_entity_varchar.value AS image, magentocatalog_category_entity_datetime.value AS end_date
FROM magentocatalog_category_entity_text, 
magentocatalog_category_entity_varchar,
 magentocatalog_category_entity_datetime
WHERE magentocatalog_category_entity_text.attribute_id='44'
AND magentocatalog_category_entity_text.entity_id='$entity_id'
AND magentocatalog_category_entity_varchar.attribute_id='133'
AND magentocatalog_category_entity_varchar.entity_id='$entity_id'
AND magentocatalog_category_entity_datetime.entity_id='$entity_id'
AND magentocatalog_category_entity_datetime.attribute_id='60'
ORDER BY magentocatalog_category_entity_datetime.value DESC";

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

Open in new window

0
rgranlund
Asked:
rgranlund
  • 12
  • 9
  • 9
  • +1
2 Solutions
 
Dave BaldwinFixer of ProblemsCommented:
The ORDER BY column must be part of the result set.  I think you need to use 'end_date' in the ORDER BY.
0
 
rgranlundAuthor Commented:
I tried that.
0
 
Dave BaldwinFixer of ProblemsCommented:
Tried this?

ORDER BY end_date DESC

You are SELECTing magentocatalog_category_entity_datetime.value AS end_date in the first line.  That means that 'end_date' by itself is in the result set and 'magentocatalog_category_entity_datetime.value' is not.
0
Technology Partners: 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!

 
rgranlundAuthor Commented:
I tried the following and it does not work either:
	$q = "SELECT magentocatalog_category_entity_text.value AS short_desc, magentocatalog_category_entity_varchar.value AS image, magentocatalog_category_entity_datetime.value AS end_date
									FROM magentocatalog_category_entity_text, magentocatalog_category_entity_varchar, magentocatalog_category_entity_datetime
									WHERE magentocatalog_category_entity_text.attribute_id='44'
											AND magentocatalog_category_entity_text.entity_id='$entity_id'
											AND magentocatalog_category_entity_varchar.attribute_id='133'
											AND magentocatalog_category_entity_varchar.entity_id='$entity_id'
											AND magentocatalog_category_entity_datetime.entity_id='$entity_id'
											AND magentocatalog_category_entity_datetime.attribute_id='60'
											ORDER BY end_date ASC";

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

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
Other than "does not work" which is useless information, what do you actually get?

Try this simpler query and let me know what you get.  Not just whether it works...
$q = "SELECT magentocatalog_category_entity_datetime.value AS end_date FROM magentocatalog_category_entity_datetime WHERE magentocatalog_category_entity_datetime.entity_id='$entity_id' AND magentocatalog_category_entity_datetime.attribute_id='60'	ORDER BY end_date ASC";

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

Open in new window

0
 
PortletPaulCommented:
It's a somewhat curious query, there appears to be 2 implied full joins that could be quite inefficient. This may be a leap of faith (as I don't know the data model) but I would prefer to see explicit joins used - I've assumed inner joins
SELECT
      mt.value AS short_desc
    , mv.value AS IMAGE
    , md.value AS end_date
FROM  magentocatalog_category_entity_text as mt
INNER JOIN magentocatalog_category_entity_varchar as mv
        ON mt.entity_id = mv.entity_id and mv.attribute_id = '133'
INNER JOIN magentocatalog_category_entity_datetime as md
        ON mt.entity_id = md.entity_id and mv.attribute_id = '60'
WHERE mt.attribute_id = '44'
    AND mt.entity_id = '$entity_id'
ORDER BY md.value ASC

Open in new window

0
 
PortletPaulCommented:
btw: either table|alias.field or alias 'end_date' should work in the order clause, I prefer using the table|alias.field

however you don't mention which dbms is being used {doh! MySQL - sorry, got it}

oh, & if the INNER JOINs don't suit your purpose try LEFT JOINs instead.
0
 
Ray PaseurCommented:
When you're dealing with something complicated, it's often a good idea to simplify the problem.  This is a pidgin code block intended to show you how to reduce the breadth of the issue into the SSCCE (read about that).  Once you're satisfied that the ORDER BY part is working to your liking, then start adding the other parts of the query.

$q 
= 
"
SELECT 
  magentocatalog_category_entity_text.value AS short_desc
, magentocatalog_category_entity_varchar.value AS image
, magentocatalog_category_entity_datetime.value AS end_date
FROM magentocatalog_category_entity_text
, magentocatalog_category_entity_varchar
, magentocatalog_category_entity_datetime
/* *
 * WHERE magentocatalog_category_entity_text.attribute_id='44'
 * AND magentocatalog_category_entity_text.entity_id='$entity_id'
 * AND magentocatalog_category_entity_varchar.attribute_id='133'
 * AND magentocatalog_category_entity_varchar.entity_id='$entity_id'
 * AND magentocatalog_category_entity_datetime.entity_id='$entity_id'
 * AND magentocatalog_category_entity_datetime.attribute_id='60'
 */
ORDER BY end_date DESC
"
;

Open in new window

0
 
rgranlundAuthor Commented:
@DaveBaldwin

Yes, I gave you insufficient information.  Thanks for you patience.  The Query Works and brings back all of the correct information that it should it just won't ORDER BY:
The returns are always in the same order:


ENDS: 2013-06-30 00:00:00

ENDS: 2013-06-29 00:00:00

ENDS: 2013-06-19 00:00:00

ENDS: 2013-06-11 00:00:00

ENDS: 2013-05-29 00:00:00

I need the information printed out in reverse.
0
 
Dave BaldwinFixer of ProblemsCommented:
At this point, I don't know which query you are talking about.  Your original query or one of the others.
0
 
rgranlundAuthor Commented:
$q = "SELECT magentocatalog_category_entity_text.value AS short_desc,											magentocatalog_category_entity_varchar.value AS image,											magentocatalog_category_entity_datetime.value AS end_date
FROM magentocatalog_category_entity_text,		magentocatalog_category_entity_varchar,										magentocatalog_category_entity_datetime
WHERE magentocatalog_category_entity_text.attribute_id='44'
AND magentocatalog_category_entity_text.entity_id='$entity_id'
AND magentocatalog_category_entity_varchar.attribute_id='133'
AND magentocatalog_category_entity_varchar.entity_id='$entity_id'
AND magentocatalog_category_entity_datetime.entity_id='$entity_id'
AND magentocatalog_category_entity_datetime.attribute_id='60'
ORDER BY end_date";

Open in new window


Returns This:
ENDS: 2013-06-30 00:00:00

ENDS: 2013-06-29 00:00:00

ENDS: 2013-06-19 00:00:00

ENDS: 2013-06-11 00:00:00

ENDS: 2013-05-29 00:00:00

I need the information printed out in reverse.
0
 
Dave BaldwinFixer of ProblemsCommented:
And what happens if you add 'ASC' after 'end_date'?  You are SELECTing 3 items.  Show us the complete results.
0
 
rgranlundAuthor Commented:
If I place ASC or DESC it always puts them out in the exact same order as shown above.  Nothing I do changes the order.
0
 
Ray PaseurCommented:
Try removing the extraneous components of the query. Isolate the part that demonstrates the problem.  Reduce the example to nothing but the problem.  This is the SSCCE and it's the best and fastest way to get help and find a solution!
0
 
PortletPaulCommented:
How are you displaying the output? what comes after the sql query in your code?
0
 
Dave BaldwinFixer of ProblemsCommented:
"If I place ASC or DESC it always puts them out in the exact same order as shown above.  Nothing I do changes the order. "

That wouldn't happen to me.  I gave you a simple query above that only uses one table to run as a test.  Have you done that yet?
0
 
PortletPaulCommented:
here is a simulation of your data (a pure guess on the details)
http://sqlfiddle.com/#!2/d8065/2

It displays that MySQL is sorting the output (ASC or DESC are both working)

I do not believe the sorting issue of your display arises from MySQL - instead the issues arises after the query. e.g. Are you placing the data into a "control"? are the properties of that "control" overriding the presentation of results?

Reducing the sql query to one table may be a good idea to help confirm the problem source but I would not just remove the where clause as proposed by Ray as you will simply get a large cartesian product of all rows from the 3 tables. (Sorry Ray, I don't see how increasing output volume is going to assist here.)

I'd really like to know what follows on after the sql query.
0
 
Ray PaseurCommented:
@PortletPaul: Your point's well taken.  I'm not trying to steer this in the direction of "increasing" output volume.  I'm trying to lead the author in the direction of creating a short and simple example that illustrates the problem -- in simplified terms, with a minimum of distractions.  It's kind of a "give a man a fish or teach a man to fish" differential.  We all know that ASC and DESC work correctly, so when someone thinks these SQL directives are not working right the way I want to lead is toward the simplification of the problem into something anyone can understand and anyone can replicate.  As soon as the author begins to remove the extraneous parts we will find ourselves on the threshold of an answer.
0
 
Dave BaldwinFixer of ProblemsCommented:
I'd like to see the 'complete results' of even one of the queries.  He can always add 'LIMIT 5' to the end of the query.  We keep getting little teeny pieces of a problem that most of us would have solved in less than 5 minutes.  I don't know why people ask questions and keep trying to hide what they are doing which often prevents us from helping them.
0
 
Ray PaseurCommented:
@DaveBaldwin: +1
0
 
PortletPaulCommented:
>> I don't know why people ask questions and keep trying to hide what they are doing
It seems to come from an assumption that it will be easier for us if the problem is distilled before we get to see it - but through the selection process of what the problem is, the true problem gets excluded.

Well that's my guess.
0
 
rgranlundAuthor Commented:
Here is the entire code, in context.  It is what I have so far:

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

//  echo '<h1>'.$today.'</h1>';
//  This determins all of the different sales categories  //
$q = "SELECT entity_id
FROM magentocatalog_category_entity
WHERE parent_id='175'";
$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
if ($r) {

	while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
		$entity_id = $row['entity_id'];

		
		//  This selects all of the Vendor Sales Names  //
		$q = "SELECT value FROM magentocatalog_category_entity_varchar WHERE attribute_id='41' AND entity_id='$entity_id'";
		$r2 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
		if ($r2) {
			while ($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC)) {
				echo '<div class="item" style="width:320px;height;283px;">	<div class="sale-item-cont">';
				$sale_name = $row2['value'];

//  echo '<h3>'.$date_end.'</h3>';
						
						$q = "SELECT value FROM magentocatalog_category_entity_varchar WHERE (attribute_id='57' AND store_id='0' AND entity_id='$entity_id')";

						$r4 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
						if ($r4) {
							$row4 = mysqli_fetch_array($r4, MYSQLI_ASSOC);
							$product_link = $row4['value'];
							
							}				
						
							$q = "SELECT magentocatalog_category_entity_text.value AS short_desc,
											magentocatalog_category_entity_varchar.value AS image,
											magentocatalog_category_entity_datetime.value AS end_date
									FROM magentocatalog_category_entity_text,
											magentocatalog_category_entity_varchar,
											magentocatalog_category_entity_datetime
									WHERE magentocatalog_category_entity_text.attribute_id='44'
											AND magentocatalog_category_entity_text.entity_id='$entity_id'
											AND magentocatalog_category_entity_varchar.attribute_id='133'
											AND magentocatalog_category_entity_varchar.entity_id='$entity_id'
											AND magentocatalog_category_entity_datetime.entity_id='$entity_id'
											AND magentocatalog_category_entity_datetime.attribute_id='60'
											ORDER BY end_date ASC";

							$r3 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
							if ($r3) {
								$row3 = mysqli_fetch_array($r3, MYSQLI_ASSOC);
								$img = $row3['image'];
								$short_desc = $row3['short_desc'];
								$date_end = $row3['end_date'];
								
								$iso_date = $date_end;
								$new_date = date('D F j', strtotime($iso_date));
								//echo $new_date;
					if ($new_date >= $today) {
								echo '<div class="product_img">
										<a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">
											<img src="http://staging.mixxcentury.com/store/media/catalog/category/' . $img . '" />
										</a></div>';

							echo '<div class="sales-verbiage"><span class="sales-title"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">'.$sale_name.'</a></span><br />
							<span class="sales-content"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">'.$short_desc.'</a></span></div>';
							}
							echo '<div class="sale-action">
									<div class="sale-action-left"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">SHOP NOW</a></div>
									<div class="sale-action-right">ENDS: ' . $new_date . '</div>';
							echo '</div>
								</div></div>';
						
						
						}


}
			}
		}

	}

 
?>

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
That makes it quite clear.  You are not looping on the query where the ORDER BY occurs.  In fact you are only getting one row from that query.  Your loop is based on the query above it which doesn't even contain that date.  See below from line 52 I believe.
if ($r3) {
   $row3 = mysqli_fetch_array($r3, MYSQLI_ASSOC);
   $img = $row3['image'];

Open in new window

0
 
Ray PaseurCommented:
What is the column definition for this table.column:

magentocatalog_category_entity_datetime.value

Is that a DATETIME column?  All internal representations of DATETIME values should use the ISO-8601 standard.  Using something like this for a filter is a sure recipe for confusion:

$today = DATE('D F j');

Information about how to handle date and time in PHP and MySQL is available in this article.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

I tried to tidy this code up to make it a little more readable, and while I don't pretend to understand the logic, I think I have found a place that will let us see why the ORDER BY clause appears to be failing.  Please see near line 50.  When you're not 100% certain what a data element contains, var_dump() is your best friend.

<?php
error_reporting(E_ALL);

//  This determins Todays Date to filter sales  //
$today = DATE('D F j');

//  echo '<h1>'.$today.'</h1>';
//  This determins all of the different sales categories  //
$q = "SELECT entity_id FROM magentocatalog_category_entity WHERE parent_id='175'";
$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
if ($r) 
{
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) 
    {
        $entity_id = $row['entity_id'];
        $q = "SELECT value FROM magentocatalog_category_entity_varchar WHERE attribute_id='41' AND entity_id='$entity_id'";
        $r2 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
        if ($r2) 
        {
            while ($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC)) 
            {
                echo '<div class="item" style="width:320px;height;283px;">    <div class="sale-item-cont">';
                $sale_name = $row2['value'];
                $q = "SELECT value FROM magentocatalog_category_entity_varchar WHERE (attribute_id='57' AND store_id='0' AND entity_id='$entity_id')";
                $r4 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
                if ($r4) 
                {
                    $row4 = mysqli_fetch_array($r4, MYSQLI_ASSOC);
                    $product_link = $row4['value'];
                }                
                $q = "SELECT magentocatalog_category_entity_text.value AS short_desc,
                             magentocatalog_category_entity_varchar.value AS image,
                             magentocatalog_category_entity_datetime.value AS end_date
                      FROM magentocatalog_category_entity_text,
                           magentocatalog_category_entity_varchar,
                           magentocatalog_category_entity_datetime
                      WHERE magentocatalog_category_entity_text.attribute_id='44'
                      AND magentocatalog_category_entity_text.entity_id='$entity_id'
                      AND magentocatalog_category_entity_varchar.attribute_id='133'
                      AND magentocatalog_category_entity_varchar.entity_id='$entity_id'
                      AND magentocatalog_category_entity_datetime.entity_id='$entity_id'
                      AND magentocatalog_category_entity_datetime.attribute_id='60'
                      ORDER BY end_date ASC";
                $r3 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
                if ($r3) 
                {
                    $row3 = mysqli_fetch_array($r3, MYSQLI_ASSOC);

                    
/* SHOW THE INFORMATION IN $row3 SO WE CAN SEE THE FORMAT OF THE DATA IN THE 'end_date' RESULT                    
                    var_dump($row3);
                    
                    
                    $img = $row3['image'];
                    $short_desc = $row3['short_desc'];
                    $date_end = $row3['end_date'];
                    $iso_date = $date_end;
                    $new_date = date('D F j', strtotime($iso_date));

                    if ($new_date >= $today) 
                    {
                        echo '<div class="product_img"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '"><img src="http://staging.mixxcentury.com/store/media/catalog/category/' . $img . '" /></a></div>';
                        echo '<div class="sales-verbiage"><span class="sales-title"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">'.$sale_name.'</a></span><br /><span class="sales-content"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">'.$short_desc.'</a></span></div>';
                    }
                    echo '<div class="sale-action"><div class="sale-action-left"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">SHOP NOW</a></div><div class="sale-action-right">ENDS: ' . $new_date . '</div>';
                    echo '</div></div></div>';
                }
            }
        }
    }
}

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
Ray, it doesn't matter.  At line 47 in your code above, only one row is fetched for the query that includes the ORDER BY.  Even if there are multiple items, only the first will be retrieved, whether it is ASC or DESC.  For his display to show the results of the ORDER BY, it would have to be part of the loop that starts at line 20.
0
 
Ray PaseurCommented:
Dave: Yes, I see that now, so maybe the solution is getting closer.  As I said, I didn't try at all to understand the logic.  I just wanted to find something to get a handle on the data, so we could see if this column was a DATE or some other kind of data definition.

I usually start my scripts with var_dump() before I do anything else, just so I can be sure I am working with the data I want.  Most of the time, var_dump() saves me a lot of time and debugging effort.
0
 
Dave BaldwinFixer of ProblemsCommented:
I suppose he could convert the section starting at line 47 into a loop that displays all the data returned for that query.  The table with the date is not part of the loop that starts at line 20 and I don't know how he would change it to include it.
0
 
rgranlundAuthor Commented:
@DaveBaldwin
I made the following change as you suggested.  It does not ASC or DESC but all of the returns are there.
@Ray, yes, that field is a DATETIME

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

//  echo '<h1>'.$today.'</h1>';
//  This determins all of the different sales categories  //
$q = "SELECT entity_id
FROM magentocatalog_category_entity
WHERE parent_id='175'";
$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
if ($r) {

	while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
		$entity_id = $row['entity_id'];

		
		//  This selects all of the Vendor Sales Names and Sale Date //
		$q = "SELECT magentocatalog_category_entity_varchar.value,
					 magentocatalog_category_entity_datetime.value AS end_date
				FROM magentocatalog_category_entity_varchar,
					 magentocatalog_category_entity_datetime
				WHERE magentocatalog_category_entity_varchar.attribute_id='41'
					AND magentocatalog_category_entity_varchar.entity_id='$entity_id'
					AND magentocatalog_category_entity_datetime.entity_id='$entity_id'
					AND magentocatalog_category_entity_datetime.attribute_id='60'
					ORDER BY end_date DESC";
				
		$r2 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
		if ($r2) {
			while ($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC)) {
				echo '<div class="item" style="width:320px;height;283px;">	<div class="sale-item-cont">';
				$sale_name = $row2['value'];
				$date_end = $row2['end_date'];
				$iso_date = $date_end;
				$new_date = date('D F j', strtotime($iso_date));
						
						$q = "SELECT value FROM magentocatalog_category_entity_varchar WHERE (attribute_id='57' AND store_id='0' AND entity_id='$entity_id')";

						$r4 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
						if ($r4) {
							$row4 = mysqli_fetch_array($r4, MYSQLI_ASSOC);
							$product_link = $row4['value'];
							
							}				
							$q = "SELECT magentocatalog_category_entity_text.value AS short_desc,
											magentocatalog_category_entity_varchar.value AS image
									FROM magentocatalog_category_entity_text,
											magentocatalog_category_entity_varchar
									WHERE magentocatalog_category_entity_text.attribute_id='44'
											AND magentocatalog_category_entity_text.entity_id='$entity_id'
											AND magentocatalog_category_entity_varchar.attribute_id='133'
											AND magentocatalog_category_entity_varchar.entity_id='$entity_id'";

							$r3 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
							if ($r3) {
								$row3 = mysqli_fetch_array($r3, MYSQLI_ASSOC);
								$img = $row3['image'];
								$short_desc = $row3['short_desc'];
								//echo $new_date;
					if ($new_date >= $today) {
								echo '<div class="product_img">
										<a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">
											<img src="http://staging.mixxcentury.com/store/media/catalog/category/' . $img . '" />
										</a></div>';

							echo '<div class="sales-verbiage"><span class="sales-title"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">'.$sale_name.'</a></span><br />
							<span class="sales-content"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">'.$short_desc.'</a></span></div>';
							}
							echo '<div class="sale-action">
									<div class="sale-action-left"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">SHOP NOW</a></div>
									<div class="sale-action-right">ENDS: ' . $new_date . '</div>';
							echo '</div>
								</div></div>';
						
						
						}


}
			}
		}

	}

 
?>

Open in new window

0
 
Ray PaseurCommented:
table with the date is not part of the loop
Maybe use a JOIN query?

I think I would like to see the CREATE TABLE statements.
0
 
PortletPaulCommented:
I would just like to remind all players here that the current query (i.e. that given in the question) is currently using implicit full joins that could produce bogus data anyway.

if you go to http://sqlfiddle.com/#!2/d8065/2 and study those results you will see the absence of joining statements in the query (either as explicit joins or within the where clause)  will produce a cartesian product of rows from the 3 tables.

Hence I would like to repeat my original observation, there should be joins.
0
 
rgranlundAuthor Commented:
Everything works on the JFiddle.  But when I implement it always places the dates in the same order, as before.  The following is my updated code.  Made I have misinterpreted what was suggested.

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

//  echo '<h1>'.$today.'</h1>';
//  This determins all of the different sales categories  //
$q = "SELECT entity_id
FROM magentocatalog_category_entity
WHERE parent_id='175'";
$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
if ($r) {

	while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)) {
		$entity_id = $row['entity_id'];

			
				echo '<div class="item" style="width:320px;height;283px;">	<div class="sale-item-cont">';
				$sale_name = $row2['value'];
				$date_end = $row2['end_date'];
				$iso_date = $date_end;
				$new_date = date('D F j', strtotime($iso_date));
						
						$q = "SELECT value FROM magentocatalog_category_entity_varchar WHERE (attribute_id='57' AND store_id='0' AND entity_id='$entity_id')";

						$r4 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
						if ($r4) {
							$row4 = mysqli_fetch_array($r4, MYSQLI_ASSOC);
							$product_link = $row4['value'];
							
							}				
							//  This selects all of the Vendor Sales Names and Sale Date //
		$q = "SELECT magentocatalog_category_entity_varchar.value,
					 magentocatalog_category_entity_datetime.value AS end_date
				FROM magentocatalog_category_entity_varchar,
					 magentocatalog_category_entity_datetime
				WHERE magentocatalog_category_entity_varchar.attribute_id='41'
					AND magentocatalog_category_entity_varchar.entity_id='$entity_id'
					AND magentocatalog_category_entity_datetime.entity_id='$entity_id'
					AND magentocatalog_category_entity_datetime.attribute_id='60'
					ORDER BY end_date DESC";
		
		$q = "SELECT
      				mt.value AS short_desc, mv.value AS image, md.value AS end_date
				FROM  magentocatalog_category_entity_text as mt
				INNER JOIN magentocatalog_category_entity_varchar as mv
        		ON mt.entity_id = mv.entity_id and mv.attribute_id = '133'
				INNER JOIN magentocatalog_category_entity_datetime as md
        		ON mt.entity_id = md.entity_id and md.attribute_id = '60'
				WHERE mt.attribute_id = '44'
    			AND mt.entity_id = '$entity_id'
				ORDER BY md.value DESC";		

							$r3 = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
							if ($r3) {
								$row3 = mysqli_fetch_array($r3, MYSQLI_ASSOC);
								$img = $row3['image'];
								$short_desc = $row3['short_desc'];
								$old_date = $row3['end_date'];
								//echo $new_date;
					if ($new_date >= $today) {
								echo '<div class="product_img">
										<a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">
											<img src="http://staging.mixxcentury.com/store/media/catalog/category/' . $img . '" />
										</a></div>';

							echo '<div class="sales-verbiage"><span class="sales-title"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">'.$sale_name.'</a></span></div>';}
?>
							<!-- span class="sales-content"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">'.$short_desc.'</a></span>';-->
							<?php
							echo '<div class="sale-action">
									<div class="sale-action-left"><a href="http://staging.mixxcentury.com/store/index.php/' . $product_link . '">SHOP NOW</a></div>
									<div class="sale-action-right">ENDS: ' . $old_date . '</div>';
							echo '</div>
								</div></div>';
						
						
						}

}
			}
		

	

 
?>

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
To put it simply, millions of web sites are using ASC and DESC in their MySQL code at this moment and it works fine.  The problem isn't with those words but with the way you have organized it.  

I have to say that I am more confused now with your latest code.  I gave you a simple query above which I suggested you run by itself without all of the other code so you could see that something works.  That is the way I create these things, one working piece at a time.
0
 
Ray PaseurCommented:
+1 for what DaveBaldwin said.  Also, it might be helpful to your efforts if you begin using coding standards - consistent indenting, etc., go a long way toward being able to read and understand your code.

I'd like to home in on lines 32 - 52 in the most recent post.  The variable beginning on line 42 overwrites the variable that was defined on line 32.  Wouldn't it make sense to omit the variable definition on line 32?  Unnecessary lines of code are very unhelpful when it comes to debugging - sort of like turn-signal fluid in a car.

I recommend that you create the SSCCE to illustrate the issue and post it here, complete with the test data we would need to use to create our own example of the problem on our own servers.  Once we have that it will be easy to share some quick progress.  Don't worry if you've never created the SSCCE before - just read the man page and follow the directions.  It doesn't have to be perfect, just free of extraneous parts.
0
 
rgranlundAuthor Commented:
I took a few steps back and looked at everything.  I realized that the date was not part of the initial WHILE statement. Once I did that, everything went smooth:

$q ="SELECT magentocatalog_category_entity.entity_id,
magentocatalog_category_entity_datetime.value AS end_date
FROM magentocatalog_category_entity,
magentocatalog_category_entity_datetime
WHERE magentocatalog_category_entity.parent_id='175'
AND magentocatalog_category_entity_datetime.entity_id=magentocatalog_category_entity.entity_id
AND magentocatalog_category_entity_datetime.attribute_id='60'
ORDER BY end_date ASC";

Open in new window

0
 
Ray PaseurCommented:
Glad to know it's pointed in the right direction now.  Thanks for the points, ~Ray
0
 
Dave BaldwinFixer of ProblemsCommented:
Yes, glad you got it worked out.  Thanks for the points.
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.

  • 12
  • 9
  • 9
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now