2

Been very frustrating but mysql is returning an empty set for this code (not echoing exists) :

if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("repository", $con);

$url = stripslashes($url);
$url = mysql_real_escape_string($url, $con); 
$exists = mysql_query("SELECT url FROM sites WHERE url = '$url' LIMIT 1");
 if (mysql_num_rows($exists) == 1) {
    echo "exists";
 }

it should not be doing this because I've tested a good amount.

The table consists of one column "url", it is datatype varchar(1000) (the maximum). the url stored for test purposes is http://en.wikipedia.org/wiki/Thailand

Improve this question
12
  • 1
    echo $url just before you fire your query and confirm that it looks like the data in your database? Commented Jul 6, 2011 at 22:51
  • 1
    1. Have you performed the same query on database? 2. What mysql_num_rows($exists) returns? 3. Show us the exact query that is generated by your code. Commented Jul 6, 2011 at 22:51
  • thats the thing also ...I tried to use this sql query in phpmyadmin...and it still returns empty set Commented Jul 6, 2011 at 22:52
  • Might be a case sensitivity issue? As Dave said, check the input and the actual data in the database. Commented Jul 6, 2011 at 22:53
  • @Praneet Sharma: well, here is the answer. Commented Jul 6, 2011 at 22:53

3 Answers 3

Reset to default
1

Here's what you do as a first step. Remove the WHERE url = '$url' from your query altogether and print out mysql_num_rows($exists) before using it.

That should be enough to tell if it's one of the two likeliest problems:

  • bad URL resulting in no rows being returned; or
  • bad row caused by database containing other than you expect.

Based on your comments to date, the former is the most likely. If it turns out you get a row back without the where clause, you'll have to figure out why your URL is incorrect. This may be a case-sensitivity issue or a padding (size) issue, among other things.

If, as you mention in a comment, like works where = doesn't, then we need to see your data.

Execute (at the DB level):

select concat('[',url,']') from sites

and show us exactly what the output is. Similarly, output the URL being used by the code with something like:

print_r($url)

immediately before executing the mysql_query.

Please append the output from both those commands to your question.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

this ends up working if the where is gone...as I mentioned above.....I use varchar(1000)....should I just use varchar?
Then it's a case sensitivity problem.
I don't see how it could be case sensitivity if like works where = doesn't, since like respects case as well. @Praneet, you need to start following advice if you want to solve this problem. I've updated the answer with the exact steps to try.
@paxdiablo: I don't think LIKE or = is case-sensitive. It depends on the collation used, according to this: dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html
0

I do not have experience in PHP programming but should not it be "SELECT url FROM sites WHERE url ='". $url."' LIMIT 1" Please ignore this if $url works inside quotes (" ")

Improve this answer

1 Comment

the OP's syntax for the query is valid, your syntax is also valid, but more cumbersome.
-1

That will only check if MySQL only returned exactly one row. Check for

if (mysql_num_rows($exists) > 0)

assuming there is such data you're checking for, of course. You might want to broaden the search using LIKE and wildcards if you aren't absolutely sure the URLs you're passing are identical.

Improve this answer

4 Comments

I know there is only one row in database though...I checked
True, I just tend to see that check a lot as a source of many problems. It's probably the equality check on the string, though.
@Praneet Sharma: ".I tried to use this sql query in phpmyadmin...and it still returns empty set" vs "I know there is only one row in database though". Do you have a row that fits that condition or not?
As was asked before, the URL in the database and the URL in the variable must match completely (case, whitespace, ...) if you're using the equality comparator. If they're not identical, you'll have to use LIKE, although I rather see my URLs uniform so you can actually test for equality.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.