0

I am trying to create a working sql query that will give an accurate FLAG for a known or unknown IP address in our database upon hitting our landing page. I use this

// get the IP of the user on this landing page
$ip_address=$_SERVER['REMOTE_ADDR']; 
echo $ip_address. ' ip address<br/>';

// see if we know this user..
$search_first="SELECT COUNT(DISTINCT(ip_address)) FROM `qb_sessions` WHERE `ip_address` = '$ip_address' ";
$has_ips = mysql_query($search_first);
$has_ip = mysql_num_rows($has_ips);
echo $has_ip. ' has IP<br/>'; // should show count in table

Using this snippet does not provide the result that I need. If the table is empty, (no match to this IP), I still get a '1' as the echo of $has_ip. I really need '0' if there is no match, and anything '&gt 1' if this IP has been recorded in qb_sessions table before. The purpose of this snippet is to identify a new, non-registered hit on this landing page and later to redirect them to another page, like join a newsletter, get a discount coupon, etc.

I have tried various combinations of the sql to no avail, including escaping the (') $ip_address quotes.

Thank you in advance for any help.

Steve K
  • 35
  • 6
  • By the way, you should strongly consider switching to `mysqli` or `PDO` functions: http://php.net/manual/en/function.mysql-query.php (`mysql_query` has been deprecated for a while). – Optimae Jul 09 '17 at 16:55
  • Thank you everyone !!.. Yes, the COUNT() was throwing it off, which I figured out before looking back here... _(As for @Rajdeep Paul sidenote:... I am aware, but must work with what is on this site right now... transitions are underway in development servers.. but this item needs to be **immediately live according to my superiors**.)_ – Steve K Jul 09 '17 at 19:14
  • @SteveK Please *green ticked* one of the answers as *accepted* answer to close the question, otherwise this question will be floating around SO as *open* question. – Rajdeep Paul Jul 09 '17 at 22:10

3 Answers3

3

... If the table is empty, (no match to this IP), I still get a '1' as the echo of $has_ip.

That's because of MySQL's COUNT() function. Whether the table contains 1 row or n rows SELECT COUNT(...) ... will still return exactly one row containing a number which shows the number of rows returned from the SELECT query.

Since you're using PHP's mysql_num_rows() function anyway to count number of rows, drop COUNT() from your SELECT query.

$search_first="SELECT DISTINCT(ip_address) FROM `qb_sessions` ...

Sidenote: Don't use mysql_* functions, they are deprecated as of PHP 5.5 and are removed altogether in PHP 7.0. Use mysqli or pdo instead. And this is why you shouldn't use mysql_* functions.

Rajdeep Paul
  • 16,887
  • 3
  • 18
  • 37
3
SELECT COUNT(DISTINCT(ip_address))

This is always going to return a row. Exactly one row. Containing exactly one number. That number might be a zero, but there will be a row in the result containing that number.

mysql_num_rows($has_ips)

This will always be 1, because the above query will always return one row.

Instead of counting the returned rows, examine the already-counted returned value. Something like this:

$has_ips = mysql_query($search_first);
$row = mysql_fetch_array($has_ips);
$has_ip = $row[0];
echo $has_ip. ' has IP<br/>';

(I think that's how a result would be fetched, it's been a while since I've written PHP code. But the idea is straightforward enough, you want the first (only) element of the first (only) returned row.)

David
  • 208,112
  • 36
  • 198
  • 279
0

Should be binding your parameters via PDO.

Here is an alternative way, here I simply check if any results have been returned.

$stmt = $conn->prepare("SELECT * FROM qb_sessions WHERE ip_address=:ip_address");
$stmt->bindParam(':ip_address', $ip_address);
$ip_address = "73.85.207.17"; // example ip
$stmt->execute();
$result = $stmt->fetchAll();
echo (count($result) > 0) ? 'IP found' : 'IP NOT found';
MackProgramsAlot
  • 583
  • 1
  • 3
  • 16