Solved

Query ASC and DESC not working

Posted on 2013-06-01
36
375 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
[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
  • 12
  • 9
  • 9
  • +1
36 Comments
 
LVL 83

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 83

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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
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 83

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 110

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 83

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 83

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 110

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 83

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 110

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

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 110

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 83

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 110

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 83

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 110

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 83

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 110

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 83

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 110

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 110

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 83

Expert Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

738 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