Databases
StackOverflow2013
Postsdbo
POPerforming a simple search in MySQL db with variable amount of input
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPerforming a simple search in MySQL db with variable amount of input
    primarykey
    data
    text
    <p>I have a many-to-many db with these three tables, Films, Ambiences, Films_Ambience: </p> <pre><code>CREATE TABLE Films ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), Title VARCHAR(255)); CREATE TABLE Ambiences ( id INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(id), ambienceName VARCHAR(255)); CREATE TABLE Films_Ambiences ( film_id INT NOT NULL, ambience_id INT NOT NULL, PRIMARY KEY (film_id, ambience_id), FOREIGN KEY (film_id) REFERENCES Films(id) ON UPDATE CASCADE, FOREIGN KEY (ambience_id) REFERENCES Ambiences(id) ON UPDATE CASCADE); </code></pre> <p>I am using information from a form to search for specific films (e.g. film that is funny and scary at the same time). The form are simply 'ticks' next to given names. The information is sent by $_POST.</p> <p>The problem is that I don't know how many requirements will there be. I know the maximum number that a user can choose but I cannot tell how many or which they will actually pick (I could do that by checking <code>isset($_POST['somethin'])</code> but it would be very.. monotonous if I had, for example, 20 different options. So I cannot do anything like:</p> <pre><code>$ambience1 = $_POST["a1"]; $ambience2 = $_POST["a2"]; $ambience3 = $_POST["a2"]; ... ... ... </code></pre> <p>and: </p> <pre><code>SELECT *,GROUP_CONCAT(ambienceName SEPARATOR ' ') AS ambiences FROM Films AS f INNER JOIN Films_Ambiences as fa ON f.id = fa.film_id INNER JOIN Ambiences AS a ON a.id = fa.ambience_id GROUP BY Title HAVING (ambiences LIKE '%$ambience1%' AND ambiences LIKE '%$ambience2%' AND ... </code></pre> <p>I'm not even sure where to start. Can I do it with SQL or rather PHP? </p> <p>Here's an <strong><a href="http://sqlfiddle.com/#!2/23afc/19" rel="nofollow">SQLFiddle</a></strong> if U like. </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSharkz
    1. USSharkz
    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. COYour query isn't correct even if you do know the number of ambiences. A single ambience can't match both sad and funny at the same time.
      singulars
      1. POPerforming a simple search in MySQL db with variable amount of input
      1. USBarmar
    2. COAnd there's no column `a.ambiences`.
      singulars
      1. POPerforming a simple search in MySQL db with variable amount of input
      1. USBarmar
    3. COWhat I meant was just ambiences, I believe. Edited.
      singulars
      1. POPerforming a simple search in MySQL db with variable amount of input
      1. USSharkz
 

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