delaen Posted March 18, 2003 Share Posted March 18, 2003 In the new_products.php module I am trying to change a query a little bit. select p.products_id, p.products_image, p.products_tax_class_id, pd.products_description, pd.products_name, IF(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and pd.products_id = p.products_id order by p.products_date_added DESC limit " . MAX_DISPLAY_NEW_PRODUCTS I am trying to change this so that it shows the latest products that aren't "upcoming". In other words, I want to display the products that were most recently added but aren't "live" yet. I've tried a few different ways but it never seems to work right. Does anyone know how to do this?[/code] Link to comment Share on other sites More sharing options...
delaen Posted March 18, 2003 Author Share Posted March 18, 2003 Nevermind, figured it out. Link to comment Share on other sites More sharing options...
Ajeh Posted March 18, 2003 Share Posted March 18, 2003 What was the solution? Link to comment Share on other sites More sharing options...
delaen Posted March 18, 2003 Author Share Posted March 18, 2003 What was the solution? Well, I changed that query to: select p.products_id, p.products_image, p.products_tax_class_id, pd.products_description, pd.products_name, products_date_available, IF(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and pd.products_id = p.products_id and to_days(products_date_available) <= to_days(now()) order by p.products_date_added DESC limit " . MAX_DISPLAY_NEW_PRODUCTS It seems to work the way I wanted it to, but I'm not sure if it's exactly right. I haven't been able to test it completely (it hasn't even been one day and I didn't take a ton of time testing it), but so far it seems like products are showing up in the "New Products" box one day later than I had planned. I'm sure I just missed something small somewhere. Link to comment Share on other sites More sharing options...
delaen Posted March 30, 2003 Author Share Posted March 30, 2003 I'm still having problems with this, but at least I think I figured out why. I changed the query to $new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, pd.products_description, pd.products_name, p.products_date_available, IF(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and pd.products_id = p.products_id and to_days(p.products_date_available) <= to_days(now()) order by p.products_date_available DESC limit " . MAX_DISPLAY_NEW_PRODUCTS); And that worked fine for a few days after the new releases became available. A few days later it stopped working and I had no idea why. Suddenly it was just pulling seemingly random products out and displaying them. As soon as the next weeks new releases were live, it was corrected again. But a few days later it was all wrong again. I was trying to figure out why when I noticed that all of the products that were no longer "expected" had a product_date_available value of '0000-00-00'. Does anyone know why that would be reset like that? Does it have something to do with mySQL, or is it something that can be worked out in osc? Or none of the above? Link to comment Share on other sites More sharing options...
Ajeh Posted March 30, 2003 Share Posted March 30, 2003 I just happen to have one turn from expected to available and the products_date_available is replaced with 0000-00-00 00:00:00 rather than blank. This updates the table to be right ... but your code does not take into account blanks and 0000-00-00 00:00:00 Link to comment Share on other sites More sharing options...
delaen Posted March 30, 2003 Author Share Posted March 30, 2003 This updates the table to be right ... but your code does not take into account blanks and 0000-00-00 00:00:00 You're right, it doesn't. But what I was wondering was why - after a product's "date_available" date passes - the date is being set to 0000-00-00 00:00:00. Is that something that is supposed to be happening? As far as I know, that is something that is happening independantly of my code. Link to comment Share on other sites More sharing options...
Ajeh Posted March 30, 2003 Share Posted March 30, 2003 It is not in your code. It is when the products_date_available is reset so it is current. That changes the value from a real date/time to the 0000-00-00 00:00:00 Link to comment Share on other sites More sharing options...
delaen Posted March 30, 2003 Author Share Posted March 30, 2003 Is there any way to stop it from doing so? If so...would that affect anything else? Link to comment Share on other sites More sharing options...
Ajeh Posted March 30, 2003 Share Posted March 30, 2003 Technically, that works with everything else ... except your code ... :shock: Change what you are testing for. The field is either blank or 0000-00-00 00:00:00 when the product is current. Link to comment Share on other sites More sharing options...
delaen Posted March 30, 2003 Author Share Posted March 30, 2003 How would I sort that then? I was hoping to have it still be sorted by product_date_available DESC. I want it to display the last 5 products that have been released. For the time being I've changed it to sort by product_date_added and that kind of works. Although with it this way, I have to make sure I enter products in the order I want them to be listed. I can't add something 6 months in advance, add other products (which are already available i.e in my case, very old DVD's that I just haven't added to my db yet.), and then when it's time to be released expect that product to be listed at the top. If I could somehow sort it by product_date_available, I could though. Since all the products that are now available have 0's as the date, there is no way for me to distinguish between something that was available a long time ago and something that just became available recently. I stink at explaining things sometimes :P Link to comment Share on other sites More sharing options...
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.