Databases
StackOverflow2013
Postsdbo
POhtml select multiple choice input, store them in mysql db and search for matches
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POhtml select multiple choice input, store them in mysql db and search for matches
    text
    copied!<p>My question first and then (many...infos): what are my options thinking of usability and speed when i want a user to be able to select mulitple answers from a dropdownbox, save them on their profile, then let other users key in their searchcriteria (can be multiple again) and finally find users by those mutiple searchcriteria? </p> <p>The infos: At the moment i am busy with a part of my website being a matchmaking system, much like datingsites use.</p> <p>(I am using Mysqli, PHP and jQuery for my site)</p> <p>I am using a dating site as an example to elaborate on my question: user A fills in his/her profile and enters their preference for a certain car brand: BMW, Volkswagen and Mercedes. User B wants to search the site for members who like BMW and/or Mercedes. </p> <p>My original idea was to let user A fill in an input multiple html field and store it as an array (or comma-separated) in one column in my db. Then figure out how to select-search through that column which has stored arrays in it for user B.</p> <p>So basically user A can enter multiple car brands in their profile and user B can give their criteria being also multiple car brands; the select query should fetch any row for matching criteria.</p> <p>The input-field multiple looks like:</p> <pre><code>&lt;tr&gt;&lt;td&gt;Which car brand(s) do you like?&lt;/td&gt;&lt;td&gt; &lt;select multiple name="CarBrandPref[]"&gt; &lt;option value="BMW" &gt; BMW&lt;/option&gt; &lt;option value="Ford" &gt; Ford&lt;/option&gt; &lt;option value="Mercedes" &gt; Mercedes &lt;/option&gt; &lt;option value="GMC" &gt; GMC&lt;/option&gt; &lt;option value="Volkswagen" &gt; Volkswagen &lt;/option&gt; &lt;option value="Toyota" &gt; Toyota &lt;/option&gt; &lt;option value="Audi" &gt; Audi &lt;/option&gt; &lt;/select&gt; &lt;/td&gt;&lt;/tr&gt; </code></pre> <p>The profile table lay-out would be something like:</p> <pre><code> Id Name CarbrandPref -------------------------------------------------------- 1 Mike bmw,volkswagen,mercedes 2 Paul Mercedes, ford, GMC 3 Axel GMC, ford, toyota, audi 4 John ford, bmw </code></pre> <p>User B would give searchcriteria 'bmw and mercedes'. The query should then return 3 rows:</p> <pre><code>Id Name CarbrandPref --------------------------------------------------------- 1 Mike bmw,volkswagen,mercedes 2 Paul Mercedes, ford, GMC 4 John ford, bmw </code></pre> <p>Upon my searches on SO and other sites to get this working i noticed everyone saying that this (storing in arrays in db) was bad for performance etc. etc. Furthermore i can't get the select-search through the arrays in mysql to work.</p> <p>So now i'm at a loss what to do. I don't want to add a column for each possble carbrand, but what to do else? Is there anyone who can help me with this? Any ideas are much appreciated!</p> <p>I hope this is clear enough for someone to help me. If it is not, please tell me and i'll add the requested info.</p> <p>I have used these links to be able to store the dropdownbox-populated arrays into the mysql database:</p> <p><a href="http://www.aleixcortadellas.com/main/2009/03/20/492/" rel="nofollow">http://www.aleixcortadellas.com/main/2009/03/20/492/</a></p> <p><a href="http://toolspot.org/how-to-store-array-mysql.php" rel="nofollow">http://toolspot.org/how-to-store-array-mysql.php</a></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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