Barand

Your attempt doesn't use the same tables SELECT dr.id as 'id', name FROM drive_rating dr LEFT JOIN drive_routes dro ON dro.rating = dr.id WHERE dr.id=? compared with SELECT *,(SELECT dc.id FROM drive_cargotype dc, drive_routes dr WHERE dc.id = dr.cargotype AND dr.id=? ORDER BY dr.id DESC LIMIT 1) as cargo FROM drive_cargotype";

The complex part was combining the keywords. There is no combining required in the latest requirement of yours. Anyway, here's the code with keywords sorted by count, as the info required was in the arrays. <?php $narray[]="Trump denounces violence after supporters beat Mexican man"; $narray[]="Doyle: What my dad could teach Donald Trump"; $narray[]="Bush slams Trump, defends using anchor babies"; $narray[]="Coming up Trumps: could a British TV star do a Donald and enter politics?"; $narray[]="Watch Rachel Maddow Explain Donald Trump’s ‘Genius’ Campaign on Tonight Show"; $narray[]="Trump touts making Time cover while taking heat over attack"; $narray[]="First Draft: Today in Politics: Rivals Can No Longer Ignore Donald Trump’s Long Shadow"; $narray[]="Donald Trump insists he’s conservative"; $narray[]="GOP candidates hold dueling town halls"; $narray[]="New York City has no way to fire Donald Trump"; $narray[]="Donald Trump pushes birthright citizenship to forefront of political debate"; $narray[]="Jeb Bush takes fight to Donald Trump in N.H."; $narray[]="Rand Paul explains why he wants to stop ‘birthright citizenship’"; $narray[]="Trump attacks Facebook over foreigners"; $narray[]="Donald Trump tops GOP field in Florida, Pennsylvania, second in Ohio"; $narray[]="Donald Trump draws New Hampshire town hall crowd wild; jabs Jeb Bush"; $narray[]="While in Vegas, O’Malley makes an appearance in front of Trump’s hotel"; $narray[]="Trump’s immigration plan has GOP rivals on edge"; $narray[]="Donald Trump calls out Mark Zuckerberg on immigration"; $narray[]="Deny citizenship to babies illegal immigrants in US: Donald Trump"; $narray[]="Donald Trump takes a break from the campaign trail to join a long list of celebrities to perform jury duty"; $narray[]="Trump: Deny citizenship to babies of people illegally in US"; $narray[]="Trump Says He Would Deport Illegal Immigrants"; $narray[]="From campaign to court: Trump reports for jury duty in NYC"; $narray[]="Donald Trump says he will ‘deport millions of illegal immigrants’"; $narray[]="Trump outlines immigration specifics"; $narray[]="Donald Trump to Iowa boy: ‘I am Batman’"; $narray[]="Trump blunt but vague: No birthright citizenship, millions of illegal immigrants ‘have to go’"; $narray[]="Trump: end ‘birthright citizenship’"; $narray[]="Trump: Deport children of immigrants living illegally in US"; $narray[]="DNC blasts Donald Trump , Jeb Bush for comments about women"; $narray[]="Trump says would raise visa fees to pay for Mexican border wall"; $narray[]="What does Donald Trump think of immigrants, Saudi Arabia and the Iran nuclear deal?"; $narray[]="Donald Trump Releases Plan to Combat Illegal Immigration"; $narray[]="Donald Trump releases his immigration policy on his GOP presidential campaign website"; $narray[]="Donald Trump warns that Iran deal will lead to Nuclear Holocaust"; $narray[]="Trump details domestic, foreign policies, answers critics, matches fellow challengers"; $narray[]="Donald Trump’s legacy of luxury"; $narray[]="Clinton defends, Trump attacks Saturday at the high-profile Iowa State Fair"; $narray[]="Donald Trump says he would deport all illegal immigrants as president"; $narray[]="Donald Trump breaks the rules at the Iowa State Fair"; $narray[]="Thanks, Donald, but I don’t want to be ‘cherished’ | Barbara Ellen"; $narray[]="Front-runners skirt the soapbox"; $narray[]="Hillary Clinton, Donald Trump and the Trumpcopter descend on the Iowa State Fair"; $narray[]="Op-Ed Columnist: Introducing Donald Trump, Diplomat"; $narray[]="Trump forced to break from campaign trail for jury duty, skipped five summonses since 2006"; $narray[]="Donald Trump forced to take break from campaign trail for jury service"; $narray[]="Tables turned on Trump’s chief tormentor"; $narray[]="Donald Trump will serve jury duty in NYC next week"; $filtered = filter_my_array($narray); // keywords only array $keywords = []; $kwindex = index_keywords($filtered, $keywords); // index of keywords // // find items with no keywords // $otheritems = []; foreach ($filtered as $k=>$v) { if (count($v)==0) $otheritems[] = $k; } // // create output of the indexed lists // // rearrange key words by desc no of occurences / alpha sequence $countedKeywords = []; foreach ($keywords as $kw => $n) { $countedKeywords[$n][] = $kw; } $output = ''; krsort($countedKeywords); foreach ($countedKeywords as $n => $kws) { sort($kws); foreach ($kws as $kw) { $output .= "<h4>$kw<span class='count'>({$n}x)</span></h4><ul>"; foreach($kwindex[$kw] as $i) { $output .= "<li>" . str_ireplace($kw, "<span class='hi'>$kw</span>", $narray[$i]) . "</li>\n"; } $output .= "</ul>\n"; } } if (count($otheritems) > 0) { $output .= "<h4>Non-keyword items</h4><ul>"; foreach ($otheritems as $i) { $output .= "<li>{$narray[$i]}</li>\n"; } $output .= "</ul>\n"; } /******************************************************************************* * helper functions ********************************************************************************/ function filter_my_array($array) { // reduces the lines of text to arrays of the keywords in the line $results = []; foreach ($array as $k => $str) { $str = no_punc($str); $a = array_filter(explode(' ', $str), 'remove_noise'); $results[$k] = $a; } return $results; } function remove_noise($x) { $stopWords = array('about','an','and','are','as','at','be','by','com','de','en','for','from', 'how','in','is','it','la','of','on','or','that','the','this','to','was','what','when','where', 'who','will','with','und','the','www','donald','trump'); return strlen($x) > 3 && !in_array(strtolower($x), $stopWords); } function index_keywords($array, &$kwords) { // gets the line numbers containing each keyword $results = []; foreach ($array as $k => $kwarr) { foreach ($kwarr as $kw) { $results[$kw][] = $k; if (isset($kwords[$kw])) { ++$kwords[$kw]; // count keyword usage } else { $kwords[$kw]=1; } } } return $results; } function no_punc($str) { $allow = array_merge([32,39], range(ord('a'), ord('z')), range(ord('0'), ord('9'))); $k = strlen($str); $res = ''; $str = strtolower($str); for ($i=0; $i<$k; $i++) { if (in_array(ord($str[$i]), $allow) ) { $res .= $str[$i]; } else $res .= ' '; } return $res; } ?> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Keyword Index</title> <style type='text/css'> .hi { font-weight: 700; color: red; } .count { font-weight: 100; color: #f44; } </style> </head> <body> <?=$output?> </body> </html> Bye.

