Basic Help with Query

[DataMouse]

Hi Guys

 

I'm trying to customise a site.

Basically, I have a table view (four columns).

In each column, I want to showone attribute - product name is the first column.

 

I need to see how to write the first column, so that i can learn how to fill the others.

 

Basically, I need a query that will list the newest 40 products (in date added to store order), and showing how to show this as a result on my page.

 

I've tried playing about with it, but I just get loads of errors.

Can anyone help, please?

 

Thanks

 

DM

 

BTW - There are no product images for this site. It is just the product name text that I need for column 1.

Thanks

Edited August 8, 2008 by DataMouse

[spooks]

new_products.php module lists latest product in date added order, though no date limit.

 

Not sure what you say re display, might help : http://addons.oscommerce.com/info/6051

[DataMouse]

new_products.php module lists latest product in date added order, though no date limit.

 

Not sure what you say re display, might help : http://addons.oscommerce.com/info/6051

 

Cheers Sam.

I'm scrapping the new_products.php page completely and using a box in my index page.

The idea is that the page should look like this:

 

Does that explain it better?

[spooks]

Cheers Sam.

I'm scrapping the new_products.php page completely and using a box in my index page.

The idea is that the page should look like this:

 

Does that explain it better?

 

No, image link is broken!

[DataMouse]

It's showing on my screen in the forum:

http://www.booksatrillion.com/Example1.jpg

[DataMouse]

Okey doke.

I got it to show one poduct "kinda" like I would like it.

See www.booksatrillion.com to see what I mean.

 

I'm after a basic list view with a "buy now" button at the end of every product row.

 

However, if I unlimit my query, it tries to list all products across the page, rather that 1 per line.

I'm geussing that I need an array for each product column.

 

My current code is attached for reference:

 

<?php
 $products_new_array = array();

 $products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
 $products_new_split = new splitPageResults($products_new_query_raw, 1);

 if (($products_new_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3'))) {
?>
	  <td width="300"><div align="left"><?php echo '<b><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products_new['products_id']) . '">' . $products_new['products_name'] . '</a></b><br/>';?></div></td>
	  <?php
 }
?>
	  <?php
 if ($products_new_split->number_of_rows > 0) {
$products_new_query = tep_db_query($products_new_split->sql_query);
while ($products_new = tep_db_fetch_array($products_new_query)) {
  if ($new_price = tep_get_products_special_price($products_new['products_id'])) {
	$products_price = '<s>' . $currencies->display_price($products_new['products_price'], tep_get_tax_rate($products_new['products_tax_class_id'])) . '</s> <span class="productSpecialPrice">' . $currencies->display_price($new_price, tep_get_tax_rate($products_new['products_tax_class_id'])) . '</span>';
  } else {
	$products_price = $currencies->display_price($products_new['products_price'], tep_get_tax_rate($products_new['products_tax_class_id']));
  }
?>
	  <td width="150"><div align="left"><?php echo '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products_new['products_id']) . '">' . $products_new['manufacturers_name'] . '</a><br/>';?></div></td>
	  <td width="150"><div align="left"><?php echo '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products_new['products_id']) . '">' . $products_new['products_price'] . '</a><br/>';?></div></td>
	  <td align="right" valign="middle" class="main"><div align="left"><?php echo '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products_new['products_id']) . '">' . $products_new['products_model'] . '</a><br/>';?></div></td>
	  <?php
}
 } else {
?>
	  <?php
}
?>
	  <td width="73" align="right"><?php echo '<a href="' . tep_href_link(FILENAME_PRODUCTS_NEW, tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $products_new['products_id']) . '">' . tep_image_button('../../../../../images/button_buynow.png', IMAGE_BUTTON_IN_CART) . '</a>'; ?></td>
	</tr>
	<tr>
	  <td colspan="3"><?php echo $products_new_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS_NEW); ?></td>
	  <td colspan="2" align="right" valign="middle" class="main"><?php echo TEXT_RESULT_PAGE . ' ' . $products_new_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>

[spooks]

Your image is still duff, firefox reports its corrupt.

 

I think your not understanding the product_listing, if you look through the code in my contrib (posted ealier) you may see the way.

[DataMouse]

Really appreciate this Sam - but I am completely stuck now!

 

The contribution you have looks awesome. Allowing the customer to change the view is simply superb.

However, its a bit of a sledgehammer to crack a walnut for my needs.

I just need to list products.

 

The default joins product attributes together like:

 

Product Image + Product Name

Procust desciption

Product Price

Product manufacturer

 

I just want to isolate the "Product Name" bit - but allow it to do this:

 

Product name Manufacturer Price Model BuyNow

Product name Manufacturer Price Model BuyNow

Product name Manufacturer Price Model BuyNow

Product name Manufacturer Price Model BuyNow

 

I can do it with just one entry - but need help with the array and reading it correctly.

Edited August 8, 2008 by DataMouse

[spooks]

this will do that, you need to add the missing bits & sort layout etc

 

 

$info_box_contents = array();
 $info_box_contents[] = array('text' => sprintf(TABLE_HEADING_NEW_PRODUCTS, strftime('%B')));

 new contentBoxHeading($info_box_contents);

 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, p.products_price, pd.products_name, if(s.status, s.specials_new_products_price, '') as specials_new_products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' 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, p.products_price, pd.products_name, if(s.status, s.specials_new_products_price, '') as specials_new_products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . 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' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' 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)) {
$info_box_contents[] = array('align' => 'right',
									   'params' => ' width="100%" valign="top"',
									   'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a> ' . $currencies->display_price($new_products['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])). ' <a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $products['products_id']) . '">' . tep_image_button('button_in_cart.gif', IMAGE_BUTTON_BUY_NOW) . '</a>' );


 }

 new contentBox($info_box_contents);

[DataMouse]

Thanks Sam

It's got me going in teh right direction.

 

I'm having a fiddle to see how to get it into a columnar/table view and add in my additional fields for model