Barand

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.

It's just a matter of organising your ajax response. Send it back as an array containing two arrays, one for table A and one for table B

The technique you want is AJAX +-----------------+ +-----------------+ | browser page | | Server | |-----------------| AJAX request |-----------------| | Send | -----------------------------> | Query DB | | | with search data | | | | | | | | | | | | | | Process | <---------------------------- | return response | | response | | | | | | | | | | | | | | | +-----------------+ +-----------------+

The best place to set them is in your php.ini file. If you have startup errors (such as syntax errors) you need display_startup_errors ON, but you can't set that from within a script. This is the relevant section of my development php.ini file.

A alternative to setting them as selected when you add them to ListB is to use a javascript function to select them all just prior to submitting the form... <?php if (isset($_GET['selected_counties'])) { foreach ($_GET['selected_counties'] as $cname) { echo "$cname<br>"; } } echo '<hr>'; ?> <!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'> function doSubmit() { $.each( $("#selected_counties").children(), function(k,v) { $(v).attr("selected","selected") // set items as selected }) return true; // submit the form } </script> </head> <body> <form onsubmit = "return doSubmit()"> <select name='selected_counties[]' id='selected_counties' multiple size='5'> <option>Adams</option> <option>Barron</option> <option>Buffalo</option> <option>Clark</option> <option>Dodge</option> </select> <button type='submit'>Submit</button> </form> </body> </html>

Good a reason as any.

WHy have two lists of counties. Can't the user just select multiple counties (ctrl-click) in the first list? For example <?php if (isset($_GET['counties'])) { foreach ($_GET['counties'] as $cname) { echo "$cname<br>"; } } echo '<hr>'; ?> <!doctype html> <html> <head> <meta charset="utf-8"> <title>Bookstore</title> </head> <body> <form> <select name='counties[]' multiple size='5'> <option>Adams</option> <option>Barron</option> <option>Buffalo</option> <option>Clark</option> <option>Dodge</option> </select> <button type='submit'>Submit</button> </form> </body> </html>

Doh! Forget that - I just answered my own question as I posted - if they aren't selected they don't get sent. When you write the options to ListB, set their selected attributes <option selected>Adams</option>

Why does that matter? As soon as the user clicks "Submit", the searchTest.php page should appear with the search results (at least I assume that happens next).

If I understand what you're saying Show available counties in ListA Drag some counties from ListA to ListB When counties are selected in ListB, add them to ListC You stated you are doing a database search. Do you then want to submit ListC to search for items in those counties?