MySQL query help needed

[NCA2]

Here is my current query within a php script that executes perfectly fine:


<?
$getdade = date("Y-m-d");
$query = @mysql_query("SELECT ID,TITLE,URL,MEMBER FROM sites WHERE ACTIVE='1' AND APPROVED='1' AND HOURSTOPPER != '$hour' AND AVLCREDITS='1' AND MEMBER != '$surfid' AND ID != '$ds'");
$num = @mysql_num_rows($query);

while ($i < $num) {

$e = @mysql_fetch_array($query);

$query2 = @mysql_query("SELECT ID FROM track WHERE OWNER=$e[MEMBER] AND MEMBER='$surfid' AND DATE='$getdate'");
$n = @mysql_fetch_array($query2);

if (!$n[ID]) {
echo "$e[ID],$e[TITLE],$e[URL],$e[MEMBER]<br>";
}

$i++;
}
?>


There has to be a more effiecient way to execute this as just 1 query and bypass using this type of php script routine to pull the rows out of the database that I want. Anyone know what the query would look like to combine the above process into one mysql query?

And one more thing, I want to rows that it finds to be distinct for the MEMBER column in the SITES table. So that the rows do not contain more than one row that contains the same "MEMBER" number. Not sure if you would use distinct or not...

[chazzy]

you could easily use a join. however, are you trying to just find the members that don't have tracks? is that the logic that this is trying to follow?

[Brooksie155]

You can run a select across multiple tables, the only thing i'm confused about is that in the first query you search MEMBER != '$surf_id' and in the second you search for MEMBER = '$surf_id'. I am assuing that MEMBER is independant in each data. In which case you can link the tables on MEMBER (1st query) with OWNER (2nd querty) like this.

SELECT
sites.ID AS SiteID, sites.TITLE, sites.URL
tracks.ID AS TrackID
FROM sites, tracks
WHERE 1
AND sites.MEMBER != '$surf_id'
AND sites.AVLCREDITS = '1'
AND sites.HOURSTOPPER = '$hour'
AND sites.APPROVED = '1'
AND sites.ACTIVE = '1'
AND tracks.OWNER = sites.MEMBER
AND tracks.MEMBER = '$surf_id'
AND tracks.ID = '$ds'
AND tracks.DATE = '$getdate'
GROUP BY (sites.MEMBER);

The most important line here is 'AND tracks.OWNER = sites.MEMBER ' as it links the two tables.

The 'GROUP BY sites.MEMBER' will producs a recordset which has been grouped by that field, therefore removing duplicates. Alternativly you could put 'SELECT DISITNCT(sites.MEMBER)' as long as it is the first element on the selection list.

This will return a single recordset as if it was all drawn fromthe same table. I have refernenced the ID columns as SiteID and TrackID to avoid amibiguity when retrieving the data.

[NCA2]

Hey thanks for the help, but its not working when I changed it to my actual titles of the data, here is what I have...

SELECT sites.ID, sites.TITLE, sites.URL, sites.MEMBER FROM sites WHERE sites.MEMBER != '$i[ID]' AND sites.MEMBER != '$ls' AND sites.AVLCREDITS = '1' AND sites.HOURSTOPPER != '$hour' AND sites.APPROVED = '1' AND sites.ACTIVE = '1' AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade' GROUP BY (sites.MEMBER) ORDER BY RAND() LIMIT 20

It will work perfectly fine, but some rows do not have a connecting line in the track table, so if I remove this...

AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade'

it works fine, but does not check the entries in the track table. Is there any way to do something like an if statement in a query? so that if it contains a link between tables of "track.OWNER = sites.MEMBER" it will then connect them and look at things, and if not, it wont....

[chazzy]

SELECT sites.ID, sites.TITLE, sites.URL, sites.MEMBER FROM sites WHERE sites.MEMBER != '$i[ID]' AND sites.MEMBER != '$ls' AND sites.AVLCREDITS = '1' AND sites.HOURSTOPPER != '$hour' AND sites.APPROVED = '1' AND sites.ACTIVE = '1' AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade' GROUP BY (sites.MEMBER) ORDER BY RAND() LIMIT 20

you can't reference track.col_name if you're not including it in your select statement.

[NCA2]

Oops I knew that. (I've done this stuff over 100 times before lol)

Ok, here is my query and it still isnt working to how I want it to be, my main question is, sometimes there will not be a joining row in the "track" table to join up with the "sites" table. Is there a way to just say if there is then... kinda thing in a mysql query?


SELECT sites.ID, sites.TITLE, sites.URL, sites.MEMBER FROM sites,track WHERE sites.MEMBER != '$i[ID]' AND sites.MEMBER != '$ls' AND sites.AVLCREDITS = '1' AND sites.HOURSTOPPER != '$hour' AND sites.APPROVED = '1' AND sites.ACTIVE = '1' AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade' GROUP BY (sites.MEMBER) ORDER BY RAND() LIMIT 20


Because in a certain case it will not work, but if I remove the...


AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade'


it will work due the fact that there was no joining row in the track table.

Any input is greatly appreciated...

[chazzy]

I would go with a solution like in this thread actually
http://www.webdeveloper.com/forum/showthread.php?t=90634
he's using a left join and only showing null id's from the second table (IE his only shows the results that aren't in the other table)
you're doing something very similar. you should be able to adapt it.