Build tep_db_query request

[Pagerunner]

Hi,

I created some boxes on the index.php and I want them to show the products inside the subcatagories. I don't know how to built this function tep_db_query (); for mapping all the content inside the subcatagories.

Can anybody help me?

[Guest]

This query will retrieve all products from all categories sorted by categories_id and products_name if you need to retrieve just products for a specific category just add the categories_id that represents the subcategory as a condition and remove the categories order part.

 

btw the current category_id is loaded in $current_category_id in application_top.php

 

  $products_query = tep_db_query("SELECT p.products_id, pd.products_name, pc.categories_id FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " pc WHERE p.products_id = pd.products_id AND p.products_id = pc.products_id AND p.products_status = 1 AND pd.language_id = (int)($languages_id) ORDER BY pc.categories_id, pd.products_name");

[Pagerunner]

Hi enigma1,

thanks for your help.

I copied your code in my additional box, but it didn't work. I think I misunderstood you in some way.

Here is the code of my box. Maybe you can give me an advice or a correction how I have to do it fixing the problem

 

Greetz Mike

 

<?php

 if (isset($current_category_id) && ($current_category_id > 0)) {
   $products_query = tep_db_query("SELECT p.products_id, pd.products_name, pc.categories_id FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " pc WHERE p.products_id = pd.products_id AND p.products_id = pc.products_id AND p.products_status = 1 AND pd.language_id = (int)($languages_id) pc.categories_id='645', pd.products_name");
 } else {
   $products_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_ordered > 0 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_NEW_PRODUCTS);
 }

 if (tep_db_num_rows($products_query) >= MIN_DISPLAY_NEW_PRODUCTS) {
?>
<!-- boxone //-->
         <tr>
           <td>
<?php
   $add_Box_contents = array();
   $add_Box_contents[] = array('text' => ('<a href="http://www.paracomp.de/haendlershop/index.php?cPath=559_645" class=\'add_BoxHeadingLink\'>'. BOX_HEADING_HOT_STUFF . '</a>'));

   new add_BoxHeading($add_Box_contents, false, false);

   $rows = 0;
   $boxone_products_list = '<table border="0" width="180" cellspacing="0" cellpadding="1">';
   while ($boxone_products = tep_db_fetch_array($products_query)) {
     $rows++;
     $boxone_products_list .= '<tr><td class="infoBoxContents" valign="top">'  . '</td><td class="infoBoxContents">'. '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $boxone_products['products_id']) . '">' .tep_image(DIR_WS_IMAGES . $boxone_products['products_image'], $boxone_products['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT).'<br>'. tep_row_number_format($rows). '. <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $boxone_products['products_id']) . '">' . $boxone_products['products_name'] . '</a></td></tr>';
   }
   $boxone_products_list .= '</table>';

   $add_Box_contents = array();
   $add_Box_contents[] = array('align' => 'center',
                  'text' => '<MARQUEE direction="up" height="138" scrollAmount="0" onMouseover="scrollAmount=2" onMouseout="scrollAmount=0">'.$boxone_products_list.'</MARQUEE>');

   new add_Box($add_Box_contents);
?>
           </td>
         </tr>
<!-- boxone //-->
<?php
 }
?>

[Guest]

couple of things you want to check

 

1. Whats the 645 category? You definetely dont want to hard-code numbers there. Instead you should match it with the current_category_id you also forgot the "and" operator. So instead of this:

$products_query = tep_db_query("SELECT p.products_id, pd.products_name, pc.categories_id FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " pc WHERE p.products_id = pd.products_id AND p.products_id = pc.products_id AND p.products_status = 1 AND pd.language_id = (int)($languages_id) pc.categories_id='645', pd.products_name");

 

use this:

$products_query = tep_db_query("SELECT p.products_id, pd.products_name, pc.categories_id FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " pc WHERE p.products_id = pd.products_id AND p.products_id = pc.products_id AND p.products_status = 1 AND pd.language_id = (int)($languages_id) and pc.categories_id=(int)$current_categories_id, pd.products_name");

 

2. dont use hardcoded http references for your pages.

  $add_Box_contents[] = array('text' => ('<a href="http://www.paracomp.de/haendlershop/index.php?cPath=559_645" class=\'add_BoxHeadingLink\'>'. BOX_HEADING_HOT_STUFF . '</a>'));

instead use the tep_href_link function and appent the categories id your want (or products) using a query or the global var.

[Pagerunner]

Hi enigma1,

its funny. The more I got help from you, the less I understand. I'm kidding ...

Normal $PLS="Process Learning Syndrom"; :-)

I played a little with this $current_catagory_id but nothing seems to change.

Here's the Link to the page I'm are working on.

TestPage

There are nine boxes. The example box is in the first line column three.

Inside the last catagory "Specials" in the navigation you find all the subcatagories representing all the boxes you find on the page.

The first box in the first line represents the specials.php. The second box in the first line represents my own notebooks.php. Its just a copy of the specials.php, but with its own database table.

In this case you have to create first the new products in the admin section, change to the catagory specials/notebooks, place them into, then they appear in the box.

Handling it just for one box its okay, but for nine boxes its inconvenient

The more convenient way is to it directly with the tep_db_query function.

And now my problems began and changed my life :-))) In the meantime I know much more about php and mysql, then before starting, but its still to less. But I like it and I'm very, very hungry to learn more :-)

