larry29936

@Barand, I fixed the code so it doesn't error, but the ip's are wrong, many displaying as 0.0.123.678, which I know are wrong. The new code is: <?php $query = $con->query('SELECT inet_ntoa(IP_ADDRESS)as address, FILENAME, country, area, city FROM download WHERE FILENAME is not null ORDER BY country,area,city'); while ($row = $query->fetch()) { echo "<tr>"; echo "<td>" . $row['address'] ."</td>"; echo "<td>" . $row['FILENAME'] ."</td>"; echo "<td>" . $row['country'] . "</td>"; echo "<td>" . $row['area'] . "</td>"; echo "<td>" . $row['city'] . "</td>"; echo "</tr>"; } ?> The sql file that the data was imported from had the ip's as strings that went into a column that is int(50) unsigned. I checked the import file and none of the imported ip's start with 0.0.

I have ip_addresses stored in the database as unsigned integers. I want to display them in standard xxx.xxx.xxx.xxx format in a table. In my code, I tried the following: <?php while ($row = $query->fetch()) { $address = inet_ntoa($row['IP_ADDRESS']); //Throws error here echo "<tr>"; // echo "<td>" . $row['IP_ADDRESS'] ."</td>"; echo "<td>" . $row['$address'] ."</td>"; echo "<td>" . $row['FILENAME'] ."</td>"; echo "<td>" . $row['country'] . "</td>"; echo "<td>" . $row['area'] . "</td>"; echo "<td>" . $row['city'] . "</td>"; echo "</tr>"; } ?> It throws the following error: Parse error: syntax error, unexpected 'inet_ntoa' (T_STRING) in /home/larry/web/test/public_html/report1.php on line 205 How do I do the conversion so I can display the ip_address? Thanks in advance, Larry

That was the problem. It was connecting to the production db, not the test.db. Thanks for all your help.

I wasn't clear, sorry. The error was on column 'area'.

same error on column area after removing country. It's like it doesn't like lower case field names. That wasn't it. I changed the area column to upper case, still errors.

I have the following php code that errors as indicated: $query = $con->query('SELECT FILENAME, country, area, city FROM download WHERE FILENAME is not null'); Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'country' in 'field list' in /home/larry/web/test/public_html/report1.php:47 Stack trace: #0 /home/larry/web/test/public_html/report1.php(47): PDO->query('SELECT FILENAME...') #1 {main} thrown in /home/larry/web/test/public_html/report1.php on line 47 The Select statement doesn't error when run in mysql shell or phpmyadmin. Here's the result of show create table download: localhost/test/download/ http://localhost/phpmyadmin/tbl_sql.php?db=test&table=download&token=5739c407033be3e118287bc7a9041c2c Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. Your SQL query has been executed successfully. show create table download download CREATE TABLE `download` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `LOG_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `IP_ADDRESS` int(64) unsigned NOT NULL, `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `ID` (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1266 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci Does anyone have an idea why this is happening?

@Barand, here's what you asked for: download CREATE TABLE `download` ( `ID` int(5) NOT NULL AUTO_INCREMENT, `LOG_TIME` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `IP_ADDRESS` int(64) unsigned NOT NULL, `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=1266 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci ip_lookup CREATE TABLE `ip_lookup` ( `id` int(25) unsigned NOT NULL AUTO_INCREMENT, `start_ip` int(64) unsigned NOT NULL, `end_ip` int(64) unsigned NOT NULL, `continent` varchar(2) COLLATE utf8_general_mysql500_ci NOT NULL, `country` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL, `area` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL, `city` varchar(50) COLLATE utf8_general_mysql500_ci NOT NULL, PRIMARY KEY (`id`), KEY `start_ip` (`start_ip`) ) ENGINE=InnoDB AUTO_INCREMENT=5277086 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci

@Barand, ran the following in mysql shell and it took 7 minutes to run: SELECT download.FILENAME , ip_lookup.country , ip_lookup.area , ip_lookup.city FROM download INNER JOIN ip_lookup ON download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip WHERE download.FILENAME is not null; There are 5 million + records in the ip_lookup table so that's probably the hang up. That was one reason why I wanted all the information in the download table.

@Barand, This query gives an error of "Operand should contain 1 column(s)" in mysql shell: SELECT (download.FILENAME, ip_lookup.country, ip_lookup.area, ip_lookup.city) FROM download, ip_lookup WHERE download.IP_ADDRESS BETWEEN ip_lookup.start_ip and ip_lookup.end_ip; That's why I wanted all data in one table.

I'm new to using triggers and really not sure how to proceed. I have an access table that a row is inserted whenever someone opens the website or downloads a file with the following structure: `ID` int(5) NOT NULL autoincrement, `LOG_TIME` datetime NOT NULL DEFAULT current_timestamp(), `IP_ADDRESS` int(64) unsigned COLLATE utf8_general_mysql500_ci NOT NULL, `FILENAME` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `country` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `area` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL, `city` varchar(50) COLLATE utf8_general_mysql500_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci; When the site is accessed, a row is inserted with only the first 3 fields are filled. When a file is downloaded another row is inserted with the first 4 fields filled. I want to create a trigger such that when a download row is inserted, the IP_ADDRESS is compared to another table to update the country, area, and city fields. I can currently do that for the whole table in mysql shell using the following code: UPDATE access t2, ip_lookup t1 SET t2.country = t1.country, t2.area = t1.area, t2.city = t1.city WHERE ((t2.IP_ADDRESS) BETWEEN (t1.start_ip) AND (t1.end_ip)) AND t2.FILENAME is not null and t2.country is null; How would I write an "after insert" trigger to update the last 3 fields based on the ip of the row that was inserted because of a download? Thanks in advance, Larry

I went ahead and re-created the tables and set all ip's as int(64) unsigned and imported the data again. This time the data imported OK.

I used binary(64) because for IPv6 ip's the db wanted the unsigned int to have a width of 128 vs binary worked at width of 64. I could recreate the tables and copy the data over but that seems like a lot of work. What do you suggest?

@gw1500se, I've decided to try to go about it in a different way but I'm having a problem extracting the ip_address from an array, as follows: $stmt = $con->query("SELECT DISTINCT(IP_ADDRESS) FROM download WHERE FILENAME is not NULL"); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $temp = $row ; echo var_dump($temp); // shows as "array(1) { ["IP_ADDRESS"]=> string(64) "92.240.60.16" }" $country = $con->query("SELECT (country) FROM ip_lookup WHERE '$temp' between start_ip and end_ip"); $area = $con->query("SELECT (area) FROM ip_lookup WHERE '$temp' between start_ip and end_ip"); $city = $con->query("SELECT (city) FROM ip_lookup WHERE '$temp' between start_ip and end_ip"); $test = $con->query("UPDATE TABLE download SET country = '$country', area = '$area', city= '$city' WHERE IP_ADDRESS = '$temp' and FILENAME is not NULL") ; } All ip_addresses are stored as binary(64) and the errors I'm getting follow: Notice: Array to string conversion in /home/larry/web/test/public_html/report.php on line 26 Notice: Array to string conversion in /home/larry/web/test/public_html/report.php on line 27 Notice: Array to string conversion in /home/larry/web/test/public_html/report.php on line 28 Recoverable fatal error: Object of class PDOStatement could not be converted to string in /home/larry/web/test/public_html/report.php on line 29 I'd appreciate some guidance. Larry

@StevenOliver, thanks for the response. I think that unless the "column not found" is resolved it doesn't make any sense to worry about the rest. BTW, the IP's are stored as binaries because I kept getting size errors trying to store them as unsigned integers. Still looking into why, but I think it may be because of IPv6 IP's.

Replacing t1 & t2 with actual table names didn't help in the report. same fatal error, " Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'download.country' in 'field list'". It runs both with t1 & t2 and with table names in mysql shell.

No, I haven't. Thanks for that. I'll do it to confirm nothing get's changed from the original. EDIT: Took the sql from the echo and it runs fine in mysql shell. I'm going to try taking the shorthand out of the sql and put in the actual table names to see what happens.

that query runs fine in a mysql shell. I don't understand why it fails in the php. Will have to run it by folks in the mysql forums.

I already tried that but I'm getting a "Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't2.country' in 'field list' " from $sql1 = 'Update download t2, ip_lookup t1 set t2.country = t1.country, t2.area = t1.area, t2.city = t1.city where ((t2.IP_ADDRESS) = (t1.start_ip) OR (t2.IP_ADDRESS) > (t1.start_ip)) AND ((t2.IP_ADDRESS) = (t1.end_ip) OR (t2.IP_ADDRESS) < (t1.end_ip)) AND (t2.FILENAME is not null and t2.country is null)'; I'm going to try making a trigger for the download table so that on insert it takes care of having to call that later.

OK, first query is gone and last query looks like this: $stmt = $con->prepare($sql2); $stmt->execute(); $result = $stmt->fetchAll(); Does the loop come next? Currently, it would give me 70+ rows of data.

So, get rid of the first query and get rid of the loop on the last query. What will the fetch give me, a count of the rows or the actual data to be inserted in the table?

I'm creating a report page and can't figure out how to retrieve multiple rows from a table and display them in html. There are 5 data elements in each row and I'm thinking that using a form in the html might be the best way as I'm totally ignorant about tables in html. I'm a newbie to php and can't figure out how to accomplish this. Here's the code that I have so far: <?php $filename = NULL; session_start(); // start of script every time. // setup a path for all of your canned php scripts $php_scripts = '../php/'; // a folder above the web accessible tree // load the pdo connection module require $php_scripts . 'PDO_Connection_Select.php'; //******************************* // Begin the script here // Connect to the database if (!$con = PDOConnect("foxclone")): { echo "Failed to connect to database" ; exit; } else: { $sql = 'SELECT COUNT(IP_ADDRESS) FROM download WHERE FILENAME IS NOT NULL'; $sql1 = 'Update download t2, ip_lookup t1 set t2.country = t1.country, t2.area = t1.area, t2.city = t1.city where ((t2.IP_ADDRESS) = (t1.start_ip) OR (t2.IP_ADDRESS) > (t1.start_ip)) AND ((t2.IP_ADDRESS) = (t1.end_ip) OR (t2.IP_ADDRESS) < (t1.end_ip)) AND (t2.FILENAME is not null and t2.country is null)'; $sql2 = 'SELECT (IP_ADDRESS, FILENAME, country, area, city) from download where FILENAME is not null'; // Update the table $stmt = $con->prepare($sql1); $stmt->execute(); // Get count of rows to be displayed in table $stmt = $con->prepare($sql); $stmt->execute() ; $cnt = $stmt->fetch(PDO::FETCH_NUM); // retrieve one row at a time $i = 1; while($i <= $cnt){ $stmt = $con->prepare($sql2); $row->execute(array('')); // Do I need an array here? // from here on, I'm lost $i++; I'd appreciate any guidance you can provide or understandable tutorials you can point me to. Larry

@Barand, sorry for the delay in responding, life got in the way. Your solution worked, thank you. I have a new issue which I'll create a new post for.

Got it working but having a problem getting the data into the fields in the proper format. Here's the sql I used to get it working: LOAD DATA LOCAL INFILE '/home/larry/web/test/public_html/dbip-city-lite-2020-04.csv' INTO TABLE ip_lookup FIELDS TERMINATED BY ',' ; Here's the structure of the table: CREATE TABLE `foxclone`.`ip_lookup` ( `id` INT(10) NOT NULL AUTO_INCREMENT , `start_ip` VARCHAR(20) NOT NULL , `end_ip` VARCHAR(20) NOT NULL , `continent` VARCHAR(2) NOT NULL , `country` VARCHAR(2) NOT NULL , `area` VARCHAR(50) NOT NULL , `city` VARCHAR(50) NOT NULL , `latitude` DECIMAL NOT NULL , `longitude` DECIMAL NOT NULL , PRIMARY KEY (`id`(50))) ENGINE = InnoDB; Here's sample data from the csv: 105.155.190.0,105.155.191.67,AF,MA,Rabat-Salé-Kénitra,Rabat,34.015,-6.83272 105.155.191.68,105.155.191.255,AF,MA,Marrakesh-Safi,Marrakesh,31.6342,-7.99994 105.155.192.0,105.155.192.255,AF,MA,Fès-Meknès,Meknes,33.873,-5.54073 105.155.193.0,105.155.193.255,AF,MA,Oriental,Berkane,34.92,-2.32 105.155.194.0,105.155.194.255,AF,MA,Rabat-Salé-Kénitra,Rabat,34.015,-6.83272 105.155.195.0,105.155.195.255,AF,MA,Oriental,Oujda,34.6814,-1.90858 The id column isn't autoincrementing, the start_ip is showing the wrong format (2a00:10:a9ff:ffff:ff) and the rest of the fields are in the wrong columns.

I've been trying to load a table from a 450MB .csv file but keep getting syntax errors. The insert sql looks like this: LOAD DATA LOCAL INFILE '/home/larry/web/test/public_html/dbip-city-lite-2020-04.csv' INTO TABLE IP_LOOKUP FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; The code was copied from [https://www.mysqltutorial.org/import-csv-file-mysql-table/] Does anyone see where I've gone wrong? Additional question: How to accomplish this import on my web host? Thanks in advance, Larry

@maxxd, Thnks for the reply. How do I identify the section of the styles.css file so that the html file knows how to format a section of the page? In other words, the tag in the html, like <div id="download" class="??????">?