How to show every single THANG?

tommy168 · February 22, 2011, 9:58pm

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
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

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\
\
This is to inform you that $query is updated, please go to this link to search for it:\\r\
\
http:/asdfasdfasdfasdf/view/View.php?LinkID=$LinkID \\r\
\
Regards,\\r\
\\";
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.";
}

}
}
?>

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

SpacePhoenix · February 22, 2011, 11:15pm

<?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 &lt;$email&gt;";
$subject = "The query is updated.";
$headers = "From: asdfasdfasdf";
$body = "Hello $fname,\\r\

This is to inform you that $query is updated, please go to this link to search for it:\r

http:/asdfasdfasdfasdf/view/View.php?LinkID=$LinkID \r

Regards,\r
\";
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.

tommy168 · February 23, 2011, 5:01pm

Hi SpacePhoenix thx for ur reply

so these r structures of the 3 tables i used

Table link_specificities:



CREATE TABLE `link_specificities` (
 `LS_ID` int(10) unsigned NOT NULL auto_increment,
 `LS_LINK` int(10) unsigned NOT NULL,
 `LS_STAGE` int(10) unsigned NOT NULL,
 `LS_ANATOMY_PATH` int(10) unsigned NOT NULL,
 `LS_CELL_TYPE` int(10) unsigned NOT NULL,
 `LS_AREA_EXCISION` int(10) unsigned default NULL,
 `LS_CELL_EXCISION` int(10) unsigned 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



CREATE TABLE `links` (
 `L_ID` int(10) unsigned NOT NULL auto_increment,
 `L_STATUS` tinyint(4) NOT NULL default '0' COMMENT '1: submiting new line stage\
2: displayed in public db\
3: waiting to be checked by admin',
 `L_CREATION_TIME` datetime NOT NULL,
 `L_MODIFY_TIME` datetime NOT NULL,
 `L_CREATOR_IP` int(10) NOT 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\
',
 `L_PHP_SESSION` varchar(32) NOT NULL COMMENT 'PHP session that created this link',
 `L_PROMOLOCUS` int(10) unsigned NOT NULL,
 `L_SPECIES` int(10) unsigned NOT NULL,
 `L_TRANS_TYPE` int(10) unsigned NOT NULL COMMENT 'transgene type',
 `L_TRANSGENE_INSERTION` varchar(100) NOT NULL default '1',
 `L_INDUCIBILITY` binary(1) NOT NULL default '0',
 `L_AUTHOR` int(10) unsigned default NULL,
 `L_IS_PUBLISHED` binary(1) NOT NULL default '0',
 `L_PUBLISH_LINK` int(10) unsigned default NULL,
 `L_MGI_ID` int(10) unsigned default NULL,
 `L_STOCK_NUMBER` varchar(100) default NULL,
 `L_VERIFIED` tinyint(4) NOT NULL default '0' COMMENT '0: Unverified by author\
1: Verified by author\
3: Mail Sent\
4: Declined',
 `L_AUTHOR_COMMENT` int(10) unsigned default NULL,
 `L_MODIFY` int(10) unsigned default NULL COMMENT 'ID link to replace',
 `L_pro_locus` varchar(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



CREATE TABLE `cell_type` (
 `CT_ID` int(10) unsigned NOT NULL auto_increment,
 `CT_NAME` varchar(100) NOT NULL,
 `CT_IS_PRIMARY` tinyint(3) unsigned NOT NULL,
 `CT_IS_MENU` tinyint(3) unsigned 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:



$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($get6, MYSQLI_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

SpacePhoenix · February 24, 2011, 2:05pm

Please note the query un-untested.

<?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\
\
This is to inform you that $query is updated, please go to this link to search for it:\\r\
\
http:/asdfasdfasdfasdf/view/View.php?LinkID=$LinkID \\r\
\
Regards,\\r\
\\";
    
    // 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 error, mail could not be sent.";
    }*/
}

?>