WHERE / AND query problem

[ebryant]

I am trying to check to see if a row already exists in a DB and I must be doing something wrong. I am trying to use something like below that uses four AND clauses (1 for each column in the row) to see if there is a match in the DB :

 

$result = mysql_query("SELECT * FROM diffs WHERE id='$this->id' AND name='$name'");
$num = mysql_num_rows($result);
  if ($num >= 1){
    return true;
  } else {
    return false;
  }  

 

If I use the above with only 1 where match [WHERE id=$this->id] it works fine but as soon as I add [AND name=$name], I get a a warning "mysql_num_rows(): supplied argument is not a valid MySQL result resource" I am not sure what I am doing wrong. Any help would be appreciated. thanks 

[Maq]

Chances are your query is failing.  Temporarily change your query to this to find out what the error and query value is:

$sql = "SELECT * FROM diffs WHERE id='$this->id' AND name='$name'";
echo $sql;
$result = mysql_query($sql) or die(mysql_error());

[Simon Mayer]

Just a thought... Did you mean to use $name? If you have $this->id, I'd expect you to be using $this->name too.

[ebryant]

I used [name=$name] as an example the real 2nd column was [table=$table]

 

The error I got wasn't really helpful:

 

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table='basic'' at line 1.

However, the problem seems to be that one of my columns is named "table" is usage of this term off limits for SQL syntax? When I used the 3rd column [field=$field] and didn't match the table column it seems to work fine.

 

[DavidAM]

"table" is a reserved word in mySql. To use it as a column name, you need to surround it with back-ticks:

 

AND `table` = 'basic'

[Maq]

I used [name=$name] as an example the real 2nd column was [table=$table]

 

The error I got wasn't really helpful:

 

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table='basic'' at line 1.

However, the problem seems to be that one of my columns is named "table" is usage of this term off limits for SQL syntax? When I used the 3rd column [field=$field] and didn't match the table column it seems to work fine.

 

That error message is very helpful actually.  Where did that column come from?  There was no mention of it in the first post.

[ebryant]

Ok, well that clears things up.

 

I might've expected phpMyadmin to throw up a warning when I named the column.

 

I guess I'll change it to something else, 'tbl', etc.

 

Thanks everyone 

[ebryant]

I didn't mention it in the first post because I was trying to simplify the code and inadvertently changed the actual thing causing the error.

 

I hadn't considered I was using a reserved word.

 

 

[Pikachu2000]

Why would you expect an error? There's no problem with using that name.

[ebryant]

FYI: another reason I had not expected it was a reserved word was that the INSERT statement was working fine. Apparently, using that column name only causes a problem in the SELECT statement. 

[Maq]

FYI: another reason I had not expected it was a reserved word was that the INSERT statement was working fine. Apparently, using that column name only causes a problem in the SELECT statement. 

 

I'm pretty sure it should.

[Simon Mayer]

I guess I'll change it to something else, 'tbl', etc.

 

Changing the name is one solution, but it is good practice to use grave accents (backticks) ` `

ideally avoid reserved words AND use grave accents

 

Eventually you may be unlucky enough to come up against another reserved word. Perhaps if the table was built by someone else. If you are used to using grave accents, you are unlikely to encounter any unexpected syntax errors.