Barand

👍

That's a 'belt and braces' approach. If you have the screening_id then you have the movie_id and screen

A boolean expression resolves to 0 (false) or 1 (true). Use one in your sort. ORDER BY employee <> 'Senthil', category

All you're showing is snippets of code with no context of where in the code they belong. Anything output from the php script in response to an ajax request is sent back in the ajax response. If ypu want to output the screening_id to the screen it needs to be done in the response-handling function. For example, to create a link to another page, passing the sgid, when a screen number id clicked function(resp) { $.each(resp, function(mid, mdata) { let title = `<h2>${mdata.title}</h2><h4 class='w3-text-gray'>${mdata.genre} (${mdata.runtime})</h4>` $("#movie-listings").append(title) $.each(mdata.screenings, function(dt, ddata) { let datesub = `<h3>${dt}</h3>` $("#movie-listings").append(datesub) $("#movie-listings").append("<div class='screenings'") $.each(ddata, function(k, sdata) { let scr = `<div class='screening'><b>${sdata.start}</b><br> <a href='myOtherPage.php?sgid=${sdata.sgid}'>${sdata.sno}</a> //<<<< ADD </div>` $("#movie-listings").append(scr) }) $("#movie-listings").append("</div>") }) }) },

In you javascript function that handles the jax response, log the response data in the console log. From there you can copy it, json_decode() it then view it with print_r()

Solution1, naturally because it is the simplest. (Although I did once have a holiday job working for a guy who would prefer solution2 as multiplication was beyond him) But being a one-liner doesn't always make it the simplest ... $solution4 = array_sum(array_map(fn($v) => $v / sin(M_PI/6), array_fill_keys(range(0,4), 1)));

A little exercise in logic... If you have startup errors, the code is not executed. If the code is not executed, how can you execute "display_startup_errors"? It has to be pre-set in the php.ini file.