That is a completely different output from the problem you initially posed. Are you deliberately wasting my time?

two medium-sized tables mysql> SELECT COUNT(*) FROM tablea; +----------+ | COUNT(*) | +----------+ | 11956 | +----------+ mysql> SELECT COUNT(*) FROM tableb; +----------+ | COUNT(*) | +----------+ | 654071 | +----------+ Look for missing dates mysql> SELECT COUNT(DISTINCT thedate) as dates -> FROM tablea A -> LEFT JOIN tableb B -> ON A.thedate = B.DATA_COMPETENZA -> WHERE b.DATA_COMPETENZA IS NULL; +-------+ | dates | +-------+ | 10525 | +-------+ 1 row in set (0.05 sec) mysql> SELECT COUNT(DISTINCT thedate) as dates -> FROM tablea A -> WHERE NOT EXISTS -> ( -> SELECT * FROM tableb -> WHERE DATA_COMPETENZA = A.thedate -> ); +-------+ | dates | +-------+ | 10525 | +-------+ 1 row in set (0.08 sec)

You need to set the random value inside the loop, not just once. while ($Health > 0) { $LostHealth = rand(1,10); $Health = $Health - $LostHealth; echo "<p>A Zombie just hit you doing <strong>$LostHealth</strong> damage!</p>"; echo "<p>You now have $Health HP</p>"; }

$prevkey = ''; foreach ($flavors as $k1 => $arr) { foreach ($arr as $k2 => $v ) { if ($v=='') { if (isset($flavors[$prevkey][$k2])) { $flavors[$k1][$k2] = $flavors[$prevkey][$k2]; } } } $prevkey = $k1; } echo '<pre>',print_r($flavors, true),'</pre>'; Sorry, I had $prevkey = $k1; in the wrong place.

Why would a friend do that to you? Have you upset him recently? Stick to your familiar joins and don't introduce subqueries with inefficient join syntax (... FROM A, B WHERE ...)

