Databases
StackOverflow2013
Postsdbo
POSelect 2-3 number combination from 5 column Table MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect 2-3 number combination from 5 column Table MySQL
    primarykey
    data
    text
    <p>Table structure:</p> <pre><code> MyTable ( ID INT AUTO_INCREMENT, Num1 INT, Num2 INT, Num3 INT, Num4 INT, PRIMARY KEY(ID) )engine=InnoDB; </code></pre> <p>Now i have around <strong>20-30k</strong> records. <code>Num1, Num2, Num3</code> and <code>Num4</code> are just some random numbers. Im trying to select <code>2</code> and <code>3</code> <strong>number combinations</strong> from this table. For example lets say i have the following rows in table:</p> <pre><code>ID Num1 Num2 Num3 Num4 1 20 11 9 150 2 30 11 20 19 3 40 45 11 20 </code></pre> <p>I would want to select the most frequently used <strong>2</strong> number combinations and then <strong>3</strong> number combinations. So note that <strong>20</strong> and <strong>11</strong> appear <strong>3</strong> times in table means the combination <code>20,11 or 11,20</code> doesnt matter the order has <strong>count 3</strong> and so on for other combinations.</p> <p>I want to retrieve this data in PHP array so that i can do some calculations or display on screen.</p> <p>What i tried so far:</p> <pre><code> SELECT * FROM MyTable WHERE (Num1 = :num1 AND Num2 = :num2) OR (Num1 = :num1 AND Num3 = :num2) OR (Num1 = :num1 AND Num4 = :num2) OR (Num2 = :num1 AND Num1 = :num2) OR (Num2 = :num1 AND Num3 = :num2) OR (Num2 = :num1 AND Num4 = :num2) OR *** *** </code></pre> <p>and so on for all combinations. Now this gets annoying if i try to use it for <strong>3</strong> number combinations. </p> <ol> <li>Is there a better and efficient way of doing this?</li> <li>Do i need to restructure table to make this easier?</li> <li>Will restructured table be normalized? (Right now i think is normalized if not please tell me)</li> </ol>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGGio
    1. USGGio
    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
    1. VO
      singulars
      1. POSelect 2-3 number combination from 5 column Table MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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