Jump to content
Sign in to follow this  
DataMouse

Basic Help with Query

Recommended Posts

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 by DataMouse

Share this post


Link to post
Share on other sites

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


Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Share this post


Link to post
Share on other sites
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: Example1.jpg

 

Does that explain it better?

Share this post


Link to post
Share on other sites
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: Example1.jpg

 

Does that explain it better?

 

No, image link is broken!


Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Share this post


Link to post
Share on other sites

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>

Share this post


Link to post
Share on other sites

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.


Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Share this post


Link to post
Share on other sites

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 by DataMouse

Share this post


Link to post
Share on other sites

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);


Sam

 

Remember, What you think I ment may not be what I thought I ment when I said it.

 

Contributions:

 

Auto Backup your Database, Easy way

 

Multi Images with Fancy Pop-ups, Easy way

 

Products in columns with multi buy etc etc

 

Disable any Category or Product, Easy way

 

Secure & Improve your account pages et al.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×