larry29936

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

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

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

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

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

@requinix Fixed that but now getting a new error:

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

This is the line that errors out:

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: How do I fix this?

@Barand - that's what I copied from the ip_address field of the user. Are you saying that inet_aton(user_ip) failed or that putting it in single quotes was wrong?

I'm trying to get an id for a row in a lookup table based on an ip address. All ip's are stored as the result inet_aton(ip) in fields that are varbinary(16). The lookup table has ip ranges (start_ip and end_ip) and countries associated with the range. 1219006512 is the inet_aton value of a user's ip address. When I run the following query in phpmyadmin, I'm getting multiple rows. I get multiple rows with a different result set if I run the query in mysql in the terminal. Query follows: SELECT * FROM lookup WHERE ('1219006512' >= start_ip and '1219006512'<= end_ip); What am I doing wrong?

Good point. 😀

Even better would be to allow editing until there's a new post in the thread.

I found a solution as follows: <?php if($chk == 0): ?> <div>stuff....</div> <?php else: ?> <div>other stuff...</div> <?php endif; ?>

@gw1500se OK, using your code as follows: $stmt = $pdo->prepare("UPDATE files SET filename = '$srcname', logtime=now() WHERE id = 4"); $stmt->execute() ; $chk=1; } if ($chk == 0) { echo \" <div class="container"> <div class="row" style="color:red"> <br><br><br><br><br><br> <center>Database Update Failed</center> </div> </div>" } else { echo \" <div class="container"> <div class="row"> <br><br><br><br><br><br> <center>Database Updated</center> </div> </div>" } ?> I get the following error on the line with the echo: Parse error: syntax error, unexpected '"' (T_CONSTANT_ENCAPSED_STRING), expecting identifier (T_STRING) in /home/larry/web/test/public_html/update.php on line 141