Product Listing

DataMouse · September 16, 2008

Hi all

I have a site at http://www.booksatrillion.com/index.php that lists products in a table format (no images).

I have added additional fields with a contribution and am wondering how to pull these field values into my query to populate the page.

The searches that I've done so far only show how to view these in product_info.php (which I had already figured out).

Can anyone help?

For info, the extra fields are in the table

products_extra_fields

and are the ISBN and Author columns.

My current code is attached. Thanks

Danny

<table width="823" border="0" cellspacing="0" cellpadding="0">
	<tr>
	  <td width="300" valign="top"><p>Title</p></td>
	  <td width="150" valign="top"><p>Author</p></td>
	  <td width="150" valign="top"><p>Price</p></td>
	  <td width="150" valign="top"><p>ISBN</p></td>
	</tr>

	<tr>
	  <td height="300" colspan="5">
	  <?php
 $info_box_contents = array();

 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' => 'left',
   'params' => ' width="100%" valign="top"',
   'text' => '<td align="left"  width="300" valign="top">
				<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a>
			  </td><td width="150" align="center">' .  $new_products['products_image'] . ' 
			  </td><td width="150" align="right">' . 
				$currencies->display_price($new_products['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])). 
			  '</td><td width="150" align="right"><a href="' . tep_href_link(basename($PHP_SELF), 
				tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $new_products['products_id']) . '">' . tep_image_button('../../../../../images/button_buynow.png', IMAGE_BUTTON_IN_CART) . '</a>
			   </td>' );


 }
 new contentBox($info_box_contents);
?>
	  </td>
	</tr>

  </table>

DataMouse · September 18, 2008

Guys

Are there any takers on this?

I know that I have to change the two queries to include the new table and it's fieds - but cannot figure out how to join.

Can anyone help, please?

♥geoffreywalton · September 19, 2008

the site www.w3schools.com should help as will http://dev.mysql.com/doc/refman/5.0/en/index.html but this is not as user friendly.

DataMouse · September 19, 2008

the site www.w3schools.com should help as will http://dev.mysql.com/doc/refman/5.0/en/index.html but this is not as user friendly.

Thanks geoffrey

I know how to write queries (I am an Access developer) - but am stumped on this one.

I just need help in adding the table in.

Should it be a LEFT join? should I join to PRODUCTS o another table?

This is what I'm stuck on.

Thanks for your help.

♥FWR Media · September 19, 2008

You could try something like the following, but there will need to be a products_id field in your new table to match on

	$new_products_query = tep_db_query("
 SELECT p.products_id, p.products_image, p.products_tax_class_id, p.products_price, pd.products_name, pef.isbn, pef.author, 
 IF(s.status, s.specials_new_products_price, '') AS specials_new_products_price
 FROM (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd) 
 LEFT JOIN " . TABLE_SPECIALS . " s p.products_id = s.products_id
 LEFT JOIN products_extra_fields pef ON pef.products_id = p.products_id
 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);

♥FWR Media · September 19, 2008

I typo'd 3 times :blink: so my suggestion is the above in its current form.

DataMouse · September 19, 2008

I've not tested, but it looks pretty good.

Thanks ever so much Robert - I'll let you know how I get on.

Danny

♥FWR Media · September 19, 2008

Grrr typo 5

LEFT JOIN " . TABLE_SPECIALS . " s ON p.products_id = s.products_id

Tip*

If you only want results where entries exist in the new table use an INNER JOIN . .

INNER JOIN products_extra_fields pef ON pef.products_id = p.products_id

DataMouse · September 21, 2008

OK. This isn't woking.

I've had a look at the database, and it seems that there are two tables installed for the extra products.

One table acts as a junction (products_extra_fields) and has a product_id field.

The second (products_to_products_extra_fields) is where the data is actually kept in a field called "products_extra_fields_value"

So, I'm guessing that i need to reference from product_id in the standard p.products_id to products_extra_fields.products_id, then link products_extra_fields.products_extra_fields_id to products_to_products_extra_fields.products_extra_fields_id

Finally, i need to pull the content from the field products_to_products_extra_fields.products_extra_fields_value where the products_extra_fields_id=1 (for example)

This is getting very messy!

Can you help again, please?

DataMouse · September 21, 2008

Actually, scrap that.

The table called products_to_products_extra_fields has a field called product_id and contains the data that I need if I can restrict the result with a WHERE or HAVING clause.

The structure lookslike this:

products_id products_extra_fields_id products_extra_fields_value

1 1 60093544

1 2 Deborah van Rooyen

1 3 DAEz46zG0060093544

1 4 0060093544

1 5 9780060093549

2 1 56456465

2 2 Deborah van Rooyen

2 3 DAEz46zGasdasdas3544

2 4 3435345345345

2 5 97803453455549

So, I'd need to use product_id to pull all the fields for that product, then use the products_extra_fields_id field to show just the products_extra_fields_value that I want in my table.

Does that make sense?

I know how to do this as a standard SELECT query - but I don't know how to do a select for one value, then move to the next value with another SELECT. Do I need an array?

Thanks

DataMouse · September 21, 2008

OK.

I have a query that returns the data that i need (I think):

	$extra_fields_query = tep_db_query("
SELECT pef.products_extra_fields_status as status, pef.products_extra_fields_name as name, ptf.products_extra_fields_value as value
FROM ". TABLE_PRODUCTS_EXTRA_FIELDS ." pef
LEFT JOIN  ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf
ON ptf.products_extra_fields_id=pef.products_extra_fields_id
WHERE ptf.products_id=". (int) $new_products['products_id'] ." and ptf.products_extra_fields_value<>'' and (pef.languages_id='0' or pef.languages_id='".$languages_id."')
ORDER BY products_extra_fields_order");

I then have my standard array and code to produce the table:

  $row = 0;
 $col = 0;
 $info_box_contents = array();
 while ($new_products = tep_db_fetch_array($new_products_query)) {

$info_box_contents[] = array(
   'align' => 'left',
   'params' => ' width="100%" valign="top"',
   'text' => '<td align="left"  width="300" valign="top">
				<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a>
			  </td><td width="150" align="center">' .  $new_products['products_image'] . ' </td>
			  <td width="150" align="center">' .  AUTHOR VALUE TO GO IN HERE . ' </td>
			  <td width="150" align="right">' . $currencies->display_price($new_products['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])). '</td>
			  <td width="150" align="center">' .  ISBN VALUE TO GO IN HERE . ' </td>
			  <td width="150" align="right"><a href="' . tep_href_link(basename($PHP_SELF), 
				tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $new_products['products_id']) . '">' . tep_image_button('../../../../../images/button_buynow.png', IMAGE_BUTTON_IN_CART) . '</a>
			   </td>' );

