Barand

When you do an UPDATE query, the changes are applied to all records in the table unless you specify which record(s) is to be updated. I am guessing that the last pair of numbers in your two arrays are NFC 4, AFC 0 Your query needs a WHERE clause adding $query = $connection->prepare("UPDATE VNSB21_squares_matrix SET NFC = ? , AFC = ? WHERE square = ? "); $query->bind_param('iii', $n, $a, $i); for ($i=0; $i<100; $i++) { $a = $AFC[intdiv($i, 10)]; $n = $NFC[$i%10]; $query->execute(); }

Plan C - add a couple of "range" tables gpm_range ewt_range +------+--------+--------+ +-----+--------+--------+ | gpm | gpm_lo | gpm_hi | | ewt | ewt_lo | ewt_hi | +------+--------+--------+ +-----+--------+--------+ | 1.00 | 0.01 | 1.11 | | 30 | 1 | 34 | | 1.25 | 1.12 | 1.36 | | 40 | 35 | 44 | | 1.50 | 1.37 | 1.74 | | 50 | 45 | 54 | | 2.00 | 1.75 | 2.99 | | 60 | 55 | 64 | +------+--------+--------+ | 70 | 65 | 74 | | 80 | 75 | 85 | +-----+--------+--------+ code $sql = "SELECT CASE u.gpm WHEN ? THEN u.gpm ELSE concat(u.gpm, '*') END as gpm , CASE u.ewt WHEN ? THEN u.ewt ELSE concat(u.ewt, '*') END as ewt FROM uniluxmodel u JOIN gpm_range g USING (gpm) JOIN ewt_range e USING (ewt) WHERE ? BETWEEN g.gpm_lo AND g.gpm_hi AND ? BETWEEN e.ewt_lo AND e.ewt_hi "; $stmt = $db->prepare($sql); $gpm = 1.2; $ewt = 40; $stmt->execute( [ $gpm, $ewt, $gpm, $ewt ] ); // +-------+-----+ // | gpm | ewt | // +-------+-----+ // | 1.25* | 40 | * denotes not exact match // +-------+-----+ $gpm = 1.5; $ewt = 45; $stmt->execute( [ $gpm, $ewt, $gpm, $ewt ] ); // +------+-----+ // | gpm | ewt | // +------+-----+ // | 1.50 | 50* | // +------+-----+ $gpm = 2.0; $ewt = 60; $stmt->execute( [ $gpm, $ewt, $gpm, $ewt ] ); // +------+-----+ // | gpm | ewt | // +------+-----+ // | 2.00 | 60 | // +------+-----+ $gpm = 2.2; $ewt = 90; $stmt->execute( [ $gpm, $ewt, $gpm, $ewt ] ); // NO RECORDS FOUND

Here's a way... $sql = "SELECT DISTINCT gpm, ewt FROM ( SELECT id, gpm FROM uniluxmodel WHERE gpm = ? ) gpm1 LEFT JOIN ( SELECT id, ewt FROM uniluxmodel WHERE ewt = ? ) ewt1 USING (id) UNION SELECT gpm, ewt FROM ( SELECT id, ewt FROM uniluxmodel WHERE ewt = ? ) ewt2 LEFT JOIN ( SELECT id, gpm FROM uniluxmodel WHERE gpm = ? ) gpm2 USING (id) ORDER BY gpm IS NULL, ewt IS NULL LIMIT 1 "; $stmt = $db->prepare($sql); $gpm = 1.2; $ewt = 40; $stmt->execute( [ $gpm, $ewt, $ewt, $gpm ] ); // +-----+-----+ // | gpm | ewt | // +-----+-----+ // | | 40 | // +-----+-----+ $gpm = 1.5; $ewt = 45; $stmt->execute( [ $gpm, $ewt, $ewt, $gpm ] ); // +------+-----+ // | gpm | ewt | // +------+-----+ // | 1.50 | | // +------+-----+ $gpm = 2.0; $ewt = 60; $stmt->execute( [ $gpm, $ewt, $ewt, $gpm ] ); // +------+-----+ // | gpm | ewt | // +------+-----+ // | 2.00 | 60 | // +------+-----+ $gpm = 2.2; $ewt = 61; $stmt->execute( [ $gpm, $ewt, $ewt, $gpm ] ); // NO RECORDS FOUND

Or give your users option menus of values that do exist so they aren't guessing

Avoid using "global". Instead, pass the $employees array as an argument to functions. EG echo getTotalSalaries($employees); function getTotalSalries($emps) { return array_sum(array_column($emps, 'salary')); }