I think you need to recheck your data and results DATA mysql> SELECT * FROM test.user_feedback WHERE user_id=1; +------------------+---------+---------+----------+---------+----------+ | user_feedback_id | user_id | item_id | positive | neutral | negative | +------------------+---------+---------+----------+---------+----------+ | 1 | 1 | 1 | 1 | 0 | 0 | | 2 | 1 | 2 | 0 | 0 | 1 | | 3 | 1 | 3 | 1 | 0 | 0 | +------------------+---------+---------+----------+---------+----------+ My Query mysql> SELECT user_id -> , SUM(positive) as totpos -> , SUM(neutral) as totneu -> , SUM(negative) as totneg -> , SUM(positive + neutral - negative) as result -> , COUNT(*) as tot -> , SUM(positive + neutral - negative)/COUNT(*)*100 as pcent -> FROM user_feedback -> WHERE user_id=1 -> GROUP BY user_id; +---------+--------+--------+--------+--------+-----+---------+ | user_id | totpos | totneu | totneg | result | tot | pcent | +---------+--------+--------+--------+--------+-----+---------+ | 1 | 2 | 0 | 1 | 1 | 3 | 33.3333 | +---------+--------+--------+--------+--------+-----+---------+ Your Query on the same data mysql> SELECT -> CONCAT(FORMAT(((SUM(positive + neutral) - negative) / count(*) * 100), 2), '%') -> AS `seller_feedback_percentage` -> FROM user_feedback -> WHERE user_id = '1'; +----------------------------+ | seller_feedback_percentage | +----------------------------+ | 66.67% | +----------------------------+

