Jump to content


Photo

Select all websites with no hits for a member.


  • Please log in to reply
12 replies to this topic

#1 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 09 October 2006 - 03:22 PM

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

#2 mlavwilson

mlavwilson
  • Members
  • PipPip
  • Member
  • 15 posts

Posted 09 October 2006 - 03:31 PM

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

#3 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 09 October 2006 - 03:33 PM

This code should work...

$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')";

Edit: On second thoughts, I'm not sure this will work.  I'm investigating 'NOT IN' further

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#4 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 09 October 2006 - 03:36 PM

huggiebear m8 please post the link where you think is the best plase to learn joins cheers.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#5 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 09 October 2006 - 03:39 PM

Red:

Try this one W3Schools

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#6 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 09 October 2006 - 03:39 PM

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

#7 redarrow

redarrow
  • Members
  • PipPipPip
  • Advanced Member
  • 7,308 posts
  • Locationlondon

Posted 09 October 2006 - 03:41 PM

thanks mate your a star cheers.
Wish i new all about php DAM i will have to learn
((EMAIL CODE THAT WORKS))
http://simpleforum.ath.cx/mail2.inc
((PAYPAL INTEGRATION THAT WORKS))
http://simpleforum.a...aypal1_info.inc

#8 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 09 October 2006 - 04:16 PM

The following code should work:

$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";

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#9 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 10 October 2006 - 07:15 AM

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

#10 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 10 October 2006 - 08:31 AM

OK,

Try this:

$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";

$member is the value of your logged in member.

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#11 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 10 October 2006 - 08:59 AM

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?

#12 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 10 October 2006 - 09:19 AM

Is there a reason why you changed the table aliases?

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

Change it to:
SELECT websites.websites_id AS WebID, websites_hits.websites_user AS WebUser FROM websites

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

Regards
Huggie
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#13 brown2005

brown2005
  • Members
  • PipPipPip
  • Advanced Member
  • 943 posts

Posted 10 October 2006 - 09:34 AM

thanks very much.. works perfect




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users