OK - hard-coding your $afc and $nfc arrays instead of randomly generating them $afc = [6,0,9,4,2,3,8,5,7,1]; $nfc = [2,9,1,4,8,7,0,3,5,6]; //$nfc = range(0,9); //$afc = range(0,9); //shuffle($nfc); //shuffle($afc); echo '<pre>Numbers table<br>'; echo "| NFC | AFC |<br>"; for ($i=0; $i<10; $i++) { printf( '| %d | %d |<br>', $nfc[$i], $afc[$i]); } echo '<hr>Matrix table<br>'; for ($i=0; $i<100; $i++) { $a = $afc[intdiv($i, 10)]; $n = $nfc[$i%10]; printf('| %02d | %d, %d<br>', $i, $n, $a); } gives this, Numbers table | NFC | AFC | | 2 | 6 | | 9 | 0 | | 1 | 9 | | 4 | 4 | | 8 | 2 | | 7 | 3 | | 0 | 8 | | 3 | 5 | | 5 | 7 | | 6 | 1 | Matrix table | 00 | 2, 6 | 01 | 9, 6 | 02 | 1, 6 | 03 | 4, 6 | 04 | 8, 6 | 05 | 7, 6 | 06 | 0, 6 | 07 | 3, 6 | 08 | 5, 6 | 09 | 6, 6 | 10 | 2, 0 | 11 | 9, 0 | 12 | 1, 0 | 13 | 4, 0 | 14 | 8, 0 | 15 | 7, 0 | 16 | 0, 0 | 17 | 3, 0 | 18 | 5, 0 | 19 | 6, 0 | 20 | 2, 9 | 21 | 9, 9 | 22 | 1, 9 | 23 | 4, 9 | 24 | 8, 9 | 25 | 7, 9 | 26 | 0, 9 | 27 | 3, 9 | 28 | 5, 9 | 29 | 6, 9 | 30 | 2, 4 | 31 | 9, 4 | 32 | 1, 4 | 33 | 4, 4 | 34 | 8, 4 | 35 | 7, 4 | 36 | 0, 4 | 37 | 3, 4 | 38 | 5, 4 | 39 | 6, 4 | 40 | 2, 2 | 41 | 9, 2 | 42 | 1, 2 | 43 | 4, 2 | 44 | 8, 2 | 45 | 7, 2 | 46 | 0, 2 | 47 | 3, 2 | 48 | 5, 2 | 49 | 6, 2 | 50 | 2, 3 | 51 | 9, 3 | 52 | 1, 3 | 53 | 4, 3 | 54 | 8, 3 | 55 | 7, 3 | 56 | 0, 3 | 57 | 3, 3 | 58 | 5, 3 | 59 | 6, 3 | 60 | 2, 8 | 61 | 9, 8 | 62 | 1, 8 | 63 | 4, 8 | 64 | 8, 8 | 65 | 7, 8 | 66 | 0, 8 | 67 | 3, 8 | 68 | 5, 8 | 69 | 6, 8 | 70 | 2, 5 | 71 | 9, 5 | 72 | 1, 5 | 73 | 4, 5 | 74 | 8, 5 | 75 | 7, 5 | 76 | 0, 5 | 77 | 3, 5 | 78 | 5, 5 | 79 | 6, 5 | 80 | 2, 7 | 81 | 9, 7 | 82 | 1, 7 | 83 | 4, 7 | 84 | 8, 7 | 85 | 7, 7 | 86 | 0, 7 | 87 | 3, 7 | 88 | 5, 7 | 89 | 6, 7 | 90 | 2, 1 | 91 | 9, 1 | 92 | 1, 1 | 93 | 4, 1 | 94 | 8, 1 | 95 | 7, 1 | 96 | 0, 1 | 97 | 3, 1 | 98 | 5, 1 | 99 | 6, 1 so which bit didn't I get? Did you even try running the code and looking at the output

I wish. I have been programming since 1968 and I'm still learning. If you want to produce web pages, learn the basics of HTML/CSS first. PHP is mainly used to create web pages dynamically and you'll have problems if you don't know how the correct output should look. When you start with PHP, familiarize yourself with php.net - the online manual there is an essential resource. Beware of tutorials and videos out there on the net - a lot of them are poor quality and outdated. One of the better ones is phpdelusions.net. And, of course, if get stuck, there's always PHPFreaks.

Something like this? $nfc = range(0,9); $afc = range(0,9); shuffle($nfc); shuffle($afc); echo '<pre>Numbers table<br>'; echo "| NFC | AFC |<br>"; for ($i=0; $i<10; $i++) { printf( '| %d | %d |<br>', $nfc[$i], $afc[$i]); } echo '<hr>Matrix table<br>'; for ($i=0; $i<100; $i++) { $a = $afc[intdiv($i, 10)]; $n = $nfc[$i%10]; printf('| %02d | %d, %d<br>', $i, $n, $a); }

You are outputting 7 data cells (indexes 0 - 6) $cells = [$brandname, $storename, $r['checkin_time'], $r['checkout_time'], $r['robot_num'], $r['date'], $r['total']]; foreach ($cells as $k => $v) { $this->Cell($this->widths[$k], 5, $v, 0, 0, 0, 0, $this->aligns[$k]); } In the class constructor, only 6 widths and alignments (0 - 5) are specified, so index 6 is missing for those arrays $this->widths = [25,65,30,20,20,20]; $this->aligns = ['L','L','L','R','R','R'];

I agree. It's great to have a discussion and knowledgable interaction about a problem rather than just delivering straight answer to a topic.

+1 It was coding like that that almost destroyed civilization as we know it back in the Y2K days.

OK - I'm senile. 7 rows in the explain results!!!

@gizmola What results do you get if you put the subquery results into a temp table and use that CREATE TEMPORARY TABLE temp_actor (actor_id smallint not null primary key); -- -- INSERT INTO temp_actor SELECT actor_id FROM actor WHERE last_name LIKE 'depp%' UNION SELECT actor_id FROM film_actor WHERE film_id = (SELECT film_id FROM film WHERE title = 'ACE GOLDFINGER'); -- -- SELECT DISTINCT f.film_id , f.title FROM film f JOIN film_actor fa USING (film_id) JOIN temp_actor USING (actor_id) ORDER BY f.title; I get the same as before (124 recs)

Weird! I see your results have 7 rows. When I run it I get 124 rows mysql> SELECT DISTINCT f.film_id, f.title -> FROM film f -> JOIN film_actor fa ON f.film_id = fa.film_id -> JOIN -> (SELECT actor_id -> FROM actor -> WHERE last_name LIKE 'depp%' -> UNION -> SELECT actor_id -> FROM film_actor -> WHERE film_id = -> (SELECT film_id FROM film WHERE title = 'ACE GOLDFINGER') -> ) actid USING (actor_id) -> ORDER BY f.title; +---------+-------------------------+ | film_id | title | +---------+-------------------------+ | 2 | ACE GOLDFINGER | | 3 | ADAPTATION HOLES | | 11 | ALAMO VIDEOTAPE | | 14 | ALICE FANTASIA | | 17 | ALONE TRIP | | 43 | ATLANTIS CAUSE | | 72 | BILL OTHERS | | 85 | BONNIE HOLOCAUST | | 92 | BOWFINGER GABLES | | 100 | BROOKLYN DESERT | . . . | 883 | TEQUILA PAST | | 895 | TOMORROW HUSTLER | | 901 | TRACY CIDER | | 909 | TREASURE COMMAND | | 919 | TYCOON GATHERING | | 925 | UNITED PILOT | | 944 | VIRGIN DAISY | | 950 | VOLUME HOUSE | | 954 | WAKE JAWS | | 957 | WAR NOTTING | | 960 | WARS PLUTO | | 967 | WEEKEND PERSONAL | | 979 | WITCHES PANIC | | 991 | WORST BANGER | | 992 | WRATH MILE | +---------+-------------------------+ 124 rows in set (0.02 sec) There are 43 films starring a "Depp" before starting on the other actors from Ace Goldfinger mysql> SELECT COUNT(DISTINCT film_id) FROM film_actor WHERE actor_id IN (100, 160); +-------------------------+ | COUNT(DISTINCT film_id) | +-------------------------+ | 43 | +-------------------------+ 1 row in set (0.01 sec) I've noticed in past projects that MariaDB can be a bit flaky with table subqueries. Don't know if that's the case here

I think you'll find that using that number as a unix timestamp is out by a little more than 10 years. It appears to be only 10 years out becouse yo are hard-coding the first 2 digits of the year (why?) $date = date("F d, Y", 20211021141214); echo $date; // October 06, 642431 If you use DateTime class $dt = new DateTime(20211021141214); echo $dt->format('F d, Y H:i:s') // October 21, 2021 14:12:14 Or, if you continue to use date(), convert the string to a valid timestamp $date = date("F d, Y", strtotime('20211021141214')); echo $date . '<br>'; // October 21, 2021

What is the actual SQL query that is generated by those incantations? PS What RDBMS are you using?

Joins to a table subquery are faster than dependent subqueries. The "IN subquery" version has one of those in the explain

phpdelusions.net

Yep, definitely faster. Timing results... IN subquery 0.0666 seconds JOIN subquery 0.0012 seconds

