Join subcategories query problem

Hi all

I want to show subcategories on only the selected dealer

But the below code is showing subcategories on all dealers.

    »  Blackberry 
             »  Battery (2)
    »  Samsung 
             »  Battery (1)

If Blackberry dealer is selected then only its subcategories should be visible.

Others should be hidden.

<?php
    $qry_sub="select distinct s.subcid, s.subname from subcategories as s
    INNER JOIN
    producttable as p
    ON
    s.subcid = p.subcatg and p.dealerid = ".$dealer_id." and p.status='Y'";
    //echo $qry_sub;
    
    echo "<ul>";
    $result_sub=mysql_query($qry_sub);

while($row_sub=mysql_fetch_array($result_sub))
{
    echo "<li></li>";
}

    echo "</ul>";
?>

Thanks
Vineet

Try it with LEFT JOIN instead of INNER JOIN

Thanks Megazoid

Got it working now

Vineet

[quote=“megazoid, post:2, topic:190253, full:true”]
Try it with LEFT JOIN instead of INNER JOIN
[/quote]there’s a WHERE condition for the p table (the table on the right) so a LEFT OUTER JOIN will behave as an inner join unless that condition is moved to the ON clause

Where? I see only ON in original post

[quote=“megazoid, post:5, topic:190253, full:true”]
Where? I see only ON in original post
[/quote]please forget you saw my post

operating on 0 coffee at the time

:blush:

2 Likes

Just a couple of points about the PHP code:

  1. The old mysql_* extension is deprecated in version 5.5 of PHP and is being removed from the next version (version 7). You should migrate over to the mysqli_* extension or to PDO.

  2. Where is the value for $dealer_id coming from? Any user submitted data needs to be sanititized and escaped. The escaping is best done using prepared statements

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.