mysql php query HAVING clause

Question

Im trying to get this query to work but i get this error: Unknown column 'zips.city' in 'having clause'

`$query = "SELECT
    zips.*
    FROM
    zips
    HAVING
    zips.city LIKE '%$city%' 
    AND
    zips.stateabbr LIKE '%$state%' 
    LIMIT 1";
$result = mysql_query($query) or die (mysql_error());`

my zips table has a city column, so im not sure what the problem is, i know im accessing the database because i can run this query with no errors:

$zip1query = "SELECT 
         zips.*
         FROM 
         zips
         WHERE
         zips.zip = '$zip'              
         ";

any advice would be much appreciated! thanks!

Yes, but that will just make the query unnecessarily complicated. Why use both when a simple WHERE will do? — Justin Ethier
– Justin Ethier, Commented Mar 1, 2010 at 22:06
If you still get the error, if means that you don't have a city column in the table — Vinko Vrsalovic
– Vinko Vrsalovic, Commented Mar 1, 2010 at 22:09
i don't need to use HAVING i just used it from some old searchquery code that I had, i wasn't sure if you needed to use it with the LIKE command. but anyways i got this error: Unknown column 'zips.city' in 'where clause', — Doug Molineux
– Doug Molineux, Commented Mar 1, 2010 at 22:09

Pascal MARTIN · Accepted Answer · 2010-03-01 22:11:22Z

The having clause doesn't mean the same thing as the where clause : when running a simple query, you should use where -- which is what you did in your second query, that works.

having is used when the condition has to be applied on the result of a group by clause.

Which means that, here, your query should be build this way :

$query = "SELECT zips.*
    FROM zips
    where zips.city LIKE '%$city%' 
        AND zips.stateabbr LIKE '%$state%' 
    LIMIT 1";

With that, if you still have an error about a non-existing or not-found column (at least for city and/or stateabbr), it'll be because that column doesn't exist in your table.

In this case, there is not much we can do : you'll have to check the structure of your table, to determine which columns it contains.

You can check that structure using a web-based tool like phpMyAdmin, or using an SQL instruction such as :

desc zips;

For reference, quoting MySQL's manual page for select :

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions.
...

Do not use HAVING for items that should be in the WHERE clause.
For example, do not write the following:
SELECT col_name FROM tbl_name HAVING col_name > 0;
Write this instead:
SELECT col_name FROM tbl_name WHERE col_name > 0;
...
The HAVING clause can refer to aggregate functions, which the WHERE clause cannot

Justin Ethier · Accepted Answer · 2010-03-01 22:04:57Z

1

Try using WHERE instead of HAVING.

answered Mar 1, 2010 at 22:04

Justin Ethier

135k53 gold badges235 silver badges291 bronze badges

1 Comment

Doug Molineux Over a year ago

thank you for your help I got this error: Unknown column 'zips.city' in 'where clause',

Vinko Vrsalovic · Accepted Answer · 2010-03-01 22:05:14Z

1

The proper way to do it is by using a WHERE clause.

$query = "SELECT
zips.*
FROM
zips
WHERE
zips.city LIKE '%$city%' 
AND
zips.stateabbr LIKE '%$state%' 
LIMIT 1";

HAVING is to be used when you are GROUPing, see here for an explanation

answered Mar 1, 2010 at 22:05

Vinko Vrsalovic

342k55 gold badges341 silver badges374 bronze badges

Comments

Doug Molineux · Accepted Answer · 2010-03-01 22:14:09Z

0

o jeez sorry guys i figured out the problem, apparently i put a space before city when i named the columns in my table. so i renamed the column and it works thanks anyway chaps! but using the where function instead of having must speed things up alot, thanks guys!

answered Mar 1, 2010 at 22:14

Doug Molineux

12.5k26 gold badges96 silver badges144 bronze badges

Collectives™ on Stack Overflow

mysql php query HAVING clause

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related