Databases
StackOverflow2013
Postsdbo
POMatch User Preference to Object Attributes Order By Match Percentage
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMatch User Preference to Object Attributes Order By Match Percentage
    primarykey
    data
    text
    <p>I'm trying to calculate how closely an item matches a user's defined preferences. The following is how I thought of doing it. But I'm not overly experienced and wanted to know if there might be a better way to go about it.</p> <p>Using cars as a simple example. We will narrow it down to color and style of car (car, van, etc.). <hr/> <strong>Part One</strong></p> <p>The user selects in an HTML form the following:</p> <pre><code>Color: ( )White, (*)Black, ( )Red Style: ( )Car, ( )Van, (*)Suv, (*)Truck </code></pre> <p>Now if I convert the above into a binary number where the <strong>first digit = first attribute</strong> (white) and continues on.</p> <p><strong>Attribute Code</strong> = 0100011 (black, suv, truck) <hr/> <strong>Part Two</strong></p> <p>Now with MySQL</p> <pre><code>Select item_id, attribute_code FROM items items table = [item_id][attribute_code] </code></pre> <p>Next use PHP to calculate how closely each items attribute code matches the users preferences.</p> <pre><code>// Set users attribute code to var $user_pref = $_POST['user_att_code']; while($row=mysql_fetch_array($result)) { // Pull attribute_code from database and put into var $item_code = $row['attribute_code']; // Set counters $count_digit = 0; $count_match = 0; // Length of attribute code $length = 7; // Start calculating match while($count_digit &lt;= $length) { // Does first digit of users code = 1? // Does first digit of items code = 1? if($user_pref{$count_digit} != 0 &amp;&amp; $user_pref{$count_digit} == $item_code{$count_digit}) { // Add a positive match point to counter $count_match++; } // Next digit in code $count_digit++; } if($count_match &gt; 0) { // Make array of item_id and match amount $item_search [$row['item_id']] = $count_match; } } // Sort array by most similar arsort($item_search); </code></pre> <p>Then a little more code is used to calculate a percentage.</p> <p>The above did the following: It took the users desired attribute code and compared it to every items attribute code in the database. It went digit by digit through each code and made a tally every time there was a match. At the end it put the tally for that item into an array and went onto the next item's attribute code.</p> <pre><code>user: 0100011 it_1: 0100011 = 100% match it_2: 0100100 = 50% match it_3: 0011000 = 0% match // If you notice the 50% does not make sense ignore it. // I left something out for simplification. </code></pre> <p>Now I know this works. However, it does not seem like a good way to go about it. <strong>Performance</strong> wise mostly. Assuming over <strong>150,000 items, and an attribute code length of about 200</strong>. That's at least 30,000,000 calculations for one search (based on above).</p> <p>Is there another way perhaps? Is this a big deal?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USeaswee
    1. USAdam
    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
    1. VO
      singulars
      1. POMatch User Preference to Object Attributes Order By Match Percentage
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 Interesting question
      singulars
      1. POMatch User Preference to Object Attributes Order By Match Percentage
      1. USNullUserException
 

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