Jump to content

How do I change this Select Statement to $pdo->query


Recommended Posts

I need to convert the following select statement to a pdo->query but have no idea how to get it working:

SELECT t.id FROM 
  ( SELECT g.* 
    FROM location AS g
    WHERE g.start <=  16785408
    ORDER BY g.start DESC, g.end DESC
    LIMIT 1
  ) AS t
WHERE t.end >= 16785408;

Here's the code I'm trying:

<?php

$php_scripts = '../../php/';
require $php_scripts . 'PDO_Connection_Select.php';
require $php_scripts . 'GetUserIpAddr.php';
function mydloader($l_filename=NULL)

{
$ip = GetUserIpAddr();
if (!$pdo = PDOConnect("foxclone_data"))
{    
    exit;
}
    if( isset( $l_filename ) ) {  
        $ext = pathinfo($l_filename, PATHINFO_EXTENSION);
        $stmt = $pdo->prepare("INSERT INTO download (address, filename,ip_address) VALUES (?, ?, inet_aton('$ip'))");
        $stmt->execute([$ip, $ext]) ; 

        $test = $pdo->prepare("SELECT t.id FROM ( SELECT g.id FROM lookup AS g WHERE g.start <= inet_aton($ip) ORDER BY g.start DESC, g.end DESC ) AS  t  WHERE t.end >=inet_aton($ip)");
        $test ->execute() ; 
        $ref = $test->fetchColumn();
        $ref = intval($ref);

        $stmt = $pdo->prepare("UPDATE download SET ref = '$ref' WHERE address = '$ip'");
        $stmt->execute() ;         

        header('Content-Type: octet-stream');
        header("Content-Disposition: attachment; filename={$l_filename}");
        header('Pragma: no-cache');
        header('Expires: 0');        
        readfile($l_filename);
       }
        
    else {
        echo "isset failed";
        }  
}
mydloader($_GET["f"]);
exit;

 It gives the following error:

Quote

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '.144.181) ORDER BY g.start DESC, g.end DESC ) AS  t  WHERE t.end >=inet_aton(7' at line 1 in /home/foxclone/test.foxclone.com/download/mydloader.php:19 Stack trace: #0 /home/foxclone/test.foxclone.com/download/mydloader.php(19): PDO->prepare('SELECT t.id FRO...') #1 /home/foxclone/test.foxclone.com/download/mydloader.php(38): mydloader('foxclone40a_amd...') #2 {main} thrown in /home/foxclone/test.foxclone.com/download/mydloader.php on line 19

How do I fix this?

Link to post
Share on other sites

This is the line that errors out:     

Quote

$test = $pdo->prepare("SELECT t.id FROM ( SELECT g.id FROM lookup AS g WHERE g.start <= inet_aton($ip) ORDER BY g.start DESC, g.end DESC ) AS  t  WHERE t.end >=inet_aton($ip)");    

   

Link to post
Share on other sites

IP addresses are strings. Strings need quotes.

Since you've posted this in the PHP forum and not the MySQL forum, I'll mention that you could do the inet_aton work within PHP if you wanted. Numbers aren't strings.

Link to post
Share on other sites
5 minutes ago, larry29936 said:

@requinix I knew that but kept missing it in the code. Just for clarification, should pdo ->query and pdo ->prepare questions be posted here or in the MySQL forum?

If the question is about how to use PDO then that's a PHP matter. If the question is about the query you're trying to run then that's a MySQL matter.

But don't worry too much about it. There's a lot of overlap. Worst case is that someone will move the thread to the right place.

Link to post
Share on other sites

@requinix Fixed that but now getting a new error:

Quote

Fatal error : Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘t WHERE t.end >=inet_aton(‘72.168.144.181’)’ at line 1 in /home/foxclone/test.foxclone.com/download/mydloader.php:19 Stack trace: #0 /home/foxclone/test.foxclone.com/download/mydloader.php(19): PDO->query(‘SELECT t.id FRO…’) #1 /home/foxclone/test.foxclone.com/download/mydloader.php(38): mydloader(‘foxclone40a_amd…’) #2 {main} thrown in /home/foxclone/test.foxclone.com/download/mydloader.php on line 19

 

Link to post
Share on other sites

Is 't.end' a string or number? Also, to me, it does not make sense to compare >= on IP addresses. IP addresses either are the same or are different. What are you really trying to do there?

Link to post
Share on other sites
17 minutes ago, larry29936 said:

@requinix Fixed that but now getting a new error:

You've managed to type non-breaking spaces around the "t". Delete it and type again. Then see if your editor doesn't have some way of turning off typing non-breaking spaces (likely when you hit Shift+space).

Link to post
Share on other sites
6 minutes ago, gw1500se said:

Is 't.end' a string or number? Also, to me, it does not make sense to compare >= on IP addresses. IP addresses either are the same or are different. What are you really trying to do there?

It's the best way to check an IP address range. But now that I look at the query, it is kinda weird what it's doing. Why is the <= condition inside the subquery but the >= is outside?