You need to exclude yourself (user #2) from the users SELECT * FROM user WHERE user.id <> 2 AND NOT EXISTS ( SELECT * FROM friend WHERE (user_id = user.id AND friend_id = 2) OR (user_id = 2 AND friend_id = user.id) ); If you do an "EXPLAIN" of a query and see a DEPENDENT SUBQUERY use a join instead mysql> EXPLAIN SELECT * FROM user -> WHERE NOT EXISTS ( -> SELECT * -> FROM friend -> WHERE (user_id = user.id AND friend_id = 2) -> OR (user_id = 2 AND friend_id = user.id) -> ) -> AND user.id <> 2; +----+--------------------+--------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+--------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | PRIMARY | user | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where | | 2 | DEPENDENT SUBQUERY | friend | index | idx2 | idx2 | 10 | NULL | 5 | Using where; Using index | +----+--------------------+--------+-------+---------------+---------+---------+------+------+--------------------------+

(Final) Plan D <?php include('db_inc.php'); error_reporting(-1); $mysqli = new mysqli(HOST,USERNAME,PASSWORD,'test'); ?> <?php $narray[]="Trump denounces violence after supporters beat Mexican man"; $narray[]="Doyle: What my dad could teach Donald Trump"; $narray[]="Bush slams Trump, defends using anchor babies"; $narray[]="Coming up Trumps: could a British TV star do a Donald and enter politics?"; $narray[]="Watch Rachel Maddow Explain Donald Trump’s ‘Genius’ Campaign on Tonight Show"; $narray[]="Trump touts making Time cover while taking heat over attack"; $narray[]="First Draft: Today in Politics: Rivals Can No Longer Ignore Donald Trump’s Long Shadow"; $narray[]="Donald Trump insists he’s conservative"; $narray[]="GOP candidates hold dueling town halls"; $narray[]="New York City has no way to fire Donald Trump"; $narray[]="Donald Trump pushes birthright citizenship to forefront of political debate"; $narray[]="Jeb Bush takes fight to Donald Trump in N.H."; $narray[]="Rand Paul explains why he wants to stop ‘birthright citizenship’"; $narray[]="Trump attacks Facebook over foreigners"; $narray[]="Donald Trump tops GOP field in Florida, Pennsylvania, second in Ohio"; $narray[]="Donald Trump draws New Hampshire town hall crowd wild; jabs Jeb Bush"; $narray[]="While in Vegas, O’Malley makes an appearance in front of Trump’s hotel"; $narray[]="Trump’s immigration plan has GOP rivals on edge"; $narray[]="Donald Trump calls out Mark Zuckerberg on immigration"; $narray[]="Deny citizenship to babies illegal immigrants in US: Donald Trump"; $narray[]="Donald Trump takes a break from the campaign trail to join a long list of celebrities to perform jury duty"; $narray[]="Trump: Deny citizenship to babies of people illegally in US"; $narray[]="Trump Says He Would Deport Illegal Immigrants"; $narray[]="From campaign to court: Trump reports for jury duty in NYC"; $narray[]="Donald Trump says he will ‘deport millions of illegal immigrants’"; $narray[]="Trump outlines immigration specifics"; $narray[]="Donald Trump to Iowa boy: ‘I am Batman’"; $narray[]="Trump blunt but vague: No birthright citizenship, millions of illegal immigrants ‘have to go’"; $narray[]="Trump: end ‘birthright citizenship’"; $narray[]="Trump: Deport children of immigrants living illegally in US"; $narray[]="DNC blasts Donald Trump , Jeb Bush for comments about women"; $narray[]="Trump says would raise visa fees to pay for Mexican border wall"; $narray[]="What does Donald Trump think of immigrants, Saudi Arabia and the Iran nuclear deal?"; $narray[]="Donald Trump Releases Plan to Combat Illegal Immigration"; $narray[]="Donald Trump releases his immigration policy on his GOP presidential campaign website"; $narray[]="Donald Trump warns that Iran deal will lead to Nuclear Holocaust"; $narray[]="Trump details domestic, foreign policies, answers critics, matches fellow challengers"; $narray[]="Donald Trump’s legacy of luxury"; $narray[]="Clinton defends, Trump attacks Saturday at the high-profile Iowa State Fair"; $narray[]="Donald Trump says he would deport all illegal immigrants as president"; $narray[]="Donald Trump breaks the rules at the Iowa State Fair"; $narray[]="Thanks, Donald, but I don’t want to be ‘cherished’ | Barbara Ellen"; $narray[]="Front-runners skirt the soapbox"; $narray[]="Hillary Clinton, Donald Trump and the Trumpcopter descend on the Iowa State Fair"; $narray[]="Op-Ed Columnist: Introducing Donald Trump, Diplomat"; $narray[]="Trump forced to break from campaign trail for jury duty, skipped five summonses since 2006"; $narray[]="Donald Trump forced to take break from campaign trail for jury service"; $narray[]="Tables turned on Trump’s chief tormentor"; $narray[]="Donald Trump will serve jury duty in NYC next week"; $filtered = filter_my_array($narray); // keywords only array $keywords = []; $kwindex = index_keywords($filtered, $keywords); // index of keywords uksort($keywords, function($a,$b){return strlen($b) - strlen($a);}); // // find items with no keywords // $otheritems = []; foreach ($filtered as $k=>$v) { if (count($v)==0) $otheritems[] = $k; } // // combine indexes // uasort($filtered, function($a,$b) {return count($b) - count($a);}); $k = count($filtered); for ($x=0; $x<2; $x++) { for ($i=0; $i<$k-1; $i++) { for ($j=$i+1; $j<$k; $j++) { $a = $filtered[$i]; $b = $filtered[$j]; if (array_intersect($a, $b)) { $filtered[$i] = array_unique(array_merge($a,$b)); $filtered[$j]=[]; } } } } foreach ($filtered as $k => $kwarr) { if (count($kwarr) == 0) { continue; } elseif (count($kwarr) > 1) { sort($kwarr); $kwarrcounted = append_counts($kwarr, $keywords); $newkw = join(' - ', $kwarrcounted); $occurs = []; foreach ($kwarr as $kw) { if (isset($kwindex[$kw])) { $occurs = array_merge($occurs, $kwindex[$kw]); // combine individual lists unset($kwindex[$kw]); // then remove them } } sort($occurs); $kwindex[$newkw] = array_unique($occurs); // add the combined index } } // // create highlighting replacement textss // $replace = $srch = []; foreach ($keywords as $kw=>$count) { $srch[] = $kw; $replace[] = "<span class='hi'>$kw</span>"; } // // create output of the indexed list // ksort($kwindex); $output = ''; foreach ($kwindex as $kw => $items) { if (count($items)==0) continue; $output .= "<h4>$kw</h4><ul>"; foreach ($items as $i) { $output .= "<li>" . str_ireplace($srch, $replace, $narray[$i]) . "</li>\n"; } $output .= "</ul>\n"; } if (count($otheritems) > 0) { $output .= "<h4>Non-keyword items</h4><ul>"; foreach ($otheritems as $i) { $output .= "<li>{$narray[$i]}</li>\n"; } $output .= "</ul>\n"; } /******************************************************************************* * helper functions ********************************************************************************/ function filter_my_array($array) { // reduces the lines of text to arrays of the keywords in the line $results = []; foreach ($array as $k => $str) { $str = no_punc($str); $a = array_filter(explode(' ', $str), 'remove_noise'); $results[$k] = $a; } return $results; } function remove_noise($x) { $stopWords = array('about','an','and','are','as','at','be','by','com','de','en','for','from', 'how','in','is','it','la','of','on','or','that','the','this','to','was','what','when','where', 'who','will','with','und','the','www','donald','trump'); return strlen($x) > 3 && !in_array(strtolower($x), $stopWords); } function index_keywords($array, &$kwords) { // gets the line numbers containing each keyword $results = []; foreach ($array as $k => $kwarr) { foreach ($kwarr as $kw) { $results[$kw][] = $k; if (isset($kwords[$kw])) { ++$kwords[$kw]; // count keyword usage } else { $kwords[$kw]=1; } } } return $results; } function no_punc($str) { $allow = array_merge([32,39], range(ord('a'), ord('z')), range(ord('0'), ord('9'))); $k = strlen($str); $res = ''; $str = strtolower($str); for ($i=0; $i<$k; $i++) { if (in_array(ord($str[$i]), $allow) ) { $res .= $str[$i]; } else $res .= ' '; } return $res; } function append_counts($karr, $keywords) { $res = []; foreach ($karr as $k=>$word) { $n = $keywords[$word]; $res[$k] = "$word<span class='count'>({$n}x)</span>"; } return $res; } ?> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Keyword Index</title> <style type='text/css'> .hi { font-weight: 700; color: red; } .count { font-weight: 100; color: #f88; } </style> </head> <body> <?=$output?> </body> </html>

Still not excessively large. I've worked on tables with 30 million+ records and still got query results back in under a second. Now what about those table structures and queries if you want help? We won't keep asking.

I increased the "noise" threshold to ignore words of 4 or less characters If you change it to 3 then it will pick up "Iowa"

Yes, but beware of dependent subqueries where you perform a separate query for every record to see if a match exists. This kills query performance. Joins don't have that problem

Also post the query/ies that are giving the problem. Rewriting the SQL can often have significant performance increase.

Oops! typo. Remove the "pm" after "ON" UPDATE pm INNER JOIN Users2 u ON pm u.mxitid = pm.ip SET pm.username = u.username

$a = [1,2,3] was introduced in 5.4

What version of PHP are you using? If it's an old version try return array( .... ) instead of return [ .... ]

UPDATE pm INNER JOIN Users2 u ON pm u.mxitid = pm.ip SET pm.username = u.username

Plan C <?php $narray[]="Trump denounces violence after supporters beat Mexican man"; $narray[]="Doyle: What my dad could teach Donald Trump"; $narray[]="Bush slams Trump, defends using anchor babies"; $narray[]="Coming up Trumps: could a British TV star do a Donald and enter politics?"; $narray[]="Watch Rachel Maddow Explain Donald Trump’s ‘Genius’ Campaign on Tonight Show"; $narray[]="Trump touts making Time cover while taking heat over attack"; $narray[]="First Draft: Today in Politics: Rivals Can No Longer Ignore Donald Trump’s Long Shadow"; $narray[]="Donald Trump insists he’s conservative"; $narray[]="GOP candidates hold dueling town halls"; $narray[]="New York City has no way to fire Donald Trump"; $narray[]="Donald Trump pushes birthright citizenship to forefront of political debate"; $narray[]="Jeb Bush takes fight to Donald Trump in N.H."; $narray[]="Rand Paul explains why he wants to stop ‘birthright citizenship’"; $narray[]="Trump attacks Facebook over foreigners"; $narray[]="Donald Trump tops GOP field in Florida, Pennsylvania, second in Ohio"; $narray[]="Donald Trump draws New Hampshire town hall crowd wild; jabs Jeb Bush"; $narray[]="While in Vegas, O’Malley makes an appearance in front of Trump’s hotel"; $narray[]="Trump’s immigration plan has GOP rivals on edge"; $narray[]="Donald Trump calls out Mark Zuckerberg on immigration"; $narray[]="Deny citizenship to babies illegal immigrants in US: Donald Trump"; $narray[]="Donald Trump takes a break from the campaign trail to join a long list of celebrities to perform jury duty"; $narray[]="Trump: Deny citizenship to babies of people illegally in US"; $narray[]="Trump Says He Would Deport Illegal Immigrants"; $narray[]="From campaign to court: Trump reports for jury duty in NYC"; $narray[]="Donald Trump says he will ‘deport millions of illegal immigrants’"; $narray[]="Trump outlines immigration specifics"; $narray[]="Donald Trump to Iowa boy: ‘I am Batman’"; $narray[]="Trump blunt but vague: No birthright citizenship, millions of illegal immigrants ‘have to go’"; $narray[]="Trump: end ‘birthright citizenship’"; $narray[]="Trump: Deport children of immigrants living illegally in US"; $narray[]="DNC blasts Donald Trump , Jeb Bush for comments about women"; $narray[]="Trump says would raise visa fees to pay for Mexican border wall"; $narray[]="What does Donald Trump think of immigrants, Saudi Arabia and the Iran nuclear deal?"; $narray[]="Donald Trump Releases Plan to Combat Illegal Immigration"; $narray[]="Donald Trump releases his immigration policy on his GOP presidential campaign website"; $narray[]="Donald Trump warns that Iran deal will lead to Nuclear Holocaust"; $narray[]="Trump details domestic, foreign policies, answers critics, matches fellow challengers"; $narray[]="Donald Trump’s legacy of luxury"; $narray[]="Clinton defends, Trump attacks Saturday at the high-profile Iowa State Fair"; $narray[]="Donald Trump says he would deport all illegal immigrants as president"; $narray[]="Donald Trump breaks the rules at the Iowa State Fair"; $narray[]="Thanks, Donald, but I don’t want to be ‘cherished’ | Barbara Ellen"; $narray[]="Front-runners skirt the soapbox"; $narray[]="Hillary Clinton, Donald Trump and the Trumpcopter descend on the Iowa State Fair"; $narray[]="Op-Ed Columnist: Introducing Donald Trump, Diplomat"; $narray[]="Trump forced to break from campaign trail for jury duty, skipped five summonses since 2006"; $narray[]="Donald Trump forced to take break from campaign trail for jury service"; $narray[]="Tables turned on Trump’s chief tormentor"; $narray[]="Donald Trump will serve jury duty in NYC next week"; $filtered = filter_my_array($narray); // keywords only array $kwindex = index_keywords($filtered); // index of keywords $keywords = array_keys($kwindex); // // find items with no keywords // $otheritems = []; foreach ($filtered as $k=>$v) { if (count($v)==0) $otheritems[] = $k; } // // combine indexes // uasort($filtered, function($a,$b) {return count($b) - count($a);}); $k = count($filtered); for ($x=0; $x<2; $x++) { for ($i=0; $i<$k-1; $i++) { for ($j=$i+1; $j<$k; $j++) { $a = $filtered[$i]; $b = $filtered[$j]; if (array_intersect($a, $b)) { $filtered[$i] = array_unique(array_merge($a,$b)); $filtered[$j]=[]; } } } } foreach ($filtered as $k => $kwarr) { if (count($kwarr) == 0) { continue; } elseif (count($kwarr) > 1) { sort($kwarr); $newkw = join(' - ', $kwarr); $occurs = []; foreach ($kwarr as $kw) { if (isset($kwindex[$kw])) { $occurs = array_merge($occurs, $kwindex[$kw]); // combine individual lists unset($kwindex[$kw]); // then remove them } } sort($occurs); $kwindex[$newkw] = array_unique($occurs); // add the combined index } } // // create highlighting replacement textss // $replace = []; foreach ($keywords as $kw) { $replace[] = "<span class='hi'>$kw</span>"; } // // create output of the indexed list // ksort($kwindex); $output = ''; foreach ($kwindex as $kw => $items) { if (count($items)==0) continue; $output .= "<h4>$kw</h4><ul>"; foreach ($items as $i) { $output .= "<li>" . str_ireplace($keywords, $replace, $narray[$i]) . "</li>\n"; } $output .= "</ul>\n"; } if (count($otheritems) > 0) { $output .= "<h4>Non-keyword items</h4><ul>"; foreach ($otheritems as $i) { $output .= "<li>{$narray[$i]}</li>\n"; } $output .= "</ul>\n"; } /******************************************************************************* * helper functions ********************************************************************************/ function filter_my_array($array) { // reduces the lines of text to arrays of the keywords in the line $results = []; foreach ($array as $k => $str) { $str = no_punc($str); $a = array_filter(explode(' ', $str), 'remove_noise'); $results[$k] = $a; } return $results; } function remove_noise($x) { $stopWords = array('about','an','and','are','as','at','be','by','com','de','en','for','from', 'how','in','is','it','la','of','on','or','that','the','this','to','was','what','when','where', 'who','will','with','und','the','www','donald','trump'); return strlen($x) > 4 && !in_array(strtolower($x), $stopWords); } function index_keywords($array) { // gets the line numbers containing each keyword $results = []; foreach ($array as $k => $kwarr) { foreach ($kwarr as $kw) { $results[$kw][] = $k; } } return $results; } function no_punc($str) { $allow = array_merge([32], range(ord('a'), ord('z')), range(ord('0'), ord('9'))); $k = strlen($str); $res = ''; $str = strtolower($str); for ($i=0; $i<$k; $i++) { if (in_array(ord($str[$i]), $allow) ) { $res .= $str[$i]; } else $res .= ' '; } return $res; } ?> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Keyword Index</title> <style type='text/css'> .hi { font-weight: 700; color: red; } </style> </head> <body> <?=$output?> </body> </html> output <html><head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <title>Keyword Index</title> <style type="text/css"> .hi { font-weight: 700; color: red; } </style> </head> <body> <h4>after - anchor - arabia - attacks - babies - birthright - blunt - border - break - breaks - british - calls - campaign - celebrities - children - citizenship - clinton - combat - coming - could - court - debate - defends - denounces - deport - descend - doyle - draft - enter - explain - explains - facebook - fight - first - forced - forefront - foreigners - genius - hillary - holocaust - ignore - illegal - illegally - immigrants - immigration - living - longer - maddow - mexican - millions - nuclear - outlines - people - perform - policy - political - politics - president - presidential - profile - pushes - rachel - raise - releases - reports - rivals - rules - saturday - saudi - service - shadow - since - skipped - slams - specifics - state - summonses - supporters - takes - teach - think - today - tonight - trail - trumpcopter - trumps - using - vague - violence - wants - warns - watch - website - would - zuckerberg</h4><ul><li>Trump <span class="hi">denounces</span> <span class="hi">violence</span> <span class="hi">after</span> <span class="hi">supporters</span> beat <span class="hi">mexican</span> man</li> <li><span class="hi">doyle</span>: What my dad <span class="hi">could</span> <span class="hi">teach</span> Donald Trump</li> <li>Bush <span class="hi">slams</span> Trump, <span class="hi">defends</span> <span class="hi">using</span> <span class="hi">anchor</span> <span class="hi">babies</span></li> <li><span class="hi">coming</span> up <span class="hi">trumps</span>: <span class="hi">could</span> a <span class="hi">british</span> TV star do a Donald and <span class="hi">enter</span> <span class="hi">politics</span>?</li> <li><span class="hi">watch</span> <span class="hi">rachel</span> <span class="hi">maddow</span> <span class="hi">explain</span> Donald Trump’s ‘<span class="hi">genius</span>’ <span class="hi">campaign</span> on <span class="hi">tonight</span> Show</li> <li><span class="hi">first</span> <span class="hi">draft</span>: <span class="hi">today</span> in <span class="hi">politics</span>: <span class="hi">rivals</span> Can No <span class="hi">longer</span> <span class="hi">ignore</span> Donald Trump’s Long <span class="hi">shadow</span></li> <li>Donald Trump <span class="hi">pushes</span> <span class="hi">birthright</span> <span class="hi">citizenship</span> to <span class="hi">fore<span class="hi">front</span></span> of <span class="hi">political</span> <span class="hi">debate</span></li> <li>Jeb Bush <span class="hi">takes</span> <span class="hi">fight</span> to Donald Trump in N.H.</li> <li>Rand Paul <span class="hi">explain</span>s why he <span class="hi">wants</span> to stop ‘<span class="hi">birthright</span> <span class="hi">citizenship</span>’</li> <li>Trump <span class="hi">attack</span>s <span class="hi">facebook</span> over <span class="hi"><span class="hi">foreign</span>ers</span></li> <li>Trump’s <span class="hi">immigration</span> plan has GOP <span class="hi">rivals</span> on edge</li> <li>Donald Trump <span class="hi">calls</span> out Mark <span class="hi">zuckerberg</span> on <span class="hi">immigration</span></li> <li>Deny <span class="hi">citizenship</span> to <span class="hi">babies</span> <span class="hi">illegal</span> <span class="hi">immigrants</span> in US: Donald Trump</li> <li>Donald Trump <span class="hi">takes</span> a <span class="hi">break</span> from the <span class="hi">campaign</span> <span class="hi">trail</span> to join a long list of <span class="hi">celebrities</span> to <span class="hi">perform</span> jury duty</li> <li>Trump: Deny <span class="hi">citizenship</span> to <span class="hi">babies</span> of <span class="hi">people</span> <span class="hi">illegal</span>ly in US</li> <li>Trump Says He <span class="hi">would</span> <span class="hi">deport</span> <span class="hi">illegal</span> <span class="hi">immigrants</span></li> <li>From <span class="hi">campaign</span> to <span class="hi">court</span>: Trump <span class="hi">reports</span> for jury duty in NYC</li> <li>Donald Trump says he will ‘<span class="hi">deport</span> <span class="hi">millions</span> of <span class="hi">illegal</span> <span class="hi">immigrants</span>’</li> <li>Trump <span class="hi">outlines</span> <span class="hi">immigration</span> <span class="hi">specifics</span></li> <li>Trump <span class="hi">blunt</span> but <span class="hi">vague</span>: No <span class="hi">birthright</span> <span class="hi">citizenship</span>, <span class="hi">millions</span> of <span class="hi">illegal</span> <span class="hi">immigrants</span> ‘have to go’</li> <li>Trump: end ‘<span class="hi">birthright</span> <span class="hi">citizenship</span>’</li> <li>Trump: <span class="hi">deport</span> <span class="hi">children</span> of <span class="hi">immigrants</span> <span class="hi">living</span> <span class="hi">illegal</span>ly in US</li> <li>Trump says <span class="hi">would</span> <span class="hi">raise</span> visa fees to pay for <span class="hi">mexican</span> <span class="hi">border</span> wall</li> <li>What does Donald Trump <span class="hi">think</span> of <span class="hi">immigrants</span>, <span class="hi">saudi</span> <span class="hi">arabia</span> and the Iran <span class="hi">nuclear</span> deal?</li> <li>Donald Trump <span class="hi">releases</span> Plan to <span class="hi">combat</span> <span class="hi">illegal</span> <span class="hi">immigration</span></li> <li>Donald Trump <span class="hi">releases</span> his <span class="hi">immigration</span> <span class="hi">policy</span> on his GOP <span class="hi"><span class="hi">president</span>ial</span> <span class="hi">campaign</span> <span class="hi">website</span></li> <li>Donald Trump <span class="hi">warns</span> that Iran deal will lead to <span class="hi">nuclear</span> <span class="hi">holocaust</span></li> <li><span class="hi">clinton</span> <span class="hi">defends</span>, Trump <span class="hi">attack</span>s <span class="hi">saturday</span> at the high-<span class="hi">profile</span> Iowa <span class="hi">state</span> Fair</li> <li>Donald Trump says he <span class="hi">would</span> <span class="hi">deport</span> all <span class="hi">illegal</span> <span class="hi">immigrants</span> as <span class="hi">president</span></li> <li>Donald Trump <span class="hi">break</span>s the <span class="hi">rules</span> at the Iowa <span class="hi">state</span> Fair</li> <li><span class="hi">hillary</span> <span class="hi">clinton</span>, Donald Trump and the <span class="hi">trumpcopter</span> <span class="hi">descend</span> on the Iowa <span class="hi">state</span> Fair</li> <li>Trump <span class="hi">forced</span> to <span class="hi">break</span> from <span class="hi">campaign</span> <span class="hi">trail</span> for jury duty, <span class="hi">skipped</span> five <span class="hi">summonses</span> <span class="hi">since</span> 2006</li> <li>Donald Trump <span class="hi">forced</span> to take <span class="hi">break</span> from <span class="hi">campaign</span> <span class="hi">trail</span> for jury <span class="hi">service</span></li> </ul> <h4>answers - challengers - critics - details - domestic - fellow - foreign - matches - policies</h4><ul><li>Trump <span class="hi">details</span> <span class="hi">domestic</span>, <span class="hi">foreign</span> <span class="hi">policies</span>, <span class="hi">answers</span> <span class="hi">critics</span>, <span class="hi">matches</span> <span class="hi">fellow</span> <span class="hi">challengers</span></li> </ul> <h4>appearance - attack - cover - front - hotel - makes - making - malley - runners - skirt - soapbox - taking - touts - vegas - while</h4><ul><li>Trump <span class="hi">touts</span> <span class="hi">making</span> Time <span class="hi">cover</span> <span class="hi">while</span> <span class="hi">taking</span> heat over <span class="hi">attack</span></li> <li><span class="hi">while</span> in <span class="hi">vegas</span>, O’<span class="hi">malley</span> <span class="hi">makes</span> an <span class="hi">appearance</span> in <span class="hi">front</span> of Trump’s <span class="hi">hotel</span></li> <li><span class="hi">front</span>-<span class="hi">runners</span> <span class="hi">skirt</span> the <span class="hi">soapbox</span></li> </ul> <h4>barbara - cherished - ellen - thanks</h4><ul><li><span class="hi">thanks</span>, Donald, but I don’t want to be ‘<span class="hi">cherished</span>’ | <span class="hi">barbara</span> <span class="hi">ellen</span></li> </ul> <h4>batman</h4><ul><li>Donald Trump to Iowa boy: ‘I am <span class="hi">batman</span>’</li> </ul> <h4>blasts - comments - women</h4><ul><li>DNC <span class="hi">blasts</span> Donald Trump , Jeb Bush for <span class="hi">comments</span> about <span class="hi">women</span></li> </ul> <h4>candidates - dueling - halls</h4><ul><li>GOP <span class="hi">candidates</span> hold <span class="hi">dueling</span> town <span class="hi">halls</span></li> </ul> <h4>chief - tables - tormentor - turned</h4><ul><li><span class="hi">tables</span> <span class="hi">turned</span> on Trump’s <span class="hi">chief</span> <span class="hi">tormentor</span></li> </ul> <h4>columnist - diplomat - introducing</h4><ul><li>Op-Ed <span class="hi">columnist</span>: <span class="hi">introducing</span> Donald Trump, <span class="hi">diplomat</span></li> </ul> <h4>conservative - insists</h4><ul><li>Donald Trump <span class="hi">insists</span> he’s <span class="hi">conservative</span></li> </ul> <h4>crowd - draws - hampshire</h4><ul><li>Donald Trump <span class="hi">draws</span> New <span class="hi">hampshire</span> town hall <span class="hi">crowd</span> wild; jabs Jeb Bush</li> </ul> <h4>field - florida - pennsylvania - second</h4><ul><li>Donald Trump tops GOP <span class="hi">field</span> in <span class="hi">florida</span>, <span class="hi">pennsylvania</span>, <span class="hi">second</span> in Ohio</li> </ul> <h4>legacy - luxury</h4><ul><li>Donald Trump’s <span class="hi">legacy</span> of <span class="hi">luxury</span></li> </ul> <h4>serve</h4><ul><li>Donald Trump will <span class="hi">serve</span> jury duty in NYC next week</li> </ul> <h4>Non-keyword items</h4><ul><li>New York City has no way to fire Donald Trump</li> </ul> </body></html>

is this what you had in mind? $prevkey = ''; foreach ($flavors as $k1 => $arr) { foreach ($arr as $k2 => $v ) { if ($v=='') { if (isset($flavors[$prevkey][$k2])) { $flavors[$k1][$k2] = $flavors[$prevkey][$k2]; } } $prevkey = $k1; } } echo '<pre>',print_r($flavors, true),'</pre>'; gives Array ( [Japanese] => Array ( [hot] => wasabi [salty] => soy sauce ) [Chinese] => Array ( [hot] => wasabi [pepper-salty] => prickly ash ) [indian] => Array ( [hot] => wasabi [pepper-salty] => prickly ash ) [mexican] => Array ( [hot] => soanzo [salty] => soy sauce ) [russian] => Array ( [hot] => soanzo [pepper-salty] => prickly ash ) [ganda] => Array ( [hot] => soanzo [pepper-salty] => prickly ash ) )

A database isn't just an alternative content holder. It also gives you the ability to search and sort.

Which you could also do with a form to update the contents of database records

If you got around to trying it you would find you get a syntax error when using LEFT JOIN without a join condition (ON or USING)

Why are you binding params to a query that has no parameters?

Nothing in that code will do it. Do you have any javascript on the page that might be putting them there?