Barand

As you aren't checking for MySQL errors, put this line just before you connect with mysqli_connect() mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); That will check for errors for you.

Not to mention the use of TEXT type for names (65,000 character team names! - must be Welsh), dates and scores

If you have Workbench why are you even bothering with phpMyAdmin?

not what you said before. and not being able to see your screen I haven't a clue what that looks like.

fixture_view (input data) +---------+--------+----------+-----------+-----------+----------+ | idmatch | weekno | hometeam | homegoals | awaygoals | awayteam | +---------+--------+----------+-----------+-----------+----------+ | 4 | 1 | Laker | 1 | 1 | Jardine | | 1 | 1 | Cowdrey | 1 | 0 | Grace | | 2 | 2 | Grace | 2 | 2 | Cowdrey | | 6 | 2 | Jardine | 1 | 3 | Laker | | 9 | 3 | Laker | 2 | 4 | Cowdrey | | 3 | 3 | Jardine | 4 | 4 | Grace | | 10 | 4 | Cowdrey | 4 | 4 | Laker | | 5 | 4 | Grace | 1 | 2 | Jardine | | 7 | 5 | Cowdrey | 2 | 0 | Jardine | | 8 | 5 | Grace | 0 | 3 | Laker | | 12 | 6 | Jardine | 1 | 4 | Cowdrey | | 11 | 6 | Laker | 4 | 1 | Grace | +---------+--------+----------+-----------+-----------+----------+ SELECT team1 as team , team2 as versus , SUM(scored) as scored , SUM(conceded) as conceded FROM ( SELECT hometeam as team1 , awayteam as team2 , homegoals as scored , awaygoals as conceded FROM fixture_view f UNION ALL SELECT awayteam as team1 , hometeam as team2 , awaygoals as scored , homegoals as conceded FROM fixture_view f ) games GROUP BY team1, team2; +---------+---------+--------+----------+ | team | versus | scored | conceded | +---------+---------+--------+----------+ | Cowdrey | Grace | 3 | 2 | | Cowdrey | Jardine | 6 | 1 | | Cowdrey | Laker | 8 | 6 | | Grace | Cowdrey | 2 | 3 | | Grace | Jardine | 5 | 6 | | Grace | Laker | 1 | 7 | | Jardine | Cowdrey | 1 | 6 | | Jardine | Grace | 6 | 5 | | Jardine | Laker | 2 | 4 | | Laker | Cowdrey | 6 | 8 | | Laker | Grace | 7 | 1 | | Laker | Jardine | 4 | 2 | +---------+---------+--------+----------+

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff Example SELECT * FROM test.compliance_complaints; +---------------+---------------+-----------------+ | compliance_id | date_received | settlement_date | +---------------+---------------+-----------------+ | 1 | 2020-04-01 | 2020-04-21 | | 2 | 2020-05-01 | NULL | | 3 | 2020-01-10 | 2020-05-01 | +---------------+---------------+-----------------+ SELECT compliance_id , date_received , settlement_date , DATEDIFF(COALESCE(settlement_date, CURDATE()), date_received) as interval_days FROM compliance_complaints; +---------------+---------------+-----------------+---------------+ | compliance_id | date_received | settlement_date | interval_days | +---------------+---------------+-----------------+---------------+ | 1 | 2020-04-01 | 2020-04-21 | 20 | | 2 | 2020-05-01 | NULL | 11 | | 3 | 2020-01-10 | 2020-05-01 | 112 | +---------------+---------------+-----------------+---------------+

Similar problem in your download table. You have defined the string version of the ip_address as varchar(32) but to hold 8 groups of 4 hex chars plus colons between them you need 40 chars.

There's an example similar to that in the SQL tutorials in my sig

I used this upload SQL LOAD DATA LOCAL INFILE 'download.csv' INTO TABLE download FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (ID, LOG_TIME, ip_address, FILENAME, @dummy, @dummy, @dummy, @dummy, @dummy) SET ADDRESS = inet6_aton(ip_address); But, like you, got 17 "error 1411" reports. These are the records giving the errors mysql> SELECT id -> , hex(address) as address -> , ip_address -> FROM download -> WHERE address IS NULL -> ORDER BY address; +------+---------+----------------------------+ | id | address | ip_address | +------+---------+----------------------------+ | 757 | NULL | 2a02:c7f:1804:b000:4970:6 | | 838 | NULL | 2a01:cb05:4ce:5900:eff0:3 | | 841 | NULL | 2a01:cb05:4ce:5900:eff0:3 | | 967 | NULL | 2a04:4540:8202:ce00:7c50: | | 968 | NULL | 2a04:4540:8202:ce00:7c50: | | 970 | NULL | 2a04:4540:8202:ce00:7c50: | | 977 | NULL | 2a04:4540:8202:ce00:7c50: | | 978 | NULL | 2a04:4540:8202:ce00:7c50: | | 1034 | NULL | 2a04:4540:820f:a000:350f: | | 1035 | NULL | 2a04:4540:820f:a000:350f: | | 1036 | NULL | 2a04:4540:820f:a000:350f: | | 1037 | NULL | 2a04:4540:820f:a000:350f: | | 1044 | NULL | 2a04:4540:820f:a000:350f: | | 1251 | NULL | 2a04:4540:8205:4200:4ddb: | | 1252 | NULL | 2a04:4540:8205:4200:4ddb: | | 1412 | NULL | 94.34.81.202, 94.34.81.202 | | 1414 | NULL | 94.34.81.202, 94.34.81.202 | +------+---------+----------------------------+ The two at the end are obviously wrong as there are two IPv4 addresses in the same field. The others that didn't convert are all the IPv6 address that you have in your csv file. I confesss, I haven't worked with IPv6 so I haven't a clue right now what is wrong with them to make them invalid.

We could use a query to find them, report them and stop them being used in the update.

Having found them, is it possible to apply a manual edit to the AS400 data to correct them?

And a couple of jobs are really badly afflicted ... Looks like a job for your AS400 team to fix it. The question is "Can you live with it for now?"

SELECT job_number , line_item , COUNT(*) as total FROM production_data GROUP BY job_number, line_item HAVING total > 1;

Is it a common occurence to have two line items the same within a job number, or have you accidentally hit upon a rare glitch? (Needs fixing either either way).

Is part number unique to a job? It's hard for me to sort the image and check for myself.

What's the problem? Job and line item are both there in that image.

Yes, however conditions could be added. The archiving step could be removed completely and replaced with a trigger function that fires only in the event of a change to either the date or description during the update. There's a link in my sig.

Do it in the query. SELECT date_received , settlement_date , DATEDIFF(settlement_date, date_received) as interval_days . . . edit: Almost forgot - you need to switch from mysql_ functions (obsolete) to mysqli or PDO (recommended)

I didn't say you had. It was a comment on the general pattern with your posts. And I'm not angry at all. It's just exasperation with the futility of trying to help. Can do.

Here we go again.. We tell you how it should be done then get involved in several pages of posts by you telling us you don't want to do it that way. I'll pass. JFDI. (BTW, what did you think of MySQL Workbench - or is your motto "Procrastinate now!")

I did show you earlier today how to convert the values on upload. In case you weren't awake, here is a query to upload the lookup data and convert the x.x.x.x to varbinary (16) at the same time LOAD DATA LOCAL INFILE 'ip_lookup.csv' INTO TABLE ip_lookup FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES -- REMOVE if no heading row (start_ip, end_ip, continent, country, area, city, @dummy, @dummy) SET start_ip = inet6_aton(start_ip) , end_ip = inet6_aton(end_ip);

My code still isn't correct after looking at your "download" data. The ip_address in the download table is already varbinary so doesn't need the inet6_aton(). (That's already been done) You are getting xxx.xxx.xxx.xxx from ip_lookup in phpMyAdmin presumably because that's what you are putting in those columns instead of converting them to varbinary with inet6_aton() - unless your upload does that conversion in which case you would need to SELECT inet6_ntoa(start_ip) to get xxx.xxx.xxx.xxx format. Your start and end ip addresses need to be correct varbinary for range checking to work. The x.x.x.x don't sort reliably so can't be used for range definitions. For example 1.0.0.1 1.0.0.2 1.0.0.11 would sort as 1.0.0.1 1.0.0.11 1.0.0.2 What is your code for uploading the "ip_lookup.csv"?

Sorry, should have been UPDATE download d INNER JOIN ip_lookup on (inet6_aton(d.ADDRESS) >= inet6_aton(ip_lookup.start_ip) AND inet6_aton(d.ADDRESS) <= inet6_aton(ip_lookup.end_ip)) SET d.refer = ip_lookup.id WHERE d.FILENAME is not null;

Those two statements would appear to contradict each other. Are you sure you don't want this... UPDATE download d INNER JOIN d.ADDRESS on (inet6_aton(d.ADDRESS) >= inet6_aton(ip_lookup.start_ip) AND inet6_aton(d.ADDRESS) <= inet6_aton(ip_lookup.end_ip)) SET d.refer = ip_lookup.id WHERE d.FILENAME is not null;

When you last posted your table structure they were unsigned integer ... 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 The ip_address in the "download" table and the "start_ip" and "end_ip" in the lookup table all need to be varbinary(16) otherwise the match on "IP_address BETWEEN start_ip AND end_ip" will not work;