The records don't add up. why?

[Salvo]

HI al

 

I am creating a small add on to my site, but I am a little stack!!

 

$products_new_query_raw = "select p.products_id, pd.products_description, p.products_model, p.products_image, pd.products_name, s.menu_id, s.menu_title_id, mt.menu_title, s.menu_date_added, s.menu_last_modified, s.expires_date, s.date_status_change, s.status, COUNT(*) from " . TABLE_PRODUCTS . " p, " . TABLE_MENU . " s, " . TABLE_MENU_TITLES . " mt, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and s.menu_title_id = mt.menu_title_id and pd.language_id = '" . $languages_id . "' and p.products_id = s.products_id GROUP BY s.products_id order by pd.products_name";

$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW);

 

 

I have the above select......... Now, I have:

4 Records in the TABLE_MENU

Product 1

Product 2

Product 3

Product 1

 

If I don't use the GROUP BY, it will list all the 4 records and it says:

Displaying 1 to 4 (of 4 new products)

 

If I use GROUP BY, it will list 3 records (this is what I want and it works), but the

Displaying 1 to 2 (of 2 new products)

 

I am not quite sure why, it seems that with GROUP BY the records are not counted

 

Does anybody know why or a better why of using GROUP BY?

 

Thanks

[Salvo]

Hi all,

 

I have been reading about GROUP BY and COUNT the records of the groups and many people have said that the counting don't match up correctly. BUT I haven't found the correct answer.

 

Does anybody (expert in the field) know the correct and simple way of counting records of group using GROUP BY?

 

Thanks

[FalseDawn]

You are lucky that mysql is so forgiving with the syntax here - on a "proper" relational DB, you'd get errors if you included non-aggregated columns in the select list when using a GROUP BY clause.

 

My guess is that the columns being selected are affecting the count in the first case - when the splitpageresults class does its counting, it removes all select fields from the query. How many product ids are in the TABLE_MENU table? If you only have 2 distinct products IDs in here, that's your answer.

[FalseDawn]

Sorry, I see you have 4 records in there. Hmmm. Don't know then. Have you tried using DISTINCT rather than GROUP BY? What version of mysql are you using?

[FalseDawn]

Or you could try grouping by all non-aggregated fields, which is what you should be doing anyway:

 

$products_new_query_raw = "select p.products_id, pd.products_description, p.products_model, p.products_image, pd.products_name, s.menu_id, s.menu_title_id, mt.menu_title, s.menu_date_added, s.menu_last_modified, s.expires_date, s.date_status_change, s.status, COUNT(*) from " . TABLE_PRODUCTS . " p, " . TABLE_MENU . " s, " . TABLE_MENU_TITLES . " mt, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_id = pd.products_id and s.menu_title_id = mt.menu_title_id and pd.language_id = '" . $languages_id . "' and p.products_id = s.products_id GROUP BY p.products_id, pd.products_description, p.products_model, p.products_image, pd.products_name, s.menu_id, s.menu_title_id, mt.menu_title, s.menu_date_added, s.menu_last_modified, s.expires_date, s.date_status_change, s.status order by pd.products_name";

[FalseDawn]

don't forget to change the "GROUP BY" text to lower case, as the splitpageresults class looks for it in lower case I think (I've modified mine, you probably haven't) and the search is case-sensitive.

[Salvo]

This below groups OK, but doesn't count correctly.

 

I need to count the GROUP OF RECORDS s.products_id

 

Any other idea?

 

$products_new_query_raw = "select DISTINCT s.products_id, pd.products_description, p.products_model, p.products_image, pd.products_name, s.menu_id, s.menu_title_id, mt.menu_title, s.menu_date_added, s.status from " . TABLE_PRODUCTS . " p, " . TABLE_MENU . " s, " . TABLE_MENU_TITLES . " mt, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' AND s.status = '1' and p.products_id = s.products_id and p.products_id = pd.products_id and s.menu_title_id = mt.menu_title_id and pd.language_id = '" . $languages_id . "' GROUP BY s.products_id order by pd.products_name";

 

$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW);

[Salvo]

Hi all again

 

I wil try my luck before I create another table to get around the GROUP BY function....

 

Is any expert out there who can solve the riddle above? Thanks

 

Salvo