Jump to content

Archived

This topic is now archived and is closed to further replies.

brown2005

Select all websites with no hits for a member.

Recommended Posts

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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
huggiebear m8 please post the link where you think is the best plase to learn joins cheers.

Share this post


Link to post
Share on other sites
Red:

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

Regards
Huggie

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.