Databases
StackOverflow2013
Postsdbo
POFind most common data values from MySQL columns?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POFind most common data values from MySQL columns?
    primarykey
    data
    text
    <p>I'm trying to integrate a music suggestion application in to my current music rating application. And I was looking for a way to suggest music, based on current tastes.</p> <p>I'm writing this question on my iPod, so the formatting is probably wrong, so I'll explain the columns. </p> <p><code>rate_id</code>, <code>username</code>, <code>artist</code>, <code>type</code> (male/female/mix), <code>song_id</code>, <code>songname</code>, <code>genre</code>, <code>year</code>, <code>like</code>, <code>dislike</code></p> <p>For example, one person likes 4 tracks, this query being:</p> <pre class="lang-sql prettyprint-override"><code>SELECT * FROM rates WHERE username='$_SESSION['username']' LIMIT 4 ORDER BY id </code></pre> <p>returns:</p> <pre class="lang-none prettyprint-override"><code>1 | mrexample | Katy Perry | F | 55 | Firework | Pop | 2010 | 1 | 0 78 | mrexample | Lady Gaga | F | 36 | Pokerface | Pop | 2010 | 1 | 0 95 | mrexample | Nelly | F | 96 | Just a Dream | Pop | 2010 | 1 | 0 106 | mrexample | Justin Bieber | M | 78 | Baby | Pop | 2010 | 1 | 0 </code></pre> <p>Ok, so from that pattern of rating, we know that mrexample mostly liked pop songs by females, written in 2010, so from that data, we form a query to our user generated music collection database:</p> <pre class="lang-php prettyprint-override"><code>mysql_query("SELECT * FROM music WHERE genre='pop' AND type='F' AND year='2010' LIMIT 5 ORDER BY RAND()") </code></pre> <p>Now, my question is: <strong>how do I find out what that common data is from a large chunk of data about that person's rates?</strong></p> <p>I'm thinking there has to be a mysql command for it, but I'm not sure :/ </p> <p>I thought about if statements, impossible, too many possibilities if part of the popular part was the artist, loads of artists are in our db. switch, same thing again. count, nothing in particular that I can count. I could mysql num rows against if's, again - too much to handle.</p> <p>Any ideas?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USbostaf
    1. USKaran
    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. POFind most common data values from MySQL columns?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POFind most common data values from MySQL columns?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POFind most common data values from MySQL columns?
      1. USBen Guild
      1. VTFavorite
    1. COWhat you are looking at is a custom requirement of your application. You need to decide what "popular" means to you and write an algorithm based on the parameters you mentioned.
      singulars
      1. POFind most common data values from MySQL columns?
      1. USSukumar
 

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