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

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

  ( 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_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"))
    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 FROM ( SELECT 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');        
    else {
        echo "isset failed";

It gives the following error:

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/ Stack trace: #0 /home/foxclone/ PDO->prepare(‘SELECT FRO…’) #1 /home/foxclone/ mydloader(‘foxclone40a_amd…’) #2 {main} thrown in /home/foxclone/ on line 19

How do I fix this?

Most likely it is this… Your $ip is a text value, not numeric. Therefore you need quotes around the data.
IP addresses like flag an error because you can not have a decimal with three decimal points.

Hope that helps…

I knew that but kept missing it looking at the code. Fixed that but now getting the following error:

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(‘’)’ at line 1 in /home/foxclone/ Stack trace: #0 /home/foxclone/ PDO->query(‘SELECT FRO…’) #1 /home/foxclone/ mydloader(‘foxclone40a_amd…’) #2 {main} thrown in /home/foxclone/ on line 19

By the way, the original sql query works in phpmyadmin

Run it on your PHPmyAdmin formatted like this so each section is on a separate line.
Then, select “retain-query” and run it again. It will show you which like is wrong and you can test it
better that way. I am slightly thinking you should just use a left or inner JOIN instead of the extra select
clause… But, for now, try to debug what is making it fail.

By the way, is there an “end” field inside the “lookup” table? I just noticed the error says it is at the end
where you compare t.end. Since you use the second SELECT to create the “t” table from the “lookup”
table, you need to make sure the lookup table contains the “end” field.

The lookup table has both the start and end fields, it’s an ip range. The original code (1st post) runs fine in phpmyadmin and I get a result in .0026 seconds. If I run the following in phpmyadmin it takes over 2 seconds:

FROM lookup
WHERE 16785408 BETWEEN start AND end

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

Well, is start and end text fields? If you compare a numeric 16785408 to a text field, the query engine has
to convert each every time it checks a new row. That would slow it down.

A little confused. In your first post, you selected from a table named location. In the second, you selected
from a table named lookup. I am confused on what you are attempting to do at this point. Are you just
trying to look up to see if the $ip is in a table or are you wanting to get one that is close to that variable?

IP’s are unique. And, you are selecting an id from a table where a number is between to other numbers.
You can do that, but, the format of the data is important.

No problem on leaving just reply and I will check in later, too…

The start and end fields are unsigned integers. The table name in the original post is incorrect, it is the lookup table. It contains an auto-increment id field, ip ranges ( start and end), country, area, and city info. What I’m trying to do is find the ip range that the inet_aton of the user’s ip falls into in the lookup table and record the id for that row in the ref field of the download table.

Hope I’ve explained what I’m trying to do sufficiently.

That might not work as you expect it to. First, IP address are NOT sequential. But, assuming that you have a correct list of an ISP’s start and ending IP numbers, you might be able to do that. Also, how are you converting the IP address to an INT value? That might be wrong way to handle it. It might be better to just remove the periods, but, then you have issues with the leading zeros. AND, if your lookup table overlaps or if it is not 100% correct, the number selected would not be correct.

Anyway, if you want to find if a value is between to fields, just do a simple compare. Something like:
SELECT id FROM lookup WHERE inet_aton($ip)<=start AND inet_aton($ip)=>end;
This should give you the ID list of all that fit. You might want to do further checks to make sure the are
all the same. You can rule out dups if there are tons of hits, but using DISTINCT to get a list of unique ID’s.

OR, you could use one of the free “WHO-IS” API’s to just check their IP and get the correct value for it.

Hope this help!

All ip’s are IPV4. The start and end fields are unsigned integers that were derived by running inet_aton on the original ip’s to avoid the inet_aton processing when doing the comparison. The ranges were checked for duplicates and overlaps. Running your suggested select statement takes over 50 minutes to run against my local server with 3000 download records. I won’t attempt to do that against the production database with over 5000 download records on my webhost as it will time out.

Do you have any suggestions other than taking the website down for maintenance, replicating the production download table locally, running the code locally, and sending the processed table back up to the production server?

Well, there are hundreds of ways to speed this up. Most likely you have an issue with the formats.
Can you zip up your 3000 records and private post it to me? You can use your PHPmyAdmin page and
export it to a zipped file and post it to me in a private message. I have never had a search with just
3000 records take 50 minutes! Oh, if your table has a huge number of fields per row, you can create
a copy of it with just the needed fields instead of the entire row of fields.

Then, I can review your data and figure out why it should take 50 minutes. That just does not seem like
it is correct. My guess is that the inet_aton() might be the problem.

Lastly, doing a little research says that you need to use “unsigned int” format for IP’s and have the field
already converted with the inet_aton() function when you enter the IP address. Also, you need to use
primary keys also. Primary keys will slow down inserts and updates, but, will speed up the searches
a large amount.

Some further thoughts… Is this on a local server? Is it on a shared server? Is it on a full dedicated server?
These might answer a few other questions. Some caching issues can be caused on different types of servers. Depending on how you answer this question, you might be able to fix the cache so it works faster.
But, this depends on your server type. Let me know this one, too. Please! Thanks.

Sorry, re-read some of your comments. I see it is a webhost you are using, so is it a shared one or a dedicated one? Also, if the server is a busy one or large queries are run on it often, it might just be a caching issue. I have the commands for MariaDB sorted out to attempt that. After I know the type of server it is.

Well, this is a process… First, in copying one of your queries, I noticed that the PHPmyAdmin panel changed the “end” into “END”. I thought that was odd, so I researched it. Seems that “end” is a MariaDB keyword used with BEGIN-END clauses. So, I renamed the start and end to ipstart and ipend just in case. Next, I took both of your keys for start and end out and just left the primary-key-id in place.
I selected a number very high up in the rows to make sure it worked a little for it. When I ran the query below, it took 1 second. I had to remove the last two entries in the files you sent because they were messed up and could not be indexed correctly. One had nulls and one had dashes in it.

FROM lookup
WHERE 3316577024 BETWEEN ipstart AND ipend
ORDER BY ipstart DESC, ipend DESC

But, then, I got thinking about this. Why would you need to order it if the results is only one id number?
I removed the ORDER-BY and it ran in .94 of a second. A minor speed difference. This still seems very slow for locating just one id number.
Next, I created an index using id-ipstart-ipend. Reran the same query and it ran in .7 of a second.
A little better, but, still that is not good enough. So, I altered the query leaving the indexes in place to this:

FROM lookup
WHERE 3316577024 >= ipstart AND 3316577024 <= ipend

Although this means putting the value in two places in the query, it ran at .5 of a second. A bit faster.
This is about twice as fast as the first try. Now back to the first query. From what I understand you are
attempting to match each IP in the download table checking them against the lookup table. To get that to
work, I think simple JOIN would work so that you do not have two separate SELECT’s in the query. I see
you have the IP in the download table in both the 999.999.999.999 format and the converted version too.
So, I tested it with this query:

SELECT as location_id, as lookup_id
FROM download AS a
INNER JOIN lookup AS b
ON a.ip_address >= b.ipstart AND a.ip_address <= b.ipend

And, this took 3.7 seconds to match up all 1477 rows in the download folder. So, I think this solves it for you! Hope you understand all the steps, one by one that I did to sort out a final query for you.
Oh, by the way, I ran this on my WAMP server on this laptop while it was running a huge number of other
things, downloads and whatever. So, on your live server, it would probably be even faster!


Many thanks for taking the time to figure this out. I’m going to print this out and go through your steps one by one in the morning. Will PM you with the final results on my laptop LAMP server.

Next, I’ll have to figure out how to code it to update the ref column for each row in the download table.

I ran your final query after renaming the start and end columns to ipstart and ipend as you did. also deleted the bad rows you found. the On my local LAMP server it ran in 7 min 40.12 sec. I don’t understand the vast difference in time to run the query. My laptop has an Intel i5 processor and 16GB memory.

By the way, the production database doesn’t have any data in the download.ref field. The end object is to update the download.ref column with the id of the lookup row that has the range that the download.ip_address falls into. Once I get the time down for the select statement I guess I could retrieve it into an array and do the update using that.

EDIT: Rebooted then ran the select again. This time done in 6 min 58.96 sec. Still not good. The times I’ve given are from running the query in mysql monitor from the command line. When I run it in phpmyadmin it takes the same amount of time even though it shows this: Showing rows 0 - 24 (1477 total, Query took 0.1428 seconds.)

No good? It went from 50 minutes to 7. That is great! I ran it again just now and it was 3.7 on my system. It is an i7 but has a bit more memory, so that might be why it is faster. But, this is not important as you need to test it on the production system. You can create a test table on the production server and run it thry that PHPmyAdmin panel and see what it gets. I think from 50 minutes to 7 is enough to try it on the bigger system instead of a small laptop…

So, did you put in the new indexes? You need to add an index with id-ipstart-ipend. And, it might also help to add an index in the download table too for id-ip_address . If you missed that step, it will run longer.

When was the last time you defragged your computer. Do Mac’s need to be defragged? In Wamp or Lamp, the database is contained inside a file and it if is fragmented, it can slow it down. Also, you might need to flush your cache, but, I don’t think that is the issue.

And, if you are updating the production database when only one item comes in at a time, this number would be almost nil. What I mean is that the end-object is to update the download.ref column when an new IP comes in. That would only be one to deal with not 1477 as we are now. The logic of this eludes me. If you just update the new IP’s as they come in, then there is no need for making the one small query any faster.

Got to leave for awhile. Hope some of this info helps you…

I’ve revised the download code to handle the one ip at a time. Processing time to retrieve the and update the download.ref for a single ip is .5 secs. after updating the indexes. That will work just fine. I can’t thank you enough for your help.

Glad we solved it. And, good luck with the live system! See you in your next post!

Sponsor our Newsletter | Privacy Policy | Terms of Service