SELECT id
FROM lookup
WHERE INET_ATON('$ip') BETWEEN start AND end
ORDER BY start DESC, end DESC

 

Link to post
Share on other sites
Posted (edited)

@requinix

20 minutes ago, requinix said:

It's the best way to check an IP address range. But now that I look at the query, it is kinda weird what it's doing. Why is the <= condition inside the subquery but the >= is outside?


SELECT id
FROM lookup
WHERE INET_ATON('$ip') BETWEEN start AND end
ORDER BY start DESC, end DESC

 

When I run it as stated above, it takes almost 2 minutes to get the id. I don't know why that crazy query works better but I get results in .0027 seconds. The lookup table has 6 million ip ranges, one row for each range. That query came from StackOverflow.

Edited by larry29936
Link to post
Share on other sites

@requinix

31 minutes ago, requinix said:

You've managed to type non-breaking spaces around the "t". Delete it and type again. Then see if your editor doesn't have some way of turning off typing non-breaking spaces (likely when you hit Shift+space).

Fixed but now getting this:

Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.end' in 'where clause' in /home/foxclone/test.foxclone.com/download/mydloader.php:19 Stack trace: #0 /home/foxclone/test.foxclone.com/download/mydloader.php(19): PDO->query('SELECT t.id FRO...') #1 /home/foxclone/test.foxclone.com/download/mydloader.php(38): mydloader('foxclone40a_amd...') #2 {main} thrown in /home/foxclone/test.foxclone.com/download/mydloader.php on line 19

Link to post
Share on other sites

I have a similar table (ip_lookup) with 5.2 million rows.

