SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast tommy168's Avatar
    Join Date
    Feb 2011
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to show every single THANG ??

    hay wut's happening forum?

    so i am currently working on a mailing list and its function is to send EVERY single user a notification email once an entry they are looking for on a database is available

    below is the mailing_list.php which sets up database connection and deals with the fields of the table:

    PHP Code:

    <?php
    include('view_function.php');
    global 
    $connect;
    $connect mysqli_connect("localhost""""") or die(mysqli_error($connect));
    $error mysqli_error($connect);
    $db_select mysqli_select_db($connect"");

    $select5 "SELECT LS_CELL_TYPE FROM link_specificities
    INNER JOIN links ON link_specificities.LS_LINK = links.L_ID
    WHERE links.L_ID = 
    $LinkID";
    $get5 mysqli_query($connect$select5) or die(mysqli_error($connect));
    while (
    $row5 mysqli_fetch_array($get5)){

    extract($row5);

    $cellType $row5['LS_CELL_TYPE'];

    }

    $select6 "SELECT CT_NAME FROM cell_type WHERE CT_ID = '$cellType'";
    $get6 mysqli_query($connect$select6) or die(mysqli_error($connect));

    $i 0;
    while (
    $row6 mysqli_fetch_array($get6)){

    extract($row6);

    $query[$i] = $row6['CT_NAME'];
    $check "select first_name, email from mailing_list WHERE query = '$query[$i]'";
    $i++;
    echo 
    $query;
    }
    update($check);

    ?>
    And below is the view_function.php that's included above:

    PHP Code:

    <?php

    function update($check){
    global 
    $query;
    global 
    $connect;
    global 
    $LinkID;

    $result mysqli_query($connect$check) or die(mysqli_error($connect));

    while (
    $row3 mysqli_fetch_array($result)){

    extract($row3);

    $fname $row3['first_name'];
    $email $row3['email'];

    $to "$fname <$email>";
    $subject "The query is updated.";
    $headers "From: asdfasdfasdf";
    $body "
    Hello 
    $fname,\r\n\nThis is to inform you that $query is updated, please go to this link to search for it:\r\n\nhttp:/asdfasdfasdfasdf/view/View.php?LinkID=$LinkID \r\n\nRegards,\r\n\";
    if(send_email(
    $to,$subject,$body,$headers)){
    //there are no errors, return empty array
    $errors = array();
    } else {
    $errors[] = "Server errormail could not be sent.";
    }

    }
    }
    ?>
    As you can c in mailing_list.php, i echoed out $query, but it only echoed ONE result of that entire array

    so wut i am trying to do is to make a while loop with a counter in it, but still only one result shows up

    Basically, i want the $query to output all items in an array so that following SELECT statement can choose the item which matches the required query and send the users the notification email bout such particular query

    How should i fix it in order to do just that?

    Thx

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    <?php

    function update($check){
    global $query;
    global $connect;
    global $LinkID;

    $result = mysqli_query($connect, $check) or die(mysqli_error($connect));

    $recipients=array();

    while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $recipients[] = $row;
    }

    foreach ( $recipients AS $recipient ) {
    echo "ID: {$recipient['id']}, Name: {$recipient['first_name']}";

    $fname = $recipient['first_name'];
    $email = $recipient['email'];

    $to = "$fname <$email>";
    $subject = "The query is updated.";
    $headers = "From: asdfasdfasdf";
    $body = "Hello $fname,\r\n\nThis is to inform you that $query is updated, please go to this link to search for it:\r\n\nhttp:/asdfasdfasdfasdf/view/View.php?LinkID=$LinkID \r\n\nRegards,\r\n\";
    if (send_email($to,$subject,$body,$headers)) {
    //there are no errors, return empty array
    $errors = array();
    } else {
    $errors[] = "Server error, mail could not be sent.";
    }
    }
    ?>

    Just looking at the three queries in mailing_list.php I reckon you should be able to get the required info in one hit using a join query, can you post the output of a SHOW CREATE TABLE query for each of the 3 tables.

    I think it may be possible to merge the two scripts into one with a single query to the database.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Enthusiast tommy168's Avatar
    Join Date
    Feb 2011
    Posts
    48
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi SpacePhoenix thx for ur reply

    so these r structures of the 3 tables i used

    Table link_specificities:

    PHP Code:

    CREATE TABLE 
    `link_specificities` (
     `
    LS_IDint(10unsigned NOT NULL auto_increment,
     `
    LS_LINKint(10unsigned NOT NULL,
     `
    LS_STAGEint(10unsigned NOT NULL,
     `
    LS_ANATOMY_PATHint(10unsigned NOT NULL,
     `
    LS_CELL_TYPEint(10unsigned NOT NULL,
     `
    LS_AREA_EXCISIONint(10unsigned default NULL,
     `
    LS_CELL_EXCISIONint(10unsigned default NULL,
     
    PRIMARY KEY  (`LS_ID`),
     
    KEY `fk_link_specificities_ana_stage1` (`LS_STAGE`),
     
    KEY `fk_link_specificities_cell_type1` (`LS_CELL_TYPE`),
     
    KEY `fk_link_specificities_excision1` (`LS_AREA_EXCISION`),
     
    KEY `fk_link_specificities_anad_part_of1` (`LS_ANATOMY_PATH`),
     
    KEY `fk_link_specificities_cell_excision1` (`LS_CELL_EXCISION`),
     
    KEY `fk_link_specificities_links1` (`LS_LINK`)
    ENGINE=MyISAM AUTO_INCREMENT=17515 DEFAULT CHARSET=latin1 

    Table links


    PHP Code:

    CREATE TABLE 
    `links` (
     `
    L_IDint(10unsigned NOT NULL auto_increment,
     `
    L_STATUStinyint(4NOT NULL default '0' COMMENT '1: submiting new line stage\n2: displayed in public db\n3: waiting to be checked by admin',
     `
    L_CREATION_TIMEdatetime NOT NULL,
     `
    L_MODIFY_TIMEdatetime NOT NULL,
     `
    L_CREATOR_IPint(10NOT NULL COMMENT 'use INET_ATON() to convert IP to integer value. Use INET_NTOA() to convert integer value to ip. INET_ATON(''192.168.10.50'') = 3232238130\n',
     `
    L_PHP_SESSIONvarchar(32NOT NULL COMMENT 'PHP session that created this link',
     `
    L_PROMOLOCUSint(10unsigned NOT NULL,
     `
    L_SPECIESint(10unsigned NOT NULL,
     `
    L_TRANS_TYPEint(10unsigned NOT NULL COMMENT 'transgene type',
     `
    L_TRANSGENE_INSERTIONvarchar(100NOT NULL default '1',
     `
    L_INDUCIBILITYbinary(1NOT NULL default '0',
     `
    L_AUTHORint(10unsigned default NULL,
     `
    L_IS_PUBLISHEDbinary(1NOT NULL default '0',
     `
    L_PUBLISH_LINKint(10unsigned default NULL,
     `
    L_MGI_IDint(10unsigned default NULL,
     `
    L_STOCK_NUMBERvarchar(100) default NULL,
     `
    L_VERIFIEDtinyint(4NOT NULL default '0' COMMENT '0: Unverified by author\n1: Verified by author\n3: Mail Sent\n4: Declined',
     `
    L_AUTHOR_COMMENTint(10unsigned default NULL,
     `
    L_MODIFYint(10unsigned default NULL COMMENT 'ID link to replace',
     `
    L_pro_locusvarchar(260) default NULL,
     
    PRIMARY KEY  (`L_ID`),
     
    UNIQUE KEY `in_session_unq` (`L_PHP_SESSION`),
     
    KEY `fk_links_promoter_locus` (`L_PROMOLOCUS`),
     
    KEY `fk_links_species1` (`L_SPECIES`),
     
    KEY `fk_links_transgene_type1` (`L_TRANS_TYPE`),
     
    KEY `fk_links_Users1` (`L_AUTHOR`),
     
    KEY `fk_links_comments1` (`L_AUTHOR_COMMENT`)
    ENGINE=MyISAM AUTO_INCREMENT=35664 DEFAULT CHARSET=latin1 

    Table cell_type

    PHP Code:

    CREATE TABLE 
    `cell_type` (
     `
    CT_IDint(10unsigned NOT NULL auto_increment,
     `
    CT_NAMEvarchar(100NOT NULL,
     `
    CT_IS_PRIMARYtinyint(3unsigned NOT NULL,
     `
    CT_IS_MENUtinyint(3unsigned NOT NULL,
     
    PRIMARY KEY  (`CT_ID`),
     
    UNIQUE KEY `CT_NAME_in` (`CT_NAME`)
    ENGINE=MyISAM AUTO_INCREMENT=1297 DEFAULT CHARSET=latin1 

    Plz gimme ur feedback bout any changes of the SQL command at the beginning of mailing_list.php

    Speaking of mailing_list.php, i have changed the while loop based on ur suggestion like this:

    PHP Code:

    $select5 
    "SELECT LS_CELL_TYPE FROM link_specificities
    INNER JOIN links ON link_specificities.LS_LINK = links.L_ID
    WHERE links.L_ID = 
    $LinkID";
    $get5 mysqli_query($connect$select5) or die(mysqli_error($connect));
    while (
    $row5 mysqli_fetch_array($get5)){

    extract($row5);

    $cellType $row5['LS_CELL_TYPE'];

    }

    $select6 "SELECT CT_NAME FROM cell_type WHERE CT_ID = '$cellType'";
    $get6 mysqli_query($connect$select6) or die(mysqli_error($connect));

    $query = array();

    while (
    $row6 mysqli_fetch_array($get6MYSQLI_ASSOC)){

    $query[] = $row6;
    echo 
    $query;
    }

    foreach(
    $show AS $show){
    $check "select first_name, email from mailing_list WHERE query = '$query'";

    }
    update($check); 

    Note that i echoed out $query, but the result is "array" and nothing inside the array is shown. then the next error displays Warning: Invalid argument supplied for foreach()


    I am a noob in making proper loops, so ur help is greatly appreciated

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 Post(s)
    Tagged
    0 Thread(s)
    Please note the query un-untested.

    PHP Code:
    <?php

    // Connect to MySQL and select database
    global $connect;
    $connect mysqli_connect("localhost""""") or die(mysqli_error($connect));
    $error mysqli_error($connect);
    $db_select mysqli_select_db($connect"");

    $sql="
        SELECT
              mailing_list.first_name
            , mailing_list.email
        FROM
            links
        INNER JOIN
            links
                ON
                    links.L_ID = link_specificities.LS_LINK
            
            link_specificities
        INNER JOIN
            cell_type
                ON
                    link_specificities.LS_CELL_TYPE = cell_type.CT_ID
        INNER JOIN
            mail_list
                ON
                    cell_type.CT_NAME = mailing_list.query
        WHERE
            links.L_ID = 
    $LinkID
    "
    ;

    $result mysqli_query($connect$sql) or die(mysqli_error($connect));

    while (
    $row mysqli_fetch_array($result)) {
        
        
    $fname $row['first_name'];
        
    $email $row['email'];
        
    $to "$fname <$email>";
        
    $subject "The query is updated.";
        
    $headers "From: asdfasdfasdf";
        
    $body "Hello $fname,\r\n\nThis is to inform you that $query is updated, please go to this link to search for it:\r\n\nhttp:/asdfasdfasdfasdf/view/View.php?LinkID=$LinkID \r\n\nRegards,\r\n\";
        
        // Test query is returning name and email correctly
        echo "
    Name$fname email$email";
        /*
        if (send_email(
    $to,$subject,$body,$headers)) {
            //there are no errors, return empty array
            
    $errors = array();
        } else {
            
    $errors[] = "Server errormail could not be sent.";
        }*/
    }

    ?>
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Bookmarks

Posting Permissions

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