How do i combine the two?

DataMouse · September 21, 2008

OK. I've tried fudging it. The page displays, but no data is returned.

Either my queries wrong, or the way the arrays are building is wrong (I think it's the latter).

Any takers?

<?php
 $info_box_contents = array();

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

$extra_fields_query_author = tep_db_query("
SELECT pef.products_extra_fields_status as status, pef.products_extra_fields_name as name, ptf.products_extra_fields_id as author, ptf.products_extra_fields_value as value
FROM ". TABLE_PRODUCTS_EXTRA_FIELDS ." pef
LEFT JOIN  ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf
ON ptf.products_extra_fields_id=pef.products_extra_fields_id
WHERE ptf.products_id=". (int) $new_products['products_id'] ." and ptf.products_extra_fields_value<>'' and ptf.products_extra_fields_id=2
ORDER BY products_extra_fields_order");

$extra_fields_query_ISBN = tep_db_query("
SELECT pef.products_extra_fields_status as status, pef.products_extra_fields_name as name, ptf.products_extra_fields_id as isbn, ptf.products_extra_fields_value as value
FROM ". TABLE_PRODUCTS_EXTRA_FIELDS ." pef
LEFT JOIN  ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf
ON ptf.products_extra_fields_id=pef.products_extra_fields_id
WHERE ptf.products_id=". (int) $new_products['products_id'] ." and ptf.products_extra_fields_value<>'' and ptf.products_extra_fields_id=1
ORDER BY products_extra_fields_order");

 $row = 0;
 $col = 0;
 $info_box_contents = array();
 while ($new_products = tep_db_fetch_array($new_products_query)) {

 while ($extra_fields_a = tep_db_fetch_array($extra_fields_query_author)) {

 while ($extra_fields_i = tep_db_fetch_array($extra_fields_query_ISBN)) {

$info_box_contents[] = array(
   'align' => 'left',
   'params' => ' width="100%" valign="top"',
   'text' => '<td align="left"  width="300" valign="top">
				<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a>
			  </td><td width="150" align="center">' .  $new_products['products_image'] . ' </td>
			  <td width="150" align="center">' .  $extra_fields_a['author'] . ' </td>
			  <td width="150" align="right">' . $currencies->display_price($new_products['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])). '</td>
			  <td width="150" align="center">' .  $extra_fields_i['ISBN'] . ' </td>
			  <td width="150" align="right"><a href="' . tep_href_link(basename($PHP_SELF), 
				tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $new_products['products_id']) . '">' . tep_image_button('../../../../../images/button_buynow.png', IMAGE_BUTTON_IN_CART) . '</a>
			   </td>' );
	}
}
 }
 new contentBox($info_box_contents);
?>

♥FWR Media · September 21, 2008

The structure you show doesn't make sense to me.

Name the fields for each table then itemise the fields you want to show results for.

DataMouse · September 21, 2008

Hi Robert.

The table is called:

products_extra_fields_value (pef)

The fields are:

products_id (standard prod id field)

products_extra_fields_id (identifies which additional field goes with product_id. I want to show where value is 1 (for ISBN) or 2 (for author))

products_extra_fields_value (contains teh actual value. ie. Author name or ISBN number).

