SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 28
  1. #1
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Every derived table must have its own alias?

    PHP Code:
    SELECT *
                
    FROM tbl_dvds 
                LEFT JOIN 
    (
                    
    SELECT filmBinding,
                    
    COUNT(filmBinding) AS formatcount
                    FROM tbl_dvds
                    WHERE filmBinding 
    <> ''
                    
    GROUP BY filmBinding)
                    
    ORDER BY binding_num DESC
                LEFT JOIN 
    (
                    
    SELECT filmAgeRating,
                    
    COUNT(filmAgeRating) AS agecount
                    FROM tbl_dvds
                    GROUP BY filmAgeRating

    above is the problematic query which is returning the Every derived table must have its own alias?

    anyone able to help me fix it please?
    thanks

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    SELECT *             
    FROM tbl_dvds              
    LEFT JOIN 
      (SELECT 
           filmBinding
         , COUNT(filmBinding) AS formatcount
       FROM tbl_dvds              
       WHERE filmBinding <> ''              
       GROUP BY filmBinding
      ) AS join1       
    ORDER BY binding_num DESC             
    LEFT JOIN 
      (SELECT
           filmAgeRating
         , COUNT(filmAgeRating) AS agecount             
       FROM tbl_dvds              
       GROUP BY filmAgeRating
      ) AS join2
    See the parts in red.

    You should get an error on that ORDER BY in the middle.
    And you don't have any join criteria (no ON clauses).

  3. #3
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    thanks for your reply, you were spot on

    im now gettng
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY binding_num DESC LEFT JOIN ( SELECT filmAgeRating , COUNT( filmAgeR' at line 1

    whats wrong with it? and re: the join criteria do i need any?
    many thanks mate!

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Try moving the ORDER BY to the end of the query.

    And if you don't specify any join criteria, it means that all rows from the second table are joined to all rows from the first table. It becomes a CROSS JOIN.

  5. #5
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok tried moving the order by to the end but had the same error so then tried removing the order by all together and i get this error

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 2

    using this code
    PHP Code:
    SELECT *             
    FROM tbl_dvds              
    LEFT JOIN
      
    (SELECT 
           filmBinding
         
    COUNT(filmBinding) AS formatcount
       FROM tbl_dvds              
       WHERE filmBinding 
    <> ''              
       
    GROUP BY filmBinding
      
    ) AS join1             
    LEFT JOIN
    (SELECT
    filmAgeRating

    COUNT(filmAgeRating) AS agecount             
    FROM tbl_dvds              
    GROUP BY filmAgeRating
    ) AS join2 
    in mydbadmin

    my aim of this is to get a count on the number of items matching each of filmBindings and filmAgeRatings from the same table

    thanks

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    I don't see a LIMIT in that query?

  7. #7
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok am getting results returned with this
    PHP Code:
                    SELECT *
                    
    FROM tbl_dvds 
                    LEFT JOIN 
                    
    (
                        
    SELECT filmBinding,
                        
    COUNT(filmBinding) AS formatcount
                        FROM tbl_dvds
                        WHERE filmBinding 
    <> ''
                        
    GROUP BY filmBinding ORDER BY formatcount DESC 

                    
    ) AS join1 ON filmID filmID
                    LEFT JOIN 
                    
    (
                        
    SELECT filmAgeRating,
                        
    COUNT(filmAgeRating) AS agecount
                        FROM tbl_dvds
                        WHERE filmAgeRating 
    <> '' && filmAgeRating <> 'To Be Announced'
                        
    GROUP BY filmAgeRating
                    
    ) AS join2 ON filmID filmID
    GROUP BY filmID 
    but the totals are not correct they are the overall totals of the number of records in the db table and not relitave to the field names?

    will try and play around with it,

    any ideas?
    thx

  8. #8
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    I don't see a LIMIT in that query?
    limit is automatically added to all queries ran inside the dbadmin.

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by ljack84 View Post
    any ideas?
    thx
    Yes, your ON clauses are useless (except for creating a correct syntax). You need criteria that join the different tables. For example:
    Code:
    SELECT *                
    FROM tbl_dvds AS maintable                
    LEFT JOIN               
      (                 
         SELECT 
             filmID
           , filmBinding
           , COUNT(filmBinding) AS formatcount              
         FROM tbl_dvds              
         WHERE filmBinding <> ''              
         GROUP BY filmID, filmBinding 
         ORDER BY formatcount DESC            
      ) AS join1 
    ON maintable.filmID = join1.filmID                
    LEFT JOIN              
       (               
          SELECT 
               filmID
             , filmAgeRating
             , COUNT(filmAgeRating) AS agecount               
          FROM tbl_dvds              
          WHERE filmAgeRating <> '' 
          AND filmAgeRating <> 'To Be Announced'               
          GROUP BY filmAgeRating         
        ) AS join2 
    ON maintable.filmID = join2.filmID

  10. #10
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok im assuming that works becuse now im getting a damn

    The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

    i only want to count the items matching two columns lol

  11. #11
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok got it sorted

    this formula works
    PHP Code:
    SELECT *
    FROM tbl_dvds AS maintable
    LEFT JOIN 
    (

    SELECT filmBindingCOUNTfilmBinding ) AS formatcount
    FROM tbl_dvds
    WHERE filmBinding 
    <> ''
    GROUP BY filmBinding
    ORDER BY formatcount DESC
    ) AS join1 ON maintable.filmBinding join1.filmBinding
    LEFT JOIN 
    (

    SELECT filmAgeRatingCOUNTfilmAgeRating ) AS agecount
    FROM tbl_dvds
    WHERE filmAgeRating 
    <> ''
    AND filmAgeRating <> 'To Be Announced'
    GROUP BY filmAgeRating
    ) AS join2 ON maintable.filmAgeRating join2.filmAgeRating 
    i remove the filmID bits and it now seems to work as expected
    thanks for your help mate really appreciate it,

    i'll pop back if i have any further problems

  12. #12
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ha back again lol,

    one little problem i have with my code now is that then echoing out the filmBindings in my php its echoing out 39000+ bindings instead of each one once?

    is this something that needs changing in the query or my php code?

    here is my code
    PHP Code:
                $query mysql_query
                
    ("
                    SELECT *
                    FROM tbl_dvds AS maintable
                    LEFT JOIN
                    (
                        SELECT filmBinding, COUNT( filmBinding ) AS formatcount
                        FROM tbl_dvds
                        WHERE filmBinding <> ''
                        GROUP BY filmBinding
                        ORDER BY formatcount DESC
                    ) AS join1 ON maintable.filmBinding = join1.filmBinding
                    LEFT JOIN 
                    (
                        SELECT filmAgeRating, COUNT( filmAgeRating ) AS agecount
                        FROM tbl_dvds
                        WHERE filmAgeRating <> ''
                        AND filmAgeRating <> 'To Be Announced'
                        GROUP BY filmAgeRating
                    ) AS join2 ON maintable.filmAgeRating = join2.filmAgeRating
                "
    );
                while(
    $row mysql_fetch_array($query))
                {
                    
    $link '';
                    
    $binding $row['filmBinding'];
                    
    $label $binding." (".$row['formatcount'].")";
                    
    $divider "?";
                    if(
    $cat)
                    {
                        
    $link $link.$divider."cat=$cat";
                        
    $divider "&amp;";
                    }
                    if(
    $age)
                    {
                        
    $link $link.$divider."age=$age";
                        
    $divider "&amp;";
                    }
                    
    $link $link.$divider."bin=$count";
                    if(
    $bin == $count)
                    {
                        echo 
    "<li class='select'><a href='$link'>".$label."</a></li>";
                    }
                    else
                    {
                        echo 
    "<li class='nonselect'><a href='$link'>".$label."</a></li>";
                    }
                    
    $count++;
                } 
    thanks

  13. #13
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    Are there 39,000 rows on the tbl_dvd table? If so, that's how many rows will be returned since there is no criteria limiting it. I have a feeling your basic premise for the SQL statement is not what you're expecting.

    What exactly are you trying to accomplish? Can you provide table structures for these three tables, some sample data for each, then a sample of the output you're expecting to see?

    Once you provide that, we should be able to steer you in the right direction.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  14. #14
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi dave,

    ok

    yeah tbl_dvds has over 39000 rows in it,

    at the moment i have two individual queries to get the number of rows for each of the unique filmBindings and each of the unique filmAgeRatings like so

    to get the number of rows for each type of binding i have
    PHP Code:
    $query mysql_query("
    SELECT filmBinding,COUNT(filmBinding) 
    AS binding_num 
    FROM tbl_dvds 
    WHERE filmBinding <> ''
    GROUP BY filmBinding 
    ORDER BY binding_num DESC
    "
    )or die (mysql_error()); 
    this would return
    Code:
    filmBinding 	binding_num
    DVD 	39042
    Blu-Ray 	2537
    3D Blu-Ray 	36
    3D DVD 	15

    and to get the number of rows for each type of agerating i have
    PHP Code:
    $query mysql_query("
    SELECT filmAgeRating,COUNT(filmAgeRating) AS cat_num 
    FROM tbl_dvds 
    WHERE filmAgeRating <> '' && filmAgeRating <> 'To Be Announced' 
    GROUP BY filmAgeRating"
    )or die (mysql_error()); 
    would return
    Code:
    filmAgeRating 	cat_num
    12 years and over 	4201
    15 years and over 	9236
    18 years and over 	4808
    Exempt 	11678
    Parental Guidance 	4945
    Universal 	4188
    but the main problem with my page at the moment is that i have too many querys and its maxing out and loosing connection when trying to perform sepecific jobs so im trying to cut down on the amount of queries and clean up my code

    here is my test page
    click on action and adventure then comedy and you will see the error

    so basically what im trying to do is put those two queries above and get the same results by combining them into one query

  15. #15
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the best code ive come up with so far is
    PHP Code:
    SELECT DISTINCT maintable.filmAgeRatingmaintable.filmBinding,formatcount,agecount             
    FROM tbl_dvds 
    AS maintable                
    LEFT JOIN               
      
    (                 
         
    SELECT 
             filmBinding
           
    COUNT(filmBinding) AS formatcount              
         FROM tbl_dvds              
         WHERE filmBinding 
    <> ''              
         
    GROUP BY filmBinding 
         ORDER BY formatcount DESC            
      
    ) AS join1 
    ON maintable
    .filmBinding join1.filmBinding                
    LEFT JOIN              
       
    (               
          
    SELECT 
               filmAgeRating
             
    COUNT(filmAgeRating) AS agecount               
          FROM tbl_dvds              
          WHERE filmAgeRating 
    <> '' 
          
    AND filmAgeRating <> 'To Be Announced'               
          
    GROUP BY filmAgeRating         
        
    ) AS join2 
    ON maintable
    .filmAgeRating join2.filmAgeRating
    WHERE maintable
    .filmBinding <> '' AND maintable.filmAgeRating <> '' AND maintable.filmAgeRating <> 'To Be Announced' AND maintable.filmAgeRating <> 'NULL'
    GROUP BY maintable.filmAgeRating,maintable.filmBinding 
    which returns 22 rows like so
    Code:
    filmAgeRating 	filmBinding 	formatcount 	agecount
    12 years and over 	3D Blu-Ray 	36 	4201
    12 years and over 	Blu-Ray 	2537 	4201
    12 years and over 	DVD 	39042 	4201
    15 years and over 	3D Blu-Ray 	36 	9236
    15 years and over 	3D DVD 	15 	9236
    15 years and over 	Blu-Ray 	2537 	9236
    15 years and over 	DVD 	39042 	9236
    18 years and over 	3D Blu-Ray 	36 	4808
    18 years and over 	3D DVD 	15 	4808
    18 years and over 	Blu-Ray 	2537 	4808
    18 years and over 	DVD 	39042 	4808
    Exempt 	3D Blu-Ray 	36 	11678
    Exempt 	3D DVD 	15 	11678
    Exempt 	Blu-Ray 	2537 	11678
    Exempt 	DVD 	39042 	11678
    Parental Guidance 	3D Blu-Ray 	36 	4945
    Parental Guidance 	3D DVD 	15 	4945
    Parental Guidance 	Blu-Ray 	2537 	4945
    Parental Guidance 	DVD 	39042 	4945
    Universal 	3D Blu-Ray 	36 	4188
    Universal 	3D DVD 	15 	4188
    Universal 	Blu-Ray 	2537 	4188
    Universal 	DVD 	39042 	4188

    is it not possible to gt something like
    Code:
    filmAgeRating 	filmBinding 	formatcount 	agecount
    12 years and over 	NULL 	NULL 	4201
    15 years and over 	NULL 	NULL 	9236
    18 years and over 	NULL 	NULL 	4808
    Exempt 	NULL 	NULL 	11678
    Parental Guidance 	NULL 	NULL 	4945
    Universal 	NULL 	NULL 	4188
    NULL   DVD 26777 NULL NULL
    NULL   Blu-Ray 3455 NULL NULL

  16. #16
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    It's giving you exactly what you're asking for, a breakdown by age and by format.

    You're definitely working way to hard for what you're looking for.....

    If you're just looking for an age count, then keep it simple:

    Code MySQL:
    SELECT filmAgeRating
             , COUNT(*) AS agecount
      FROM tbl_dvds
    WHERE filmAgeRating <> ''
        AND filmAgeRating <> 'To Be Announced'
     GROUP BY filmAgeRating

    If you're looking for a breakdown of each binding and age group, then do this:
    Code MySQL:
    SELECT filmAgeRating
             , filmBinding
             , COUNT(*) AS filmCount
      FROM tbl_dvds
    WHERE filmAgeRating <> ''
        AND filmAgeRating <> 'To Be Announced'
        AND fileBinging <> ''
     GROUP BY filmAgeRating, filmBinding
     ORDER BY filmAgeRating, filmBinding

    Then you can just keep a running total in your server side code to keep track of totals per age and when your age rating changes, print out the total first.

    Or am I missing something?
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  17. #17
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what im after is the totals for the bindings and the totals for the ages in one query but not the combined totals for bindings and age if that makes sence?

  18. #18
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    Hmmm. I didn't see the last two lines of your sample data. My apologies.

    You CAN do it using a UNION

    Code MySQL:
     SELECT 'Binding' AS groupingType
          , filmBinding AS groupingDescription
          , COUNT(filmBinding) AS groupingCount
       FROM tbl_dvds
      WHERE filmBinding <> ''
      GROUP BY filmBinding
    UNION
    SELECT 'AgeRating'   AS groupingType
         , filmAgeRating AS groupingDescription
         , COUNT(filmAgeRating) AS groupingCount
      FROM tbl_dvds
     WHERE filmAgeRating <> ''
       AND filmAgeRating <> 'To Be Announced'
       AND fileAgeRating IS NOT NULL
     GROUP BY filmAgeRating

    But I'm not sure if I see a benefit to doing it all in one query if you're not going to display them in the same table - at which point I believe the grouped method I provided earlier will give you a better result (in terms of comprehension and userfriendliness) in the long run.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  19. #19
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes! thats giving the result im looking for

    time will tell if its going to be beter to go down this route or the other way

    many thanks...

    p.s any ideas what is causing my mysql to run out of memory e.g
    Kernow Connect - for the smarter shopper: compare and save for cheap dvds, blu-rays, books, xbox, xbox 360, ps3, wii, pc games

    here is my code
    PHP Code:
    <?php
    $set 
    '0';

    menu($parentid,$pageid,$bin,$binName,$age,$ageName,$cat,$search,$searchName,$set,$path);

    function 
    menu($parentid,$pageid,$bin,$binName,$age,$ageName,$cat,$search,$searchName,$set,$path)
    {
    ?>
        <div class="left">
        <div class="menuTitle">Refine Options</div>
        <?php
        
    if(isset($pageid))
        {
            switch(
    $pageid)
            {
                case 
    "dvd";
                echo 
    "<h2 class ='menu_header'>Format</h2>";
                echo 
    "<ul>";
                if(
    $bin//If binding is set offer a reset option
                
    {
                    
    $link '';
                    
    $seperator "?";
                    if(
    $cat)
                    {
                        
    $link $link.$seperator."cat=".$cat;
                        
    $seperator "&";
                    }
                    elseif(
    $age)
                    {
                        
    $link $link.$seperator."age=".$age;
                        
    $seperator "&";    
                    }
                    else
                    {
                        
    $link $path;
                    }
                    echo 
    "<li><a href='$link'>Reset</a></li>";
                }
                
    $count 1;
                
    #$query = mysql_query
                #("
                #    SELECT *
                #    FROM tbl_dvds AS maintable
                #    LEFT JOIN
                #    (
                #        SELECT filmBinding, COUNT( filmBinding ) AS formatcount
                #        FROM tbl_dvds
                #        WHERE filmBinding <> ''
                #        GROUP BY filmBinding
                #        ORDER BY formatcount DESC
                #    ) AS join1 ON maintable.filmBinding = join1.filmBinding
                #    LEFT JOIN 
                #    (
                #        SELECT filmAgeRating, COUNT( filmAgeRating ) AS agecount
                #        FROM tbl_dvds
                #        WHERE filmAgeRating <> ''
                #        AND filmAgeRating <> 'To Be Announced'
                #        GROUP BY filmAgeRating
                #    ) AS join2 ON maintable.filmAgeRating = join2.filmAgeRating
                #");
                
    $query mysql_query("
                SELECT filmBinding,COUNT(filmBinding) 
                AS binding_num 
                FROM tbl_dvds 
                WHERE filmBinding <> ''
                GROUP BY filmBinding 
                ORDER BY binding_num DESC
                "
    )or die (mysql_error());
                while(
    $row mysql_fetch_array($query))
                {
                    
    $link '';
                    
    $binding $row['filmBinding'];
                    
    $label $binding." (".$row['binding_num'].")";
                    
    $divider "?";
                    if(
    $cat)
                    {
                        
    $link $link.$divider."cat=$cat";
                        
    $divider "&amp;";
                    }
                    if(
    $age)
                    {
                        
    $link $link.$divider."age=$age";
                        
    $divider "&amp;";
                    }
                    
    $link $link.$divider."bin=$count";
                    if(
    $bin == $count)
                    {
                        echo 
    "<li class='select'><a href='$link'>".$label."</a></li>";
                    }
                    else
                    {
                        echo 
    "<li class='nonselect'><a href='$link'>".$label."</a></li>";
                    }
                    
    $count++;
                }
                echo 
    "</ul>";
                
                echo 
    "<h2 class ='menu_header'>Browse By</h2>";
                echo 
    "<ul>";
                echo 
    "<li class='nonselect'><a href=''>Bestsellers</a></li>";
                echo 
    "<li class='nonselect'><a href=''>New Releases</a></li>";
                echo 
    "<li class='nonselect'><a href=''>Pre-Orders</a></li>";
                echo 
    "<li class='nonselect'><a href=''>Box Sets</a></li>";
                echo 
    "</ul>";
        
                echo 
    "<h2 class ='menu_header'>Age Rating</h2>";
                echo 
    "<ul>";
                
    // add a rest link to remove selected age rating
                
    if($age)
                {
                    
    $link '';
                    
    $seperator "?";
                    if(
    $cat)
                    {
                        
    $link $link.$seperator."cat=".$cat;
                        
    $seperator "&";
                    }
                    elseif(
    $bin)
                    {
                        
    $link $link.$seperator."bin=".$bin;
                        
    $seperator "&";    
                    }
                    else
                    {
                        
    $link $path;
                    }
                    echo 
    "<li><a href='$link'>Reset</a></li>";
                }
        
                
    $countz 1;
                if(
    $bin)
                {
                    
    $query mysql_query("
                    SELECT filmAgeRating,COUNT(filmAgeRating) AS cat_num 
                    FROM tbl_dvds 
                    WHERE filmAgeRating <> '' && filmAgeRating <> 'To Be Announced' && filmBinding = '
    $binName
                    GROUP BY filmAgeRating"
    )or die (mysql_error());
                }
                else
                {
                    
    $query mysql_query("
                    SELECT filmAgeRating,COUNT(filmAgeRating) AS cat_num 
                    FROM tbl_dvds 
                    WHERE filmAgeRating <> '' && filmAgeRating <> 'To Be Announced' 
                    GROUP BY filmAgeRating"
    )or die (mysql_error());
                }
                    while(
    $row=mysql_fetch_array($query))
                    {
                        
    $link '';
                        
    $divider "?";
                        
    $ar $row['filmAgeRating'];
                        if(
    $cat)
                        {
                            
    $link $link.$divider."cat=$cat";
                            
    $divider "&amp;";
                        }
                        if(
    $bin)
                        {
                            
    $link $link.$divider."bin=$bin";
                            
    $divider "&amp;";
                        }
                        
    $link $link.$divider."age=$countz";
                        if(
    $age == $countz)
                        {
                            echo 
    "<li class='select'><a href='$link'>".$ar." (".$row['cat_num'].")</a></li>";
                        }
                        else
                        {
                            echo 
    "<li class='nonselect'><a href='$link'>".$ar." (".$row['cat_num'].")</a></li>";
                        }
                        
    $countz++;
            }
                        echo 
    "</ul>";
                break;
                case 
    "cd";
                break;        
                case 
    "game";
                break;
                case 
    "book";
                break;
            }
        }
        
        echo 
    "<h2 class ='menu_header'>Category</h2>";
        if(!
    $cat)
        {
            echo 
    "<ul>";
            
    $menu mysql_query("SELECT * FROM tbl_categories WHERE catParent = '$parentid' ORDER BY catName ASC")or die(mysql_error());
        
            while(
    $row mysql_fetch_array($menu))
            {
                
    $link '';
                
    $id $row['catID'];
                
    $catName htmlspecialchars($row['catName']);
                
    $regexp "REGEXP '[[:<:]]($id)[[:>:]]'";
                
    $divider "?";
                if(
    $bin)
                {
                    
    $link $link."&amp;bin=$bin";
                    
    $regexp $regexp." && filmBinding = '$binName'";
                }
                else
                {
                    
    $regexp $regexp;
                }
                if(
    $age)
                {
                    
    $link $link."&amp;age=$age";
                    
    $divider "&amp;";
                    
    $regexp $regexp." && filmAgeRating = '$ageName'";
                }
                
                
    $link "?cat=$id".$link;
                
                switch(
    $pageid)
                {
                    case 
    "dvd";
                    
    $menuitems mysql_query("SELECT filmDepartment,filmAgeRating,filmBinding FROM tbl_dvds WHERE filmDepartment $regexp")or die(mysql_error());
                    break;
                }
                
    $rows mysql_num_rows($menuitems);
                if(
    $cat == $id)
                {
                    echo 
    "<li class='select'><a href='$link'>".$catName." (".$rows.")</a></li>";
                }
                else
                {
                    echo 
    "<li class='nonselect'><a href='$link'>".$catName." (".$rows.")</a></li>";
                }
            }
        echo 
    "</ul>";
        }
        else
        {
            
    parents($pageid,$set,$path,$parentid,$bin,$binName,$age,$ageName,$cat);
        }
    ?>
        </div><?php
    }

    function 
    parents($pageid,$set,$path,$parentid,$bin,$binName,$age,$ageName,$cat)
    {
        if(
    $set == '0')
        {
            
    $ran "false";
            echo 
    "<ul>";
        
            if(
    $cat)
            {
                
    $link '';
                
    $seperator "?";
                if(
    $bin)
                {
                    
    $link $link.$seperator.$bin;
                    
    $seperator "&";
                }
                if(
    $age)
                {
                    
    $link $link.$seperator.$age;
                }
                if(
    $cat)
                {
                    
    $link $link;
                }
                echo 
    "<li><a href='$link'>Reset</li>";?></a><?php
            
    }
            
    $set '1';
        }
        
    $pQuery mysql_query("SELECT * FROM tbl_categories WHERE catID = $cat")or die(mysql_error());
        while(
    $pRow=mysql_fetch_array($pQuery))
        {
            
    $catP $pRow['catParent'];
            
    $name $pRow['catName'];
            
    $pr $pRow['catID'];  
            
    $regexp "REGEXP '[[:<:]]($pr)[[:>:]]'";
            
    $divider "?";
            if(
    $bin)
            {
                
    $link $link."&amp;bin=$bin";
                
    $regexp $regexp." && filmBinding = '$binName'";
            }
            else
            {
                
    $regexp $regexp;
            }
            if(
    $age)
            {
                
    $link $link."&amp;age=$age";
                
    $divider "&amp;";
                
    $regexp $regexp." && filmAgeRating = '$ageName'";
            }
            
            switch(
    $pageid)
            {
                case 
    "dvd";
                
    $parentitems mysql_query("SELECT filmDepartment,filmAgeRating,filmBinding FROM tbl_dvds WHERE filmDepartment $regexp")or die(mysql_error());
                break;
            }

            
    $rows mysql_num_rows($parentitems);
            
    $idz[] = $pr;
            
    $namez[] = $name;
            if(
    $catP != $parentid)
            {
                
    parents($pageid,$set,$path,$parentid,$bin,$binName,$age,$ageName,$cat);
            }
            foreach(
    $idz as $idz)
            {
                if(
    $idz != $cat)
                {
    ?>
                    <li class="nonselect"><a href='?cat=<?php echo $idz?>'><?php echo $namez[0]." (".$rows.")"?></a></li><?php
                
    }
                else
                {
                    echo 
    "<li style='padding-left:15px;'>".$namez[0]." (".$rows.")</li>";
                }
            }
        }
        
        if(
    $ran == "false")
        {
            
    children($pageid,$bin,$binName,$age,$ageName,$cat);
            
    $ran "true";
        }
        if(
    $set == '1' && $ran == "true")
        {
            echo 
    "</ul>";
            
    $set '2';
        }    

    }

    function 
    children($pageid,$bin,$binName,$age,$ageName,$cat)
    {
        
    $query mysql_query("SELECT * FROM tbl_categories WHERE catParent = $cat")or die(mysql_error());
        while(
    $row=mysql_fetch_array($query))
        {
            
    $id $row['catID'];
            
    $parent $row['catParent'];
            
    $regexp "REGEXP '[[:<:]]($id)[[:>:]]'";
            if(
    $bin)
            {
                
    $divider "?";
                
    $regexp $regexp." && filmBinding = '$binName'";
            }
            if(
    $age)
            {
                
    $link $link."&amp;age=$age";
                
    $divider "&amp;";
                
    $regexp $regexp." && filmAgeRating = '$ageName'";
            }
            switch(
    $pageid)
            {
                case 
    "dvd";
                
    $childitems mysql_query("SELECT filmDepartment,filmBinding,filmAgeRating FROM tbl_dvds WHERE filmDepartment $regexp")or die(mysql_error());
                break;
            }
            
    $rows=mysql_num_rows($childitems);?>
            <li class="nonselect"><a href="?cat=<?php echo $id?>"><?php echo $row['catName']." (".$rows.")"?></a></li><?php
        
    }
    }
    ?>
    cheers mate!

  20. #20
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    Based on this thread, it looks like it might be a configuration problem?

    MySQL Lists: mysql:

    You could also try free_result:
    http://www.php.net/manual/en/functio...ree-result.php

    I'll scan through your code to see if there are places to optimize, but those are places to start....
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  21. #21
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank mate appreciate it!

  22. #22
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    One thing I did notice - you're doing a SELECT * on every occurrence of your tbl_categories. You'll want to change them so they select only the fields on the table you need for that method.

    You're accessing that table a lot, so limiting what you select there will help some.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  23. #23
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi,

    tbl_categories only contains 3 fields catID,catName,cat Parent all of which are used in the query.

  24. #24
    SitePoint Member
    Join Date
    Jun 2011
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ive sussed it

    what was happening was $cat is retreived from the url and used to get the parentID of the category then if that didnt match the toplevel id 'parentid' then re run the function, the trouble was that the $cat was alwasys being passed back to the function instead of the new parentID meaning it never reached the top level as it was always looking at the same record hence it was stuck in a loop until it died, so i have to pass the parentID to the function instead of $cat highlighted in red
    Code:
    function parents($pageid,$set,$path,$parentid,$bin,$binName,$age,$ageName,$cat)
    {
        if($set == '0')
        {
            $ran = "false";
            echo "<ul>";
        
            if($cat)
            {
                $link = '';
                $seperator = "?";
                if($bin)
                {
                    $link = $link.$seperator."bin=".$bin;
                    $seperator = "&";
                }
                if($age)
                {
                    $link = $link.$seperator."age=".$age;
                }
                if($bin == '' && $age == '')
                {
                    $link = $path;
                }
                echo "<li><a href='$link'>Reset</li>";?></a><?php
            }
            $set = '1';
        }
        $pQuery = mysql_query("SELECT * FROM tbl_categories WHERE catID = $cat")or die(mysql_error());
        while($pRow=mysql_fetch_assoc($pQuery))
        {
    		$catID = $pRow['catID'];
            $catP = $pRow['catParent'];
            $name = $pRow['catName'];
            $pr = $pRow['catID'];  
            $regexp = "REGEXP '[[:<:]]($pr)[[:>:]]'";
            $divider = "?";
            if($bin)
            {
                $link = $link."&amp;bin=$bin";
                $regexp = $regexp." && filmBinding = '$binName'";
            }
            else
            {
                $regexp = $regexp;
            }
            if($age)
            {
                $link = $link."&amp;age=$age";
                $divider = "&amp;";
                $regexp = $regexp." && filmAgeRating = '$ageName'";
            }
            
    		switch($pageid)
    		{
    			case "dvd";
    			$parentitems = mysql_query("SELECT filmDepartment,filmAgeRating,filmBinding FROM tbl_dvds WHERE filmDepartment $regexp")or die(mysql_error());
    			break;
    		}
            $rows = mysql_num_rows($parentitems);
            $idz[] = $pr;
            $namez[] = $name;
            if($catP != $parentid)
            {
                parents($pageid,$set,$path,$parentid,$bin,$binName,$age,$ageName,$catP);
            }
            foreach($idz as $idz)
            {
                if($idz != $_GET['cat'])
                {?>
                    <li class="nonselect"><a href='?cat=<?php echo $idz?>'><?php echo $namez[0]." (".$rows.")"?></a></li><?php
                }
                else
                {
                    echo "<li style='padding-left:15px;'>".$namez[0]." (".$rows.")</li>";
                }
            }
        }
        
        if($ran == "false")
        {
            children($pageid,$bin,$binName,$age,$ageName,$cat);
            $ran = "true";
        }
        if($set == '1' && $ran == "true")
        {
            echo "</ul>";
            $set = '2';
        }    
    
    }
    and that solved it, its now working would still like to hear of any way to improve my script im sure there are many

  25. #25
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,267
    Mentioned
    116 Post(s)
    Tagged
    1 Thread(s)
    A couple things:
    • The one thing that I see that bothers me is the constant building of the links - though I don't think that will impact your processing time too much. There is a lot of building of link information in the loops which isn't affected by the loop. If you could move those outside the loops, it would be easier to read, and easier to follow.
    • Is there a reason for all the regex stuff in there? I don't see a real benefit to it as you're dealing with an ID and not replacing a title or something.
    • This isn't a php thing, but I personally would remove the 'nonselect' class off all the list items and just make that class style the default list style for those sections. This will make your html smaller, clearer and makes the selected items stand out on quick scan of the source code.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •