Databases
StackOverflow2013
Postsdbo
POPHP/MySQL nearest neighbor - long binary sequence
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP/MySQL nearest neighbor - long binary sequence
    primarykey
    data
    text
    <p>Given an array of a series of long numbers (equal length) - 0101101111, 0111101111, 0101101101, etc.</p> <p>Find closest match to 0101101001</p> <p>OK, that's the short version. The long version is a form with 11 Yes(1) and No(0) questions (product finder). Since we know that the database may not contain an exact match to the n^r solutions = 2048, we need to find the closest match or, possibly, the closest two or three matches.</p> <p>I'm guessing we need to compare each number in each position and rank the result, but I'm stuck on the solution - if I am even going about it in the right direction.</p> <p>Thanks!!</p> <p><em><strong>I did a quick check against popnoodle answer below</em></strong>... using <strong>$lookslike=11100110011;</strong> running foreach, generated sql and ran in phpMyAdmin against a table of 6 "answers".</p> <p>Answers were ranked as follows:</p> <p>Answer -- rank</p> <p>11100110011 -- 11<br> 11100110010 -- 10<br> 11100110000 -- 9<br> 00000111010 -- 6<br> 00000111111 -- 6<br> 01101100110 -- 6 </p> <p>*edit for ranking explanation - 11 of 11 matches, 10 of 11 matches, 9 of 11 matches, 6 of 11 matches...</p> <p>Very nice.</p> <p>Added modified PHP manual example of multiple choice answers:</p> <pre><code>&lt;?php // Client Answers $input = 'abcdbcdabcd'; // array of answers to check against $answers = array('abcdbddabcd', 'abcbbcdabcd', 'abcdbcccccd'); // no shortest distance found, yet $shortest = -1; // loop through words to find the closest foreach ($answers as $answer) { // calculate the distance between the input word, // and the current word $lev = levenshtein($input, $answer); // check for an exact match if ($lev == 0) { // closest word is this one (exact match) $closest = $answer; $shortest = 0; // break out of the loop; we've found an exact match break; } // if this distance is less than the next found shortest // distance, OR if a next shortest word has not yet been found if ($lev &lt;= $shortest || $shortest &lt; 0) { // set the closest match, and shortest distance $closest = $answer; $shortest = $lev; } } echo "Input answer: $input\n" . "&lt;br /&gt;"; if ($shortest == 0) { echo "Exact match found: $closest\n" . "&lt;br /&gt;"; } else { echo "Possible Alternative: $closest?\n" . "&lt;br /&gt;"; echo "Levenshtein Distance: $lev\n"; } ?&gt; </code></pre> <p>Returns:</p> <p>Input answer: abcdbcdabcd<br> Possible Alternative: abcbbcdabcd?<br> Levenshtein Distance: 3</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USfwunder
    1. USfwunder
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou may want to look into the MySQL MATCH AGAINST method - not sure how/if it works with multiple fields, but I assume you can make some use of it - might be worth checking it out anyway
      singulars
      1. POPHP/MySQL nearest neighbor - long binary sequence
      1. USSmokeyPHP
    2. COHow will match against help here? From the info given it looks like just the one field which is a binary representation of a series of yes/no answers. Is that right?
      singulars
      1. POPHP/MySQL nearest neighbor - long binary sequence
      1. USPopnoodles
    3. COYes, that is correct. Field is binary representation of yes/no questions.
      singulars
      1. POPHP/MySQL nearest neighbor - long binary sequence
      1. USfwunder
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload