[SOLVED] checking if record exists - why doesn't this work?!

[simonp]

Hi,

 

I'm trying to find out if a record exists in a table using the following code but keep getting this error:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

 

Any ideas what I might be doing wrong!?

 

Cheers

 

Simon

 

$ipaddress = $_SERVER['REMOTE_ADDR']; 

  // Make the connection
  $db2 = mysql_connect($myHost2, $myUser2, $myPass2);

  // Select the database
  mysql_select_db($myDB2,$db2);

  $sql2 = "mysql_query(SELECT ipaddress FROM $myTable2 WHERE ipaddress = $ipaddress)";

  // Get the query as an associative array
  $result2=mysql_query($sql2,$db2);

  // Get the ID number of the new record
  $recordID2 = mysql_insert_id();

  // Close the database  connection
  mysql_close($db2);

  $num_rows = mysql_num_rows($sql2);
  echo "output:  $num_rows";

[dilum]

Here,

 

$sql2 = "mysql_query(SELECT ipaddress FROM $myTable2 WHERE ipaddress = $ipaddress)";

 

  where is coming from $myTable2...

[simonp]

Thanks for your reply dilum,

 

I'm not sure I understand what you mean.

 

$myTable2 is the table name (defined elsewhere) - is that what you meant?

 

Simon

[dropfaith]

echo  $mytable make sure its assigning the value you want

[simonp]

echo  $mytable make sure its assigning the value you want

 

Hi dropfaith - yep - it's definately ok.

[dilum]

Ok try this,

 

$sql2 = mysql_query("SELECT ipaddress FROM '$myTable2' WHERE ipaddress = '$ipaddress' ");

 

instead of this,

 

$sql2 = "mysql_query(SELECT ipaddress FROM $myTable2 WHERE ipaddress = $ipaddress)";

[simonp]

Sorry dilum - exact same error

[dropfaith]

$ipaddress = $_SERVER['REMOTE_ADDR']; 
$query = "SELECT ipaddress FROM '$myTable2'  WHERE ipaddress = '$ipaddress'";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());

[simonp]

Thanks dropfaith - I'm now seeing the error:

 

Error in query: SELECT ipaddress FROM 'users' WHERE ipaddress = '217.43.120.4'. 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 ''users' WHERE ipaddress = '217.43.120.4'' at line 1

 

Any ideas? The SQL looks good to me!

 

Cheers

 

Simon

[JasonLewis]

Make this:

$query = "SELECT ipaddress FROM '$myTable2'  WHERE ipaddress = '$ipaddress'";

 

This:

$query = "SELECT ipaddress FROM ".$myTable2."  WHERE ipaddress = '".$ipaddress."'";

[simonp]

Thanks ProjectFear (and everyone else) - that worked brilliantly.

 

Simon

[luca200]

Your mistake was not putting quotes around $ipaddress.

dilum's mistake was putting quotes around the table name.

And you don't need to make a mess of your code breaking it with dots and quotes in the way you finally did.

Best of that, you'd better take the habit to test ALL of your database commands, this way:

 

$db2 = mysql_connect($myHost2, $myUser2, $myPass2) or die(mysql_error());
mysql_select_db($myDB2,$db2) or die(mysql_error());
$result2=mysql_query($sql2,$db2) or die(mysql_error());
mysql

 

And remember: no quotes around tables or columns names. Just backticks, if you need them. At the opposite, always quotes around columns values. Quite simple