phplist forums

I was getting the following error for each list in my application.

Database error 1052 while doing query Column 'listid' in where clause is ambiguous.

I found the source of it and a fix, just unsure why I get it when others have not. Or others fixed without visiting here and acknowledging the error?

I am running version 3.0.12

I found the problem was in page list.php on/around line 239

Code: Select all

  ## we only consider confirmed and not blacklisted subscribers members of a list
  ## we assume "confirmed" to be 1 or 0, so that the sum gives the total confirmed
  ## could be incorrect, as 1000 is also "true" but will be ok (saves a few queries)
 
  ## same with blacklisted, but we're disregarding that for now, because blacklisted subscribers should not
  ## be on the list at all.
  ## @@TODO increase accuracy, without adding loads of queries.
  $query
  = ' select count(u.id) as total,'
  . ' sum(u.confirmed) as confirmed, '
  . ' sum(u.blacklisted) as blacklisted '
  . ' from ' . $tables['listuser']
  . ' lu, '.$tables['user'].' u where u.id = lu.userid and listid = ? ';


The last criteria without an alias for the table was causing the problem. I modified to the following and it fixed the errors for me.

Code: Select all

 $query
  = ' select count(u.id) as total,'
  . ' sum(u.confirmed) as confirmed, '
  . ' sum(u.blacklisted) as blacklisted '
  . ' from ' . $tables['listuser']
  . ' lu, '.$tables['user'].' u where u.id = lu.userid and u.listid = ? ';