SQL IF EXIST problem

I want to match a string var named "$ip" to a column name "ip" in "$Hood" table. If there isn't a match then do something.

So I have this code:

if (mysql_query("IF EXISTS(SELECT TOP 1  1 FROM $Hood WH开发者_StackOverflowERE ip=$ip)") == 1) 

And it's not working, it suppose to return 1 because there is a match. What's wrong here?

---

simply select the matching ip form the DB like so:

SELECT ip 
FROM $hood 
WHERE ip='$ip' 
LIMIT 1

and count the rows, if it is equal to 1 then you have a match, 0 otherwise, like so:

if(mysql_num_rows(mysql_query(SELECT ip FROM $hood WHERE ip='$ip' LIMIT 1)) == 1)

---

Mysql doesn't have top clause. You have to use limit

select * from table where condition

You can then use mysql_num_rows() to check result.

edit. Anothere alternative

select exists(select * from table where condition) as result

that gives you 1 or 0.

---

If no matches are found, no records are returned. You can't compare a NULL records set to '1' in this way.

Using COUNT is one option, but if the number of matches is very high, the following may yield performance benefits (at the cost of being longer winded)...

if (mysql_query("SELECT CASE WHEN EXISTS (SELECT * FROM $Hood WHERE ip=$ip) THEN 1 ELSE 0 END") != '1')

EDIT

On a side note, I'd advise a few debugging steps in understanding your problem...

Execute your query directly in SQL and see what the result is. Once you're happy the SQL works, you know you now only have to deal with PHP.

Build the SQL query, and echo it to the screen. Check that it's actually propperly formed, and test it directly in SQL again. Now you know your PHP is building the query properly.

Now both build and execute the query in PHP, and echo the result to the screen. Does the result match the behaviour when testing directly in SQL?

Finally, build this into the IF statement.

By validating each step you can quickly ascertain Where any issues lay and attack them directly.

---

For mysql I think that your query should be

select ip from $Hood where ip = $ip limit 0, 1.

I also hope that you're escaping or validating your data.