The other table is producing results correctly - so I know that the original query is fine. It's just adding in the additional fields that is being a pain.

There will always be additional field data, if that is a concern on the joins...

I've changed the queries, as they were referencing a table that's not needed: (still no joy though)

<?php
 $info_box_contents = array();

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

$extra_fields_query_author = tep_db_query("
SELECT ptf.products_extra_fields_id, ptf.products_extra_fields_value as value
FROM ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf
WHERE ptf.products_id=". (int) $new_products['products_id'] ." and ptf.products_extra_fields_value<>'' and ptf.products_extra_fields_id=2");

$extra_fields_query_isbn = tep_db_query("
SELECT ptf.products_extra_fields_id, ptf.products_extra_fields_value as value
FROM ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf
WHERE ptf.products_id=". (int) $new_products['products_id'] ." and ptf.products_extra_fields_value<>'' and ptf.products_extra_fields_id=1");

 $row = 0;
 $col = 0;
 $info_box_contents = array();
 while ($new_products = tep_db_fetch_array($new_products_query)) {

 while ($extra_fields_a = tep_db_fetch_array($extra_fields_query_author)) {

 while ($extra_fields_i = tep_db_fetch_array($extra_fields_query_isbn)) {

$info_box_contents[] = array(
   'align' => 'left',
   'params' => ' width="100%" valign="top"',
   'text' => '<td align="left"  width="300" valign="top">
				<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a>
			  </td><td width="150" align="center">' .  $new_products['products_image'] . ' </td>
			  <td width="150" align="center">' .  $extra_fields_a['value'] . ' </td>
			  <td width="150" align="right">' . $currencies->display_price($new_products['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])). '</td>
			  <td width="150" align="center">' .  $extra_fields_i['value'] . ' </td>
			  <td width="150" align="right"><a href="' . tep_href_link(basename($PHP_SELF), 
				tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $new_products['products_id']) . '">' . tep_image_button('../../../../../images/button_buynow.png', IMAGE_BUTTON_IN_CART) . '</a>
			   </td>' );
	}
}
 }
 new contentBox($info_box_contents);
?>

DataMouse · September 21, 2008

Hang on! I think I've found the problem!

If I change the query from

$extra_fields_query_author = tep_db_query("

SELECT ptf.products_extra_fields_id, ptf.products_extra_fields_value as value

FROM ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf

WHERE ptf.products_id=". (int) $new_products['products_id'] ." and ptf.products_extra_fields_value<>'' and ptf.products_extra_fields_id=2");

to

$extra_fields_query_author = tep_db_query("

SELECT ptf.products_extra_fields_value as author

FROM ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf

WHERE ptf.products_id=1 and ptf.products_extra_fields_value<>'' and ptf.products_extra_fields_id=2");

I can get the data to display (albeit for only one record).

So the issue must be with how I'm referencing the WHERE ptf.products_id=". (int) $new_products['products_id'] ."

DataMouse · September 21, 2008

Gotcha!

This works (although I have to rework teh layout a little):

<?php
 $info_box_contents = array();

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

	$extra_fields_query_author = tep_db_query("
SELECT ptf.products_extra_fields_value as author
FROM ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf
WHERE ptf.products_id=". (int) $new_products['products_id'] ." and ptf.products_extra_fields_value<>'' and ptf.products_extra_fields_id=2");

$extra_fields_query_isbn = tep_db_query("
SELECT ptf.products_extra_fields_value as isbn
FROM ". TABLE_PRODUCTS_TO_PRODUCTS_EXTRA_FIELDS ." ptf
WHERE ptf.products_id=". (int) $new_products['products_id'] ." and ptf.products_extra_fields_value<>'' and ptf.products_extra_fields_id=1");

 while ($extra_fields_a = tep_db_fetch_array($extra_fields_query_author)) {

 while ($extra_fields_i = tep_db_fetch_array($extra_fields_query_isbn)) {

$info_box_contents[] = array(
   'align' => 'left',
   'params' => ' width="100%" valign="top"',
   'text' => '<td align="left"  width="300" valign="top">
				<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $new_products['products_id']) . '">' . $new_products['products_name'] . '</a>
			  </td><td width="150" align="center">' .  $new_products['products_image'] . ' </td>
			  <td width="150" align="center">' .  $extra_fields_a['author'] . ' </td>
			  <td width="150" align="right">' . $currencies->display_price($new_products['products_price'], tep_get_tax_rate($new_products['products_tax_class_id'])). '</td>
			  <td width="150" align="center">' .  $extra_fields_i['isbn'] . ' </td>
			  <td width="150" align="right"><a href="' . tep_href_link(basename($PHP_SELF), 
				tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $new_products['products_id']) . '">' . tep_image_button('../../../../../images/button_buynow.png', IMAGE_BUTTON_IN_CART) . '</a>
			   </td>' );
	}
}
 }
 new contentBox($info_box_contents);
?>

Thanks ever so much for your help Robert. You're a star!

Product Listing

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived