Solved

Query ASC and DESC not working

Posted on 2013-06-01
36
336 Views
Last Modified: 2013-06-10
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
Comment
Question by:rgranlund
  • 12
  • 9
  • 9
  • +1
36 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39213403
The ORDER BY column must be part of the result set.  I think you need to use 'end_date' in the ORDER BY.
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39213524
I tried that.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39213534
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39213554
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39213565
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39213900
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39213914
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39214604
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39214723
@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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39214778
At this point, I don't know which query you are talking about.  Your original query or one of the others.
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39214826
$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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39214917
And what happens if you add 'ASC' after 'end_date'?  You are SELECTing 3 items.  Show us the complete results.
0
 
LVL 7

Author Comment

by:rgranlund
ID: 39214932
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39214957
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39214964
How are you displaying the output? what comes after the sql query in your code?
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39214965
"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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215017
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39215084
@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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39215123
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39215146
@DaveBaldwin: +1
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215199
>> 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
 
LVL 7

Author Comment

by:rgranlund
ID: 39215209
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
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 250 total points
ID: 39215267
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 39215911
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39216467
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39216568
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39216586
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39216600
@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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39216704
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39217935
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
 
LVL 7

Author Comment

by:rgranlund
ID: 39219894
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
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39220173
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39220477
+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
 
LVL 7

Author Closing Comment

by:rgranlund
ID: 39235894
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 39235920
Glad to know it's pointed in the right direction now.  Thanks for the points, ~Ray
0
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 39235974
Yes, glad you got it worked out.  Thanks for the points.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn how to dynamically set the form action using jQuery.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

19 Experts available now in Live!

Get 1:1 Help Now