query not found

[kproc]

Hi below is a query that gets the user info from table user. I'm trying to change it so that if the user Id is found in the table buddylink field owner_id then do not include the record.

I'm sure I'm missing something simple

thank you for any help

$get_owner_information = mysql_query("SELECT * FROM users U, buddylink B WHERE U.user_id = '$owner_id' AND B.owner_id != '$owner'")or die("Error: ".mysql_error()); 

[NogDog]

Perhaps...

SELECT * FROM users WHERE user_id NOT IN (SELECT owner_id FROM buddylink)

Note: this requires MySQL 4.1 or later so that sub-queries can be uses.

[kproc]

I cannot do sub queries with my current web hosting company, how do I do this without a sub query

[NogDog]

Can you provide a bit more info on the two tables involved and how they are related? (You could do a SHOW CREATE TABLE table_name on each table and copy-and-paste the results here, then let us know which columns establish any relations between the tables.)

[kproc]

this may sound dumb but I cannot figure out how to show the table as you discribed in your last post

[kproc]

The below query is what I have this far..

I want to only display users if their user_id is not found in the the table buddylink (field - owner_id) and they don't having a matching buddylink in the column buddy_id which matches the value spouse_id.

$get_owner_information = mysql_query("SELECT * FROM users U, buddylink B WHERE user_id = '$owner_id' AND B.owner_id != '$owner_id' AND B.buddy_id != '$spouse_id'")or die("Error: ".mysql_error()); 

[NogDog]

this may sound dumb but I cannot figure out how to show the table as you discribed in your last post

You could do it in a PHP script as:

<?php
// supply applicable values for connection and DB select:
$connx = mysql_connect('localhost', 'username', 'password') or die("Connx failed: ".mysql_error());
mysql_select_db('database_name') or die("Select DB failed. ".mysql_error());
// get create spec for each table:
$sql = "SHOW CREATE TABLE `users`";
$result = mysql_query($sql) or die(mysql_error()."<br>Query: $sql");
$row = mysql_fetch_array($result);
echo "<pre>".print_r($row[1], TRUE)."</pre>";

$sql = "SHOW CREATE TABLE `buddylink`";
$result = mysql_query($sql) or die(mysql_error()."<br>Query: $sql");
$row = mysql_fetch_array($result);
echo "<pre>".print_r($row[1], TRUE)."</pre>";
?>

[kproc]

Below are the two tables

The user_id from users is put in buddylink columns owner_id and buddy_id

The query that I'm trying to create is if user_id is found in B.owner_id and the variable value seaching B.buddy_id is found then don't get that user. I hope that this makes sence

