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

larry29936 · August 17, 2020

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?

larry29936 · August 17, 2020

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)");

requinix · August 17, 2020

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.

larry29936 · August 17, 2020

@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?

requinix · August 17, 2020

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.

larry29936 · August 17, 2020

@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

gw1500se · August 17, 2020

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?

requinix · August 17, 2020

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).

requinix · August 17, 2020

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

larry29936 · August 17, 2020

@gw1500seThe comparison is to a range of ip addresses. g_start and t_end are both unsigned int's

larry29936 · August 17, 2020

@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 August 17, 2020 by larry29936

larry29936 · August 17, 2020

@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

larry29936 · August 17, 2020

I have to go out for a bit. I'll reply when I get back, about an hour.

mac_gyver · August 17, 2020

the sub-query was originally selecting everything, the g.*.

you need to select the end column, so that there is a t.end for the outer query to test.

requinix · August 17, 2020

36 minutes ago, larry29936 said:

Fixed but now getting this:

The query I posted won't have that problem.

Barand · August 17, 2020

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)

requinix · August 17, 2020

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).

Barand · August 17, 2020

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 |
+----+-------------+------------+------------+--------+-----------------+-----------------+---------+------+---------+----------+-----------------------+

requinix · August 17, 2020

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.

larry29936 · August 18, 2020

@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.

Barand · August 18, 2020

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.

Barand · August 18, 2020

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)

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information