Oscommerce Question posted in
The official documentation can be found here

Count 3 tables and check in one of them for existing ID – Oscommerce

1st query; both tables contain all categories_ids

SELECT 
* 
FROM 
categories c,
categories_description cd 
WHERE c.categories_id = cd.categories_id 
ORDER BY sort_order, cd.categories_name

2nd query; this table maybe hold a categories_id

SELECT 
count(*) 
AS total 
FROM 
products_to_categories 
WHERE 
categories_id = "'+ catid +'"'

I need a way to row all categories from the first query (to make a list) and to give me a yes/no or 0/1 from the second query in one SQL query.

The result would look like this:

categories_id | categories_name | total(*)
    1         |    categorie1   |   21       
    2         |    categorie2   |   0 (if categories_id in  'products_to_categories' does not exist

I require it in the following code:

var dbSize = 5 * 1024 * 1024; // 5MB
var db = openDatabase("Oscommerce", "1.0", "Oscommerce Database", dbSize);

var categories={};

var list_str = '';
db.transaction(function (tx) {
    tx.executeSql('SELECT * FROM categories c,categories_description cd WHERE c.categories_id = cd.categories_id ORDER BY categories_id', [], function (tx, results) {
        list_str += '<ul data-role="listview" data-inset="true" data-theme="d">';
        var len = results.rows.length, i;

        for (i = 0; i < len; i++) {
            var r = results.rows.item(i);
            categories[r.categories_id] = r;
        }
        for(key in categories)
        {
            var parent = 0;  
            var value=categories[key];
            catId = value['categories_id'];
            catName = value['categories_name'];
            catImage = value['categories_image'];
            parentId = value['parent_id'];
            if (parentId == parent) 
            {
                list_str += '<li id="'+ catId +'"><a class="parentlink" parentid="'+ parentId +'" catid="'+ catId +'" catname="'+ catName +'"><h3>' + catName + '</h3><p>' + catImage + '</p></a></li>';
                ///i need to do an else around here if the rowed list has products
            }
        }
        list_str += '</ul>';

        $('#parents').html(list_str).find('ul').listview();
    }); 
});

The total output should generate something like this (watch the count bubbles in the list).

Tags:

2

Answers


  1. 0

    Try out something like this:

    select 
  c.categories_id,
  cd.categories_name,
  count(p2c.categories_id) as total
from
  categories c
  join categories_description cd
    on c.categories_id = cd.categories_id
  left join product_to_categories p2c
    on p2c.categories_id = c.categories_id
  group by 
    c.categories_id, 
    cd.categories_name
  order by c.sort_order, cd.categories_name
    Login or Signup to reply.
  2. 0

    This select should be what you want:

    SELECT 
 c.categories_id, cd.categories_name, 
 case when aa.total_per_id is null then 0
   else aa.total_per_id 
 end as total
FROM categories as c
   join categories_description as cd on c.categories_id = cd.categories_id
   left join (
    select a.categories_id, 
     count(*) as total_per_id from product_to_categories a
    group by a.categories_id ) as aa on aa.categories_id = c.categories_id
ORDER BY c.sort_order, cd.categories_name;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search