Jump to content

Join and foreach loop


chomedey

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/195482-join-and-foreach-loop/
Share on other sites

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.

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

Archived

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

×
×
  • 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.