Barand

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;

Do you have any records in your download table with NULL ip_adresses after the inet_aton() update? If so they will have invalid ADDRESS values.

I know. If only someone would invent JOINs.

Are ip_start and ip_end columns the string values or are they varbinary(16)?

That should work OK Try sorting the data on address. Any weird values might go to either the top or the end and make them easy to find. At what point do you use inet6_aton()?

Tried to replicate the error message myself ... My table CREATE TABLE `download` ( `download_id` int(11) NOT NULL AUTO_INCREMENT, `ip_address` varbinary(16) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, PRIMARY KEY (`download_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; My csv data ip_address,address "","255.50.1.123" "","255.50.11.123" "","255.50.2.123" "","255.50.4.123" Running the csv upload query, but omitting the IGNORE to skip the header line) LOAD DATA LOCAL INFILE 'c:/inetpub/wwwroot/test/ip_test.csv' INTO TABLE download FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' -- IGNORE 1 LINES (ip_address, address) SET ip_address = inet6_aton(address); Gave the message And this in the table SELECT download_id , HEX(ip_address) as binaryIP , address as stringIP FROM download; +-------------+----------+---------------+ | download_id | binaryIP | stringIP | +-------------+----------+---------------+ | 1 | NULL | address | | 2 | FF32017B | 255.50.1.123 | | 3 | FF320B7B | 255.50.11.123 | | 4 | FF32027B | 255.50.2.123 | | 5 | FF32047B | 255.50.4.123 | +-------------+----------+---------------+ EDIT: Also, specifying the wrong line terminator character (eg /n instead of \n) gave the same 1411 warning and the records in the table were like this +-------------+----------+----------------------------------------------------+ | download_id | binaryIP | stringIP | +-------------+----------+----------------------------------------------------+ | 8 | NULL | address"" | | 9 | NULL | "255.50.1.123"","255.50.11.123"","255.50.2.123" | +-------------+----------+----------------------------------------------------+

That is setting the value to 3, not testing if it is equal to 3. The equality operator is "=="

Another approach would be to have a table in which you record users' logins. CREATE TABLE `user_login` ( `login_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`login_id`), KEY `idx_user_login_user_id` (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; To get the last login you query the table for the latest time. For example, suppose the table contains +----------+---------+---------------------+ | login_id | user_id | login_time | +----------+---------+---------------------+ | 1 | 1 | 2020-05-08 10:31:20 | | 2 | 2 | 2020-05-09 14:54:11 | | 3 | 1 | 2020-05-09 14:55:39 | +----------+---------+---------------------+ then, for user #1 SELECT COALESCE(MAX(login_time), 'Data unavailable') as last_login FROM user_login WHERE user_id = 1; +---------------------+ | last_login | +---------------------+ | 2020-05-09 14:55:39 | +---------------------+ but for user #3 SELECT COALESCE(MAX(login_time), 'Data unavailable') as last_login FROM user_login WHERE user_id = 3; +------------------+ | last_login | +------------------+ | Data unavailable | +------------------+ After getting the last login, write the new login (the login time will be inserted automatically) INSERT INTO user_login (user_id) VALUES ( ? );

I don't think you need bother with the json. Just define your table as something like this CREATE TABLE `bookmark` ( `bookmark_id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `page_link` varchar(50) DEFAULT NULL, `css_icon` varchar(50) DEFAULT NULL, `icon_photo_link` varchar(50) DEFAULT NULL, `language` varchar(10) DEFAULT NULL, `icon_title` varchar(50) DEFAULT NULL, PRIMARY KEY (`bookmark_id`), UNIQUE KEY `unq_page_link` (`page_link`), KEY `idx_user` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I stopped reading earlier when I spotted the quotes. Then I read the rest of the line. That syntax is completely wrong. You were almost there with the prepared statement that you apparently abandoned. A pity because that is the correct method, you should not be putting variable directly into the sql. Omit id from the column list and the corresponding null in the values (that will happen anyway) Your bind_param is missing the variable types eg ('ssss')

Don't put column names in single quotes.

No. One table named "bookmark" and store the user's id in each bookmark record. If your site currently uses a database, just put the extra table in that. You can check how many each user has with a simple SELECT COUNT(*) as total FROM bookmark WHERE userid = ?; How that is stored depends on what those "necessary values" are. If the arrays always contains the same keys I'd create a column in the bookmark table for each key. If the keys vary from bookmark to bookmark then store as a json encoded array (MySQL 5.7+ supports json columns) If "bookmark_title" should be unique, enforce it in the table by placing a UNIQUE constraint on that column.

That isn't the most comprehensive requirements specification that I have worked from so there is a bit of guesswork involved, such as the comparison criteria being job number and line item matching, and which columns are to be updated from the csv data. If this is the case, your "production_data" table should have an index on these columns (as my temp table below has). Anyway, given those caveats, the processing would be along the lines below (4 queries instead of 40,000) and should give you a guide. <?php ## ## This initial section would normally be in an included file ## const HOST = 'localhost'; const USERNAME = '???'; const PASSWORD = '???'; const DATABASE = '???'; function pdoConnect($dbname=DATABASE) { $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); $db->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true); return $db; } ## ## Connect to DB server ## $db = pdoConnect(); ## ## create a temporary table to store the csv data ## $db->exec("CREATE TEMPORARY TABLE production_csv ( id int not null auto_increment primary key, enterprise tinytext, part_num text, description text, qty int, line_item varchar(11), job_num int, work_order varchar(50), psm varchar(50), date_change_flag tinyint, scheduled_ship_date date, on_hold tinyint, on_hold_reason varchar(50), total_hours decimal(10,2), worfc varchar(50), INDEX job_line (job_num, line_item) )"); ## ## load the csv data into the table ## $db->exec("LOAD DATA LOCAL INFILE 'prod_data.csv' INTO TABLE production_csv FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (@dummy, @dummy, enterprise, part_num, desc, qty, line_item, job_num, work_order, psm, date_change_flag, scheduled_ship_date, on_hold, on_hold_reason, total_hours, worfc) "); ## ## write matching production_data records to archive ## $db->exec("INSERT INTO production_archive SELECT pd.* FROM production_data pd JOIN production_csv USING (job_num, line_item) "); ## ## update the production_data table from the production_csv table ## $db->exec("UPDATE production_data d JOIN production_csv c USING (job_num, line_item) SET d.enterprise = c.enterprise, d.part_number = c.part_num, d.description = c.description, d.qty = c.qty, d.as400_ship_date = c.scheduled_ship_date, d.hold_status = c.on_hold ") ?>

Show us the table structure/s you are working with and the csv layout. Tell us precisely what the goal is and let's see if we can come up with a more efficient way than 40,000 queries.

Databases were designed for matching and searching. Any particular reason why you pull data from the DB then do the searching? That is not the way to use key() array_search returns the key of the found item, not a count.

I'd do something like this $data = json_decode($j, 1); foreach ($data['results'][0]['address_components'] as $address) { if (in_array('sublocality', $address['types'])) { echo "Sublocality : {$address['long_name']}<br>"; } if (in_array('administrative_area_level_1', $address['types'])) { echo "Administrative area level 1 : {$address['long_name']}<br>"; } }

Here's one way <?php $n = 1; for ($i=0; $i<12; $i++) { printf("%15d | %-20s\n", $n, weird_format($n)); $n *= 10; } function weird_format($n) { if (($k = strlen($n)) < 4) return $n; $a = substr($n, 0, $k-3); // split off the last 3 chara $b = substr($n, -3); if (!($k%2)) $a = ' '.$a; // ensure first section is even no of chars $c = str_split($a, 2); // split into groups of 2 return trim(join(',', $c) . ',' . $b); // join them with commas } ?> Output: 1 | 1 10 | 10 100 | 100 1000 | 1,000 10000 | 10,000 100000 | 1,00,000 1000000 | 10,00,000 10000000 | 1,00,00,000 100000000 | 10,00,00,000 1000000000 | 1,00,00,00,000 10000000000 | 10,00,00,00,000 100000000000 | 1,00,00,00,00,000

First, tell mysqli to throw exceptions on errors mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); then try{} it.

VARBINARY (16) would give you a common type for IPv4 and IPv6