There is an alternative that you might want to consider and that is to create an additional "status" column in your users table, adding it as a generated column which will automatically maintain the user status based on the date calculations (as the above queries do). At present, I have mysql> select * from users; +----+--------------------------------------+-------+---------------------+---------------------+ | id | unique_id | fname | created_at | visited | +----+--------------------------------------+-------+---------------------+---------------------+ | 1 | 5a9c4d9c-c20d-11ee-b072-8cec4b4596d3 | aaaa | 2023-03-24 21:25:06 | NULL | | 2 | 5a9c50bd-c20d-11ee-b072-8cec4b4596d3 | bbbb | 2023-05-14 21:25:07 | 2024-02-01 21:25:07 | | 3 | 5a9c5116-c20d-11ee-b072-8cec4b4596d3 | cccc | 2023-07-11 21:25:07 | 2024-02-01 21:25:07 | | 4 | 5a9c5152-c20d-11ee-b072-8cec4b4596d3 | dddd | 2023-12-13 21:25:07 | NULL | | 5 | 5a9c5189-c20d-11ee-b072-8cec4b4596d3 | eeee | 2023-08-12 21:25:07 | NULL | | 6 | 5a9c51ba-c20d-11ee-b072-8cec4b4596d3 | ffff | 2023-09-27 21:25:07 | NULL | | 7 | 5a9c51ec-c20d-11ee-b072-8cec4b4596d3 | gggg | 2023-05-31 21:25:07 | 2023-09-01 21:25:07 | | 8 | 5a9c5221-c20d-11ee-b072-8cec4b4596d3 | hhhh | 2023-06-25 21:25:07 | NULL | | 9 | 5a9c5255-c20d-11ee-b072-8cec4b4596d3 | kkkk | 2023-09-18 21:25:07 | 2023-04-01 21:25:07 | | 10 | 5a9c5288-c20d-11ee-b072-8cec4b4596d3 | mmm | 2023-04-15 21:25:07 | NULL | +----+--------------------------------------+-------+---------------------+---------------------+ If I then alter the table, adding the new status column... mysql> ALTER TABLE users -> ADD COLUMN `status` VARCHAR(10) GENERATED ALWAYS AS -> (CASE WHEN visited IS NULL -> THEN CASE WHEN timestampdiff(DAY, created_at, now()) < 90 -> THEN 'PENDING' -> WHEN timestampdiff(DAY, created_at, now()) >= 180 -> THEN 'EXPIRED' -> ELSE 'WARNING' -> END -> ELSE CASE WHEN timestampdiff(DAY, visited, now()) < 90 -> THEN 'OK' -> WHEN timestampdiff(DAY, visited, now()) >= 180 -> THEN 'EXPIRED' -> ELSE 'WARNING' -> END -> END) VIRTUAL AFTER `visited`; I can then use a conventional GROUP BY status to get the totals of each type... mysql> SELECT status -> , COUNT(*) as total -> FROM users -> GROUP BY status; +---------+-------+ | status | total | +---------+-------+ | EXPIRED | 4 | | OK | 2 | | PENDING | 1 | | WARNING | 3 | +---------+-------+ and selecting all the data now looks like this... mysql> SELECT * FROM users; +----+--------------------------------------+-------+---------------------+---------------------+---------+ | id | unique_id | fname | created_at | visited | status | +----+--------------------------------------+-------+---------------------+---------------------+---------+ | 1 | 5a9c4d9c-c20d-11ee-b072-8cec4b4596d3 | aaaa | 2023-03-24 21:25:06 | NULL | EXPIRED | | 2 | 5a9c50bd-c20d-11ee-b072-8cec4b4596d3 | bbbb | 2023-05-14 21:25:07 | 2024-02-01 21:25:07 | OK | | 3 | 5a9c5116-c20d-11ee-b072-8cec4b4596d3 | cccc | 2023-07-11 21:25:07 | 2024-02-01 21:25:07 | OK | | 4 | 5a9c5152-c20d-11ee-b072-8cec4b4596d3 | dddd | 2023-12-13 21:25:07 | NULL | PENDING | | 5 | 5a9c5189-c20d-11ee-b072-8cec4b4596d3 | eeee | 2023-08-12 21:25:07 | NULL | WARNING | | 6 | 5a9c51ba-c20d-11ee-b072-8cec4b4596d3 | ffff | 2023-09-27 21:25:07 | NULL | WARNING | | 7 | 5a9c51ec-c20d-11ee-b072-8cec4b4596d3 | gggg | 2023-05-31 21:25:07 | 2023-09-01 21:25:07 | WARNING | | 8 | 5a9c5221-c20d-11ee-b072-8cec4b4596d3 | hhhh | 2023-06-25 21:25:07 | NULL | EXPIRED | | 9 | 5a9c5255-c20d-11ee-b072-8cec4b4596d3 | kkkk | 2023-09-18 21:25:07 | 2023-04-01 21:25:07 | EXPIRED | | 10 | 5a9c5288-c20d-11ee-b072-8cec4b4596d3 | mmm | 2023-04-15 21:25:07 | NULL | EXPIRED | +----+--------------------------------------+-------+---------------------+---------------------+---------+

That's good. You shouldn't be doing that. Don't store derived data. The invoice record should essentially contain the header information for a printed invoice (customer, invoice number, invoice date etc.). All the amounts should be in the invoice items table. The invoice total is the sum of the items in the invoices (Credit items would have a negative amount.) Customer discount would then be appplied.

