Jump to content

Select all websites with no hits for a member.


brown2005

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.