Every derived table must have its own alias?

ljack84 · June 14, 2011, 1:23pm

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

DarthGuido · June 14, 2011, 1:39pm


SELECT *             
FROM tbl_dvds              
LEFT JOIN 
  (SELECT 
       filmBinding
     , COUNT(filmBinding) AS formatcount
   FROM tbl_dvds              
   WHERE filmBinding <> ''              
   GROUP BY filmBinding
  ) [B][COLOR="Red"]AS join1[/COLOR][/B]       
ORDER BY binding_num DESC             
LEFT JOIN 
  (SELECT
       filmAgeRating
     , COUNT(filmAgeRating) AS agecount             
   FROM tbl_dvds              
   GROUP BY filmAgeRating
  ) [B][COLOR="red"]AS join2[/COLOR][/B]

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).

ljack84 · June 14, 2011, 1:52pm

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!

DarthGuido · June 14, 2011, 2:01pm

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.

ljack84 · June 14, 2011, 3:01pm

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


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

DarthGuido · June 14, 2011, 3:27pm

I don’t see a LIMIT in that query?

ljack84 · June 14, 2011, 3:32pm

ok am getting results returned with this


				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

ljack84 · June 14, 2011, 3:33pm

limit is automatically added to all queries ran inside the dbadmin.

DarthGuido · June 14, 2011, 3:38pm

Yes, your ON clauses are useless (except for creating a correct syntax). You need criteria that join the different tables. For example:


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

ljack84 · June 14, 2011, 3:42pm

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

ljack84 · June 14, 2011, 3:48pm

ok got it sorted

this formula works


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

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

ljack84 · June 14, 2011, 3:55pm

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


			$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

DaveMaxwell · June 14, 2011, 4:12pm

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.

ljack84 · June 14, 2011, 4:30pm

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


$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


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


$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


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

ljack84 · June 14, 2011, 4:43pm

the best code ive come up with so far is


SELECT DISTINCT maintable.filmAgeRating, maintable.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


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


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

DaveMaxwell · June 14, 2011, 5:33pm

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:


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:


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?

ljack84 · June 14, 2011, 5:43pm

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?

DaveMaxwell · June 14, 2011, 6:03pm

Hmmm. I didn’t see the last two lines of your sample data. My apologies.

You CAN do it using a UNION


 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.

ljack84 · June 14, 2011, 6:17pm

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
$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!

DaveMaxwell · June 14, 2011, 6:32pm

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/function.mysql-free-result.php

I’ll scan through your code to see if there are places to optimize, but those are places to start…