www.webdeveloper.com
Page 1 of 4 123 ... LastLast
Results 1 to 15 of 58

Thread: query not found

  1. #1
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197

    query not found

    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

    PHP Code:
    $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()); 
    Kevin

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,176
    Perhaps...
    Code:
    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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    I cannot do sub queries with my current web hosting company, how do I do this without a sub query
    Kevin

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,176
    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.)
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    this may sound dumb but I cannot figure out how to show the table as you discribed in your last post
    Kevin

  6. #6
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    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.


    PHP Code:
    $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()); 
    Kevin

  7. #7
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,176
    Quote Originally Posted by kproc
    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 Code:
    <?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>";
    ?>
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  8. #8
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    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

    PHP Code:
    CREATE TABLE `users` (
      `
    user_idint(25NOT NULL auto_increment,
      `
    first_namevarchar(25collate latin1_general_ci NOT NULL default '',
      `
    last_namevarchar(25collate latin1_general_ci NOT NULL default '',
      `
    DOBdate NOT NULL default '0000-00-00',
      `
    street_addressvarchar(25collate latin1_general_ci default '',
      `
    post_office_boxvarchar(25collate latin1_general_ci default '',
      `
    cityvarchar(25collate latin1_general_ci default '',
      `
    provincevarchar(25collate latin1_general_ci default '',
      `
    postalvarchar(25collate latin1_general_ci default '',
      `
    home_phonevarchar(25collate latin1_general_ci default '',
      `
    email_addressvarchar(255collate latin1_general_ci NOT NULL default '',
      `
    usernamevarchar(25collate latin1_general_ci NOT NULL default '',
      `
    passwordvarchar(255collate latin1_general_ci NOT NULL default '',
      `
    spousefirstnamevarchar(25collate latin1_general_ci NOT NULL default '',
      `
    spouselastnamevarchar(25collate latin1_general_ci NOT NULL default '',
      `
    spousedobdate NOT NULL default '0000-00-00',
      `
    spousesexvarchar(25collate latin1_general_ci default '',
      `
    spaceint(11NOT NULL default '5000000',
      `
    user_levelenum('0','1','2','3'collate latin1_general_ci NOT NULL default '0',
      `
    signup_datedatetime NOT NULL default '0000-00-00 00:00:00',
      `
    last_logindatetime NOT NULL default '0000-00-00 00:00:00',
      `
    new_logindatetime NOT NULL default '0000-00-00 00:00:00',
      `
    activatedenum('0','1'collate latin1_general_ci NOT NULL default '0',
      `
    spousemailyearvarchar(255collate latin1_general_ci NOT NULL default '0',
      `
    usermailyearvarchar(255collate latin1_general_ci NOT NULL default '0',
      `
    register_ipvarchar(255collate 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_idint(11NOT NULL auto_increment,
      `
    owner_idvarchar(30collate latin1_general_ci NOT NULL default '',
      `
    buddy_idvarchar(30collate latin1_general_ci NOT NULL default '',
      `
    statusvarchar(255collate latin1_general_ci default NULL,
      `
    relativevarchar(255collate latin1_general_ci default NULL,
      `
    mailyearvarchar(255collate latin1_general_ci NOT NULL default '',
      `
    addeddatetime 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 
    Kevin

  9. #9
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    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:
    PHP Code:
    QUERY "users"
    WHILE RETRIEVE "users"
      
    PROCESS "users"
      
    QUERY "buddylink"
      
    WHILE RETRIEVE "buddlink"
        
    PROCESS "buddylink"
      
    ENDWHILE "buddylink"
    ENDWHILE "users" 

  10. #10
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    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


    PHP Code:
    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());  


    }
      }
      }
      
    ?> 
    Kevin

  11. #11
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Question: Is loop 2 needed?

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

  12. #12
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Just trying to simply the script.... I think that this code:
    PHP 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.

  13. #13
    Join Date
    Mar 2006
    Location
    Canada
    Posts
    1,197
    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
    Kevin

  14. #14
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    You're right...

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

  15. #15
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Ok, but by the time we get to loop 3, we're down to one potential wife - or none... Right?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles