MX Shop Forum :: Category Nugget SQL??

Hello,

I am trying to customize the category nugget on latest version of PHP/MYSQL MX Shop.  Specifically I am trying to change some of the SQL that pulls up the active categories.

The SQL looks like this:

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg AND visible_prd=1 WHERE idctg_ctg IS NULL GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

I want to add another parameter to the WHERE clause but it seems if I add an AND or OR in front of WHERE idctg_ctg IS NULL  the query results seem to be blank.  For instance if I were to take the part of the SQL that states AND visible_prd=1 WHERE idctg_ctg IS NULL then the SQL query will return back all of the stores categories, but if I re-arrange the same SQL statement to WHERE idctg_ctg IS NULL AND visible_prd=1 then there are no results.

If would appreciate it if you could explain how to add another condition to the where clause, and still recieve query results.

Thanks,

Sirian

Hello,

The visible_prd = 1 is not part of the WHERE clause (since it appears before it) but is part of the JOIN condition. To add an extra WHERE parameter, you need to add this after the idctg_ctg IS NULL like this:

... WHERE idctg_ctg IS NULL AND MyField = 1...

Regards,
Razvan RACASANU

Hello Razvan,

I was just using visible_prd = 1 as an example, mainly becuase it was a parameter that was already part of the original SQL statement (I didn't want to throw another variable in the mix).  

I know that visible_prd = 1 is part of the JOIN condition, but it should also work if I take it out of the JOIN condition and add it to the WHERE clause parameter.  Meaning that the following SQL statement should work:

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg WHERE idctg_ctg IS NULL AND visible_prd=1 GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

  But it does not work!.......

I have tried to adding a parameter just as you have explained here:

... WHERE idctg_ctg IS NULL AND MyField = 1...

But for some reason, if you add an AND or OR in the WHERE clause after (or before) the following

.......idctg_ctg IS NULL.......

then the results of the query are empty!

So basically, it seems that for some reason you can't add any EXTRA variable or condition to the WHERE clause in this SQL statement without receiving an empty results.

Please help.  Thank you.

Sirian

P.S.  This SQL statement works (the original one):

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg AND visible_prd=1 WHERE idctg_ctg IS NULL GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

RESULTS:

Category1,Category2,Category3, Category4.....

THESE SQL STATEMENTs DO NOT WORK (OR RETURN EMTPY RESULTS):

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg WHERE idctg_ctg IS NULL AND visible_prd=1 GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

Results:

EMPTY (NULL)

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg AND visible_prd=1 WHERE idctg_ctg AND MyField = 1 IS NULL GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

Results:

EMPTY (NULL)

Hello,

Please attach the page with the modified query (the one with the extra condition in the WHERE clause) so we can  have a look at the code.

Regards,
Razvan RACASANU

I have attached the file that includes the parameter I am trying to get to work.  The other sql statments in the file works just fine and one of them also includes the parameter that was added to the sql statement that will not work.  I have also attached my sql database file, just in case you need to look at the structure and contents of the database. 

Also, the the uprodvisible_vis table is a many-to-many table between a master table=userlevels_ulv and detail table=products_prd (just for reference)

 The following is the SQL statement that will not work in the file that I sent you:

$colname5_master1categories_ctg = "0";
if (isset($_SESSION['kt_login_level'])) {
  $colname5_master1categories_ctg = (get_magic_quotes_gpc()) ? $_SESSION['kt_login_level'] : addslashes($_SESSION['kt_login_level']);
}
mysql_select_db($database_mx_shop, $mx_shop);
$query_master1categories_ctg = sprintf("SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM ((categories_ctg LEFT JOIN products_prd ON products_prd.idctg_prd=categories_ctg.id_ctg) LEFT JOIN uprodvisible_vis ON uprodvisible_vis.prdid_vis=products_prd.id_prd AND visible_prd=1) WHERE idctg_ctg IS NULL AND uprodvisible_vis.usrlvid_vis=%s GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC", $colname5_master1categories_ctg);
$master1categories_ctg = mysql_query($query_master1categories_ctg, $mx_shop) or die(mysql_error());
$row_master1categories_ctg = mysql_fetch_assoc($master1categories_ctg);
$totalRows_master1categories_ctg = mysql_num_rows($master1categories_ctg);

Please let me know if you have any questions or need any further information from me.  Thank you,

Sirian

Hi S G,

Regarding this query that you said it does not return any result:

"SELECT categories_ctg.id_ctg, categories_ctg.name_ctg, count(id_prd) as noprd FROM categories_ctg left join products_prd on idctg_prd=id_ctg WHERE idctg_ctg IS NULL AND visible_prd=1 GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC";

If you run this query:

SELECT *
FROM categories_ctg
LEFT JOIN products_prd ON idctg_prd = id_ctg
GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg
ORDER BY order_ctg ASC

you will see that there are no returned rows that contain both idcgt_ctg=NULL and visible_prd=1. So if you add the WHERE filter using these two conditions (like above), you will obtain empty results.

While if you run this query:

SELECT * FROM categories_ctg
left join products_prd on (idctg_prd=id_ctg AND visible_prd=1)
GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg
ORDER BY order_ctg ASC

You will see that several returned rows contain idcgt_ctg=NULL, because the extra JOIN condition (AND visible_prd=1) is actually ignored because of we used LEFT JOIN, which returns all rows from category table, filling in the columns from products table with NULLS, when there is no actual correspondence.
So you can add now the WHERE idctg_ctg IS NULL condition, but even if you obtain some results, the visible_prd=1 will not be taken into account. 

So as you can see, there is a najor difference between the two ways of building this query. And the correct version of the SQL would be the first one - and the actual problem was, like I said, that there are no records within your products table that have idctg_prd=NULL and visible_prd=1 !!!! So this is why you did not see any result.

I hope you've understood my explanation and this being said, I am also giving you a suggestion for your specific usecase:

SELECT id_ctg, name_ctg, COUNT(DISTINCT id_prd) as noprd
FROM (products_prd INNER JOIN uprodvisible_vis ON (id_prd = prdid_vis AND  usrlvid_vis = 0)) RIGHT JOIN categories_ctg ON idctg_prd = id_ctg
GROUP BY name_ctg

(this is a different case and we use INNER JOIN with two join conditions to be sure that only the needed products are shown. We don't put the "usrlvid_vis = 0" condition inside a WHERE, because it would take off the categories with no products returned by the RIGHT JOIN).

regards,
Ionut

View full message

Hello Ionut,

I do kinda get what you are saying about the SQL statements.

I tried the SQL statement you suggested:

SELECT id_ctg, name_ctg, COUNT(DISTINCT id_prd) as noprd
FROM (products_prd INNER JOIN uprodvisible_vis ON (id_prd = prdid_vis AND  usrlvid_vis = 0)) RIGHT JOIN categories_ctg ON idctg_prd = id_ctg
GROUP BY name_ctg

But it returns all of the Sub-categories and the Main Categories BOTH AS Main Categories in one extremely long row down the side of the web page.  I added the WHERE and other GROUP BY paramters (WHERE idctg_ctg IS NULL GROUP BY categories_ctg.id_ctg, categories_ctg.name_ctg ORDER BY order_ctg ASC) to try to group the results, but it just basically gave me the results that I see when I used the original category nugget SQL statement.

What I am trying to do is only have the main categories show on the page that correspond to products that I have chosen to be visible for each particular user level.  I created the "uprodvisible_vis" (many-to-many) table to store the products that each user level will be able to view when they login.  The "uprodvisible_vis" table stores the product id and the userlevel id fromt he products_prd and userlevels_ulv tables, respectively.

If this is too complex for you to give me a working answer on, then it is ok and I'll just keep testing some different solutions.

Thanks,

Sirian

I understand what your database structure is. But have you checked if you actually have any products that have usrlvid_vis = 0 and belong to a category that has id_ctg_ctg = NULL?

Ionut