products_name & products_model

[JEWbacca]

Question:

 

I was looking at the DB structure because I need to pull both the products_name and products_model.... the only problem is that the products_name is located in the products_description table and the products_model is in hte products table. :blush:

 

I'm confused on how I can get both by using a query like this..

 

if ($HTTP_GET_VARS['pID']) {
$products_query = tep_db_query("select products_id, products_model from " . TABLE_PRODUCTS . " where products_id != '" . (int)$HTTP_GET_VARS['pID'] . "' order by products_model");
} else {
$products_query = tep_db_query("select products_id, products_model from " . TABLE_PRODUCTS . " where order by products_model");
}

 

Any Suggestions?

 

Thanks,

 

Nate

[krnl]

try something like:

$products_query = tep_db_query("select pd.products_name, p.products_model from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_DESCRIPTION . " pd where products_id != '" . (int)$HTTP_GET_VARS['pID'] . "' order by p.products_model");

or something along those lines. The key is that you need to query both tables. The "p" after the TABLE_PRODUCTS aliases that tablename to "p" so you can get columns by referencing p.columnname to pull from that table. The same thing for pd.columname after TABLE_PRODUCTS_DESCRIPTION. The left join forces the output of both tables to be merged together. The query above is probably not exactly correct, but it should help point you in the right direction.

 

You can also look through some of the other left join examples throughout the code to get an idea of how they work...then go to phpMyAdmin and play with different select queries until you get the output that you;re looking for.

 

HTH.

 

Question:

 

I was looking at the DB structure because I need to pull both the products_name and products_model.... the only problem is that the products_name is located in the products_description table and the products_model is in hte products table. :blush:

 

I'm confused on how I can get both by using a query like this..

 

if ($HTTP_GET_VARS['pID']) {
$products_query = tep_db_query("select products_id, products_model from " . TABLE_PRODUCTS . " where products_id != '" . (int)$HTTP_GET_VARS['pID'] . "' order by products_model");
} else {
$products_query = tep_db_query("select products_id, products_model from " . TABLE_PRODUCTS . " where order by products_model");
}

 

Any Suggestions?

 

Thanks,

 

Nate

[JEWbacca]

Rick,

 

Thanks :D So I changed the entire thing to:

 

<?php
$products_query_selected = tep_db_query("select xsell_id from " . TABLE_PRODUCTS_XSELL . " where products_id = '" . $HTTP_GET_VARS['pID'] . "'");
$products_array_selected = array(array('id' => ''));
while ($products = tep_db_fetch_array($products_query_selected)) {
  $products_array_selected[] = array('id' => $products['xsell_id']);
}

if ($HTTP_GET_VARS['pID']) {
$products_query = tep_db_query("select pd.products_name, p.products_id, p.products_model from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id != '" . (int)$HTTP_GET_VARS['pID'] . "' order by p.products_model");
} else {
 $products_query = tep_db_query("select pd.products_name, p.products_id, p.products_model from " . TABLE_PRODUCTS . " p left join " . TABLE_PRODUCTS_DESCRIPTION . " pd where order by products_model");
}
while ($products = tep_db_fetch_array($products_query)) {
  $products_array[] = array('id' => $products['products_id'],
								 'text' => $products['products_model']);
}
?>

 

but now i'm getting theses errors, i'm not too sure...

 

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where p.products_id != '1076' order by p.products_model' at line 1

 

select pd.products_name, p.products_id, p.products_model from products p left join products_description pd where p.products_id != '1076' order by p.products_model

 

Thanks,

 

Nate

[JEWbacca]

The error was because both tables have a row named product_id ... I think

 

Needed this...

 

ON (p.products_id = pd.products_id)