cant seem to get record in db

[runnerjp]

<?php $query = "SELECT fe.*, u.email, u.ID FROM forum_email as fe, users as u WHERE u.ID = fe.user_id AND fe.topic_id = " . $forumpostid . " AND fe.user_id != " . $id . " AND fe.mailed=1";
$res = @mysql_query($query) or die ("sendNotification query fails :".mysql_error());
echo $forumpostid; echo $id;
//nobody wants an email, stop now
if(mysql_num_rows($res)==0)
{
echo "no records returned";
        exit;
}?>

 

i have a record in

 

        forum_email

user_id topic_id mailed

1             484       1

 

but it keeps saying no reords returned...

 

this is the 1st time i have used a joining query hense an error is prob in there!

[Mchl]

Remove all conditions from WHERE part of your query except u.ID = fe.user_id, and see what you get. Then add conditions one by one, and see if they're working as you want them.

[bobbinsbro]

i've never used JOIN before either, but i believe you have to actually use the keyword for it to work... 

 

try:

$query = "SELECT fe.*, u.email, u.ID FROM forum_email as fe INNER JOIN users as u ON u.ID = fe.user_id WHERE fe.topic_id = " . $forumpostid . " AND fe.user_id != " . $id . " AND fe.mailed=1";

[Mchl]

Not really. MySQL optimizer translates INNER JOIN to syntax as in first post.

[runnerjp]

nice try bob but didnt work.. i got mail sent when i didnt use where so its in my where clause i take it

[Psycho]

You can also try echoing your query to the page to ensure all the variables are set properly. Are you positive that $forumpostid and $id have valid values?

[runnerjp]

ok i have sorted that part

$query = "SELECT fe.*, u.email, u.ID FROM forum_email as fe INNER JOIN users as u ON u.ID = fe.user_id WHERE fe.topic_id = " . $forumpostid . "  AND fe.mailed=1";

 

i was thinking why do i need to get fe.user_id != " . $id . "  when im getting all the users under that topic

 

its just this part now

 

<?php

while(?? = mysql_fetch_assoc($res) ) {
        //define the receiver of the email
        $to = ??
        //define the subject of the email
        $subject = 'Test email';
        //define the message to be sent. Each line should be separated with \n
        $message = "im sending an email too myself.";
        //define the headers we want passed. Note that they are separated with \r\n
        $headers = "From: n0144040@ntu.ac.uk\r\nReply-To: webmaster@example.com";
        //send the email
        $mail_sent = @mail( $to, $subject, $message, $headers );
        //if the message is sent successfully print "Mail sent". Otherwise print "Mail failed" 
        echo $mail_sent ? "Mail sent" : "Mail failed";
}?

 

if i was to send an email in the loop what would my while(?? = mysql_fetch_assoc($res) ) be ?? and how would i get the users emails from the query above??

 

the emails are stored in the user table under email

[runnerjp]

ok i think i would need to do this

 

<?php $query = "SELECT fe.*, u.email, u.ID FROM forum_email as fe INNER JOIN users as u ON u.ID = fe.user_id WHERE fe.topic_id = " . $forumpostid . "  AND fe.mailed=1";
$res = @mysql_query($query) or die ("sendNotification query fails :".mysql_error());
echo $forumpostid; echo $id;
//nobody wants an email, stop now
if(mysql_num_rows($res)==0)
{
echo "no records returned";
        exit;
}

// ADDED CODE TO SEND EMAIL MESSAGES
$subj = 'Forum Updated';
$from = ' [your return information ] ';
$text = ' [your message text] ';

while ($row = mysql_fetch_assoc($res)) {
        extract ($row);
        if (!mail($email, $subj, $text, $from)) {
                echo "<br />MAIL FAILED FOR $email \n";
        }
}?>

 

$email --- houw would i get each users email form the query?