Join and foreach loop

[chomedey]

Hi there,

 

I have two tables - one called my_network:

 

$query = 'CREATE TABLE my_network (

my_networkID INT UNSIGNED NOT NULL AUTO_INCREMENT,

username VARCHAR(16) NOT NULL,

add_username VARCHAR(16) NOT NULL,

date_entered DATETIME NOT NULL,

PRIMARY KEY (my_networkID)

)';

 

and the other called preferences:

 

$query = 'CREATE TABLE preferences (

preferencesID INT UNISIGNED NOT NULL AUTO_INCREMENT,

username VARCHAR(16) NOT NULL,

send_email ENUM('Y','N') DEFAULT 'Y' NOT NULL,

mail_permissions tinytext NOT NULL,

mail_on_comments ENUM('Y','N') DEFAULT 'Y' NOT NULL,

mail_on_comments_on_comments ENUM('Y','N') DEFAULT 'Y' NOT NULL,

mail_on_add_to_my_network ENUM('Y','N') DEFAULT 'Y' NOT NULL,

mail_on_new_deepshare_by_my_network ENUM('Y','N') DEFAULT 'Y' NOT NULL,

date_entered DATETIME NOT NULL,

PRIMARY KEY (preferencesID),

UNIQUE KEY (username)

)';

 

What I am trying to do is look in preferences to see which of the users who have added the SESSION user to their my_network wants to be notified when the SESSION user adds a new entry (mail_on_new_deepshare_by_my_network = 'Y'), and then do a foreach loop on that array of usernames to mail each one a notification.

 

Does that make sense?

 

This is what I have so far ...

 

$query = "SELECT * FROM my_network LEFT JOIN preferences ON my_network.username = preferences.username WHERE add_username='$username'";

 

if ($r = mysql_query($query)) {

 

while ($row = mysql_fetch_array($r, MYSQL_ASSOC)) {

 

foreach ($row['add_username']) as

 

Any help much appreciated.

 

Cheers.

 

Julian

[ksugihara]

it looks like preferenceID and my_networkID are both autoincrement, and assuming that the only time one row will be inserted into one table is when it is also inserted into the other, you could pull the info out by that.

 

SELECT * FROM preferences WHERE my_network.id = $id AND preferences.preferenceID = $id

 

I think theres a more efficient way to do that, but Im not to savvy with join statements.

[chomedey]

That would be good if the two tables were only ever updated together, but unfortunately they are not.  Preferences is updated when the user actively manages their preferences, while my_network is updated whenever the user adds a person they are interested in to their network.  The two tables are completely distinct.

 

Thanks anyway.

 

Any other suggestions?

 

Cheers.

 

Julian

[ksugihara]

are there any tables that can be compared? you atleast need one. whether the username always be the same, or adding a field specifically for that manner, you need atleast one table where you can compare.

[chomedey]

The username is the same in both tables ...