Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Urgently need help in changing sql query in new_products.php


clementwong

Recommended Posts

HI,

 

Can anyone please tell me how to change the sql query below so that I can get only the 6 latest added products displayed? This is urgent. Thank you.

 

 

Clement

 

 

 

 

<!-- new_products //-->

<tr>

<td>

<?php

$info_box_contents = array();

$info_box_contents[] = array('text' => TABLE_HEADING_NEW_PRODUCTS);

 

new infoBoxHeading1($info_box_contents,true,true,false);

?>

<div style="background-color:#fff; border:1px solid #A6B3BA; border-width:0px 1px; text-align:center; ">

<?php

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

} else {

$new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

}

 

$row = 0;

$col = 0;

$info_box_contents = array();

while ($new_products = tep_db_fetch_array($new_products_query)) {

$new_products['products_name'] = tep_get_products_name($new_products['products_id']);

 

$sql = 'SELECT `products_description` FROM `products_description` WHERE products_id ='.$new_products['products_id'].' && language_id='.(int)$languages_id;

$description_query = tep_db_query($sql);

$description = mysql_fetch_array($description_query, MYSQL_ASSOC);

$description['products_description'] = substr($description['products_description'], 0, 65);

$desc_len = strlen($description['products_description']);

$description['products_description'][$desc_len-1] = '.';

$description['products_description'][$desc_len-2] = '.';

$description['products_description'][$desc_len-3] = '.';

 

if ( ($col == 1 && $row == 0) || ($col == 1 && $row == 2) ){

$info_box_contents[$row][$col] = array('align' => 'center',

'params' => 'width="1" height="100%" valign="middle" ',

'text' => '<table height="" width="100%" border="0" cellspacing="0" cellpadding="0" >

<tr>

<td><div style="width:1px;"><span style="font-size:1px;"> </span></div></td>

</tr>

</table>');

}

elseif (($col == 0 && $row == 1) || ($col == 2 && $row == 1)) {

$info_box_contents[$row][$col] = array('align' => 'center',

'params' => 'height="1"',

'text' => '<table width="95%" border="0" cellspacing="0" cellpadding="0" >

<tr>

<td style="font-size:1px;background:url(images/points.gif) repeat-x;"> </td>

</tr>

</table>');

}

elseif ($col == 1 && $row == 1) {

$info_box_contents[$row][$col] = array('align' => '',

'params' => '',

'text' => '<span style="font-size:1px;"> </span>');

}

else {

$info_box_contents[$row][$col] = array('align' => 'center',

'params' => 'class="main" width="50%" valign="top" align="left"',

'text' => '<table width="96%" cellpadding=0 cellspacing=0 border=0 style="margin-bottom:4px;">

<tr>

 

<td colspan="2" height="35" style="padding:0px 10px;" align=left><div class="productName"><b>'.$new_products['products_name'].'</b></div></td>

</tr>

<tr>

<td style="padding-bottom:2px;"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $new_products['products_image'], $new_products['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT, 'align="absmiddle"') . '</a></td>

<td height="32" valign="bottom" align="left" style="color:#586770; font-size:11px; font-weight:bold;">Price:<br/><span style="color:#C20000; font-size:14px; font-weight:bold; "> ' . $currencies->display_price($new_products['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])).'</span></td>

</tr>

 

<tr>

<td colspan="2" class="main" valign=top align="right" style="padding-right:1px; >'. tep_draw_form('cart_quantity', tep_href_link(FILENAME_PRODUCT_INFO, tep_get_all_get_params(array('action')) . 'action=add_product')).'

<table width="100%" border="0" cellspacing="0" cellpadding="0">

<tr>

<td style="padding-top:1px; padding-right:1px;" ><a href="'.tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']).'">'.tep_image_button('button_details.gif', IMAGE_BUTTON_DETAILS).'</a></td><td style="padding-top:1px;"><a href="' . tep_href_link(FILENAME_DEFAULT, tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $new_products['products_id']) . '">' . tep_image_button('button_add_to_cart.gif', IMAGE_BUTTON_IN_CART) . '</a></td>

</tr>

</table></form>

</td>

</tr>

</table>'); }

 

 

$col ++;

if ($col > 2) {

$col = 0;

$row ++;

}

 

}

new contentBox($info_box_contents, 0);

?>

</div>

<?php new infoBoxFooter(''); ?>

</td>

</tr>

<!-- new_products_eof //-->

Link to comment
Share on other sites

HI,

 

Can anyone please tell me how to change the sql query below so that I can get only the 6 latest added products displayed? This is urgent. Thank you.

Clement

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {

$new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

The MAX_DISPLAY_NEW_PRODUCTS is being set seomwhere. Not sure what file.

You could manually change this yourself :

p.products_date_added desc limit 6);

 

or :

http://www.oscommerce.info/kb/osCommerce/G..._and_Tricks/264

Link to comment
Share on other sites

You have to enter into your database and look for the MAX_DISPLAY_NEW_PRODUCTS inside you have to change the variable to display just 6.

I can't tell you more because I changed all my new_products.php

 

 

Ah yes.

In the config table. You could run this sql :

 

update configuration

set configuration_value = '6'

where configuration_key = 'MAX_DISPLAY_NEW_PRODUCTS'

 

david

Link to comment
Share on other sites

Ah yes.

In the config table. You could run this sql :

 

update configuration

set configuration_value = '6'

where configuration_key = 'MAX_DISPLAY_NEW_PRODUCTS'

 

david

 

 

 

I've run the sql supply by you in the database and it seem no use. The new_products is still out of control. Which means when I set 6 at "Maximum number of new products to display in a category" at the control panel... it shows only 2 items in random and not the one that is added most recently... All I want to know is to understand what actually the new_products.php is getting and displaying from the database?

Link to comment
Share on other sites

I've run the sql supply by you in the database and it seem no use. The new_products is still out of control. Which means when I set 6 at "Maximum number of new products to display in a category" at the control panel... it shows only 2 items in random and not the one that is added most recently...

 

Hm. I'm stumped.

this should be taking care of things :

order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

Check the products_date_added in the products table and make sure the date stamp is being inserted properly.

 

I have to run - but can take a closer look later tonight.

 

d

Link to comment
Share on other sites

I've run the sql supply by you in the database and it seem no use. The new_products is still out of control. Which means when I set 6 at "Maximum number of new products to display in a category" at the control panel... it shows only 2 items in random and not the one that is added most recently... All I want to know is to understand what actually the new_products.php is getting and displaying from the database?

Link to comment
Share on other sites

I've run the sql supply by you in the database and it seem no use. The new_products is still out of control. Which means when I set 6 at "Maximum number of new products to display in a category" at the control panel... it shows only 2 items in random and not the one that is added most recently... All I want to know is to understand what actually the new_products.php is getting and displaying from the database?

 

can you send URL ?

 

d

Link to comment
Share on other sites

I've changed all the date_added in the database for all items to an older date and then changed 6 items to the most current date... but the new_products.php is not showing the new one, it even shows the older ones...

 

I am thinking of getting rid of the new_product and replace with my own html. This thing is getting into my nerves...

Link to comment
Share on other sites

I've changed all the date_added in the database for all items to an older date and then changed 6 items to the most current date... but the new_products.php is not showing the new one, it even shows the older ones...

 

I am thinking of getting rid of the new_product and replace with my own html. This thing is getting into my nerves...

 

http://www.oscommerce.com/community/contributions,3802

Link to comment
Share on other sites

I've changed all the date_added in the database for all items to an older date and then changed 6 items to the most current date... but the new_products.php is not showing the new one, it even shows the older ones...

 

I am thinking of getting rid of the new_product and replace with my own html. This thing is getting into my nerves...

 

Hi Clement - I know you reference the new_products.php file in your original post.

 

Just to be clear :

There is also a products_new.php file :

http://www.urbanteesonline.com/estore/cata...roducts_new.php

 

Is this the file you mean to reference ?

Link to comment
Share on other sites

I don't get the hole posting here.

The new_products part (new products for ...) is by default correct as you seem to want it. This is set in includes/modules/new_products.php

 

but on the url provided I only see the featured products, not the new ones... So I think you need to be looking at a hole different part of code.

Link to comment
Share on other sites

Actually there are two of them, one is the default products_new.php, I have no problem with it, the other one, which I published in this thread, is somehow I think something has done wrong in the sql query. That's why I need clarification and opinion from you guys.

 

Somehow, I have removed the new_products.php and replace it with my own flash. If any of you still interested to look deep into the new_products.php, just let me know, I can set it up in another domain for troubleshooting. I just need to carry on with this website...

 

I do appreciate your help.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...