SELECT id , unique_id , fname , date_format(visited, '%b %e %Y %h:%i%p') as visit_date , date_format(created_at, '%b %e %Y %h:%i%p') as create_date , CASE WHEN visited IS NULL THEN timestampdiff(DAY, created_at, now()) ELSE timestampdiff(DAY, visited, now()) END as elapsed , CASE WHEN visited IS NULL THEN CASE WHEN timestampdiff(DAY, created_at, now()) < 90 THEN 'PENDING' WHEN timestampdiff(DAY, created_at, now()) >= 180 THEN 'EXPIRED' ELSE 'WARNING' END ELSE CASE WHEN timestampdiff(DAY, visited, now()) < 90 THEN 'OK' WHEN timestampdiff(DAY, visited, now()) >= 180 THEN 'EXPIRED' ELSE 'WARNING' END END as status FROM users ORDER BY visited DESC; +----+--------------------------------------+-------+--------------------+---------------------+---------+---------+ | id | unique_id | fname | visit_date | create_date | elapsed | status | +----+--------------------------------------+-------+--------------------+---------------------+---------+---------+ | 2 | 5a9c50bd-c20d-11ee-b072-8cec4b4596d3 | bbbb | Feb 1 2024 09:25PM | May 14 2023 09:25PM | 1 | OK | | 3 | 5a9c5116-c20d-11ee-b072-8cec4b4596d3 | cccc | Feb 1 2024 09:25PM | Jul 11 2023 09:25PM | 1 | OK | | 7 | 5a9c51ec-c20d-11ee-b072-8cec4b4596d3 | gggg | Sep 1 2023 09:25PM | May 31 2023 09:25PM | 154 | WARNING | | 9 | 5a9c5255-c20d-11ee-b072-8cec4b4596d3 | kkkk | Apr 1 2023 09:25PM | Sep 18 2023 09:25PM | 307 | EXPIRED | | 1 | 5a9c4d9c-c20d-11ee-b072-8cec4b4596d3 | aaaa | NULL | Mar 24 2023 09:25PM | 315 | EXPIRED | | 8 | 5a9c5221-c20d-11ee-b072-8cec4b4596d3 | hhhh | NULL | Jun 25 2023 09:25PM | 222 | EXPIRED | | 6 | 5a9c51ba-c20d-11ee-b072-8cec4b4596d3 | ffff | NULL | Sep 27 2023 09:25PM | 128 | WARNING | | 5 | 5a9c5189-c20d-11ee-b072-8cec4b4596d3 | eeee | NULL | Aug 12 2022 09:25PM | 539 | EXPIRED | | 4 | 5a9c5152-c20d-11ee-b072-8cec4b4596d3 | dddd | NULL | Dec 13 2023 09:25PM | 51 | PENDING | | 10 | 5a9c5288-c20d-11ee-b072-8cec4b4596d3 | mmm | NULL | Apr 15 2023 09:25PM | 293 | EXPIRED | +----+--------------------------------------+-------+--------------------+---------------------+---------+---------+ Summary SELECT SUM( (visited IS NULL AND timestampdiff(DAY, created_at, now()) >= 180) OR (visited IS NOT NULL AND timestampdiff(DAY, visited, now()) >= 180) ) as expired , SUM( (visited IS NULL AND timestampdiff(DAY, created_at, now()) BETWEEN 90 and 179) OR (visited IS NOT NULL AND timestampdiff(DAY, visited, now()) BETWEEN 90 and 179) ) as warning , SUM(visited IS NULL AND timestampdiff(DAY, created_at, now()) < 90) as pending , SUM(visited IS NOT NULL AND timestampdiff(DAY, visited, now()) < 90) as ok FROM users; +---------+---------+---------+------+ | expired | warning | pending | ok | +---------+---------+---------+------+ | 5 | 2 | 1 | 2 | +---------+---------+---------+------+

Update - not there yet. Selecting visited and created_at in a query that returns only a single row is meaningless - you will just get single arbitrary dates from any one of your 349 records. The "working" final solution does not include those cases where the user has visited but not in the last 3 or 6 months (You have the Xs but not the Os below) Summary decision table... Has visited? | Y Y Y | N N N | | | | How long ago? | <=3 3 - 6 >=6 | <=3 3 - 6 >=6 | --------------------+-----------------------+-----------------------+ | | | EXPIRED | O | X | | | | WARNING | O | X | | | | PENDING | | X | | | | OK | X | |

What error messages are you getting? Do you have error reporting turned on? [PS]... $values 3 has an element where $z = "10.36.240.65". $values2 has no such element therefore it throws an undefined key error - which error reporting would have told you. In your php.ini file, set error_reporting = E_ALL display_errors = on display_startup_errors = on