I prefer to JOIN to the subquery - it should be faster SELECT DISTINCT f.film_id, f.title FROM film f JOIN film_actor fa ON f.film_id = fa.film_id JOIN (SELECT actor_id FROM actor WHERE last_name LIKE '%depp%' UNION SELECT actor_id FROM film_actor WHERE film_id = (SELECT film_id FROM film WHERE title = 'ACE GOLDFINGER') ) actid USING (actor_id) ORDER BY f.title;

You might have more success with function toggleResponseArea() { if (document.getElementById("commentResponse").style.display == 'block') { document.getElementById("commentResponse").style.display = 'none'; } else { document.getElementById("commentResponse").style.display = 'block'; } } Or, easier still $("#reply").click( function() { $("#commentResponse").toggle() })

try SELECT f.title as film_or_actor , concat(a.first_name, ' ', a.last_name) as `Search for` , 'Film' as `type` FROM film f JOIN film_actor fa USING (film_id) JOIN actor a USING (actor_id) WHERE a.last_name LIKE '%depp%' UNION SELECT concat(a.first_name, ' ', a.last_name) , f.title , 'Actor' FROM film f JOIN film_actor fa USING (film_id) JOIN actor a USING (actor_id) WHERE f.film_id = 2 ORDER BY `type`, film_or_actor;

What is that supposed to achieve?

Here's an example (using the database from my SQL tutorials) Output Code <?php define("HOST",'localhost'); define("USERNAME",'????'); define("PASSWORD",'????'); define("DATABASE", 'jointute'); // uses DB from my SQL tutorials $db = pdoConnect(); ################################################################################# ## handle AJAX request ## if (isset($_GET['ajax'])) { if ($_GET['ajax']=='classdata') { $response = [ 'tablea' => [], 'tableb' => [] ]; // keys are the ids of the destination tbody elements $res = $db->prepare("SELECT s.subject , concat(t.fname, ' ', t.lname) as name FROM teacher_subject ts JOIN teacher t USING (teacherid) JOIN subject s USING (subjectid) JOIN ( SELECT DISTINCT subjectid FROM choice c JOIN pupil p ON c.pupilid = p.pupilid AND p.classid = ? ) subj USING (subjectid) WHERE ts.classid = ? ORDER BY ts.subjectid "); $res->execute( [ $_GET['cid'], $_GET['cid'] ] ); $response['tablea'] = $res->fetchAll(); $res = $db->prepare("SELECT concat(p.fname, ' ', p.lname) as name , GROUP_CONCAT(s.subject SEPARATOR ', ') as subjects FROM choice c JOIN pupil p USING (pupilid) JOIN subject s USING (subjectid) WHERE p.classid = ? GROUP BY p.pupilid "); $res->execute( [ $_GET['cid'] ] ); $response['tableb'] = $res->fetchAll(); exit(json_encode($response)); } } ################################################################################# $buttons = ''; for ($i='A'; $i<'G'; $i++) { $buttons .= "<button class='classbtn' value='$i'>$i</button>&emsp;"; } 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); return $db; } ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>County Selection</title> <script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script> <script type='text/javascript'> $().ready(function() { $(".classbtn").click( function() { var cid = $(this).val() $(".classbtn").removeClass("selectbtn") $(this).addClass("selectbtn") $.get( "", // request sent to self {"ajax":"classdata", "cid":cid}, function(resp) { $.each(resp, function(k,v) { var tbody = $("#"+k) $(tbody).html("") $.each(v, function(k1, r){ var row = $("<tr>") $.each(r, function(k2, c) { $(row).append($("<td>", {text:c})) }) $(tbody).append(row) }) }) }, "JSON" ) }) }) </script> <style type='text/css'> body { font-family: arial, sans-serif; } table { border-collapse: collapse; width: 600px; margin: 16px; } td, th { padding: 8px; } th { background-color: #EEE; text-align: left; } .buttons { padding: 16px; border-bottom: 1px solid gray; } .classbtn { background-color: #EEE; cursor: pointer; } .selectbtn { background-color: #008000; color: white; } </style> </head> <body> <div class='buttons'> Select a class &emsp; <?=$buttons?> </div> <h3>Pupils</h3> <table border='1'> <tr><th>Name</th><th>Subjects</th></tr> <tbody id='tableb'></tbody> </table> <h3>Teachers</h3> <table border='1'> <tr><th>Subject</th><th>Teacher</th></tr> <tbody id='tablea'></tbody> </table> </body> </html>

The main difference I found when switching from v7.4 to v8.0 was that v8.0 is stricter on variable types. You may have to make some minor changes.