Jump to content

larry29936

Members
  • Posts

    128
  • Joined

  • Last visited

Everything posted by larry29936

  1. @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.
  2. I have to go out for a bit. I'll reply when I get back, about an hour.
  3. @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
  4. @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.
  5. @gw1500seThe comparison is to a range of ip addresses. g_start and t_end are both unsigned int's
  6. @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?
  7. 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?
  8. @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?
  9. 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?
  10. Even better would be to allow editing until there's a new post in the thread.
  11. I found a solution as follows: <?php if($chk == 0): ?> <div>stuff....</div> <?php else: ?> <div>other stuff...</div> <?php endif; ?>
  12. @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
  13. I'm trying to display an html <div> based on the state of a variable set during php execution . The variable is $chk and is set to either 0 or 1 with 0 meaning failed and 1 meaning pass. Here's the code: if <?php echo "{$chk}";?> == 0 <div class="container"> <div class="row" style="color:red"> <br><br><br><br><br><br> <center>Database Update Failed</center> </div> </div> else <div class="container"> <div class="row"> <br><br><br><br><br><br> <center>Database Updated</center> </div> </div> Both of the <div>'s display after the completion of the php instead of the one based on the $chk variable. I've tried several ways to get the value of $chk but neither print_r nor echo seem to work. $chk is set to 0 at the start of the php execution. Thanks in advance, Larry
  14. I have the following php block that works on an existing bootstrap webpage: <?php $isos = glob('download/*.iso'); $iso = $isos[count($isos) -1]; $isoname = basename($iso); $isodown = "/".$isoname; $md5file = md5_file($iso); $pdfs = glob('download/foxcloneV*.pdf'); $pdf = $pdfs[count($pdfs) -1]; $pdfname = basename($pdf); $debs = glob('download/*.deb'); $deb = $debs[count($debs) - 1]; $debname = basename($deb);; $srcs = glob('download/*.tar.*'); $src = $srcs[count($srcs) - 1]; $srcname = ltrim($src,'download/'); ?> When I try to use it in a non-bootstrap website, it fails with the following errors: Notice: Undefined offset: -1 in /home/larry/web/test2/public_html/download.php on line 104 Warning: md5_file(): Filename cannot be empty in /home/larry/web/test2/public_html/download.php on line 106 Notice: Undefined offset: -1 in /home/larry/web/test2/public_html/download.php on line 109 Notice: Undefined offset: -1 in /home/larry/web/test2/public_html/download.php on line 113 Notice: Undefined offset: -1 in /home/larry/web/test2/public_html/download.php on line 117 Each of these failures is the line after the glob. Is the glob failing or is the syntax wrong for non-bootstrap use? Either way, my thinking is that php should behave the same regardless of what the base model for the website is. Both the bootstrap and non-bootstrap sites are running locally under apache2.4 and php7.2. Can someone provide some insight?
  15. @Barand, it's showing most of my IPV6 ranges as overlapping Showing rows 0 - 499 (49184 total, Query took 2.0331 seconds.)
  16. @Barand, I'm getting a sql error when I run your code: SELECT * -> FROM ( -> SELECT CASE WHEN start <= @xend AND end >= @xstart -> THEN id -> ELSE NULL -> END as found -> , @xstart := start as start -> , @xend := end as end -> FROM ip_lookup -> JOIN (select @xstart := null, @xend := null) init -> WHERE id > 3000000 -> ) search -> WHERE found IS NOT NULL; Here's the error: MySQL said: #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 '-> FROM ( -> SELECT CASE WHEN start <= @xend AND end >= @xstart ' at line 2 Happens both local and on host server.
  17. @Barand, cycling through 5 million pairs of start and end ip's in the lookup table is a chore. I can't think of a way to automate the check for overlapping or duplicate ranges. Do you have any suggestions?
  18. @Barand, I used the original ip_lookup.csv to populate the ip_lookup table and the start and end ip fields are in dot notation for IPv4 ip's and the IPv6 ip's look correct with the start ip's ending with :: and the end ip's ending with :f or :ff. The problem area for me is the download table. I've emptied the original table and modified it to match yours. I exported the download table from the website and tried to import it into the login_time, address, and filename fields into the modified table but I keep getting the following error: " ERROR 1242 (21000): Subquery returns more than 1 row" when I use the following in mysql shell: LOAD DATA LOCAL INFILE '/home/larry/web/sql/download.csv' INTO TABLE download FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (id,log_time, address, filename); I get the same error if I try to import via phpMyadmin. It appears to be coming from the trigger: CREATE TRIGGER `ip_address_and_refer` BEFORE INSERT ON `download` FOR EACH ROW BEGIN SET NEW.ip_address = inet6_aton(NEW.address), NEW.refer = (SELECT id FROM lookup i WHERE NEW.ip_address BETWEEN i.start AND i.end ); END Do I need to add a "limit 1" to the trigger?
  19. @Barand, before I start I want to be sure about something. In your create table for ip_lookup you have both start and end set as varbinary(16). Will that handle full IPv6 addresses?
  20. @Barand, the ip_lookup table is 5,200,000 rows, around half of them are IPv6 ip's and includes location data. The ip's will have to import into varbinary(40) fields instead of varbinary(16) fields so I can work with them. The download table currently has about 2000 rows. There's nothing I can do about previously entered IPv6 ip's in the download table but there aren't that many, less than 100. I think it will take forever to reconfigure everything and import the data. I have nothing but time but it will be pretty boring to do everything on my local server then have to export everything to my web host as well as modify my php code to handle the changes. I guess I'll be busy for the next few days. Thanks for the help.
×
×
  • 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.