Barand
-
Posts
24,323 -
Joined
-
Last visited
-
Days Won
794
Posts posted by Barand
-
-
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.
-
1
-
1
-
-
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
-
METHOD 1
-
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.
-
1
-
-
Are those arrays coming from database tables?
-
The mysql function NOW() gives the current datetime.
mysql> SELECT timestampdiff(DAY, '2024-01-28 09:41:36', NOW() ) as diff; +------+ | diff | +------+ | 1 | +------+
-
1
-
-
PHP
$created = new DateTime('2024-01-27 09:41:00'); $visited = new DateTime('2024-01-29 09:41:00'); echo $visited->diff($created)->format('%a days'); // 2 days
SQL
SELECT created , visited , timestampdiff(DAY, created, visited) as days FROM test_1; +---------------------+---------------------+------+ | created | visited | days | +---------------------+---------------------+------+ | 2024-01-27 09:41:00 | 2024-01-29 09:41:00 | 2 | +---------------------+---------------------+------+
-
1
-
-
Except that trying to execute
ini_set('display_startup_errors', '1');
when the code doesn't run because of startup errors, is as much use as a chocolate teapot. It has to be set in the php.ini file.
-
2
-
-
Have you used the network tab in your browser developer tools to check the ajax responses?
-
Just as it says on the tin - YOUR connection code should go there
-
Something like this?
CODE
<?php include 'db_inc.php'; // YOUR CONNECTION $pdo = pdoConnect('movies'); // CODE GOES HERE ################################################################################ ## PROCESS AJAX REQUESTS ################################################################################ if (isset($_GET['ajax'])) { $res = $pdo->prepare("SELECT m.id as movie_id , m.title , m.image , g.description as genre , CONCAT(m.running_time DIV 60, ' hrs ', m.running_time % 60, ' mins') as running_time , date_format(sg.screen_on, '%W, %D %b') as date , s.name as screen_num , TIME_FORMAT(sg.screen_at, '%H:%i') as start_time FROM screening sg JOIN screen s ON sg.screen_id = s.id JOIN movie m ON sg.movie_id = m.id JOIN genre g ON g.id = m.genre WHERE dayname(screen_on) = :day ORDER BY movie_id, screen_on, sg.screen_at "); $res->execute([ 'day' => $_GET['day'] ]); $data = []; # # Put data into an array with same structure a required output # - array of movies, each movie having arrays of screenings # foreach ($res as $r) { if (!isset($data[$r['movie_id']])) { $data[$r['movie_id']] = [ 'title' => $r['title'], 'image' => $r['image'], 'genre' => $r['genre'], 'runtime' => $r['running_time'], 'screenings' => [] ]; } $data[$r['movie_id']]['screenings'][$r['date']][] = ['start' => $r['start_time'], 'sno' => $r['screen_num'] ]; } exit(json_encode($data)); } ?> <!DOCTYPE html> <html lang="en"> <head> <meta name="generator" content="PhpED 12.0 (Build 12010, 64bit)"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>olumide</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.1/css/all.min.css"> <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> <script type='text/javascript'> function showScreenings(day) { $("#movie-listings").html("") $.get( "", {"ajax":1, "day":day}, 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>${sdata.sno}</div>` $("#movie-listings").append(scr) }) $("#movie-listings").append("</div>") }) }) }, "JSON" ) } </script> <style type='text/css'> .days { padding: 16px; text-align: center; } .screening { width : 20%; display: inline-block; margin-right: 16px; margin-bottom: 8px; padding: 4px; border: 5px solid black; font-size: 9pt; } </style> </head> <body> <nav class="days"> <button onclick="showScreenings('Monday')">Monday</button> <button onclick="showScreenings('Tuesday')">Tuesday</button> <button onclick="showScreenings('Wednesday')">Wednesday</button> <button onclick="showScreenings('Thursday')">Thursday</button> <button onclick="showScreenings('Friday')">Friday</button> <button onclick="showScreenings('Saturday')">Saturday</button> <button onclick="showScreenings('Sunday')">Sunday</button> </nav> <div id='movie-listings'class='w3-content w3-padding w3-card-4'> <!-- LISTINGS GO HERE --> </div> </body> </html>
-
2
-
1
-
-
What about all the other data in your query results - whare should that be shown (if at all)?
-
An image (or mock-up) of what you do want would be more helpful than an image of what you don't want 🙄
-
I had in mind something like this...
CREATE TABLE `win_log` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) DEFAULT NULL, `won` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `collected` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_win_log_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Bob (user_id #123) wins on Saturday. He also wins on Sunday.
Each time he wins, you record the win with this query (inserting $_SESSION['user_id'] into the table)
INSERT INTO win_log (user_id) VALUES (?);
So now you have
TABLE: win_log +----+---------+---------------------+-----------+ | id | user_id | won | collected | +----+---------+---------------------+-----------+ | 1 | 123 | 2024-01-20 18:53:21 | NULL | | 2 | 123 | 2024-01-21 10:33:45 | NULL | +----+---------+---------------------+-----------+
On Monday he goes to collect his tokens. You check he is allowed to by...
SELECT COUNT(*) as tot FROM win_log WHERE user_id = ? AND collected IS NULL;
If the returned count is > 0 he can go ahead and collect.
You record the collection with...
UPDATE win_log SET collected = NOW() WHERE user_id = ? AND collected IS NULL ORDER BY won LIMIT 1;
You now have
+----+---------+---------------------+---------------------+ | id | user_id | won | collected | +----+---------+---------------------+---------------------+ | 1 | 123 | 2024-01-20 18:53:21 | 2024-01-22 19:01:25 | | 2 | 123 | 2024-01-21 10:33:45 | NULL | +----+---------+---------------------+---------------------+
-
Perhaps maintain log tables of winners (userID and timestamo) and also log when they claimed their reward.
On the page where they collect the reward you would first check if they are allowed to be there (winner and not collected) and, if not, redirect them elsewhere.
-
The highlighted </div> tags have no corresponding <div> tags...
When the page is run, the browser adds five </div> tags at the end to close off unclosed div elements...
In addition, your table markup is inconsistent, with some rows having three columns and others only having two.
-
1
-
-
Use the javascript debugger to step through the code to see if it's doing what you expect and that the variables contain what you expect.
Almost there - but need help from a expert.
in PHP Coding Help
Posted
There are examples here