Indexing is a prime requirement of relational databases. Even if you aren't using formal foreign key constraints, the foreign keys should be indexed. So should other columns frequently used in searching.

There are examples here

An alternative (if your MySQL supports WINDOW functions) which puts the totals in each record.. SELECT id , unique_id , fname , date_format(visited, '%b %e %Y %h:%i%p') as vis_date , date_format(created_at, '%b %e %Y %h:%i%p') as cr_date , visited , created_at , CASE WHEN visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15 THEN 'EXPIRED' WHEN visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3 THEN 'PENDING' WHEN visited IS NOT NULL THEN 'OK' ELSE '???' END as status , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15) OVER () as expired , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3) OVER () as pending , SUM(visited IS NOT NULL) OVER () as ok FROM users ORDER BY visited DESC What about those who joined between 3 and 15 months ago but haven't visited yet. What status should they have?

You would get totals of 1 or 0 for every record. SELECT id , unique_id , fname , date_format(visited, '%b %e %Y %h:%i%p') as vis_date , date_format(created_at, '%b %e %Y %h:%i%p') as cr_date , visited , created_at , visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15 as expired , visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3 as pending , visited IS NOT NULL as ok FROM users ORDER BY visited DESC You could then accumulate them as you list the results

This query should do it SELECT SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) >= 15) as expired , SUM(visited IS NULL AND timestampdiff(MONTH, created_at, now()) < 3) as pending , SUM(visited IS NOT NULL) as ok FROM users;

Which? Update, or Insert new record?

Do you update the visited date each time the user visits, or is a new record created each time they visit?

What is the query tht is getting this data?

All you are doing in the outer query is getting brand and stock_id then using those to determine the records that get updated. Use a single query joining to the stock table UPDATE prices_{$company_id} p INNER JOIN price_rules pr ON p.company_id = pr.company_id INNER JOIN feeds f ON f.id = p.feed_id INNER JOIN stock_{$company_id} s ON s.stock_id = p.stock_id AND s.csv_data = pr.brand AND s.company_id = p.company_id AND s.attribute_id = 5 SET p.brand_markup_percentage = (p.stock_price / 100) * pr.brand_markup_percentage WHERE p.company_id = ? AND f.disable_price_rules = 0 AND pr.brand_price_or_percent = 1 AND p.stock_price BETWEEN pr.min_price AND pr.max_price AND p.price_profile = ? Why do write your queries so that you have scroll right into the middle of next week to see what it's doing? You wouldn't write your other code on one line without linebreaks and indentations.

That is almost exactly the same problem as before. Create a $values array (as in method 2 in previous solution) for each of the $alpha arrays and compare them.

If it is not connected with this topic, start another one.

To loop through an array, use foreach(). There are a couple of ways to skin this cat. METHOD 1 Loop through alpaha1 getting the OID and Value from each element For each element search alpha2 for the matching OID and get its value METHOD 2 Extract array of values from each array with OID as the array keys METHOD 1 $results = []; // loop through first array getting OIDs and Values foreach ($alpha1 as $arr) { $results[$arr['OID']] = [ 'val1' => $arr['Value'], 'val2' => '' ]; // search second array for same OID and get its value foreach ($alpha2 as $arr2) { if ($arr2['OID'] == $arr['OID']) { $results[$arr['OID']]['val2'] = $arr2['Value']; break; } } } // print the results echo '<pre>'; printf("%-20s%-30s%-30s<br><br>", 'OID', 'Val 1', 'Val 2'); foreach ($results as $k => $v) { printf("%-20s%-30s%-30s<br>", $k, $v['val1'], $v['val2']); } echo '</pre>'; METHOD 2 $values1 = array_column($alpha1, 'Value', 'OID'); $values2 = array_column($alpha2, 'Value', 'OID'); echo '<pre>'; printf("%-20s%-30s%-30s<br><br>", 'OID', 'Val 1', 'Val 2'); foreach ($values1 as $k => $v) { printf("%-20s%-30s%-30s<br>", $k, $v, $values2[$k]); } echo '</pre>'; Both methods output ... OID Val 1 Val 2 10.12.15.161 D8 B0 53 C4 01 E5 GA871 10.15.65.47 5C D0 6E F0 F9 2E MC990 10.36.240.52 84 B1 E4 6E 93 4F svc_rover@gstt.local 10.36.240.53 1C 6A 76 41 09 B9 svc_rover@gstt.local 10.36.240.59 84 B1 E4 70 C7 D9 svc_rover@gstt.local 10.36.240.60 6C 7E 67 41 27 C3 svc_rover@gstt.local 10.36.240.67 CE B9 5E 32 17 61 svc_rover@gstt.local

