Select all websites with no hits for a member.

[brown2005]

CREATE TABLE `members` (
  `members_id` bigint(15) NOT NULL auto_increment,
  PRIMARY KEY  (`members_id`)
) TYPE=MyISAM AUTO_INCREMENT=2 ;

INSERT INTO `members` VALUES (1);

CREATE TABLE `websites` (
  `websites_id` bigint(15) NOT NULL auto_increment,
  PRIMARY KEY  (`websites_id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

INSERT INTO `websites` VALUES (1);
INSERT INTO `websites` VALUES (2);
INSERT INTO `websites` VALUES (3);
INSERT INTO `websites` VALUES (4);

CREATE TABLE `websites_hits` (
  `websites_id` bigint(15) NOT NULL default '0',
  `websites_user` bigint(15) NOT NULL default '0'
) TYPE=MyISAM;

INSERT INTO `websites_hits` VALUES (1, 1);

I have the above three tables.

What I want to is say i have a member logged in, in this case, member 1...

I want to select all the websites from websites that have no hits in the websites hits table, so it should select 2,3 and 4... cause it has hits already

any ideas how please

[mlavwilson]

T1 LEFT JOIN T2 ON (T1.A=T2.A AND T2.A is null)

[HuggieBear]

This code should work...

[code]
$sql = "SELECT w.websites_id FROM websites w WHERE w.website_id NOT IN (SELECT distinct(wh.websites_id) FROM websites_hits wh WHERE wh.websites_user = '$member_id')";
[/code]

[size=8pt][color=red][b]Edit:[/b][/color] On second thoughts, I'm not sure this will work.  I'm investigating 'NOT IN' further[/size]

Regards
Huggie

[redarrow]

huggiebear m8 please post the link where you think is the best plase to learn joins cheers.

[HuggieBear]

Red:

Try this one [url=http://www.w3schools.com/sql/sql_join.asp]W3Schools[/url]

Regards
Huggie

[brown2005]

i looked at that site but can't find anything on when its not in a table...

[redarrow]

thanks mate your a star cheers.

[HuggieBear]

The following code should work:

[code]
$sql = "SELECT w.websites_id, wh.websites_user FROM websites w LEFT JOIN websites_hits wh ON (w.websites_id = wh.websites_id) WHERE wh.websites_user is null";
[/code]

Regards
Huggie

[brown2005]

Hi.

That code is cool, but it only selects the records from website hits that are NUll.

What I really needed was to select all the websites with no hits for a certain member..

Thanks for the help

[HuggieBear]

OK,

Try this:

[code]$sql = "SELECT w.websites_id, wh.websites_user FROM websites w LEFT JOIN websites_hits wh ON (w.websites_id = wh.websites_id AND wh.websites_user = '$member') WHERE wh.websites_user is null";[/code]

$member is the value of your logged in member.

Regards
Huggie

[brown2005]

cool... works on phpmyadmin but with code

<?php

session_start();

$database_host = "";
$database_username = "";
$database_password = "";
$database_name = "";

$connection = mysql_connect($database_host, $database_username, $database_password) or die(mysql_error());
$db = mysql_select_db($database_name, $connection);

$member = "1";

$sql = "SELECT websites.websites_id, websites_hits.websites_user FROM websites LEFT JOIN websites_hits ON (websites.websites_id = websites_hits.websites_id AND websites_hits.websites_user = '$member') WHERE websites_hits.websites_user is null;";

$query = mysql_query($sql) or die(mysql_error());


while($array = mysql_fetch_array($query))
{
 
echo $array['websites.websites_id'];

echo"<br><br>";
 
}

?>

i get nothing.. ne ideas?

[HuggieBear]

Is there a reason why you changed the table aliases?

Anyway, probably best to give your columns aliases too, so where you have:
[code]SELECT websites.websites_id, websites_hits.websites_user FROM websites[/code]

Change it to:
[code]SELECT websites.websites_id AS WebID, websites_hits.websites_user AS WebUser FROM websites[/code]

Then in your php, use the aliases:
[code]while($array = mysql_fetch_array($query)){
  echo $array['WebID'];
}|[/code]

Regards
Huggie

[brown2005]

thanks very much.. works perfect