Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Categories db_query help!


Guest

Recommended Posts

I am trying to pull only certain categories from my database where the categories_id is equal to a certain value. However it seems that everytime I put in a where clause nothing happens. Maybe someone can be of some help,

 

Here is the db_query that I believe needs to be changed:

 

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$value . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");

 

This is found inside of /includes/boxes/categories.php

 

Thanks for you help,

Link to comment
Share on other sites

I am trying to pull only certain categories from my database where the categories_id is equal to a certain value. However it seems that everytime I put in a where clause nothing happens. Maybe someone can be of some help,

 

Here is the db_query that I believe needs to be changed:

 

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$value . "' and c.categories_id = cd.categories_id and cd.language_id='" . (int)$languages_id ."' order by sort_order, cd.categories_name");

 

This is found inside of /includes/boxes/categories.php

 

Thanks for you help,

 

I run this in the default database and I have result back, so I believe the query is fine, so please check your data and the value that you pass to the query.

 

Good Luck. :D

 

Query:

 

SELECT c.categories_id, cd.categories_name, c.parent_id

FROM CATEGORIES c, CATEGORIES_DESCRIPTION cd

where c.parent_id = 1

and c.categories_id = cd.categories_id

and cd.language_id = 1

ORDER BY sort_order, cd.categories_name

 

Result:

 

categories_id categories_name parent_id

17 CDROM Drives 1

4 Graphics Cards 1

8 Keyboards 1

16 Memory 1

9 Mice 1

6 Monitors 1

5 Printers 1

7 Speakers 1

Link to comment
Share on other sites

maybe $value isnt defined properly previous to this query, you need to check back to the code that creates the $value and see if its ok, try just echo-ing value somewhere after ther query to see if the right value is being picked up

 

<?php echo $value ?>

Reddy to Rumble

 

Thank you osCommerce and all who Contribute to her!

Link to comment
Share on other sites

What I want to do is to create several categories boxes. Therefore I am trying to only grab certain categories (top catagories with no parent) and place them in one categories box. Then I will do the same with another catagories box but instead of grabbing the catagories I just did, I will specify other categories.

 

I know this sounds complicated but I believe that a where statement could allow me to only grab certain top categories.

 

Thanks for the help

Link to comment
Share on other sites

Let's put it this way, your SQL can return top catagories with no parent if the value of c.parent_id = 0

 

Query:

SELECT c.categories_id, cd.categories_name, c.parent_id

FROM CATEGORIES c, CATEGORIES_DESCRIPTION cd

where c.parent_id = 0

and c.categories_id = cd.categories_id

and cd.language_id = 1

ORDER BY sort_order, cd.categories_name

 

Result:

 

categories_id categories_name parent_id

1 Hardware 0

2 Software 0

3 DVD Movies 0

 

 

"Hardware", "Softare" and "DVD Movies" are the top catagories that has no parent already.

 

parent_id is a require field in the database, so "0" should be the top level already.

 

To conclude, I believe your SQL is correct and I suggest you dump the query variable as described by Rumble in order to debug what is wrong.

 

Hope this help. :D

Link to comment
Share on other sites

I guess I'm still being unclear about my problem. I know that the code works and I know that if I make set parent_id to "0" that it will only show the top categories however that isn't what I want.

 

I'm trying to make multiple category boxes. I want to split the existing categories box up. I want to place some parent cetegories and thier sub-categories in one box and some other parent categories and thier sub-categories in another box. Therefore I am going to make multiple categories.php files that have different db_query's in them so that only certain parent categories are called and displayed while others are not called by that box but by another.

 

Therefore, my question is, how can I pull only some categories, and thier subsiquent sub-categories while not pulling others? Could it be in the db_query line? Do I need a where category_id = "something"? I'm not sure.

 

Thanks for you time and help,

Link to comment
Share on other sites

if you only want to pull specific top categories you need a 'where parent_id =' statement in there and specify the parent id's manually, unless there is some other identifying field for these categories.

 

Or you could add an extra field in the categories table called categories_box and all the ones you want in the first category list put 1 in this field, all the categories you want in the next category list put a 2 etc etc then do the where statement as

 

where categories_box = '1'

Reddy to Rumble

 

Thank you osCommerce and all who Contribute to her!

Link to comment
Share on other sites

I had never thought of that - what would be the code to enter another column in one of my categories tables, and which categories table should I put it in?

 

Thanks for the help,

Link to comment
Share on other sites

Well you would need to insert a FIELD called categoies_box in the TABLE called categories in your database.

 

Soemthing like this should do it

 

ALTER TABLE categories ADD categories_box int(2) default '0' not null AFTER last_modified;

 

Then you would either need to add the values for that field for your existing categories

 

OR....

 

Create a new field in the admin section to update it from there ie the admin/categories.php page. This is a bit more complicated

[/code]

Reddy to Rumble

 

Thank you osCommerce and all who Contribute to her!

Link to comment
Share on other sites

Thanks, it seemed to work right off the bat. I made 4 different categories boxes and was able to place all the categories in one box at a time. However when I tried to place certain categories in specific boxes it messed up! I could move one category away from the group but after that only the categories I had moved were appearing? (for example if there was 2 categories in the top box and the rest, 67, in another one, only the 2 were vissable) As well, if I only moved one category, there would be a space where that category should have been withen the group of categories, and clicking any of the group of categories would result in everything disappearing except for the 1 I had moved?

 

Please help,

Link to comment
Share on other sites

In the categories table for each category did you input a value in the categories_box field?

 

For example all categories you want to appear in the first categories infobox should have 1 as a value, for the second they should have 2, the third 3, etc etc etc

 

Then in each of the boxes/categories.php files eg 1, 2, 3 and 4 the where statement should be

 

....... where categories_box = '1' ............

....... where categories_box = '2' ............

....... where categories_box = '3' ............

....... where categories_box = '4' ............

respectively etc etc

Reddy to Rumble

 

Thank you osCommerce and all who Contribute to her!

Link to comment
Share on other sites

I did that and it seems that I can move the whole lot of categories to each categories box. However the problem arises when I try to seperate them. If I move a category down a box it just disappears. When I move one category up a box, it appears in the box above, but it causes a ghost category (a white space) in the box where it came from. Finally, when I move two categories up a box, they appear, but the rest of the categories disappear? If you want me to send you the set of files for interest sake I will, I'm completely stumped!

 

If you have a chance, please take a look,

 

Thanks in advance,

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...