CREATE TABLE `ip_lookup` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `start` varbinary(16) DEFAULT NULL,
  `end` varbinary(16) DEFAULT NULL,
  `continent` varchar(45) DEFAULT NULL,
  `country` varchar(45) DEFAULT NULL,
  `area` varchar(45) DEFAULT NULL,
  `city` varchar(145) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_iplookup_ip` (`start`,`end`),
  KEY `idx_ip_lookup_end` (`end`)
) ENGINE=InnoDB AUTO_INCREMENT=5277083 DEFAULT CHARSET=utf8;

FYI, here's a benchmark comparison of those two queries

mysql> SELECT id, country, area, city
    -> FROM ip_lookup
    -> WHERE INET6_ATON('72.168.144.181') BETWEEN start AND end;
+--------+---------------+------------+------------+
| id     | country       | area       | city       |
+--------+---------------+------------+------------+
| 806584 | United States | California | Littlerock |
+--------+---------------+------------+------------+
1 row in set (3.49 sec)

mysql> SELECT t.id, country, area, city FROM
    ->   ( SELECT g.*
    ->     FROM ip_lookup AS g
    ->     WHERE g.start <=  INET6_ATON('72.168.144.181')
    ->     ORDER BY g.start DESC, g.end DESC
    ->     LIMIT 1
    ->   ) AS t
    -> WHERE t.end >= INET6_ATON('72.168.144.181');
+--------+---------------+------------+------------+
| id     | country       | area       | city       |
+--------+---------------+------------+------------+
| 806584 | United States | California | Littlerock |
+--------+---------------+------------+------------+
1 row in set (0.07 sec)

 

  • Like 1
Link to post
Share on other sites
41 minutes ago, Barand said:

I have a similar table (ip_lookup) with 5.2 million rows. [...]

That's quite a difference. What does the EXPLAIN say? I would think they should be the same: index scan to find the start, index scan to find the end, return everything (which should be just the one row since ranges don't overlap).

Link to post
Share on other sites
mysql> EXPLAIN SELECT id, country, area, city
    -> FROM ip_lookup
    -> WHERE INET6_ATON('72.168.144.181') BETWEEN start AND end;EXPLAIN SELECT id, country, area, city
+----+-------------+-----------+------------+------+-----------------------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys                     | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | ip_lookup | NULL       | ALL  | idx_iplookup_ip,idx_ip_lookup_end | NULL | NULL    | NULL | 4774295 |    25.00 | Using where |
+----+-------------+-----------+------------+------+-----------------------------------+------+---------+------+---------+----------+-------------+

and

mysql> EXPLAIN SELECT t.id, country, area, city FROM
    ->   ( SELECT g.*
    ->     FROM ip_lookup AS g
    ->     WHERE g.start <=  INET6_ATON('72.168.144.181')
    ->     ORDER BY g.start DESC, g.end DESC
    ->     LIMIT 1
    ->   ) AS t
    -> WHERE t.end >= INET6_ATON('72.168.144.181');
+----+-------------+------------+------------+--------+-----------------+-----------------+---------+------+---------+----------+-----------------------+
| id | select_type | table      | partitions | type   | possible_keys   | key             | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+------------+------------+--------+-----------------+-----------------+---------+------+---------+----------+-----------------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL            | NULL            | NULL    | NULL |       1 |   100.00 | NULL                  |
|  2 | DERIVED     | g          | NULL       | range  | idx_iplookup_ip | idx_iplookup_ip | 19      | NULL | 2387147 |   100.00 | Using index condition |
+----+-------------+------------+------------+--------+-----------------+-----------------+---------+------+---------+----------+-----------------------+

 

Link to post
Share on other sites

Ohhh... yeah. Okay. This makes sense now. A (start, end) key finds the start quickly but the end is only useful for an exact match of a start value. Which is useless for the second half of a BETWEEN. And the lone end key won't do anything useful. So you have to do it in two pieces.

I was thinking the (start, end) would somehow work for finding an end given an initial position located by the start value, but that's totally wrong.

Link to post
Share on other sites

@Barand I need to run this against an entire 3000 row download table. Would something like this work?

<?php

$query = $pdo->query('SELECT DISTINCT ip_address FROM download' );
$result = $query -> fetch();
foreach( $result as $row ) {
    $ip_address = intval($row['ip_address']);
    UPDATE download
       SELECT t.id FROM
          ( SELECT g.*
            FROM ip_lookup AS g
            WHERE g.start <=  $ip_address
            ORDER BY g.start DESC, g.end DESC
            LIMIT 1
          ) AS t
       WHERE t.end >= $ip_address;
    SET ref = t.id WHERE download.ip_address = $ip_address;

All ip's are IPV4. The fields in the query are stored as unsigned int's using inet_aton. The ip_lookup table has already been checked for duplicates and overlaps.

Link to post
Share on other sites

No.

ip_lookup.start and ip_lookup.end are varbinary(16) so the ip_address you are comparing also needs to be varbinary(16).

We have been here before! I gave you a clue in the code I posted above. Read it.

Once you fix that you need to work on the syntax for joining tables to do an update.

Link to post
Share on other sites

Ignore what I said about the join in this case. The fastest way I've found so far is to incorporate the code which gets the id into a MYSQL function, EG

CREATE FUNCTION `getLookupId`(ip VARCHAR(60)) RETURNS int(11)
BEGIN
DECLARE lookupid INT;
SELECT t.id INTO lookupid FROM
   ( SELECT g.*
     FROM ip_lookup AS g
     WHERE g.start <=  INET6_ATON(ip)
     ORDER BY g.start DESC, g.end DESC
     LIMIT 1
   ) AS t
 WHERE t.end >= INET6_ATON(ip);
RETURN lookupid;
END

TEST...

mysql> select * from download;
+----+-----------------+------+
| id | address         | ref  |
+----+-----------------+------+
|  1 | 140.50.228.2    | NULL |
|  2 | 178.225.157.177 | NULL |
|  3 | 102.125.142.27  | NULL |
|  4 | 66.75.44.127    | NULL |
|  5 | 33.25.97.27     | NULL |
|  6 | 145.50.228.2    | NULL |
|  7 | 183.225.157.177 | NULL |
|  8 | 107.125.142.27  | NULL |
|  9 | 71.75.44.127    | NULL |
| 10 | 38.25.97.27     | NULL |
| 11 | 44.153.111.171  | NULL |
| 12 | 88.0.224.154    | NULL |
| 13 | 49.153.111.171  | NULL |
| 14 | 93.0.224.154    | NULL |
| 15 | 218.255.51.103  | NULL |
+----+-----------------+------+
15 rows in set (0.00 sec)

mysql> update download set ref = getLookupId(address);       -- update using the function
Query OK, 15 rows affected (0.11 sec)
Rows matched: 15  Changed: 15  Warnings: 0

mysql> select d.id
    ->      , d.address
    ->      , l.country
    ->      , l.city
    -> FROM download d
    ->      JOIN
    ->      ip_lookup l ON d.ref = l.id;
+----+-----------------+---------------+----------------------------+
| id | address         | country       | city                       |
+----+-----------------+---------------+----------------------------+
|  1 | 140.50.228.2    | United States | "Columbus (East Columbus)" |
|  2 | 178.225.157.177 | Netherlands   | "The Hague"                |
|  3 | 102.125.142.27  | Sudan         | Khartoum                   |
|  4 | 66.75.44.127    | United States | "San Diego"                |
|  5 | 33.25.97.27     | United States | "Columbus (East Columbus)" |
|  6 | 145.50.228.2    | Netherlands   | "Delft (Tanthof-West)"     |
|  7 | 183.225.157.177 | China         | Dongguan                   |
|  8 | 107.125.142.27  | United States | "Washington D.C."          |
|  9 | 71.75.44.127    | United States | Salisbury                  |
| 10 | 38.25.97.27     | United States | Newark                     |
| 11 | 44.153.111.171  | United States | "San Diego (Clairemont)"   |
| 12 | 88.0.224.154    | Spain         | Madrid                     |
| 13 | 49.153.111.171  | China         | "Beijing (Chaoyang Qu)"    |
| 14 | 93.0.224.154    | France        | Paris                      |
| 15 | 218.255.51.103  | Hong Kong     | "Kowloon City"             |
+----+-----------------+---------------+----------------------------+
15 rows in set (0.00 sec)

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.