comparing columns and output

[royal1664]

Hi, i'm very new to this so please speak slowly

 

here i my code...

<?php
$restult = NoReturn;

$dbcon=mysqli_connect(*******,*********,*************,************);

if (mysqli_connect_errno($con))
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$restult=$mysqli_query("SELECT 'COL 4' FROM TABLE 1 WHERE (COL 1 >= $clientIP) && (WHERE COL 2 <= $clientIP)");

echo $restult;

?>

as you might be able to see, i have a database with column 1 as the from ip addresses and column 2 as the to ip addresses. column 4 has the country name that the range applies to.

When i run this code on my server withing a .php file nothing comes back. can anyone point me in the right direction as to what i'm doing wrong?

 

many thanks,

royal1664

[ragax]

Hi Royal,

 

Before doing anything in PHP, I would recommend making sure that your SQL works by speaking directly to your database.

You can do that for instance in PhpMyAdmin, or, better,  on a desktop MySQL client (there are excellent free ones).

This will help you debug faulty syntax, such as the two WHERE clauses in your SELECT.

 

Then, when your SQL works, take it to PHP. At that stage, you know that what you're debugging is your PHP, not the SQL.

When writing your first PHP query, you may want to look at the manual and modify a working example.

Byt the way there are several ways of talking to the database in PHP, and in the long run, you may want to consider working with the PDO methods rather than the older functions.

 

This approach of "divide and conquer" when debugging will help you at any level of skill in both SQL and PHP. It makes it much easier to know where your problem is.

 

ps

As a hint, if the WHERE looks at integer columns, this would be valid SQL, without needing to add any quotes around the column and table names.

 

SELECT somecolumn

FROM mytable
WHERE anothercolumn <= 320000 AND athirdcolumn >=3210000

Edited July 26, 2013 by ragax

[Barand]

I would seriously recommend you change your column and table name
1. Use something meaningful
2. Don't use spaces in identifiers. If you really must you need backticks around the name.

Also, as already pointed out, you have other syntax errors.

The result of a query is a result resource and cannot be echoed as a variable. You need to fetch rows from the result them echo the content of the row.
 

$result=$mysqli_query("SELECT `COL 4` FROM `TABLE 1` WHERE $clientIP BETWEEN `COL 1` AND `COL 2`");
$row = mysqli_fetch_assoc($result);
echo $row['COL 4'];

Edited July 26, 2013 by Barand

[mac_gyver]

from a functional standpoint, you need to store the integer representation of the ip addresses so that the comparisons will work and you would need to convert the client ip address to its integer representation. the dotted-quad format for ip addresses are strings and in general cannot be compared greater/less than (would require that you add leading zeros to make each quad value 3 characters.)

[ragax]

Oh, building on what Mac_Gyver said, the most important thing is to make sure your integer column for the ip address is UNSIGNED. Otherwise a number of ip addresses will result in negative values that won't convert back.

 

For conversion, in my view best is to use the native MySQL INET_ATON and INET_NTOA function.

But in a WHERE, always contrive to place functions on the right / value side of the operator, not on the left / column side, e.g.

$query = "... WHERE ip = INET_ATON('".$string."').....";

NOT

$query = "... WHERE INET_NTOA(ip) = '".$string."'.....";

This is because a function on the left side of the operator will make it impossible for the optimizer to use the index on the ip column (assuming there is one).

 

The other option is to use the php ip2long and long2ip functions, with some modification:

$trueIPint = sprintf('%u', ip2long($ip));

[jazzman1]

from a functional standpoint, you need to store the integer representation of the ip addresses so that the comparisons will work and you would need to convert the client ip address to its integer representation. the dotted-quad format for ip addresses are strings and in general cannot be compared greater/less than (would require that you add leading zeros to make each quad value 3 characters.)

 

Who says that the ip address should be only ipv4? 

[royal1664]

Sorry, just wanted to say thanks for your replies on this. I've been going through reading (studying) it and it has been very helpful!

 

Cheers!!

 

Royal.