Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Basic Help with Query


DataMouse

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
Link to comment
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.

Link to comment
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?

Link to comment
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.

Link to comment
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>

Link to comment
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.

Link to comment
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
Link to comment
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.

Link to comment
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

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...