Many to Many / Distinct problem

[idiparker]

Hi,

I'm building a database of radio stations, but have found that some stations have multiple formats (especially the college ones). So I did two tables, one for the basic radio information and one for the formats, both with a StationID as the key between them.

So say Station 1 can be:

Alternative
Jazz
Classical
Folk
News

Station 2 is:
Jazz
Blues
News

etc, etc.

The problem arises when I try to pull up a list of all Jazz stations and then list what other formats they play. Say "show me all the Jazz stations" and then create a list of other formats these stations play.

I made a subquery, selecting 1) all the stations that have Jazz and then 2) all of the formats for the above StationIDs.

That's where I get into a problem. In the prior example, I'd get a listing like this:

Jazz
Alternative
Classical
Folks
News
Jazz
Blues
News

Obviously the repeating part of this is a problem.

Does anyone have any ideas? I've looked at creating a temporary table with the results of the first search, but was wondering if there is any easier way.

[twilson]

Use SELECT DISTINCT on the types...

mySQL manual:

http://dev.mysql.com/doc/mysql/en/SELECT.html

The DISTINCT option is covered at about the 1/2 way point on the page.

Peace,
Tom

[idiparker]

I tried Distinct but it didn't really work. Because it was a subquery, it was looking for DISTINCT items in each subquery, not in the overall search.

For example, I'd search for all Jazz stations and get Station 1 and Station 2. I'd then search for all other formats that were listed with StationID's of 1 or 2, but since each search was a "new" search the Distinct seemed reset each time. NEWS was distinct in a search of those records with StationID=1, and then again it was distinct in a search of those records with StationID 2.

Or am I missing something about Distinct?

[twilson]

Not missing something really, just doing too many queries.

When you get all the StationID's back, loop them into a string like:

while ($Data = mysql_fetch_array($STATION_QUERY)) {
    $TYPE_WHERE .= "StationID='$Data[StationID]' || ";
}

//Cut off the excess || 's
$TYPE_WHERE  = substr($TYPE_WHERE, 0, -4);

//Use $TYPE_WHERE in your query
$TYPE_QUERY = mysql_select("SELECT DISTINCT Type FROM TypeTable WHERE $TYPE_WHERE");

That should work since the types will all be returned in ONE query.

Let me know if there are any issues!

Peace,
Tom 🙂

[idiparker]

Thanks. That seems to be pushing the right StationID into the search. Now I just have to find a way to display the results.

I tried another WHILE mysql_fetch_array for the Format query, but that didn't seem to work. It returned mysql_fetch_array(): supplied argument is not a valid MySQL result resource in...

Is there something about nesting two mysql_fetch_arrays?

Here is my code. Format is the name of the table (and of the field, which is a bit confusing).

$result = mysql_query("select StationID from format where format = 'Jazz'");

while ($row = mysql_fetch_array($result)) {
	$TYPE_WHERE .= "StationID='$row[StationID]'  ||_'";
	$TYPE_WHERE=substr($TYPE_WHERE,_0,_-4);

$result2 = mysql_query("select distinct format FROM_format_WHERE_$TYPE_WHERE");
while ($row2 = mysql_fetch_array($result2)) {
echo $row2->format;
}
}

In the line starting $result2 I threw in a mysql_error that came back saying I had to check the syntax for my search, but when I take the error message out it runs through to the next line were it says mysql_fetch_array is not valid. Wonder if it has anything to do with mysql version I'm using (4.0.20 I think).

[twilson]

Try this:

$result = mysql_query("select StationID from format where format = 'Jazz'");

while ($row = mysql_fetch_array($result)) {
	$TYPE_WHERE .= "StationID='$row[StationID]'  || ";
}

$TYPE_WHERE = substr($TYPE_WHERE,0,-4);


$result2 = mysql_query("SELECT DISTINCT format FROM format WHERE $TYPE_WHERE");

while ($row2 = mysql_fetch_array($result2)) {
    echo $row2['format'];
 }

That mysql_fetch_array error usually means the QUERY is bad.

Peace,
Tom

[tomhath]

DISTINCT is what you want, should be something like this (untested):

select distinct f1.format 
from formats f1
inner join formats f2
on f1.stationid = f2.stationid
where f2.format = 'jazz'
  and f1.format <> 'jazz'

[idiparker]

Well I'm just a gidget or whatever...Looks like I just misplaced a }

It worked. Thank you very much for solving something that has bugged me for quite awhile.

I should note that I cut and pasted your script into my page and when I uploaded it, all hell broke loose. I guess there is something in cut and paste from this site that renders things funky (I retyped it all by hand it worked fine).

Peace
http://www.penguinsix.com/

[idiparker]

Where do the '||' come in and why?

I've gotten this to work in a test.php file, but migrating to a "real" page is causing some problems, mainly with the "||' characters.

We have

$TYPE_WHERE .="StationID='$row[StationID]'  || ";
}
$TYPE_WHERE = substr($TYPE_WHERE,0,-4);

However, it keeps returning the string as:

StationID='1'||

It's not chomping or trimming (but like I said, it is in the test.php file).

I'm curious why PHP puts them, or why I'm adding them in that one line and taking them out in the other?