BTW the other boxes in the second and third line are just html code.

Thats just for clearness. Oh, don't want to bother you. If it's to much, just let it me know.

Okay, back to this $current_category_id variable.

You wrote:

if you need to retrieve just products for a specific category just add the categories_id that represents the subcategory as a condition and remove the categories order part.

Thats what I thought and what I want. The cpath number to one of the nine subcatagories is for e.g. is 559_645. You correct me for not using hardcoded numbers.

Okay, but where does the php-script know, which subcatagory I want to show? Where do I have to put in the cpath=559_645?

I thought I have to write it either in the $catagories_id as part of the tep_db_query function or in the $current_category_id inside this if statement (isset($current_category_id) && ($current_category_id > 0)).

Where is the relation between the variable $current_category_id in the application_top.php and the variable $current_category_id in the box script.

The value of $current_category_id in the application_top.php is zero. Changing the value doesn't have any significant change on the page.

One thing is really curious. The example box within the pasted code shows the bestseller products ??? Do you have any explanation for it ???

 

Have a nice day

 

Mike

[Guest]

why you want to use a separate category for the specials? And if you do why you have to fill it in the admin panel. This should be driven by code only. In other words once you have a discount on a product it becomes a special and the TABLE_SPECIALS contains those items. Now for your category box you could add the last row to be the specials as you have and "special case it".

 

The code I posted earlier had the $curent_category_id incorrect. So it should be like this. I also removed the last products_name field (why was there? if you need to sort them use the ORDER sql directive)

$products_query = tep_db_query("SELECT p.products_id, pd.products_name, pc.categories_id FROM " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " pc WHERE p.products_id = pd.products_id AND p.products_id = pc.products_id AND p.products_status = 1 AND pd.language_id = (int)($languages_id) and pc.categories_id=(int)$current_category_id");

The $current_category_id is loaded in application top when the category path (cPath) is present. So its a global variable and before posting the contents of a category you could easily use as an sql condition to retrieve the products you want to post. As of the relationship $current_category_id is the same everywhere since its a global.

 

Ok but now say you want to display scrolling products inside each box on that page you posted. That goes back to the original question. In that case the $current_category_id is of no, use but you still need to retrieve the category_id you want and therefore a similar approach should take place. So lets say want to setup notebooks.

 

So you take a categories query and place the condition for the name to match notebooks (as a test, but in your final code you might want an automated script to pick up say the last 3 categories for your shop as the top row).

 

so to get the last category added

$cat_query = tep_db_query("select c.categories_id, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where cd.language_id = '" . (int)$languages_id . "' order by c.date_added ");

now each time you call the tep_db_fetch_array on cat_query you retrieve the category array in a last to first date-added order. The very first one to be retrieved is the last category added to your shop. So here is your category_id field will be:

$my_cat_array = tep_db_fetch_array($cat_query);
$my_cat_id = $my_cat_array['categories_id'];

So now you have the id to setup the sql for the products to retrieve

[Pagerunner]

Hi enigma1,

thx again, but I still have problems.

The main catagory, I called "specials" doesn't have any relation to the specials.php. It is identical only in the name. Without any function.

The subcatagories inside this maincatagory "specials" share the same name like the additional boxes on the page. They are only containers for the products I want to show in the boxes. The variable cpath always follows the same sample, cpath=maincatagory_subcatagory1_subcatagory2_subcatagory3_etc. So, How can I hand the variable $cpath to my box over? Do I have to create my own query like:

$myown_query = tep_get_path($current_category_id = '559_645') or maybe myown function in the generals.php.

In the generals.php I found this function tep_get_subcategories. Is this maybe the function I have to use? Where can I found the declaration of tep_db_query?

I feel being part of "LOST". New season starts today in Germany. :-)))

 

Greetz Mike, have a nice day

[Guest]

So, How can I hand the variable $cpath to my box over? Do I have to create my own query like:

$myown_query = tep_get_path($current_category_id = '559_645')

yes you will have to. The problem with the hardcoded number is that if you move later a category or do some maintainance to your dbase these numbers will change. So you should identify it in a different way. For instance you could match the categories name. The cpath is available but again points to a category passed through the HTTP_GET_VARS and usually represents the current category.

 

In the generals.php I found this function tep_get_subcategories. Is this maybe the function I have to use?

 

tep_get_subcategories take a category id as input (2nd arg) and returns an array of categories under the given id.(1st arg reference). Once you filter your category id you could retriieve the sub-categories ids using that function.

 

as of the tep_db_query is in includes\functions\database.php

 

So the hard part will be to filter products from a category_id you want. A useful table for it is the TABLE_PRODUCTS_TO_CATEGORIES you could retrieve all products ids once you know the category id. You could fetch the category_id for notebooks by parsing the categories name as a test:

$cat_query = tep_db_query("select c.categories_id, cd.categories_name from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where cd.categories_name = 'notebooks' and cd.language_id = '" . (int)$languages_id"'");

eventually you should replace the name comparison with something more elegant. For example you could create a custom sql table storing those special categories names/ids and control it through your admin cpanel. So if you decide to rename/delete/add in the future categories updates to all tables and to your shop will be done automatically.