mysql php query HAVING clause

Question

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

Answer 1

Try using WHERE instead of HAVING.

Answer 2

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

Answer 3

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

Answer 4

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!