I guess I'll never know how and why they were created in such an unusable format. You first task is to correct that shortcoming and restructure the two arrays. I suggest $alpha1 = [ "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.2.10.12.15.161, Type=OctetString, Value= D8 B0 53 C4 01 E5", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.2.10.15.65.47, Type=OctetString, Value= 5C D0 6E F0 F9 2E", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.2.10.36.240.52, Type=OctetString, Value= 84 B1 E4 6E 93 4F", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.2.10.36.240.53, Type=OctetString, Value= 1C 6A 76 41 09 B9", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.2.10.36.240.59, Type=OctetString, Value= 84 B1 E4 70 C7 D9", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.2.10.36.240.60, Type=OctetString, Value= 6C 7E 67 41 27 C3", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.2.10.36.240.67, Type=OctetString, Value= CE B9 5E 32 17 61" ]; $alpha2 = [ "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.3.10.12.15.161, Type=OctetString, Value=GA871", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.3.10.15.65.47, Type=OctetString, Value=MC990", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.3.10.36.240.52, Type=OctetString, Value=svc_rover@gstt.local", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.3.10.36.240.53, Type=OctetString, Value=svc_rover@gstt.local", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.3.10.36.240.59, Type=OctetString, Value=svc_rover@gstt.local", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.3.10.36.240.60, Type=OctetString, Value=svc_rover@gstt.local", "OID=.1.3.6.1.4.1.14823.2.2.1.1.2.1.1.3.10.36.240.67, Type=OctetString, Value=svc_rover@gstt.local" ]; $alpha1 = array_map('convert', $alpha1); // apply the calback function convert() to each element $alpha2 = array_map('convert', $alpha2); function convert($str) // callback function { $a = explode(', ', $str); $res = []; foreach ($a as $str2) { $b = explode('=', $str2); if ($b[0] == 'OID') { // we only want the last 4 elements $res[$b[0]] = join('.', array_slice(explode('.', $b[1]), -4)); } else $res[$b[0]] = $b[1]; } return $res ; } You now have two arrays in this format, which are much more easily processed... $arr = array ( 0 => array ( 'OID' => '10.12.15.161', 'Type' => 'OctetString', 'Value' => ' D8 B0 53 C4 01 E5', ), 1 => array ( 'OID' => '10.15.65.47', 'Type' => 'OctetString', 'Value' => ' 5C D0 6E F0 F9 2E', ), 2 => array ( 'OID' => '10.36.240.52', 'Type' => 'OctetString', 'Value' => ' 84 B1 E4 6E 93 4F', ), 3 => array ( 'OID' => '10.36.240.53', 'Type' => 'OctetString', 'Value' => ' 1C 6A 76 41 09 B9', ), 4 => array ( 'OID' => '10.36.240.59', 'Type' => 'OctetString', 'Value' => ' 84 B1 E4 70 C7 D9', ), 5 => array ( 'OID' => '10.36.240.60', 'Type' => 'OctetString', 'Value' => ' 6C 7E 67 41 27 C3', ), 6 => array ( 'OID' => '10.36.240.67', 'Type' => 'OctetString', 'Value' => ' CE B9 5E 32 17 61', ), ); That's the hard part done. Now it's just a matter of looping through one array and looking for matches in the other.