CREATE TABLE `users` (
  `user_id` int(25) NOT NULL auto_increment,
  `first_name` varchar(25) collate latin1_general_ci NOT NULL default '',
  `last_name` varchar(25) collate latin1_general_ci NOT NULL default '',
  `DOB` date NOT NULL default '0000-00-00',
  `street_address` varchar(25) collate latin1_general_ci default '',
  `post_office_box` varchar(25) collate latin1_general_ci default '',
  `city` varchar(25) collate latin1_general_ci default '',
  `province` varchar(25) collate latin1_general_ci default '',
  `postal` varchar(25) collate latin1_general_ci default '',
  `home_phone` varchar(25) collate latin1_general_ci default '',
  `email_address` varchar(255) collate latin1_general_ci NOT NULL default '',
  `username` varchar(25) collate latin1_general_ci NOT NULL default '',
  `password` varchar(255) collate latin1_general_ci NOT NULL default '',
  `spousefirstname` varchar(25) collate latin1_general_ci NOT NULL default '',
  `spouselastname` varchar(25) collate latin1_general_ci NOT NULL default '',
  `spousedob` date NOT NULL default '0000-00-00',
  `spousesex` varchar(25) collate latin1_general_ci default '',
  `space` int(11) NOT NULL default '5000000',
  `user_level` enum('0','1','2','3') collate latin1_general_ci NOT NULL default '0',
  `signup_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `new_login` datetime NOT NULL default '0000-00-00 00:00:00',
  `activated` enum('0','1') collate latin1_general_ci NOT NULL default '0',
  `spousemailyear` varchar(255) collate latin1_general_ci NOT NULL default '0',
  `usermailyear` varchar(255) collate latin1_general_ci NOT NULL default '0',
  `register_ip` varchar(255) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=64 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

CREATE TABLE `buddylink` (
  `buddylink_id` int(11) NOT NULL auto_increment,
  `owner_id` varchar(30) collate latin1_general_ci NOT NULL default '',
  `buddy_id` varchar(30) collate latin1_general_ci NOT NULL default '',
  `status` varchar(255) collate latin1_general_ci default NULL,
  `relative` varchar(255) collate latin1_general_ci default NULL,
  `mailyear` varchar(255) collate latin1_general_ci NOT NULL default '',
  `added` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`buddylink_id`)
) ENGINE=MyISAM AUTO_INCREMENT=76 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci 

[NightShift58]

I'm not sure you need data from both tables at the same time. When I look at the query examples you've been posting, it seems that your scrpt already has the user data.

If your hosting arrangement doesn't allow you to perform subqueries, your best bet - and possibly more efficient - way to do this would be to nest two PHP WHILE loops. The outer loop would pull data from the "users" table and the inner loop would pull the data from "buddylink". In pseudo-code:

QUERY "users"
WHILE RETRIEVE "users"
  PROCESS "users"
  QUERY "buddylink"
  WHILE RETRIEVE "buddlink"
    PROCESS "buddylink"
  ENDWHILE "buddylink"
ENDWHILE "users" 

[kproc]

Below is the code that I'm working with it has many while statements in it.

The idea is if the user added their spouse to their buddy list then I don't want them to display in the table.

as noted above the user_id is the common theme in both tables. the owner_id would be the user_id and and the buddy id woudl be the spouse id.

thank you for all the help. the below will return the ids but will not display the email address for all that I'm echoing $a

while($row_get_spousedob = mysql_fetch_array($get_spousedob)){ //Loop 1
  
  
  //get values needed for querys to check if spouses are registered member
  
  $owner_id = $row_get_spousedob['user_id'];
  $spouse_fname = $row_get_spousedob['spousefirstname'];
  $spouse_lname = $row_get_spousedob['spouselastname'];
  $spouse_dob = $row_get_spousedob['spousedob'];
  
   echo $owner_id;

  $bday = explode("-",$row_get_spousedob['spousedob']);
  $create_bday = date(Y).'-'.$bday[1].'-'.$bday[2]; 
  
  //query to check is spouse is registered member and get their user_id
 $get_spouse_user_id = mysql_query("SELECT user_id, first_name, last_name, DOB FROM users WHERE last_name = '$spouse_lname' AND first_name = '$spouse_fname' AND DOB = '$spouse_dob'")or die("Error: ".mysql_error());
  
  while($spouse_id_row = mysql_fetch_assoc($get_spouse_user_id)){ //loop 2
  
  //spouses user_id from table users
  $spouse_id = $spouse_id_row['user_id'];
 
 // get spouses birthday and create variables to us in email to send to users
  $get_day = $bday[2];
  $full_month = date("F",mktime(0,0,0,substr($row_get_spousedob['spousedob'],5,2))); 
  $week_day = date("l",mktime(0,0,0,0,$get_day));
  $day = date("j",mktime(0,0,0,0,$get_day));

  $days_to = (strtotime($create_bday) - strtotime(date("Y-m-d"))) / (60 * 60 * 24);

//
$get_owner_information = mysql_query("SELECT * FROM users WHERE user_id = '$owner_id'")or die("Error: ".mysql_error()); 

 while($row_spouse=mysql_fetch_assoc($get_owner_information)){ //loop 3
 
$a = $row_spouse['email_address'];

// check is person add spouse to buddy list
$spouse_check_buddy_table = mysql_query("SELECT owner_id, buddy_id FROM buddylink WHERE owner_id = '$owner_id' AND buddy_id = '$spouse_id'")or die("Error: ".mysql_error()); 


$buddy_matches=mysql_num_rows($spouse_check_buddy_table);

echo $buddy_matches;

//////
echo $a.'<br />';
echo $spouse_id.'<br />';
echo $owner_id;

}
 
 // end loop 3

// start of code to send email
  if(isset($_POST['spousebdayEmail'])) {
  
  //create email reminder
   $subject = $row_get_spousedob['spousefirstname'].' '.$row_get_spousedob['spouselastname'].' '. "birthday ALERT ";
  
  $message = $row_get_spousedob['spousefirstname'].' '.$row_get_spousedob['spouselastname']." Birthday is on ".$week_day.', '. $full_month.' '.$day." which is " .$days_to. " daysfrom the date this email was sent.
  
Login to www.familyclick.ca to see if ".$row_get_spousedob['spousefirstname'].' '.$row_get_spousedob['spouselastname']. " has completed a gift wish list.  If they are a registered member there is a good chance their updated contact information is available.
  
This reminder is courtesy of Family Click
  
Please don't respond to this message";

   $x = 1;
   $hold = 50; // quantity of emails sent before 3 sec delay
      
   $email_count=mysql_num_rows($get_owner_information);
  
 
   while ($sendemail = mysql_fetch_array($get_owner_information)) {
   $email = $sendemail['email_address'];
   mail($email, $subject,
   $message, "From:FamilyClick.ca Reminders <webmaster@familyclick.ca>");

   $x++;
    if($x == $hold) { // When $x is equal to $hold, a 3 sec delay will occur avoiding php to timeout
    sleep(3);
    $x = 0;
    
   } // end of while loop
   
  
  $mailyear = date(Y);
  
  mysql_query("UPDATE users SET spousemailyear='$mailyear' WHERE spouse_id = '$spouse_id'") 
or die(mysql_error());  


}
  }
  }
  
?> 

[NightShift58]

Question: Is loop 2 needed?

In other words, can a user enter more than one spuse in the application?

[NightShift58]

Just trying to simply the script.... I think that this code:

//query to check is spouse is registered member and get their user_id
$get_spouse_user_id = mysql_query("
SELECT user_id, first_name, last_name, DOB
FROM users
WHERE last_name = '$spouse_lname'
AND first_name = '$spouse_fname'
AND DOB = '$spouse_dob'")
or die("Error: ".mysql_error()); 

needs to be LIMITed to 1, based on the fact that in the user record, one can only store 1 spouse. Being so, the following loop shoud be simplified to an inline construct rather than a loop.

[kproc]

no but there could be more then on spouse displayed, in the table. the table shows spouses birthdays coming with in two days. the second query gets the spouses user_id from the table users. the id is needed to query table buddylink to see if they have been added to the buddy list

[NightShift58]

You're right...

There could be more than one DAYsy SHIFT, but not all must be married to NIGHT SHIFT. You caught me...

[NightShift58]

Ok, but by the time we get to loop 3, we're down to one potential wife - or none... Right?