Barand

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

According to my manual...

@Beluga Don't attribute things to me that I didn't say. Don't hijack other people's threads Don't resurrect old topics.

mysql> CREATE TABLE `download` ( -> `download_id` int(11) NOT NULL AUTO_INCREMENT, -> `ip_address` varchar(50) DEFAULT NULL, -> `address` varchar(50) DEFAULT NULL, -> PRIMARY KEY (`download_id`) -> ); mysql> INSERT INTO download (address) VALUES ('66.249.75.215'); mysql> UPDATE `download` SET `ip_address` = hex(inet_aton(`ADDRESS`)) WHERE ip_address is null; mysql> SELECT address -> , ip_address -> FROM download; +---------------+------------+ | address | ip_address | +---------------+------------+ | 66.249.75.215 | 42F94BD7 | +---------------+------------+

I'd expect 1 & 4

... WHERE b.user_id = 1 AND a.user_id <> b.user_id; -- ADD THE CONDITION

mysql> CREATE TABLE `download` ( -> `download_id` int(11) NOT NULL AUTO_INCREMENT, -> `ip_address` int(10) unsigned DEFAULT NULL, -> `address` varchar(50) DEFAULT NULL, -> PRIMARY KEY (`download_id`) -> ); mysql> INSERT INTO download (address) VALUES ('66.249.75.215'); mysql> UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null; mysql> SELECT address -> , HEX(ip_address) as ip_address -> FROM download; +---------------+------------+ | address | ip_address | +---------------+------------+ | 66.249.75.215 | 42F94BD7 | +---------------+------------+

Of course it does - as already stated, inet_aton() is an SQL function not PHP

$stmt = $pdo->prepare("UPDATE `download` SET `ip_address` = inet_aton(`ADDRESS`) WHERE ip_address is null;"); @gw1500se Those look like backticks to me

https://www.php.net/manual/en/mysqli.prepare.php

Simple rule: if you are LEFT JOINing to a table, put any conditions on that table in the ON clause. (If you put them in the WHERE clause, the join behaves as an INNER JOIN)

Don't run queries in side loops. Use a single query which joins the two tables. Use prepared statements. Use PDO, it's easier and better than mysqli. You could use a second query to group/sum the product totals but as you are listing all the products anyway, you may as well total them into an array as you go. <?php // assumes you have a PDO conection here $tdata = ''; $totdata = ''; $date_from = $_POST['date1'] ?? date('Y-m-d', strtotime('first day of this month')); $date_to = $_POST['date2'] ?? date('Y-m-d'); $res = $db->prepare("SELECT o.order_id , CONCAT(o.firstname, ' ', o.lastname) as full_name , DATE_FORMAT(o.date_added, '%M %e, %Y') as date , p.name as product , p.quantity as qty FROM oc_order o JOIN oc_order_product p USING (order_id) WHERE o.order_status_id <> 0 AND o.date_added BETWEEN ? AND ? ORDER BY o.order_id, product "); $res->execute( [ $date_from, $date_to ] ); $prevorder = 0; $totals = []; foreach ($res as $r) { if ($r['order_id'] != $prevorder) { $tdata .= "<tr><td>{$r['full_name']}</td><td>{$r['date']}</td><td colspan='2'>&nbsp;</td></tr>"; $prevorder = $r['order_id']; } $tdata .= "<tr><td colspan='2'>&nbsp;</td><td>{$r['product']}</td><td>{$r['qty']}</td></tr>"; // accumulate totals by product if (isset($totals[$r['product']])) { $totals[$r['product']] += $r['qty']; } else { $totals[$r['product']] = $r['qty']; } } arsort($totals); foreach ($totals as $p => $q) { $totdata .= "<tr><td>$p</td><td>$q</td></tr>"; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Example</title> <style type="text/css"> table {width: 50%; margin: 16px 50px; border-collapse: collapse; } th {background-color: #000; color: #FFF; padding: 8px;} td {padding: 4px 8px;} </style> </head> <body> <h3>Orders</h3> <table> <tr><th>Customer</th><th>Date</th><th>Product</th><th>Quantity</th></tr> <?=$tdata?> </table> <h3>Product Totals</h3> <table> <tr><th>Product</th><th>Total Qty</th></tr> <?=$totdata?> </table> </body> </html> Giving Your data needs nomalizing. It should be something like this +----------------+ +---------------+ +------------------+ +-----------------+ | customer | | oc_order | | oc_order_product | | product | +----------------+ +---------------+ +------------------+ +-----------------+ | cust_id |----+ | order_id |----+ | order_product_id | +---| product_id | | firstname | | | date_added | +---| order_id | | | name | | lastname | +---| cust_id | | product_id |---+ | price | | email | | status_id | | quantity | +-----------------+ +----------------+ +---------------+ +------------------+

A common cause of memory exhaustion is infinite recursion. Are you sure about that callback?

try SELECT DISTINCT u.firstname , u.lastname FROM ssm_chat_link a JOIN ssm_chat_link b USING (chat_id) JOIN user u ON a.user_id = u.user_id WHERE b.user_id = 1;