simple query - it won't work, any suggestions?

May 23, 2006

<?php
if ($HTTP_GET_VARS['products_id']) { 
  $info_box_contents = array();
  new contentBoxHeading($info_box_contents);

  $row = 0;
  $col = 0;
  $info_box_contents = array();

	$xsell_cat_query = tep_db_query("select manufacturers_id
									 from " . TABLE_MANUFACTURERS . "
									 where p.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'");

	$xsell_cat_array = tep_db_fetch_array($xsell_cat_query);
	$xsell_category = $xsell_cat_array['manufacturers_id'];
	$new_limit = MAX_DISPLAY_ALSO_PURCHASED - $num_products_xsell;
	$xsell_prod_query = tep_db_query("select distinct p.products_id, 
													  p.products_image, 
													  pd.products_name,
													  pd.products_description  
									  from " . TABLE_PRODUCTS . " p,
										   " . TABLE_MANUFACTURERS . " pc,
										   " . TABLE_PRODUCTS_DESCRIPTION . " pd 
									  where pc.manufacturers_id = '" . $xsell_category . "' and 
											pc.manufacturers_id = p.manufacturers_id and 
											p.products_id != '" . $HTTP_GET_VARS['products_id'] . "' and 
											p.products_id = pd.products_id and 
											pd.language_id = '" . $languages_id . "' and 
											p.products_status = '1' 
									  order by pd.products_name asc 
									  limit " . MAX_DISPLAY_ALSO_PURCHASED);

   while ($xsell = tep_db_fetch_array($xsell_prod_query)) {
	 $xsell['products_name'] = tep_get_products_name($xsell['products_id']);
	 $info_box_contents[$row][$col] = array('align' => 'center',
											'params' => 'class="smallText" width="33%" valign="top"',
											'text' => '<table><tr><td colspan="2" align="center" class="smallText">' . $xsell['products_name'] . '</td></tr><tr><td><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $xsell['products_image'], $xsell['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></td><td class="smallText" valign="top"><br><i>'.substr($xsell['products_description'],0,70).'</i></td></tr></table>');
	 $col ++;
	 if ($col > 1) {
	   $col = 0;
	   $row ++;
	 }
   }
  new contentBox($info_box_contents);
}

?>

error:

1109 - Unknown table 'p' in where clause

select manufacturers_id from manufacturers where p.manufacturers_id = ''

[TEP STOP]

when i remove the p. from where p.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'"); it just shows up blank, no errors and no result (even though there should be)

kgt · May 23, 2006

 $xsell_cat_query = tep_db_query("select manufacturers_id
									 from " . TABLE_MANUFACTURERS . "
									 where manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'");

Remove the p.

May 24, 2006

like i said at the bottom in my first post, i tried that - nothing at all shows.

kgt · May 24, 2006

like i said at the bottom in my first post, i tried that - nothing at all shows.

Well, the p. is nonsensical, since you haven't given any table the alias of "p". That definitely won't work.

Looking further, you've got

pc.manufacturers_id = '" . $xsell_category

Which won't give you anything. You need

$xsell_category['manufacturers_id']

You've also got

$new_limit = MAX_DISPLAY_ALSO_PURCHASED - $num_products_xsell;

but I don't see where $num_products_xsell is set. It also doesn't look like you're using $new_limit, so that line can go.

<?php
if ($HTTP_GET_VARS['products_id']) {
  $info_box_contents = array();
  new contentBoxHeading($info_box_contents);

  $row = 0;
  $col = 0;
  $info_box_contents = array();

	$xsell_cat_query = tep_db_query("select manufacturers_id
									 from " . TABLE_MANUFACTURERS . "
									 where manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'");

	$xsell_cat_array = tep_db_fetch_array($xsell_cat_query);
	$xsell_category = $xsell_cat_array['manufacturers_id'];
	$new_limit = MAX_DISPLAY_ALSO_PURCHASED - $num_products_xsell;
	$xsell_prod_query = tep_db_query("select distinct p.products_id,
													  p.products_image,
													  pd.products_name,
													  pd.products_description  
									  from " . TABLE_PRODUCTS . " p,
										   " . TABLE_MANUFACTURERS . " pc,
										   " . TABLE_PRODUCTS_DESCRIPTION . " pd
									  where pc.manufacturers_id = '" . $xsell_category['manufacturers_id'] . "' and
											pc.manufacturers_id = p.manufacturers_id and
											p.products_id != '" . $HTTP_GET_VARS['products_id'] . "' and
											p.products_id = pd.products_id and
											pd.language_id = '" . $languages_id . "' and
											p.products_status = '1'
									  order by pd.products_name asc
									  limit " . MAX_DISPLAY_ALSO_PURCHASED);

   while ($xsell = tep_db_fetch_array($xsell_prod_query)) {
	 $xsell['products_name'] = tep_get_products_name($xsell['products_id']);
	 $info_box_contents[$row][$col] = array('align' => 'center',
											'params' => 'class="smallText" width="33%" valign="top"',
											'text' => '<table><tr><td colspan="2" align="center" class="smallText">' . $xsell['products_name'] . '</td></tr><tr><td><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $xsell['products_image'], $xsell['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></td><td class="smallText" valign="top"><br><i>'.substr($xsell['products_description'],0,70).'</i></td></tr></table>');
	 $col ++;
	 if ($col > 1) {
	   $col = 0;
	   $row ++;
	 }
   }
  new contentBox($info_box_contents);
}

?>

May 24, 2006

i tried with the p. because this is being posted on a page that already has the manufacuturers_id called from the products table, so i was trying to save a few database calls, but it doesn't seem to be working :)

this is an addon to the x-sell contribution, i didn't write it i'm just trying to modify it to pull items inside a manufacturer instead of inside a category; which is what it's currently doing

i tried the new code you posted but it still shows nothing.

kgt · May 24, 2006

i tried with the p. because this is being posted on a page that already has the manufacuturers_id called from the products table, so i was trying to save a few database calls, but it doesn't seem to be working :)

this is an addon to the x-sell contribution, i didn't write it i'm just trying to modify it to pull items inside a manufacturer instead of inside a category; which is what it's currently doing

i tried the new code you posted but it still shows nothing.

Yeah, the p. will save you nothing, and should be causing errors. Adding a p. is actually a way of explicitly specifying the table from which to pull the field. If you give a table an alias like so:

select * from manufacturers AS p where p.manufacturers_id = 1

or shorthand:

select * from manufacturers p where p.manufacturers_id = 1

You're just giving the table another name for this query. It will affect no other queries and won't use any other queries to run this one. You do this when you're joining two tables, such as manufacturers and products. Both tables have a field called manufacturers_id so you need to be able to specify which is which. If you give the tables aliases, you don't have to type out

where products.manufacturers_id = manufacturers.manufacturers_id

If you don't give a table an alias in a query, then you're telling MySQL to pull information from a non-existing table. The table p doesn't exist (or I assume it doesn't anyways), and you haven't supplied an alias of that name, so it should fail on that.

What will save you a query is removing this line:

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

You're getting the products_name in the query, so you don't need another database call to look it up.

I can tell you're trying to pull something together here. Let's take a step back - what are you trying to display?

And by "nothing" do you mean the box isn't displayed? Is it displayed with errors? Displayed with the correct heading but no content?

May 24, 2006

thanks for the lengthy explanation, i wish everybody would do that when they try to explain something - it really helps a lot!! :)

I can tell you're trying to pull something together here. Let's take a step back - what are you trying to display?

on products_info.php - manufacturers_id is already called.

i want to pull other items from the same manufacturers table that the customer may be interested in.

what this script currently does is pull items from the same category that the customer may be interested in.. but i fingered it up a bit by just trying to replace products_to_category with manufacturers table, etc, etc.

And by "nothing" do you mean the box isn't displayed? Is it displayed with errors? Displayed with the correct heading but no content?

there was never a heading to begin with (i prefer it that way), but it's just blank. no box, no errors

kgt · May 25, 2006

Are you sure

$HTTP_GET_VARS['manufacturers_id']

Exists and is set correctly?

kgt · May 25, 2006

Try this code and see if it works:

<?php
if ($HTTP_GET_VARS['products_id']) {
  $info_box_contents = array( 'text' => '' );//create a new box heading with no heading text
  new contentBoxHeading($info_box_contents);

  $row = 0;
  $col = 0;
  $info_box_contents = array();

	$xsell_cat_query = tep_db_query("select manufacturers_id
									 from " . TABLE_MANUFACTURERS . "
									 where manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'");

	$xsell_cat_array = tep_db_fetch_array($xsell_cat_query);
	$xsell_category = $xsell_cat_array['manufacturers_id'];
	$xsell_prod_query = tep_db_query("select distinct p.products_id,
													  p.products_image,
													  pd.products_name,
													  pd.products_description  
									  from " . TABLE_PRODUCTS . " p,
										   " . TABLE_MANUFACTURERS . " pc,
										   " . TABLE_PRODUCTS_DESCRIPTION . " pd
									  where pc.manufacturers_id = '" . $xsell_category . "' and
											pc.manufacturers_id = p.manufacturers_id and
											p.products_id != '" . $HTTP_GET_VARS['products_id'] . "' and
											p.products_id = pd.products_id and
											pd.language_id = '" . $languages_id . "' and
											p.products_status = '1'
									  order by pd.products_name asc
									  limit " . MAX_DISPLAY_ALSO_PURCHASED);

   while ($xsell = tep_db_fetch_array($xsell_prod_query)) {
	 $info_box_contents[$row][$col] = array('align' => 'center',
											'params' => 'class="smallText" width="33%" valign="top"',
											'text' => '<table><tr><td colspan="2" align="center" class="smallText">' . $xsell['products_name'] . '</td></tr><tr><td><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $xsell['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $xsell['products_image'], $xsell['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></td><td class="smallText" valign="top"><br><i>'.substr($xsell['products_description'],0,70).'</i></td></tr></table>');
	 $col ++;
	 if ($col > 1) {
	   $col = 0;
	   $row ++;
	 }
   }
  new contentBox($info_box_contents);
}

?>

Jan Zonjee · May 25, 2006

This part totally doesn't make any sense. Why query for the manufacturers_id if you already have it (which I doubt):

<?php
	$xsell_cat_query = tep_db_query("select manufacturers_id
									 from " . TABLE_MANUFACTURERS . "
									 where manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'");

Perhaps what he wants to do is get the manufacturers_id, given the products id:

<?php
	$xsell_cat_query = tep_db_query("select manufacturers_id
									 from " . TABLE_PRODUCTS . "
									 where products_id = '" . $HTTP_GET_VARS['products_id'] . "'");

May 25, 2006

Are you sure

$HTTP_GET_VARS['manufacturers_id']

Exists and is set correctly?

manufacturers_id is called on products_info.php

the particular code i'm working with is being called as an include.. i would assume they could work together, am i wrong?

here's the manufacturers query from products_info:

$manufacturer_query = tep_db_query("select m.manufacturers_id, m.manufacturers_name from " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS . " p  where p.products_id = '" . $HTTP_GET_VARS['products_id'] . "' and p.manufacturers_id = m.manufacturers_id");
$manufacturer = tep_db_fetch_array($manufacturer_query);

Try this code and see if it works:

no errors and no result, just blank

This part totally doesn't make any sense. Why query for the manufacturers_id if you already have it (which I doubt):

i didn't write this code, i'm just trying to change over the products_to_categories query to pull the manufacturers instead.. and i'm sure it's obvious i'm no php guru :)

simple query - it won't work, any suggestions?

Recommended Posts

Guest

kgt

Guest

kgt

Guest

kgt

Guest

kgt

kgt

Jan Zonjee

Guest

Archived

Browse

Activity