Mysql Left join little problem

Discussion in 'PHP' started by Surjit Singh, Aug 28, 2010.

  1. #1
    hi!
    i have little problem with left join

    below script working but i want to add restriction

    field_id = 16 (any value)
    and
    user_id = 65 (any value)

    pls help me how to do this


    id    user_id    field_id    value
    PHP:

    <?php
      $dbHost   =  "localhost";
    $dbUser   =  "root";
    $dbPass   =  "123";
    $dbName   =  "family";
    $options = '';
    
    $link=mysql_connect("$dbHost","$dbUser","$dbPass");
    if(!$link){exit(mysql_error());}
    mysql_select_db("$dbName",$link) or exit(mysql_error());
    
    function display_children($value, $level) {
        $result = mysql_query("SELECT a.user_id, Deriv1.Count FROM `jos_community_fields_values` a LEFT OUTER JOIN (SELECT value, COUNT(*) AS Count FROM `jos_community_fields_values` GROUP BY value) Deriv1 ON a.user_id = Deriv1.value WHERE a.value=" . $value);
        echo "<ul>";
        while ($row = mysql_fetch_assoc($result)) {
            if ($row['Count'] > 0) {
                echo '<li><a>'.$row['user_id'].'</a>';
             display_children($row['user_id'], $level + 1);
             echo "</li>";
            } elseif ($row['Count']==0) {
                echo '<li><a>'.$row['user_id'].'</a></li>';
            } else;
        }
        echo "</ul>";
    }
    
    
    echo display_children(0, 1);
    print mysql_error();
    ?>
    PHP:
     
    Surjit Singh, Aug 28, 2010 IP